DBD-Pg-1.12 is not passing all tests when run with PostgreSQL 7.2
using perl 5.6.1 and DBI 1.18 on NetBSD-1.5.1/i386. Testing with
DBI 1.21 and PostgreSQL 7.2.1 made no difference.
t/04execute.........FAILED tests 5-7
Failed 3/10 tests, 70.00% okay
...
t/12placeholders....DBD::Pg::st execute failed: ERROR: parser: parse error at or
near "foo" at t/12placeholders.t line 122.
04execute test 5 is this:
eval {
local $dbh->{PrintError} = 0;
$sth = $dbh->prepare(q{
SELECT id
, name
FROM test
WHERE id = ?
AND name = ?
});
$sth->bind_param(1, 2);
$sth->execute();
};
if ($@) {
print "ok $n\n"; $n++;
} else {
print "not ok $n\n"; $n++;
}
The query as reported in the PostgreSQL log:
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query:
SELECT id
, name
FROM test
WHERE id = 2
AND name = NULL
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
No error so $@ is not set and the test fails. Running the same query
interactively doesn't produce an error either. I think that '= NULL'
is legal SQL (if not too useful? :-) but perhaps someone with stronger
SQL knowledge can comment? Tests six and seven are similar.
t/12placeholders is failing here:
1..9
ok 1
DBD::Pg::st execute failed: parameter unknown at t/12placeholders.t line 27.
DBD::Pg::st execute failed: parameter unknown at t/12placeholders.t line 27.
Database handle destroyed without explicit disconnect.
23 $quo = $dbh->quote("\\'?:");
24 $sth = $dbh->prepare(qq{
25 INSERT INTO test (name) VALUES ($quo)
26 });
27 $sth->execute();
main::main(t/12placeholders.t:23): $quo = $dbh->quote("\\'?:");
DB<1>
main::main(t/12placeholders.t:24): $sth = $dbh->prepare(qq{
main::main(t/12placeholders.t:25): INSERT INTO test (name) VALUES ($quo)
DB<1> p $quo
'\\''?:'
If I follow that correctly, the backslash that was quoted in the perl
string is quoted with a second backslash, which is correct. The
single quote is correctly doubled.
The question mark is not quoted which is fine for PostgreSQL, however
execute() is treating it as a placeholder which it should not, since
it is within SQL quoted text.
Looking at dbd_st_execute() the following code looks too simplistic:
for single quotes it will only handle backslash escapes but not the
SQL '' machanism:
if (in_literal) {
/* check if literal ends but keep quotes in literal */
if (*src == in_literal && *(src-1) != '\\') {
in_literal = 0;
}
*dest++ = *src++;
continue;
}
I don't think the simple lookback to see if the previous character is
a backslash is going to be good enough, either: what happens if the
previous character was the second backslash in a \\ pair?
I suspect that the "right" fix to this is to add a new state variable
to keep track of whether the previous character was a backslash
escape, and then handle the '' case as well.
If this isn't a known problem I'll perhaps be able to look at it at
the weekend.
Regards,
Giles
