Issues

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

Description

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:

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.

Comments

Note that I have only tested this patch with PostgreSQL 8.1

Assigning to Bill Karwin

Changing fix version to 0.8.0.

Reformat sample in description to avoid wide-screen effect.

Please categorize/fix as needed.

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

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.

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.

Reassigning as Ralph is the maintainer of Zend_Db

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

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 ?

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';

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

Unassigning myself.

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

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


CREATE TABLE test (pk serial PRIMARY KEY, testcol boolean NOT NULL);

class Test extends Zend_Db_Table_Abstract
{
    protected $_name = 'test';
}

// ... Initialize things ...

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

Result:

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

Reassigned to component maintainer

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

"Zend_1.11.2_fix_Zend_Db_Statement_Pdo.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.

Alternative solution

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.

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

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

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

Zend_1.11.4_PDO_support_for_LOB_and_avoid_unsuitable_PDO_methods.patch

Thanks.