Skip Menu |

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

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

People
Owner: Nobody in particular
Requestors: wonko [...] cpan.org
Cc:
AdminCc:

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



Subject: Escaped single quotes throwing off bind param detection
Perfectly valid SQL with escaped singled quotes (like the kind that $dbh->quote create) will mess up bind param detection such that you get an error message like so: execute failed: called with 1 bind variables when 0 are needed It's hard to tell if this is a problem with DBI (tried with 1.604) or DBD::mysql (tried with latest 4.007), but here's a simple example that will trigger the error (note, tables don't actually need to exist): #!/usr/bin/perl use strict; use warnings; use DBI; my $dbh = DBI->connect('dbi:mysql:test', 'root', ''); my $sth = $dbh->prepare_cached(q/SELECT foo FROM bar WHERE bar.text = 'Tim\'s Test' AND bar.bool = ?/); $sth->execute(0); If you replace that SQL with this: my $sth = $dbh->prepare_cached(q/SELECT foo FROM bar WHERE bar.text = "Tim's Test" AND bar.bool = ?/) Then everything is ok. But since in the system I'm working the SQL is generated several layers deeper it's hard to do. And it's made more complicated by the fact that 'Tim\'s Test' is what $dbh->quote("Tim's Test") produces. So you'd think it would do the right thing :)
Subject: Re: [rt.cpan.org #36227] Escaped single quotes throwing off bind param detection
Date: Tue, 27 May 2008 23:58:06 +0100
To: Michael Peters via RT <bug-DBI [...] rt.cpan.org>
From: Tim Bunce <Tim.Bunce [...] pobox.com>
On Tue, May 27, 2008 at 04:49:41PM -0400, Michael Peters via RT wrote: Show quoted text
> > Tue May 27 16:49:40 2008: Request 36227 was acted upon. > Transaction: Ticket created by WONKO > Queue: DBI > Subject: Escaped single quotes throwing off bind param detection > > Perfectly valid SQL with escaped singled quotes (like the kind that > $dbh->quote create) will mess up bind param detection such that you get > an error message like so: > > execute failed: called with 1 bind variables when 0 are needed > > It's hard to tell if this is a problem with DBI (tried with 1.604) or > DBD::mysql (tried with latest 4.007),
It's the driver. Please move or resubmit the ticket to the DBD-mysql queue. Thanks. Tim.
Subject: patch and unit test
From: zhurs [...] yandex.ru
Hi. I wrote small patch(only 4 symbols added ;-) and unit-test. It works for me.
diff -Nru ../DBD-mysql-4.006/dbdimp.c ./dbdimp.c --- ../DBD-mysql-4.006/dbdimp.c 2007-12-12 00:41:50.000000000 +0300 +++ ./dbdimp.c 2008-10-10 16:09:17.000000000 +0400 @@ -80,7 +80,7 @@ while ((c = *ptr) && c != end_token) { if (c == '\\') - if (! *ptr) + if (! *(++ptr)) continue; ++ptr; diff -Nru ../DBD-mysql-4.006/t/43count_params.t ./t/43count_params.t --- ../DBD-mysql-4.006/t/43count_params.t 1970-01-01 03:00:00.000000000 +0300 +++ ./t/43count_params.t 2008-10-10 16:05:40.000000000 +0400 @@ -0,0 +1,106 @@ +#!/usr/local/bin/perl +# +# $Id: 40bindparam.t 6127 2008-10-08 22:36:13Z zhur $ +# +# This is a skeleton test. For writing new tests, take this file +# and modify/extend it. +# + +$^W = 1; + + +# +# Make -w happy +# +$test_dsn = ''; +$test_user = ''; +$test_password = ''; + + +# +# Include lib.pl +# +use DBI (); +use vars qw($COL_NULLABLE); +$mdriver = ""; +foreach $file ("lib.pl", "t/lib.pl") { + do $file; if ($@) { print STDERR "Error while executing lib.pl: $@\n"; + exit 10; + } + if ($mdriver ne '') { + last; + } +} +if ($mdriver eq 'pNET') { + print "1..0\n"; + exit 0; +} + +sub ServerError() { + my $err = $DBI::errstr; # Hate -w ... + print STDERR ("Cannot connect: ", $DBI::errstr, "\n", + "\tEither your server is not up and running or you have no\n", + "\tpermissions for acessing the DSN $test_dsn.\n", + "\tThis test requires a running server and write permissions.\n", + "\tPlease make sure your server is running and you have\n", + "\tpermissions, then retry.\n"); + exit 10; +} + +$dbh = DBI->connect($test_dsn, $test_user, $test_password, + { RaiseError => 1, AutoCommit => 1}) or ServerError() ; + +# +# Main loop; leave this untouched, put tests after creating +# the new table. +# +while (Testing()) { + $table = "t1"; + + # Connect to the database + Test($state or ($dbh = DBI->connect($test_dsn, $test_user, + $test_password, {mysql_enable_utf8 => 1}))) + or ServerError(); + + # Create table + Test($state or $sth = $dbh->do("DROP TABLE IF EXISTS $table")) + or DbiError($dbh->err, $dbh->errstr); + + Test($state or $sth = $dbh->do("CREATE TABLE $table (id int, name varchar(100))")) + or DbiError($dbh->err, $dbh->errstr); + + # + Test($state or $sth = $dbh->prepare("INSERT INTO $table (name, id)" + . " VALUES ('Charles de Batz de Castelmore, comte d\\'Artagnan', ?)")->execute(1)) + or DbiError($dbh->err, $dbh->errstr); + + Test($state or $sth = $dbh->prepare("INSERT INTO $table (name, id) " + . " VALUES ('Charles de Batz de Castelmore, comte d\\'Artagnan', 2)")->execute()) + or DbiError($dbh->err, $dbh->errstr); + + Test($state or $sth = $dbh->prepare("INSERT INTO $table (name, id)" + . " VALUES (?, ?)")->execute("Charles de Batz de Castelmore, comte d\\'Artagnan", 3)) + or DbiError($dbh->err, $dbh->errstr); + + + Test($state or $sth = $dbh->prepare("INSERT INTO $table (id, name)" + . " VALUES (?, 'Charles de Batz de Castelmore, comte d\\'Artagnan')")->execute(1)) + or DbiError($dbh->err, $dbh->errstr); + + Test($state or $sth = $dbh->prepare("INSERT INTO $table (id, name) " + . " VALUES (2, 'Charles de Batz de Castelmore, comte d\\'Artagnan')")->execute()) + or DbiError($dbh->err, $dbh->errstr); + + Test($state or $sth = $dbh->prepare("INSERT INTO $table (id, name)" + . " VALUES (?, ?)")->execute(3, "Charles de Batz de Castelmore, comte d\\'Artagnan")) + or DbiError($dbh->err, $dbh->errstr); + + + # + # Finally drop the test table. + # + Test($state or $dbh->do("DROP TABLE $table")) + or DbiError($dbh->err, $dbh->errstr); + + Test($state or undef $sth or 1); + +}
On Fri Oct 10 08:33:20 2008, http://zhurs.livejournal.com/ wrote: Show quoted text
> Hi. > > I wrote small patch(only 4 symbols added ;-) and unit-test. > It works for me.
Spasibo! I'll add this to 4.008 for a 4.009 release very soon, after some testing. Kind regards, Patrick
On Fri Oct 10 08:33:20 2008, http://zhurs.livejournal.com/ wrote: Show quoted text
> Hi. > > I wrote small patch(only 4 symbols added ;-) and unit-test. > It works for me.
Spasibo! I've added this patch to 4.009, which I'll release tonight. I had to modify your patch for the tests. I've re-written the tests to use Test::More since 4.006, so I converted you 43count_params.t to use Test::More. I also tested that your fix to dbdimp.c and it fixes it as well. Thank you!
fixed in 4.009