php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #33876 PDO misquotes/miscasts bool(false)
Submitted: 2005-07-27 00:14 UTC Modified: 2005-09-10 23:09 UTC
From: php at sagi dot org Assigned: wez (profile)
Status: Closed Package: PDO related
PHP Version: 5CVS-2005-07-27 (dev) OS: Linux
Private report: No CVE-ID: None
 [2005-07-27 00:14 UTC] php at sagi dot org
Description:
------------
Running latest php5 snapshot (php5-200507261230), PDO connected to pgsql 8.0 server.

I'm trying to run a query similar to this:
$res = $db->prepare('SELECT id FROM table WHERE mybool = ?');
$res->execute(array(false));

PDO throws this exception: 'SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type boolean: ""'

The query that has been executed, according to the server log, is: "SELECT id FROM table WHERE mybool = ''"

Which is obviously not right. When trying to run the same query with bool(true) parameter, PDO correctly quotes it as '1'.


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-07-27 15:16 UTC] iliaa@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

This is expected behaviour, when cast to a string bool false is converted to "" while bool true converted to "1". 
 [2005-07-27 15:56 UTC] php at sagi dot org
I know how string casting works, but this is not a string.

PDO knows, for example, how to convert PHP NULL to SQL NULL and not string('') (like string casting does). Why can't it cast bool values to an integer instead?

This behavior is bad. PDO knows how to cast the value to real php bool when selecting, but cannot cast it back when inserting/updating, which means a simple attempt to re-insert a row that has just been selected, using the same object, fails.
 [2005-07-27 16:25 UTC] wez@php.net
Try this as a workaround for now:

$res = $db->prepare('SELECT ...', array(
   PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT => true
  ));

You can blame the pretty poor native prepared statement API in pgsql for this one; it just doesn't tell PDO anything about the parameter types so it can't make an informed decision about how to treat the parameters.

 [2005-07-27 16:40 UTC] php at sagi dot org
Nope, still get the same exception and the same query is being executed according to the server log.

Still using the same php5-200507261230 snapshot.

The exact code:
$res = $db->prepare(
        'SELECT id,name,trial FROM shops WHERE trial = ?',
        array(PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT => true)
);

$res->execute(array(false));

And the result:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type boolean: ""' in /home/shopy/dev/tmp/test.php:12
Stack trace:
#0 /home/shopy/dev/tmp/test.php(12): PDOStatement->execute(Array)
#1 {main}
  thrown in /home/shopy/dev/tmp/test.php on line 12
 [2005-07-27 17:15 UTC] php at sagi dot org
For what it's worth, its seems like the pgsql _client_ library that my installation is compiled against is v7.4.7, even though the server is running v8.0. 

So I guess it never used native prepared statements and the workaround that you suggested had no affect - they're already disabled.
 [2005-08-08 07:11 UTC] php dot net at sharpdreams dot com
I "fixed" this by binding the parameters as integers:

$bool = false;
$st = $db->prepare( "insert into foo ( bar ) values ( :bar )" );
$st->bindParam( ":bar", $boo, PDO_PARAM_INT );
$st->execute();

Add the following to your pgsql:

-- begin

CREATE OR REPLACE FUNCTION int_to_bool(int4)
  RETURNS bool AS
$BODY$
select case
 when $1 <> 0 then TRUE
 else FALSE
end;
$BODY$
  LANGUAGE 'sql' VOLATILE;

CREATE CAST (int4 AS bool)
  WITH FUNCTION int_to_bool(int4)
  AS ASSIGNMENT;

-- end

PDO converts true/false to 1/0 which pass into the cast function.

Couldn't get an implicit ''::bool cast working.

e.g.,

CREATE OR REPLACE FUNCTION text_to_bool(text)
  RETURNS bool AS
$BODY$
select case
 when $1 <> '' then TRUE
 else FALSE
end;
$BODY$
  LANGUAGE 'sql' VOLATILE;
CREATE CAST (text AS bool)
  WITH FUNCTION text_to_bool(text)
  AS ASSIGNMENT;

And then doing "select ''::bool;" didn't work. Doin "select ''::text::bool" did work, though, so you could factor that into your PDO statements, too:

insert into foo(bar) values(:bar::text);

which will call the above cast ('' -> text -> bool).
 [2005-09-02 08:44 UTC] jam at zoidtechnologies dot com
the problem boils down to this: php casts a boolean with value False to "". pgsql requires booleans to have either a "t" or "f" value, and doesn't know what to do about an empty string being used as a boolean value. unfortunately the sql standard to which pgsql attempts to adhere is not clear on this issue and in fact marks the boolean type as "optional".

there are two possible fixes for this:

(1) use a ternary operator in the php script-- when handling a boolean value, explicitly set the variable to be used in the query data to either 't' or 'f':
$foo = $bar ? 't' : 'f';

(2) add a check into PDO such that when it is connected to a pgsql database and it is handling a boolean type, make sure that False is changed to 'f'.
 [2005-09-10 23:09 UTC] wez@php.net
This issue has been resolved on the tip of the 5.1 branch.
There was a bug, but your script was still technically "wrong".

By default, PDO treats all data as strings, so the bool was being cast to string, which proved to be incompatible with the bool that pgsql expected.

You need to tell PDO when you're binding booleans (or any type that might be ambiguous to the driver); you can do this using either PDOStatement::bindParam() or PDOStatement::bindValue():

$res->bindValue(1, false, PDO_PARAM_BOOL);
$res->execute();

You can view the test case for the bug here:
http://viewcvs.php.net/viewcvs.cgi/php-src/ext/pdo_pgsql/tests/Attic/bug_33876.phpt?rev=1.1.2.1

 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Dec 06 00:01:31 2024 UTC