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.