Bug #30033 for DBD-mysql: Quote marks in comments can break bind parameters

Tue Oct 16 11:23:02 2007 ovid [...] cpan.org - Ticket created

Subject:

Quote marks in comments can break bind parameters

The following program produces the error: DBD::mysql::db selectrow_arrayref failed: called with 1 bind variables when 0 are needed at mysqlbug.pl line 15. DBD::mysql::db selectrow_arrayref failed: called with 1 bind variables when 0 are needed at mysqlbug.pl line 15 The SQL is legal in MySQL (tested on both 5.0.38 and 4.0.18), but fails in DBD::mysql 3.008 and 4.005. Removing the single quote mark in the SQL comment or having an even number of quote marks fixes the problem. Cheers, Ovid #!/usr/bin/perl use strict; use warnings; use DBI; my $dsn = "DBI:mysql:database=mydatabase;host=localhost"; my $dbh = DBI->connect( $dsn, qw/user pass/, { RaiseError => 1 } ) or die $DBI::errstr; $dbh->do('DROP TABLE IF EXISTS buggy'); $dbh->do('CREATE TABLE buggy ( id int(3) )'); $dbh->do('INSERT INTO buggy (id) VALUES (1)'); $dbh->selectrow_arrayref(<<'END', {}, 1); SELECT id FROM buggy -- it's a bug! WHERE id = ? END

Sun Nov 04 07:07:11 2007 CAPTTOFU [...] cpan.org - Taken

Tue Dec 11 13:25:54 2007 CAPTTOFU [...] cpan.org - Correspondence added

The problem is the single quote in your statement in "It's". The driver parses the statement, it sees the single quote in "It's", which it skips when parsing the statement - tring parse out the place-holders (the '?' char). I could fix this, but it would add more statement parsing overhead for something that's taken 12 years to stumble upon ;). If you really must have a comment in your statement that includes a single quote, have the server parse the statement, using server-side prepare. You can do this by doing: $dbh->{mysql_server_prepare} = 1; query.... $dbh->{mysql_server_prepare} = 0; or $dbh = DBI->connect("DBI:mysql:database=mydb;host=host;mysql_server_prepare=1"... Server side prepare is turned off in the driver by default because of some issues with libmysql, but works just fine by turning it on.

Tue Dec 11 13:25:57 2007 The RT System itself - Status changed from 'new' to 'open'

Tue Dec 11 13:26:00 2007 CAPTTOFU [...] cpan.org - Status changed from 'open' to 'rejected'

Tue Dec 11 13:31:05 2007 CAPTTOFU [...] cpan.org - Correspondence added

On Tue Dec 11 13:25:54 2007, CAPTTOFU wrote: Show quoted text

> The problem is the single quote in your statement in "It's". > > The driver parses the statement, it sees the single quote in "It's", > which it skips when parsing the statement - tring parse out the > place-holders (the '?' char). I could fix this, but it would add more > statement parsing overhead for something that's taken 12 years to > stumble upon ;). > > If you really must have a comment in your statement that includes a > single quote, have the server parse the statement, using server-side > prepare. You can do this by doing: > > $dbh->{mysql_server_prepare} = 1; > query.... > $dbh->{mysql_server_prepare} = 0; > > or > > $dbh = > DBI->connect("DBI:mysql:database=mydb;host=host;mysql_server_prepare=1"... > > Server side prepare is turned off in the driver by default because of > some issues with libmysql, but works just fine by turning it on. > >

BTW: use strict; use warnings; use Data::Dumper; use Getopt::Long; use DBI; our $opt_prepared; GetOptions ( "p|prepared" => \$opt_prepared ); my $dsn = "DBI:mysql:database=test;host=localhost"; $dsn .= ";mysql_server_prepare=1" if $opt_prepared; my $dbh = DBI->connect( $dsn, 'foo', 'fee', { RaiseError => 1 } ) or die $DBI::errstr; unlink('./bug3033.trace.log'); $dbh->trace(4, './bug3033_trace.log'); $dbh->do('DROP TABLE IF EXISTS buggy'); $dbh->do('CREATE TABLE buggy ( id int(3) )'); $dbh->do('INSERT INTO buggy (id) VALUES (1)'); my $query= "SELECT id FROM -- It's a bug buggy WHERE id = ?"; print "with var:\n"; my $ref= $dbh->selectall_arrayref($query, {}, 1); print Dumper $ref; print "with string terminator:\n"; $ref= $dbh->selectall_arrayref(<<END, {}, 1); SELECT id FROM buggy -- It's a bug! WHERE id = ? END print Dumper $ref;

Tue Dec 11 13:31:09 2007 The RT System itself - Status changed from 'rejected' to 'open'

Sun Jan 13 20:44:28 2008 hiratara [...] cpan.org - Correspondence added

I found that it happens when I use a following SQL, too. Is it same problem?? I am embarrassed due to this problem. #!/usr/bin/perl use strict; use warnings; use DBI; my $dsn = "DBI:mysql:database=test;host=localhost"; my $dbh = DBI->connect( $dsn, qw/test test/, { RaiseError => 1 } ) or die $DBI::errstr; $dbh->do('DROP TABLE IF EXISTS buggy'); $dbh->do('CREATE TABLE buggy ( id int(3) )'); $dbh->do('INSERT INTO buggy (id) VALUES (1)'); $dbh->selectrow_arrayref(sprintf(<<'END', $dbh->quote(q/It's a bug!/)), {}, 1); SELECT id FROM buggy WHERE id = %s or id = ? END In short, the created SQL is this: SELECT id FROM buggy WHERE id = 'It\'s a bug!' or id = ?

Mon Jan 14 15:19:11 2008 curtis_ovid_poe [...] yahoo.com - Correspondence added

Subject:	Re: [rt.cpan.org #30033] Quote marks in comments can break bind parameters
Date:	Mon, 14 Jan 2008 20:17:54 +0000
To:	bug-DBD-mysql [...] rt.cpan.org
From:	Ovid <curtis_ovid_poe [...] yahoo.com>

It does indeed look like it might be the same problem. Now there are two ways of getting to it. The author's not inclined to fix the problem, but if you file this in that bug report, it might be more incentive for him to do something about this. In your case, you can probably get around this problem with a bind param, but it would be nice for the underlying problem to be fixed. Cheers, Ovid On 14 Jan 2008, at 01:44, Masahiro Honma via RT wrote: Show quoted text

> > <URL: http://rt.cpan.org/Ticket/Display.html?id=30033 > > > I found that it happens when I use a following SQL, too. Is it same > problem?? > I am embarrassed due to this problem. > > > #!/usr/bin/perl > > use strict; > use warnings; > > use DBI; > > my $dsn = "DBI:mysql:database=test;host=localhost"; > my $dbh = DBI->connect( $dsn, qw/test test/, { RaiseError => 1 } ) > or die $DBI::errstr; > > $dbh->do('DROP TABLE IF EXISTS buggy'); > $dbh->do('CREATE TABLE buggy ( id int(3) )'); > $dbh->do('INSERT INTO buggy (id) VALUES (1)'); > $dbh->selectrow_arrayref(sprintf(<<'END', $dbh->quote(q/It's a > bug!/)), {}, 1); > SELECT id > FROM buggy > WHERE id = %s or id = ? > END > > > > > In short, the created SQL is this: > SELECT id > FROM buggy > WHERE id = 'It\'s a bug!' or id = ? > >

Sat Apr 10 13:11:58 2010 CAPTTOFU [...] cpan.org - Correspondence added

Fixed in latest SVN commit. Added logic for both -- and /* */ style comments.

Sat Apr 10 13:11:59 2010 CAPTTOFU [...] cpan.org - Status changed from 'open' to 'resolved'

Wed Apr 14 15:36:19 2010 CAPTTOFU [...] cpan.org - Broken in 4.013 added

Wed Apr 14 15:36:20 2010 CAPTTOFU [...] cpan.org - Broken in 3.0008 deleted

Wed Apr 14 15:36:20 2010 CAPTTOFU [...] cpan.org - Broken in 4.005 deleted