| Subject: | Preparing a statement containing \? corrupts even literal strings, causing bad SQL later. |
Calling $dbh->prepare on a string containing '\?' - for example when using the JSON operators in Postgres - causes the statement string to be modified. This is particularly nasty when a subroutine contains such a string as a literal: preparing the literal string a second time causes an error from bad SQL (mismatch in number of arguments). Surely prepare() should not modify its arguments, especially literal strings!
Example code:
my $sql = 'SELECT capabilities \? ? as can FROM users WHERE username = ?';
print "Preparing statement:\n $sql\n";
my $sth = $dbh->prepare($sql)
$sql gets modified! Running the example program, the correct statement is prepared the first time, but preparing the same literal string again results in error:
$ perl dbi-placeholder-bug-2016-04.pl
Preparing statement:
SELECT capabilities \? ? as can FROM users WHERE username = ?
Preparing statement:
SELECT capabilities ? ? as can FROM users WHERE username = ?
Preparing statement:
SELECT capabilities ? ? as can FROM users WHERE username = ?
This appears to happen in DBD/Pg.pm, line 273, in sub prepare:
DBD::Pg::st::_prepare($sth, $statement, @attribs) || 0;
The Perl Debugger fails to trace into that statement, nor have I been able to locate st.pm or any other place (XS code?) where the offending _prepare is defined. Am happy to trace this further given a clue how to proceed.
| Subject: | dbi-placeholder-bug-2016-04.pl |
#!/usr/bin/env perl
use DBI;
my $dbh = DBI->connect('DBI:Pg:dbname=test1')
or die "Couldn't connect to database: " . DBI->errstr;
sub test {
my $sql = 'SELECT capabilities \? ? as can FROM users WHERE username = ?';
print "Preparing statement:\n $sql\n";
my $sth = $dbh->prepare($sql)
or die "Couldn't prepare statement: " . $dbh->errstr;
}
test;
test;
test;
$dbh->disconnect;