MySQL Bugs: #27625: Parser is confused by quotes in comments

Bug #27625	Parser is confused by quotes in comments
Submitted:	3 Apr 2007 19:48	Modified:	20 Feb 2013 22:44
Reporter:	Raphael Hertzog	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S3 (Non-critical)
Version:	4.004	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I'm forwarding a bug reported to the Debian bug tracking system.
http://bugs.debian.org/311040

The SQL parser is apparently confused by single quotes in comments.

Example of problematic SQL query:

  -- 'Tis the quote that confuses DBI::MySQL
  SELECT ?

DBD::MySQL will miss the "?", and assume there's no parameter to bind.
Note that the syntax is valid, and the MySQL server has no problem
parsing it (if you replace "?" with something else).

How to repeat:
$ perl -MDBI -lwe 'print DBI->connect("DBI:mysql:mysql_read_default_file=~/.my.cnf")->selectrow_array("-- \x27 \n SELECT ?", undef, 42)'
  DBD::mysql::db selectrow_array failed: called with 1 bind variables when 0 are needed at -e line 1.

Thank you for a bug report. Verified just as described:

openxs@suse:~/dbs/5.0> perl -MDBI -lwe 'print
> DBI->connect("DBI:mysql:mysql_read_default_file=~/.my.cnf")->selectrow_array(
"--\x27 \n SELECT ?", undef, 42)'
DBD::mysql::db selectrow_array failed: called with 1 bind variables when 0 are n
eeded at -e line 1.
Use of uninitialized value in print at -e line 1.

openxs@suse:~/dbs/5.0> perl -MDBI -lwe 'print
DBI->connect("DBI:mysql:mysql_read_default_file=~/.my.cnf")->selectrow_array("SELECT ?", undef, 42)'
42

Here's a failing testcase for this issue along with a proposed patch that skips comments altogether while parsing the SQL syntax for placeholders. Please consider including something like this.

Cheers,
-- 
Niko Tyni (Debian Perl Group)
ntyni@iki.fi

Failing testcase

Attachment: 0001-Add-a-failing-testcase-for-http-bugs.debian.org-31.patch (text/x-patch), 1.59 KiB.

Proposed patch

Attachment: 0002-Skip-comments-when-parsing-the-SQL-syntax.patch (text/x-patch), 1.02 KiB.

This patch will not fix the problem, because the parser is also thrown off if there is a quote character embedded in a string literal. For example, this code will also cause this error:

my $sth = $dbh->prepare(my $q = "SELECT 'aa\\'bb' WHERE a = ?");
$sth->execute(1);

Right, it's indeed not enough. Here's a new try along with a few more tests.

New try at a proposed patch

Attachment: 27625-new.patch (text/x-patch), 2.93 KiB.

The patch is insufficient because MySQL also allows comments started with '#'. So, something like:
SELECT
# 'bad
?
will fail.

Thank you for the report.

We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql

I re-submitted your report to https://rt.cpan.org/Public/Bug/Display.html?id=83494
Please subscribe to the new report on CPAN and work with DBD::mysql developers in case if they need additional details.