MySQL Bugs: #77013: find_closing_quote makes .execute fail with HY000 when a SQL comment has a quote

Bug #77013	find_closing_quote makes .execute fail with HY000 when a SQL comment has a quote
Submitted:	12 May 2015 10:28	Modified:	7 Aug 2024 3:16
Reporter:	Georg Bauhaus	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.3.4	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The statement parsing in driver/parse.c skips all text after it sees a quote (') in an SQL comment which has no matching counterpart, like in an English word in the possessive case (e.g. "-- Miller's column").

When the driver parses a SQL statement in parse() of driver/parse.c, it calls tokenize(), which calls find_closing_quote(). Now consider a statement spanning multiple lines of text, like this 2-line command:

-- add the new object's identity
INSERT INTO Example (id) VALUES (?)

The effect when calling .execute is this:
'The SQL contains 0 parameter markers, but 1 parameters were supplied

An ODBC trace shows that SQLNumParams yields a SMALLINT 0.

The reason seems to be that the parsing process just skips to the end of the statement's second line, thus skipping the following text entirely (which is valid SQL, also valid MySQL AFAIK).
There is an assertion in tokenize() that might have drawn attention to the matter, right after the call to find_closing_quote(), but it is commented

      parser->query->last_char= find_closing_quote(parser);
      /*assert(parser->last_char!=NULL); /* no closing quote? */

These calls would explain why using an even number of quote characters does work, but using an odd number of quotes in a comment generates failure.

The issue stays the same when using '#' for SQL commenting, or /* */.

How to repeat:
CREATE TABLE Example (id INTEGER)

Then, using some ODBC client, make it issue the following calls. I'm using pyodbc as an example:

import pyodbc

my_cmd = """-- add the new object's identity
INSERT INTO Example (id) VALUES (?)
"""

conn = pyodbc.connect("DSN=Abcdef")
thing_named_cursor = conn.cursor()

thing_named_cursor.execute(my_cmd, (123, ))

conn.commit()
conn.close()

Suggested fix:
Ad hoc:
When parsing SQL for matching quotes in driver/parse.c, turn the state machine off while parsing comments.

Hello  Georg Bauhaus,

Thank you for the bug report.
I tried to reproduce the issue at my end using single quote string values but not seeing any issues with data inserting.
Could you please list out exact steps you tried out at your end, this would help us to reproduce the issue at our end.

Thanks,
Chiranjeevi.

I have repeated all steps from installing the connector to reproducing
the issue once more on a freshly installed system. Just so I understand
correctly, I take it you meant a single quote in the programming language
string containing a SQL statement, not a quote in some SQL "string" value?

1.
The connector was dowloaded from the mysql download site at dev.mysql.com,
version 5.3.4 for Debian, mysql-connector-odbc-5.3.4-linux-debian6.0-x86-32bit.tar.gz. 
(A mac installation uses the same version.)

2.
Installation as per the instructions, that is, libraries libmyodbc5?.so
were copied into /usr/local/lib. unixodbc is installed.

3.
The Unicode ODBC driver has been installed into /etc/odbcinst.
A data source Abcdef has been installed in /etc/odbc.ini.

4.
I can connect via this ODBC data source Abcdef just fine.
isql produces a prompt and I can request data from tables
of the database. The other end is a MySQL 5.5 server.

Access also works using Python and pyodbc,
using the ODBC connector and the same data source.

5.
The steps leading to the error, as per Unix script(1):

georg@debian:~$ python
Python 2.7.9 (default, Mar  1 2015, 18:22:53) 
[GCC 4.9.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> conn = pyodbc.connect("DSN=Abcdef")
>>> c = conn.cursor()
>>> cmd = """-- add new item's id
... INSERT INTO Example (id) VALUES (?)
... """
>>> c.execute(cmd, (123, ))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')
>>> 
georg@debian:~$ exit
exit

Script done on Wed 13 May 2015 08:33:25 PM CEST

Hello  Georg Bauhaus,

Thank you for your feedback.
Thank you for providing requested details, I'm still not seeing any issues while inserting data with single quoted strings. Please see test details from my environment.

Thanks,
Chiranjeevi.

test results

mysql> use test
Database changed
mysql> select * from a;
+----+----------+
| id | txt      |
+----+----------+
|  1 | chiran   |
|  2 | chiran's |
|  3 | Miller's |
+----+----------+
3 rows in set (0.10 sec)

mysql>

Hello Chiranjeevi Battula,

I need to emphasize that you seem misunderstand:
The issue is not a quote in an SQL-"string" as you seem
to be assuming. Not at all.

The issue is about quotes in SQL *comments* that confuse the ODBC layer.
It is about SQL-Text at the programming language level,
passed to the ODBC layer to be handled by the MySQL driver,
not about SQL-"Strings": not about VARCHAR, not about CHAR.

There is no "string" column whatever in the complete table definition,
repeated here once more, from the "How to repeat" section of the original report:

CREATE TABLE Example (id INTEGER)

(Note that the Example table has nothing but a single INTEGER,
and no other SQL datatypes are used.)

To illustrate further, the statement, again, is one, single SQL statement
that spans two (2) lines of text. It has one quote, but do note where the quote is!
The quote character is not in any VARCHAR, as you seem
to have been assuming. The quote is part of an SQL *comment*.
The comment is the first line of the single statement, the line that starts with "-- ",
and this comment has the quote, right after the word "object" of the comment,
in -> "object's" <-:

-- add the new object's identity
INSERT INTO Example (id) VALUES (?)

The quote is the 22nd character of this statment.
Written with line numbers, the single SQL statement has two lines of text:

line 1: -- add the new object's identity
line 2: INSERT INTO Example (id) VALUES (?)

line 1 is the culprit, it has the quote that triggers the issue when
passing the entire SQL text in one piece to the ODBC layer.

If, for example, the SQL statement text were instead written in a C
program for ODBC, It'd be written like this:

char* query =
  "-- add the new object's identity\n"   /* note the quote on this "line" */
  "INSERT INTO Example (id) VALUES (?)\n";   /* watch out: One (1) C-string, but no (0) SQL-"string"! */

So, 1 C-string, 0 SQL-"strings" involved. But! One quote in the *SQL-comment* part.
Similarly, in the original test case,
there was 1 Python-string, 0 SQL-"strings". It is repeated here:

my_cmd = """-- add the new object's identity
INSERT INTO Example (id) VALUES (?)
"""

In case you don't read Python: the Python-Variable my_cmd is now one Python-string,
comprising all text from  after the first """ to before the second """.
The variable my_cmd will be passed to the ODBC layer. If printed, it will look like this:

"-- add the new object's identity\nINSERT INTO Example (id) VALUES (?)\n"

Note the newline characters, and note the single quote in the comment part.
This is how this programming language string gets passed to the ODBC layer.

The ODBC layer is confused by the presence of a quote in a comment.

Here is a test case that recreates the issue using VBScript on Windows:

' start of code
Option Explicit
Dim con
Set con = CreateObject("ADODB.Connection")
con.Open _
        "Driver={MySQL ODBC 5.3 Unicode Driver};" & _
        "Server=localhost;Port=3307;" & _
        "User=root;Password=whatever;" & _
        "Database=test;"
con.Execute _
        "DROP PROCEDURE IF EXISTS hypot"
con.Execute _
        "CREATE PROCEDURE hypot ()" & vbCrLf & _
        "BEGIN " & vbCrLf & _
        "    -- Gord's comment " & vbCrLf & _
        "    PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypot'; " & vbCrLf & _
        "    SET @a = 3; " & vbCrLf & _
        "    SET @b = 4; " & vbCrLf & _
        "    EXECUTE stmt1 USING @a, @b; " & vbCrLf & _
        "    DEALLOCATE PREPARE stmt1; " & vbCrLf & _
        "END " & vbCrLf & _
        ""
        con.Close
' end of code

The above code fails with ...

C:\__tmp\bug77013.vbs(12, 1) Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.13]SQLBindParameter not used for all parameters

... but if I change the line ...

        "    -- Gord's comment " & vbCrLf & _

... to ...

        "    -- Gord''s comment " & vbCrLf & _

... then the code runs fine and the stored procedure is created.

Hello Georg Bauhaus,

Thank you for your feedback.
Verified this behavior on Visual Studio 2013 (VB.Net) with  MySQL Connector / ODBC 5.3.4.

Thanks,
Chiranjeevi.

Posted by developer:
 
After parser block was re-worked in the ODBC Driver version 8.0 the issue is no longer repeatable.
The test cases pass without problems with any number of single and double quotes in the comment etc.