php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #44597 [PATCH] Postgres driver does not prepare booleans correctly
Submitted: 2008-04-01 21:00 UTC Modified: 2013-10-15 11:54 UTC
Votes:32
Avg. Score:4.7 ± 0.8
Reproduced:27 of 27 (100.0%)
Same Version:9 (33.3%)
Same OS:5 (18.5%)
From: kenaniah at gmail dot com Assigned:
Status: No Feedback Package: PDO related
PHP Version: 5.2.6 OS: Red Hat 4.1.1
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2008-04-01 21:00 UTC] kenaniah at gmail dot com
Description:
------------
When using postgres via PDO and attempting to execute an INSERT or UPDATE query using $stmt->execute(array_values($data)) syntax, postgres returns an error for any boolean fields that may be present. 



Reproduce code:
---------------
<?php 

// $db is my PDO connection object

$values = array(true, false);

$sql = "UPDATE table SET boolean_column1 = ?, boolean_column2 = ?";

$stmt = $db->prepare($sql);

$stmt->execute($values);

?>

Expected result:
----------------
PDO will recognize that the values in the array are boolean, and will provide these values to the prepared statement as correctly-formatted booleans.

Actual result:
--------------
PostgreSQL 8.1.9 returns an error stating that the provided values for the booleans are not in the correct format, and may need to be type-casted.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-10-07 19:23 UTC] dac514 at hotmail dot com
This is happening to me too, OS X and CentOS, PHP 5.2.6

I am converting a web application from MySql to PostgreSQL and i've 
run into a roadbloack that is forcing me to find every single boolean 
query and manually binding it instead of benefiting from execute() 
$input_parameters. PITA! I discovered the explanation to this "bug" 
here:

http://ca.php.net/manual/en/pdostatement.execute.php#84990

As of 5.2.6 you still can't use PDOStatement->execute() 
$input_parameters to pass a boolean to PostgreSQL. To do that, you'll 
have to call bindParam() with explicit types for *each* parameter in 
the query.

Pseudo example, where col5 is of type boolean (i.e. tinyint(1) in 
MySQL)

$q = 'INSERT INTO table (col1, col2, col3, col4, col5, col6) VALUES (?
, ?, ?, ?, ?, ?)';
$v = array('foo1', 'foo2', 'foo3', foo4', false, 'foo6');
$st = $db->prepare($q);
$st->execute($v);

PostgreSQL complains and the script dies. Leaving me in the cold and I 
have to rewrite the code, which becomes excessively painful when the 
queries are dynamically generated. PostgreSQL workaround, boooooo!

$q = 'INSERT INTO table (col1, col2, col3, col4, col5, col6) VALUES (?
, ?, ?, ?, ?, ?)';
$v = array('foo1', 'foo2', 'foo3', foo4', false, 'foo6');
$st = $db->prepare($q);
$st->bindParam(1, $v[0]], PDO::PARAM_STR);
$st->bindParam(2, $v[1]], PDO::PARAM_STR);
$st->bindParam(3, $v[2]], PDO::PARAM_STR);
$st->bindParam(4, $v[3]], PDO::PARAM_STR);
$st->bindParam(5, $v[4]], PDO::PARAM_BOOL);
$st->bindParam(6, $v[5]], PDO::PARAM_STR);
$st->execute();

Can we get a fix for this soon?
 [2009-02-04 03:57 UTC] kenaniah at gmail dot com
This issue seems like it would be a very easy fix and can be reproduced without fail, regardless of server environment or PHP version. 

A fix would be greatly appreciated
 [2009-04-25 15:02 UTC] jani@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/


 [2009-05-03 01:00 UTC] 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-06-14 11:53 UTC] execut3 at gmail dot com
The issue is still not fixed, I'm with PHP 5.2.6 on Ubuntu and 
postgresql 8.3.

ERROR: invalid input syntax for type boolean: ""
 [2009-09-13 19:08 UTC] ant at specialops dot ath dot cx
I can still reproduce this on PHP 5.3.0 and PostgreSQL 8.4.1.
 [2009-09-13 20:55 UTC] kenaniah at gmail dot com
This is still reproducible on 5.3.0 paired with PG 8.x
 [2009-09-21 19:18 UTC] sjoerd@php.net
I think this is not a bug but a limitation of execute(): it assumes the values in the array are string. If you want it interpreted differently, you should call bindParam() with a data_type parameter.

I filed Bug #49614 "PDOStatement::execute assumes string values in array" to clarify the documentation.
 [2009-09-21 19:48 UTC] kenaniah at gmail dot com
In response to sjoerd, this may very well be a product of bad documentation, but that does not exclude the functional use case. One could reasonably claim that proper detection of parameter types should in fact be part of the functional definition of execute(). Virtually every database interface built on top of PDO works around this boolean "bug" and allows support for mixed content in the parameter array to a prepared statement.

IMHO, the PDO core should therefore be no different. Whether classified as a bug or a feature, I believe that this should still be addressed.
 [2009-09-22 18:31 UTC] sjoerd@php.net
Currently, every variable is assumed to be PDO_PARAM_STR. This patch changes this to PDO_PARAM_INT or PDO_PARAM_BOOL if the passed variable is a long or a bool, respectively.

http://www.gissen.nl/files/bug44597.patch

This may break existing scripts, which depend on false being converted to an empty string.
 [2009-10-04 18:46 UTC] Sjoerd@php.net
It is a bad idea to determine the PDO type from the PHP type.

First, it would break existing scripts which assume false is cast to an empty string, like this:
$a[] = strstr($foo, $bar); // may return false
$pdo->execute($a);

Secondly, the correct type to use is the type of the column, not the type of the PHP parameter. Consider the following query:
SELECT * FROM foo WHERE a=?
If a is a boolean, the parameter to execute() or bindBaram() should be converted to a boolean, no matter what the type of the passed parameter is.

Finally, one of PHP features is that it dynamically changes types. The type of a variable should be transparent to the user. Therefore, the behavior of a function should not change when it is passed another type.

To solve this, you should always specify the PDO type. Only the programmer knows which types the column in the query have, PHP can not determine this automatically.
 [2009-10-05 06:07 UTC] kenaniah at gmail dot com
In response to sjored, I believe there is huge disagreement over that issue, and I can personally speak for many of my colleagues in saying that. While I understand the repercussions of the patch, I would also like to point out that the example cited depends on buggy functionality in the first place. For that reason alone, I humbly submit that such a case should not be considered when weighing the implementation of the patch. 

On the second point, I believe we have another difference of opinion. All DBMSs perform their own casting on query parameters to match internal data types. For example, certain DBs honor the *string* 'False' as a boolean value, whereas a simple boolean cast performed in PHP would result in the said parameter evaluating to TRUE. In addition, other transformations may be applied to a passed parameter based on localization, custom data types, complex data types, etc. which vary from vendor to vendor and schema to schema. The role of PDO should be to transparently forward parameters to queries via their respective PHP and PDO-recognized data types. 

Now concerning the third point: PHP is a loosely-typed language. There is beauty in being able to provide mixed parameters to functions. There is nothing wrong with allowing a "mixed" parameter to be passed to a query either. Most DBs operate perfectly fine when receiving mixed parameters, and rightfully throw an error when something is amiss. Passing 'True', TRUE, or 1 to a boolean database field is perfectly acceptable in many systems.

And concerning your last statement: PHP should never under any circumstance attempt to think for the programmer. I expect a database abstraction layer to pass parameters along transparently *because* it is an abstraction layer. A programming language is not smarter than the one who implements it, and it is impossible to mitigate an error in logic. Rather, it is better for an error to be returned in order so that the erroneous logic be corrected, as their may be an even greater issue at hand. 

In closing, I believe that the implementation of a patch for this issue would be more inline with the general philosophy and design patterns that govern PHP than the current functionality today, to the point that I maintain my position that the current functionality is in fact buggy. I merely ask that PDO -- true to the form and function of an abstraction layer -- would pass parameters along in their respective data types without casting them to "string" of all things. I thank everyone who has participated in this issue thus far (especially sjored for the patch submitted), and am looking forward to this issue being resolved in an upcoming release.
 [2009-10-05 06:08 UTC] kenaniah at gmail dot com
*Sjoerd

My apologies on the incorrect spelling.
 [2010-10-12 12:20 UTC] ddebernardy at yahoo dot com
I'm in agreement with the last commenter. The PGSQL driver handles booleans that 
are typecast as integers properly, but not the actual booleans. These really ought 
to work out of the box.
 [2013-08-20 18:04 UTC] mike@php.net
-Status: Open +Status: Feedback
 [2013-08-20 18:04 UTC] mike@php.net
Please try using this snapshot:

  http://snaps.php.net/php5.4-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/

Seems to work correctly in recent versions.
 [2013-10-15 11:54 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 [2014-07-24 15:44 UTC] marcello dot nuccio at gmail dot com
As a workaround, I use the following expression to pass boolean values:

  $boolvar ?: 'false'

Very hackish, but that's the best workaround I have found.
 [2014-12-12 09:10 UTC] dczechowski at interia dot pl
I'm using php 5.6.4 on Windows and this bug is still present

test case:
<?php
//CREATE TABLE booltest(name varchar(30) not null, bool_col BOOLEAN NOT NULL)

$db = new PDO('pgsql:host=127.0.0.1 dbname=dev', 'postgres', '12Qwerty');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $db->prepare('INSERT INTO booltest (name, bool_col) VALUES (:name, :bool_col)');

$params = array(
    'normal boolean true' => true,
    'normal boolean false' => false, // this one will fail
    'string boolean true' => 'true',
    'string boolean false' => 'false',
    'int boolean true' => 1,
    'int boolean false' => 0    
);

foreach ($params as $key => $value){
    echo "$key...";
    $stmt->execute(array('name' => $key, 'bool_col' => $value));
    echo "OK! <br>";
}

echo "all OK!";
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 15:01:28 2024 UTC