| Subject: | Broken functionality when using '?'-characters in data |
Version 0.03 of the mysqlPP driver does not process data containing '?'-characters correctly. This is so, whether or not one uses placeholders.
Given a table looking like:
CREATE TABLE test_blobs (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
data BLOB
);
Both the following code snippets will not work correctly if $data contains '?'-characters. In the descriptions below I'll assume that:
$data = "mydata?hoho?or";
a) without placeholders
...
$sth = $dbh->prepare("INSERT INTO test_blobs");
$sth->execute();
$id = $dbh->{"mysql_insertid"};
$sth = $dbh->prepare(
"UPDATE test_blobs".
" SET data = ".$dbh->quote($data).
" WHERE id = ".$id);
$sth->execute();
...
b) with placeholders
...
$sth = $dbh->prepare("INSERT INTO test_blobs");
$sth->execute();
$id = $dbh->{"mysql_insertid"};
$sth = $dbh->prepare(
"UPDATE test_blobs".
" SET data = ?".
" WHERE id = ?");
$sth->execute($data, $id);
...
The offending code lines can be found in version 0.03 of mysqlPP.pm. The most relevant lines are as follows:
- in sub prepare() lines 142 through 154:
sub prepare
{
my $dbh = shift;
my ($statement, @attribs) = @_;
my $sth = DBI::_new_sth($dbh, {
Statement => $statement,
});
$sth->STORE(mysqlpp_handle => $dbh->FETCH('mysqlpp_connection'));
$sth->STORE(mysqlpp_params => []);
$sth->STORE(NUM_OF_PARAMS => ($statement =~ tr/?//));
$sth;
}
- in sub execute() lines 298 through 314:
sub execute
{
my $sth = shift;
my @bind_values = @_;
my $params = (@bind_values) ?
\@bind_values : $sth->FETCH('mysqlpp_params');
my $num_param = $sth->FETCH('NUM_OF_PARAMS');
if (@$params != $num_param) {
# ...
}
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;
}
The logic is that a call to prepare() will "figure out" how many placeholders there are. That logic assumes that all '?'-characters are placeholders. This is partly wrong, as shown in example a) above.
It's when a subsequent call to execute() is done things will go kablooing. The logic will assume that the counting done in the previous prepare() call is correct.
If no placeholders where used it will search'n'replace all the '?'-characters in the statement with non-existing data (observer the non-implemented check on line 306), i.e. "mydata?hoho?or" will be translated into "mydataNULLhohoNULLor", which clearly is wrong.
If placeholders are used the given data is quoted using $dbh->quote() and then a simple search'n'replace is done on '?'-characters. The problem is that there is no effective way for the quote() call to actually quote the '?'-characters that are part of the actual data. This means that the '?'-characters in, as part of the actual data, will be interpreted as new placeholders, i.e. "UPDATE test_blobs SET data = ? WHERE id = ?" (with the placeholder data as "mydata?hoho?or" and "13") will be translated into "UPDATE test_blobs SET data = 'mydata13hohoNULLor' WHERE id = NULL", which clearly is wrong.
A quick workaround is to do something like in the below. That code is _very_ ugly, but works.
...
sub quote_blob
{
my $val = shift(@_);
if (!defined($val)) { return "NULL"; }
# the following should be enough for MySQL
$val =~ s/\\/\\\\/gos;
$val =~ s/\'/\\\'/gos;
$val =~ s/\"/\\\"/gos;
$val =~ s/\n/\\n/gos;
$val =~ s/\r/\\r/gos;
$val =~ s/\t/\\t/gos;
$val =~ s/\0/\\0/gos;
return "'".$val."'";
}
$sth = $dbh->prepare("INSERT INTO test_blobs");
$sth->execute();
$id = $dbh->{"mysql_insertid"};
$sth = $dbh->prepare(
"UPDATE test_blobs".
" SET data = ".quote_blob($data).
" WHERE id = ".$id);
$sth->STORE("NUM_OF_PARAMS" => 0);
$sth->execute();
...
It seems to me that it would be possible to make mysqlPP more working without a total re-write, but the amount of work that would mount up to is too much for me to include any patch info here (at least at this point).
The fact that some of the code above might contain writing typos or that it's not as nice or efficient as it could be will not change the fact that this problem is real and bothersome.
To make this report more compleate I'll include some environmental info (although that should not matter):
perl -v : v5.6.1 for i386-openbsd
uname -a : OpenBSD hostname 3.2 GENERIC#25 i386