Skip Menu |
 

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

Report information
The Basics
Id: 30033
Status: resolved
Priority: 0/
Queue: DBD-mysql

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

Bug Information
Severity: (no value)
Broken in: 4.013
Fixed in: (no value)



Subject: Quote marks in comments can break bind parameters
Download (untitled) / with headers
text/plain 943b
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
Download (untitled) / with headers
text/plain 828b
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.
Download (untitled) / with headers
text/plain 1.7k
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;
Download (untitled) / with headers
text/plain 673b
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>
Download (untitled) / with headers
text/plain 1.2k
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 is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

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