| Subject: | Placeholders don't work if value contains '?' |
The execute() method currently assumes all occurrences of '?' in the SQL statement are placeholders, even when they're quoted. Worse, it replaces them one at a time, so if a value contains '?' the next value gets inserted into the middle of it.
Try something like this and you'll get an SQL syntax error:
$dbh->do("INSERT INTO t1 VALUES (?, ?, ?, ?)", undef, undef,
'this is a test?', '???', 'it is?');
I've attached a patch that should fix the bug.
--- mysqlPP.pm.orig 2003-05-16 21:21:40.000000000 -0400
+++ mysqlPP.pm 2003-05-16 21:25:22.000000000 -0400
@@ -347,11 +347,20 @@
# ...
}
my $statement = $sth->{Statement};
- for (my $i = 0; $i < $num_param; $i++) {
- my $dbh = $sth->{Database};
- my $quoted_param = $dbh->quote($params->[$i]);
- $statement =~ s/\?/$quoted_param/e;
- }
+ my $dbh = $sth->{Database};
+ my $i = 0; # index for @$params
+ # Can't just replace all occurrences of ? --
+ # need to skip it when it occurs in quotes:
+ $statement =~
+ s{
+ ( # capture everything
+ [^'"?]+ | # nonquoted, non-? OR
+ '[^']*' | # single-quoted text OR
+ "[^"]*" | # double-quoted text OR
+ (\?) # placeholder
+ )
+ }
+ { $2 ? $dbh->quote($params->[$i++]) : $1 }gex;
my $mysql = $sth->FETCH('mysqlpp_handle');
my $result = eval {
$sth->{mysqlpp_record_iterator} = undef;