Bug->Req #54023 [Opn]: PDO prepared statements using SQLite can't bind non-text values in expressions
Edit report at https://bugs.php.net/bug.php?id=54023&edit=1
ID: 54023
Updated by: cmb@php.net
Reported by: sl9 at gmx dot net
Summary: PDO prepared statements using SQLite can't bind
non-text values in expressions
Status: Open
-Type: Bug
+Type: Feature/Change Request
Package: PDO SQLite
Operating System: Linux i386
PHP Version: 5.3SVN-2011-02-15 (SVN)
Block user comment: N
Private report: N
New Comment:
Behavior confirmed: <https://3v4l.org/LsC9s>.
However, I don't think this is a bug (since it's possible to call
::bindValue() explicitly), but rather a missing feature. Given
that a special casing in PDOStatement is suggested, discussion on
the internals@ mailing appears to be appropriate.
Previous Comments:
------------------------------------------------------------------------
[2011-02-15 11:50:41] sl9 at gmx dot net
Original patch notes from #45259
I have experienced this bug also. I tried most of the windows builds which are available on the
download page: 5.3.4 (ts and nts, vc6 and vc9) and 5.2.16 (ts and nts).
My tests try to execute a prepared statement with parameters and what I'd like PDO to do is map
my php variable to a PDO_PARAM_* type. This is where things go wrong.
Example:
$db = new PDO('sqlite::memory:');
$stmt = $db->prepare('SELECT "string" = ? AS test');
$stmt->execute(array('string'));
$row = $stmt->fetch();
echo 'test: ', $row['test'], PHP_EOL;
// prints "test: 1", I expect "test: 1"
$stmt = $db->prepare('SELECT 50 = ? AS test');
$stmt->execute(array(50));
$row = $stmt->fetch();
echo 'test: ', $row['test'], PHP_EOL;
// prints "test: 0", I expect "test: 1"
The strange thing is:
$stmt = $db->prepare('SELECT 50 = 50 AS test');
$stmt->execute();
$row = $stmt->fetch();
echo 'test: ', $row['test'], PHP_EOL;
// prints "test: 1", I expect "test: 1"
This indicates sqlite knows what I mean, but somewhere in PDO things go bump.
I've investigated by looking into the source (SVN 5.3 rev 306430 from yesterday on a fresh
Debian lenny install). And devised a few test cases and a patch. In the pdo_sqlite extension it all
works peachy, although for the mapping of ZVAL to PDO_PARAM_* I've had to edit the pdo_stmt.c
file in the pdo extension. I don't know if this is acceptable as I can imagine many drivers
depend on the pdo extension being frozen.
I've added an exception to the PDOStatement::execute() handler, the handler sets the default
PDO_PARAM_* to PDO_PARAM_ZVAL to signal that no explicit type was set. So the driver can do
it's own mapping. This only takes effect when the driver for the statment is sqlite. Others
might depend on the default to be PDO_PARAM_STR.
This cannot stay PDO_PARAM_STR because really_register_bound_param() will try and autoconvert the
ZVAL if it is PDO_PARAM_STR or a couple of other param types. I assume this will destroy the
original ZVAL's type and will make it indistinguishable from an explicitly defined
PDO_PARAM_STR param.
So now the pdo_sqlite_stmt_param_hook() will receive a parameter with PDO type PDO_PARAM_ZVAL when
it is not explicitly set by the user, this will allow the driver to inspect the given zval and bind
it accordingly. This has also been included in the patch which are alterations of only the
pdo_sqlite_stmt_param_hook() function. I've revised it a bit, it looked odd to me all those
return statements scattered throughout the nested switch statements.
I've only tested this with the SVN build from yesterday on Debian lenny x86. Maybe we should
test it on more platforms? And can someone comment on the patch, especially if we can merge it into
the svn 5.3 branch?
------------------------------------------------------------------------
[2011-02-15 11:46:56] sl9 at gmx dot net
Description:
------------
This report is an addition to / repost of the bug reported in #45259 from 2008. I replied on that
one a few weeks ago but nobody seemed to take notice.
This bug seems to also have been reported in bug #53475 (2010).
It concerns: When using PDO with the SQLite backend and prepared SQL statements, it appears to be
impossible to bind anything but a text or NULL value to a parameter.
As a remark I would like to mention, binded values other than string and NULL types are cast to a
string automatically.
The bug is explained quite well in bug #45259. And I've added a patch only for the 5.3 branch
as that has support for the PHP_PARAM_ZVAL type (which 5.2 lacks apparently). The current patch will
not easily be backported to 5.2, I think.
I've tested the patch below with the svn commit I could grab today (308350). It includes a
couple of test cases which should cover the bug quite well.
Test script:
---------------
$dbase = new PDO('sqlite::memory:');
$stmt = $dbase->prepare('SELECT 1=? AS result');
$stmt->execute(array(1));
echo '(INT == BOUNDINT) = ', ($stmt->fetchColumn() == 1 ? 'true' :
'false'), PHP_EOL;
$stmt = $dbase->prepare('SELECT "1"=? AS result');
$stmt->execute(array(1));
echo '(STRING == BOUNDINT) = ', ($stmt->fetchColumn() == 1 ? 'true' :
'false'), PHP_EOL;
Expected result:
----------------
(INT == BOUNDINT) = true
(STRING == BOUNDINT) = false
Actual result:
--------------
(INT == BOUNDINT) = false
(STRING == BOUNDINT) = true
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=54023&edit=1
Thread (3 messages)