Edit report at https://bugs.php.net/bug.php?id=47615&edit=1
ID: 47615
Comment by: cornelius dot howl at gmail dot com
Reported by: kenaniah at gmail dot com
Summary: PDO parameter binding is in clear violation of the
ISO 9075 standard
Status: Open
Type: Feature/Change Request
Package: PDO related
Operating System: *
PHP Version: 5.2.9
Block user comment: N
Private report: N
New Comment:
BTW, My PHP version 5.3.10.
pdo_pgsql
PDO Driver for PostgreSQL => enabled
PostgreSQL(libpq) Version => 9.1.2
Module version => 1.0.2
Revision => $Id: pdo_pgsql.c 321634 2012-01-01 13:15:04Z felipe $
pdo_sqlite
PDO Driver for SQLite 3.x => enabled
SQLite Library => 3.7.10
pgsql
PostgreSQL Support => enabled
PostgreSQL(libpq) Version => 9.1.2
Multibyte character support => enabled
SSL support => enabled
Active Persistent Links => 0
Active Links => 0
Previous Comments:
------------------------------------------------------------------------
[2012-03-05 18:11:35] cornelius dot howl at gmail dot com
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 44
------------------------------------------------------------------------
[2009-03-10 18:32:51] 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)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=47615&edit=1
