php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #47615 PDO parameter binding is in clear violation of the ISO 9075 standard
Submitted: 2009-03-10 18:32 UTC Modified: 2017-04-24 19:15 UTC
Votes:10
Avg. Score:4.2 ± 1.2
Reproduced:9 of 9 (100.0%)
Same Version:1 (11.1%)
Same OS:1 (11.1%)
From: kenaniah at gmail dot com Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.2.9 OS: *
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: kenaniah at gmail dot com
New email:
PHP Version: OS:

 

 [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)

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2011-02-21 20:42 UTC] jani@php.net
-Package: Feature/Change Request +Package: PDO related
 [2012-03-05 18:11 UTC] 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
 [2012-03-05 18:13 UTC] cornelius dot howl at gmail dot com
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
 [2015-07-23 21:51 UTC] chealer at gmail dot com
Following ticket #49614, PDO::execute's manual page was changed to specify that its second parameter should only contain strings.
I doubt this is correct though.
 [2017-04-24 19:15 UTC] adambaratz@php.net
-Status: Open +Status: Not a bug
 [2017-04-24 19:15 UTC] adambaratz@php.net
Yes, 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();
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Tue Mar 02 15:01:24 2021 UTC