|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2009-03-10 18:32 UTC] kenaniah at gmail dot com
Description:
------------
Referencing ISO 9075, PDO does not properly bind boolean parameters when the parameter type has not been made known to PDO.
According to the standard, booleans represent a truth, false, or unknown value. According to the SQL language definition (feel free to reference SQL-92), the SQL equivalents for a boolean value are TRUE, FALSE, and NULL respectively.
The PDO core should automatically convert boolean values to their proper SQL counterparts, and it should be the role of the client driver to deal with these values if the database platform in question does not support the ISO standard.
Rather than listing workarounds, we ask that the PDO core be brought into compliance with the SQL standards, and that individual database drivers be modified to handle the cases in which their underlying database is not standards compliant.
Reproduce code:
---------------
$res = $db->prepare('SELECT id FROM table WHERE mybool = ?');
$res->execute(array(false));
Expected result:
----------------
SQL statement sent to server:
SELECT id FROM table WHERE mybool = FALSE
(unless modified by DB driver due to a lack of standards compliance on the part of the DB)
Actual result:
--------------
Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for type boolean: ""'
^^for Postgres driver (Postgres is an standard compliant DB as far as booleans are concerned)
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Tue Apr 23 18:01:34 2024 UTC |
Same here. <?php $db = new PDO('pgsql:host=localhost;dbname=test;', 'root', '123123' ); // $db = new PDO('sqlite::memory:'); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->query( <<<EOS CREATE TABLE products( name varchar(256), subtitle varchar(256), sn varchar(128), spec text, content text, category_id integer, is_cover boolean, thumb varchar(250), image varchar(250), lang varchar(5), price integer, token varchar(128), hide boolean default FALSE, created_on timestamp, created_by integer ); EOS ); $stm = $db->prepare('INSERT INTO products ( name,sn,subtitle,hide,token,price,content,spec,created_by,created_on) VALUES (:name,:sn,:subtitle,:hide,:token,:price,:content,:spec,:created_by,:created_on) '); $stm->execute(array( ':name' => 'Test', ':sn' => 'Test', ':subtitle' => '123', ':hide' => false, ':token' => 'd862899f5c4b38f6b6a161e77c2936fb5541deb5', ':price' => 0, ':content' => '<p>123</p>', ':spec' => '<p>123</p>', ':created_by' => 1, ':created_on' => '2012-03-06T01:37:50+08:00', )); foreach( $db->query('select * from products') as $item ) { var_dump( $item ); } Which throws PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: ""' in /Users/c9s/git/Work/phifty/pdo_test.php:44 Stack trace: #0 /Users/c9s/git/Work/phifty/pdo_test.php(44): PDOStatement->execute(Array) #1 {main} thrown in /Users/c9s/git/Work/phifty/pdo_test.php on line 44 Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: ""' in /Users/c9s/git/Work/phifty/pdo_test.php on line 44 PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: "" in /Users/c9s/git/Work/phifty/pdo_test.php on line 44Yes, PDOStatement::execute() treats all parameters as strings. To get this query to execute correctly, perform like this: $res = $db->prepare('SELECT id FROM table WHERE mybool = ?'); $res->bindValue(1, false, PDO::PARAM_BOOL); $res->execute();