ZF-1457: Map native PHP booleans to SQL expressions

Issue Type: Bug Created: 2007-05-30T08:21:56.000+0000 Last Updated: 2012-08-31T09:12:11.000+0000 Status: Postponed Fix version(s): Reporter: Marcin Lulek (ergo14) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

  • zf-crteam-needsexpert

Related issues: - ZF-300



When attempting to insert or update data in {ostgreSQL database that contain boolean type values i get this response from PostgreSQL:

<pre class="highlight">
Invalid text representation: 7 ERROR:  invalid input syntax for type boolean: ""'

It occurs only when using boolean type values.

I have somewhere a temporary fix which seems to solve the issue:

In Zend_Db_Adapter_Pdo_Abstract in the beggining of query() function add :

<pre class="highlight">
    foreach($bind as $k => $v) {
        if(is_bool($v)) {
            $bind[$k] = $v ? 't' : 'f';

seems to fix the issue.


Posted by Bill Karwin (bkarwin) on 2007-05-30T12:34:17.000+0000

This can't be a blocker, because there is a workaround: you can convert PHP bool variables to the corresponding the 't' or 'f' values in your application code.

Posted by Bill Karwin (bkarwin) on 2007-07-09T15:33:19.000+0000

Reword summary.

This applies to all RDBMS, not just PostgreSQL, because there may be no implicit mapping from PHP native boolean values to SQL expressions. This could also apply to PHP native null values.

Posted by James Dempster (letssurf) on 2007-08-16T10:39:16.000+0000

surely some kind of solution can be worked into the framework for this, like maybe some adapter level casting?

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:50.000+0000

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2008-04-18T13:11:49.000+0000

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Posted by Wil Sinclair (wil) on 2008-12-04T12:53:15.000+0000

Reassigning as Ralph is the maintainer of Zend_Db

Posted by Mathias Seiler ( on 2009-01-06T06:04:17.000+0000

Please mark this as "needed" (or so). It's not just nice to have, but essential when dealing with this kind of database and data.

The workaround by converting bool variables into 't' or 'f' chars/strings is not very nice ... Can I help fixing this issue somehow? We really need this :)


Posted by Ralph Schindler (ralph) on 2009-01-09T12:21:35.000+0000

Datatype issues

Posted by Ralph Schindler (ralph) on 2009-05-22T11:29:39.000+0000

So, how would this work:

<pre class="highlight">
$db->query('SELECT * FROM some_table WHERE some_column = :someBoolValue', array('someBoolValue' => true));

There is no way for the statement to know that some_column is the BOOLEAN pgsql datatype? Does it matter? Should we blindly map all php booleans to 't' or 'f' pre-bind and execute?

After looking at the code, it appears this should be applied in the _execute() and bindValue() methods of Zend_Db_Statement_Pdo, does that sound about right?


Posted by Ralph Schindler (ralph) on 2009-05-22T12:20:57.000+0000

I have not tested it yet, but would this work:

<pre class="highlight">
Index: library/Zend/Db/Statement/Pdo.php
--- library/Zend/Db/Statement/Pdo.php   (revision 15638)
+++ library/Zend/Db/Statement/Pdo.php   (working copy)
@@ -229,6 +229,14 @@
         try {
             if ($params !== null) {
+                // ensure PGSQL will cast the php boolean correctly
+                if ($this->_adapter->getConnection()->getAttribute(PDO::ATTR_DRIVER_NAME) == 'PDO_PGSQL') {
+                    foreach ($params as $paramName => $paramValue) {
+                        if (is_bool($paramValue)) {
+                            $params[$paramName] = ($paramValue) ? 'true' : 'false';
+                        }
+                    }
+                }
                 return $this->_stmt->execute($params);
             } else {
                 return $this->_stmt->execute();

Posted by C Snover (snover) on 2009-07-14T22:52:55.000+0000

Of the three RDBMS I use regularly (PostgreSQL, MySQL, SQLite), all three of them support the use of the integer values 1 and 0 on boolean columns to correspond to TRUE and FALSE, so that would be my recommendation for how to properly map PHP booleans. I've had workarounds in place for this issue since at least 1.0 on my codebase so I am very surprised to see that this bug is still open.

PostgreSQL is not the only RDBMS that needs this fix. The current method of casting also generates errors in MySQL when it is running in ANSI mode. "SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'boolean_column' at row 1"

Here is the fix I have been using for a year with no problems:

<pre class="highlight">
--- Zend/Db/Adapter/Pdo/Abstract.php    (revision 16719)
+++ Zend/Db/Adapter/Pdo/Abstract.php    (working copy)
@@ -220,6 +220,9 @@

         if (is_array($bind)) {
             foreach ($bind as $name => $value) {
+                if (is_bool($value)) {
+                    $bind[$name] = $value = $value ? 1 : 0;
+                }
                 if (!is_int($name) && !preg_match('/^:/', $name)) {
                     $newName = ":$name";

Posted by Marc Bennewitz (private) (mabe) on 2009-10-21T11:15:44.000+0000

Since MySQL 4.1 the constants TRUE & FALSE were added:…

I think it is better to use the constants in MySQL too because if a real boolean datatype will be added in the future it could be used directly.

Posted by C Snover (snover) on 2010-02-10T13:37:08.000+0000

Hey Matthew,

Instead of constantly pushing this to "next mini release", how about committing the patch I wrote in July of last year to close the bug?


Posted by Dolf Schimmel (Freeaqingme) (freak) on 2010-02-10T14:53:22.000+0000

Please note that Matthew didn't update this version specifically. Now that 1.10.1 has been released, all issues that were assigned that version, were upped by one.

The reason it's taking so long is probably that Ralph will have to review and evaluate the patch very thoroughly since this issue is basically asking for BC breaks. I can however imagine that with ZF2.0 in mind, this will be less of a problem now.

Posted by Ralph Schindler (ralph) on 2011-02-17T13:02:25.000+0000

This will be addressed in ZF 2.0's Db component

Posted by Doug Needham (doug-needham) on 2012-02-24T21:42:04.000+0000

It is one thing to address it in ZF 2.0's Db component, but given that this remedy was mentioned a year ago and a number of us are still using 1.11, combined with the fact that a fix was placed here 2.5 years ago by C Snover (who had been using it for a year at that time)... is it unreasonable to have expected a fix in the 1.x ZF code???

Have you found an issue?

See the Overview section for more details.


© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.