Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id:
30033
Status:
resolved
Priority:
Low/Low
Queue:

People
Owner:
CAPTTOFU [...] cpan.org
Requestors:
ovid [...] cpan.org
Cc:
AdminCc:

BugTracker
Severity:
(no value)
Broken in:
4.013
Fixed in:
(no value)



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
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.
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;
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 = ?
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 = ? > >
Fixed in latest SVN commit. Added logic for both -- and /* */ style comments.


This service runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.