ZF-538: PostgreSQL: Add explicit types to parameter binding

Issue Type: Patch Created: 2006-11-11T02:57:15.000+0000 Last Updated: 2013-02-13T12:04:00.000+0000 Status: Postponed Fix version(s): Reporter: Alex Adriaanse (alexadriaanse) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: - ZF-300

Attachments: - db_explicit_bind.patch


If you have a table in a PostgreSQL database that has a boolean column, you use fetchNew() from a Zend_Db_Table child class to create a new row, set (or leave) the boolean column in this row to false, and then call save() on this row object it will result in the following error:

<pre class="literal">
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22P02]: 
Invalid text representation: 7 ERROR:  invalid input syntax for type boolean: ""' 
in .../library/Zend/Db/Adapter/Abstract.php:156
Stack trace:
#0 .../library/Zend/Db/Adapter/Abstract.php(156): PDOStatement->execute(Array)
#1 .../library/Zend/Db/Adapter/Abstract.php(254): Zend_Db_Adapter_Abstract->query('UPDATE ...', Array)
#2 .../library/Zend/Db/Table.php(270): Zend_Db_Adapter_Abstract->update('', Array, ' =...')
#3 .../library/Zend/Db/Table/Row.php(161): Zend_Db_Table->update(Array, ' =...')
#4 .../library/: Zend_Db_Table_Row->save()

It turns out that this is caused by the false value being directly sent to the database as ''. Explicitly binding this parameter with an explicit type solves the problem. The enclosed patch will automatically set these parameter types.


Posted by Alex Adriaanse (alexadriaanse) on 2006-11-11T02:58:05.000+0000

Note that I have only tested this patch with PostgreSQL 8.1

Posted by Bill Karwin (bkarwin) on 2006-11-13T15:04:14.000+0000

Assigning to Bill Karwin

Posted by Bill Karwin (bkarwin) on 2006-11-13T15:22:37.000+0000

Changing fix version to 0.8.0.

Posted by Bill Karwin (bkarwin) on 2007-01-03T23:39:19.000+0000

Reformat sample in description to avoid wide-screen effect.

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

Please categorize/fix as needed.

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

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

Posted by Alex Adriaanse (alexadriaanse) on 2008-08-15T12:21:54.000+0000

I can confirm that this is still an issue in 1.5.3. I have updated my previous patch to work against 1.5.3. It probably works only with the PDO Adapter(s). It relies on Zend_Db_Statement::_bindParam() to automatically set the type, which it does for PDO.

Posted by Alex Adriaanse (alexadriaanse) on 2008-09-02T17:25:18.000+0000

I just discovered that when you assign a binary string value to a bytea column in PostgreSQL and save it to the database using Zend_Db_Table_Row, one may get an exception with the following message: {quote} SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xff HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". {quote}

I have added a new patch, zend_db_explicit_bind_v3.patch, to work around this issue. This updated patch requires binary values to be assigned using stream sources. When Zend_Db_Statement_Pdo sees a resource as one of the column values, it sets the column type to PDO::PARAM_LOB, which causes the value to get getting treated as a binary value.

There may be a better solution that autodetects column types by calling Zend_Db_Table::info() from within Zend_Db_Statement, but getting the Zend_Db_Table object passed to the Zend_Db_Statement seems to require some invasive changes.

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

Reassigning as Ralph is the maintainer of Zend_Db

Posted by Ralph Schindler (ralph) on 2009-01-09T10:51:17.000+0000

Researching the other issues releated to this ZF-200 & ZF-300

Posted by Bruno Friedmann (brunofriedmann) on 2009-08-09T05:02:33.000+0000

Working today with ZF svn ( 17420) and a table with a binary col, I've been hit by the same bug. Is there any work around for this. Parameters setting or something ?

With native pdo the update works like a charm.

I've try the given patch (v3) but wihout success ?

Posted by Bruno Friedmann (brunofriedmann) on 2009-08-14T04:56:07.000+0000

Hi Alexander, forget the trouble. I've just mis-spelled the $_primary. I've review the type error this morning with the help of an another pair of eyes.

Now I've just put correctly inside the model, and everything is working like a charm.

protected $_name = 'view_pdc_communes';
protected $_primary = 'commune_ofs';

Posted by Bruno Friedmann (brunofriedmann) on 2009-08-14T05:00:51.000+0000

sheet ! I was posting to the wrong ZF Issue, kill my last comment please. including this one

Posted by Ralph Schindler (ralph) on 2010-02-12T11:21:37.000+0000

Unassigning myself.

Is this still an issue? If so, can someone produce a small reproduction use case.

Posted by Alex Adriaanse (alexadriaanse) on 2010-02-12T13:09:25.000+0000

Yes, I'm still able to reproduce this issue in 1.9.7. Here's a test case.

<pre class="highlight">
CREATE TABLE test (pk serial PRIMARY KEY, testcol boolean NOT NULL);

<pre class="highlight">
class Test extends Zend_Db_Table_Abstract
    protected $_name = 'test';

<pre class="highlight">
// ... Initialize things ...

$test = new Test();
$row = $test->createRow();
$row->testcol = false;


<pre class="literal">
PHP Fatal error:  Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type boolean: ""' in .../library/Zend/Db/Statement/Pdo.php:234

Posted by Thomas Weidner (thomas) on 2010-03-21T10:33:07.000+0000

Reassigned to component maintainer

Posted by Volker Grabsch (vog) on 2011-02-22T10:11:40.000+0000

The issue still exists in 1.11.2. The problem is that PDO has different semantics compared to Zend_Db:

  1. The method PDOStatement::bindValue(...) always assumes a string type if the $type parameter is missing, while Zend_Db_Statement::bindValue(...) is expected to determine the database type from the value's PHP type.

  2. The method PDOStatement::execute($params) expects the values of $params to be all strings, while Zend_Db_Statement::execute($params) is expected to use the PHP types.

This means that the Zend_Db_Statement_Pdo implementation can only make use of:

  • PDOStatement::bindValue(...) with an explicit $type argument
  • PDOStatement::execute() without the $params argument

The new patch


fixes both issues, so all types including Boolean types will work properly. In contrast to the "zend_db_explicit_bind" patches, it only affects the Pdo adapter and does not force all other adapters to use explicit binds.

Posted by Volker Grabsch (vog) on 2011-02-24T02:42:31.000+0000

Alternative solution

Posted by Volker Grabsch (vog) on 2011-02-24T02:47:26.000+0000

I improved the alternative solution as mentioned in [my previous comment|#action_44538] and provided it via a separate issue entry:

ZF-11110 Zend_Db_Statement_Pdo uses unsuitable PDO methods

Sorry for the inconvenience. I would have provided the patch directly here, but the issue tracking system did't allow me to do that.

Posted by BenoƮt Durand (intiilapa) on 2011-02-24T11:59:47.000+0000

@Volker, you are allowed to send a file on a issue only if you have signed a CLA.

Posted by Volker Grabsch (vog) on 2011-04-05T12:38:18.000+0000

fix type issues (e.g. PostgreSQL/FALSE is encoded as empty string "''" instead of "false") by avoiding unsuitable PDO methods in Zend_Db_Statement_Pdo

also provide support for LOB on resource arguments

Posted by Volker Grabsch (vog) on 2011-04-05T12:39:49.000+0000

Please review my patch and consider it for inclusion into release 1.11.5:



Have you found an issue?

See the Overview section for more details.


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

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