| 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 :)