Edit report at http://bugs.php.net/bug.php?id=45259&edit=1
ID: 45259
Comment by: sl9 at gmx dot net
Reported by: ahp at byu dot edu
Summary: PDO prepared statements using SQLite can't bind
non-text values in expressions
Status: No Feedback
Type: Bug
Package: PDO related
Operating System: Ubuntu 8.04
PHP Version: 5.2.6
Block user comment: N
Private report: N
New Comment:
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?
Previous Comments:
------------------------------------------------------------------------
[2009-05-03 01:00:14] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
------------------------------------------------------------------------
[2009-04-25 15:08:12] [email protected]
Please try using this CVS snapshot:
http://snaps.php.net/php5.2-latest.tar.gz
For Windows:
http://windows.php.net/snapshots/
------------------------------------------------------------------------
[2008-06-13 13:37:12] ahp at byu dot edu
After testing with the above-linked 5.3CVS version, the program works as
expected, so it appears that in the latest development version there is
a way to properly bind non-string variables.
Ideally, I'd like to be able to replace the explicit binding with the
more convenient hash-binding; that is, replace:
$q->bindValue(':value', 2, PDO::PARAM_INT);
$q->execute();
with:
$q->execute(Array(':value'=>2));
However, when I try this with the latest snapshot, the program again
erroneously prints '3'. Would it be possible for the variable type to
be deduced from the PHP variable subtype when the type isn't explicitly
specified?
------------------------------------------------------------------------
[2008-06-13 12:59:57] [email protected]
Please try using this CVS snapshot:
http://snaps.php.net/php5.3-latest.tar.gz
For Windows (zip):
http://snaps.php.net/win32/php5.3-win32-latest.zip
For Windows (installer):
http://snaps.php.net/win32/php5.3-win32-installer-latest.msi
5.3 now binds booleans and integers as the appropriate type, can you try
again with the CVS version.
------------------------------------------------------------------------
[2008-06-13 12:44:46] ahp at byu dot edu
Description:
------------
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.
The example program prints '3', even though 2 is less than 3. In fact,
it will always print "3" no matter what value is bound to the ':value'
parameter (with the exception of Null).
Although SQLite's type affinity system can automatically convert values
compared directly against a table column that has a type affinity, the
inability to bind non-string-typed values presents a problem with values
used in expressions, comparing against columns (especially computed
columns) in views and unions, and comparisons against columns that do
not have a type affinity.
My specific problem occurred when trying to filter for an ID value
against a key column through a view. The only workaround I found on the
web is to not use bound parameters and prepared statements, but rather
construct the SQL string afresh.
Reproduce code:
---------------
#!/usr/bin/php
<script language="php">
$db=new PDO("sqlite:temp.db");
$q=$db->prepare("SELECT min(3, :value) AS result;");
$q->bindValue(':value', 2, PDO::PARAM_INT);
$q->execute();
$row=$q->fetch();
print $row['result']."\n"; // Always prints '3'
</script>
Expected result:
----------------
The program should print '2' since min(3, 2) is 2.
Actual result:
--------------
The program prints:
3
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/bug.php?id=45259&edit=1
