ZF-6463: Binding params in where clause

Description

Hello, There is a big problem in fetch* methods on Zend_Db_Table and update, delete methods on Zend_Db_Adapter_Abstract because the where conditions parameters are not binded, they are just escaped and replaced into the condition. Here is an example:


$myTable = new Zend_Db_Table_MyTable();
$result = $myTable->fetchAll(array('label = ?' => 'MyLabel'));

What is the query executed?


SELECT MY_TABLE.* FROM MY_TABLE WHERE (label = 'MyLabel')

Wich is wrong!! the query should be


SELECT MY_TABLE.* FROM MY_TABLE WHERE (label = ?)

and MyLabel send as parameter.

For fetch methods the problem is in Db_Table_Abstract. The '?' is just replaced using the _whereExpr() function.

For update/delete methods the problem is in Db_Adapter_Abstract.

Why this problem is so important? Because in Oracle we have a limited number of unique queries to execute and this bug will create many queries in db instead of only one.

Comments

But as of ZF 1.8.0 Zend_Db_Table_Select does allow bind variables, and this is now the approach recommended by the documentation (see http://framework.zend.com/manual/en/…):


$myTable = new Zend_Db_Table_MyTable();
$result = $myTable->fetchAll($myTable->select()->where('label=:LABEL')
                                         ->bind(array(':LABEL'=>'MyLabel')));

This is the approach we are using with Oracle at the moment.

However, I think than now that Zend_Db_Select supports bind variables, Zend_Db should be upgraded to take advantage of them. For example, in Zend_Db_Select::_where() replace:


        if ($value !== null) {
            $condition = $this->_adapter->quoteInto($condition, $value, $type);
        }

with something like:


        if ($value !== null) {
            if ($this->_adapter->supportsParameters('named')) {
                $bind=$this->getBind();
                $bindVariable = ':' . count($bind)+1;
                if (isset($bind[$bindVariable]) then {
                    require_once 'Zend/Db/Select/Exception.php';
                    throw new Zend_Db_Select_Exception("Invalid use of both named and positional parameters');
                }
                $condition = str_replace('?', $bindVariable, $condition);
                $bind[$bindVariable] = $value;
                $this->bind($bind);
            } else {
                $condition = $this->_adapter->quoteInto($condition, $value, $type);
            }
        }

This would make Zend_Db_Table_Select (and Zend_Db_Table::fetchAll()) generate queries using bind parameters for the old - $result = $myTable->fetchAll(array('label = ?' => 'MyLabel')); - syntax too.

For Db_Table this can be a solution, but what about Db_Adapter (update and delete function) where i need to bind parameters in condition?

Raul, I agree that Zend_Db_Table_Adapter_Abstract::_whereExpr() should be updated to allow bind parameters in the where clause.

We have been using bind parameters on Zend_Db_Table for a while (initially using our own subclass of Zend_Db_Table), but I haven't found the time to look at ..Adapter::update and ::delete yet.

My guess is that something similar to my suggestion for Zend_Db_Select::_where() above should work, although the ::update is using positional parameters if possible, so you would need to default to using those, instead of named parameters. You would also need to add $bind as an optional parameter to ::_whereExpr() so that it could update the bind array.

It sounds like this is not a bug as its not preventing execution of queries. So I can changing to feature request.

Also, I am mostly unclear on what is being asked to be done and exactly what is asked to be supported. Can you please provide simple use case that demonstrate what is not possible and what you think should be possible?

@Ralph Schindler

Indeed the execution of queries is not affected. This is rather an optimization issue with some of the RDBMS (Oracle is one of them). So here is the problem. Let's say I have a Zend Db Table instance and I want to update a row in the table. I can do the following

$callId = 5; $calls = new Demo_Db_Table_Calls(); $where = $calls->getAdapter()->quoteInto( 'call_id = ?', $callId ); $data = array( 'is_winner' => 1 ); $calls->update( $data, $where );

The query sent out to the database server is:

UPDATE "winning_call"."call_id" SET "is_winner" = ? WHERE (call_id = '5');

The query sent out to the database server should be:

UPDATE "winning_call"."call_id" SET "is_winner" = ? WHERE (call_id = ?)

Sending queries with hard coded literals like this:

UPDATE "winning_call"."call_id" SET "is_winner" = ? WHERE (call_id = '5') UPDATE "winning_call"."call_id" SET "is_winner" = ? WHERE (call_id = '6') UPDATE "winning_call"."call_id" SET "is_winner" = ? WHERE (call_id = '7') UPDATE "winning_call"."call_id" SET "is_winner" = ? WHERE (call_id = '8')

will make the Oracle database server to think that it's dealing with unique queries (when in fact they are not, except the literal value) and create a new execution plan (which is costly in terms of resources) for each of this queries. The bottom line is the queries will take longer. If queries like:

UPDATE "winning_call"."call_id" SET "is_winner" = ? WHERE (call_id = ?)

were to be sent out to the Oracle database server an execution plan will be created ONCE (the first time) and then subsequent queries will benefit from cached execution plan.

In large application where every second lost matters and speed is of the essence, this is a big problem.

To be fair I apparently googled a possible solution to this problem, but it involves editing an oracle configuration file and as we know not everybody is in control of their database server to be able to benefit from it. You can read about it here http://oracle.com/technology/oramag/….

I should mention that this problem is also present when making use of the Zend_Db_Abstract::delete() method and possibly others.

Thank you.

This problem is still present in 1.8 and i don't think that this can be categorized as minor priority, because can be a big problem on large applications or even a blocker in applications with limited resources.

Any status on this issue. I am curious to know whether there is a possibility to use binding for update/delete actions?

I am using Zend 1.7.7 version with Oracle 10g

-Asif

ZF 1.9.6 (Not Resolved)

I use this - $myTableObject is Zend_Db_Table object

$select1 = $myTableObject->select()->limit(10,0)->order('upper(name) asc')->where(" name like :name ")->bind(array(':name'=>'5%')); $data = $myTableObject->fetchAll($select1);

And it throws an exception -

Exception information: Message: Invalid bind-variable name ':name'

0 C:\ZF-1.8.1\library\Zend\Db\Statement.php(114): Zend_Db_Statement->_parseParameters('SELECT `re_prop...')

1 C:\ZF-1.8.1\library\Zend\Db\Adapter\Mysqli.php(381): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'SELECT `re_prop...')

2 C:\ZF-1.8.1\library\Zend\Db\Adapter\Abstract.php(467): Zend_Db_Adapter_Mysqli->prepare('SELECT `re_prop...')

3 C:\ZF-1.8.1\library\Zend\Db\Table\Abstract.php(1509): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Table_Select))

A dump of Zend_Db_Select object $select1 shows that the information exists in the class

["_bind:protected"] => array(1) { [":name"] => string(2) "5%"

["_bind:protected"] => array(1) { [":name"] => string(2) "5%" }

I suppose this binding needs to be implemented to enable queries that are more optimized for Oracle as well as MySql. We are working with 100's of thousands of rows and is a bottle-neck.

This is also a real problem when using the Sqlsrv Adapter with Microsoft SQL Server and trying to delete or update something querying with a unicode string.

As the _quote method of Zend_Db_Adapter_Sqlsrv quotes only in a non-unicode matter. (For Microsoft SQL Server it's required to prepend a "N" before a string to flag it as unicode) So we must use prepared statements for every query to the server which could contain a unicode string.

Querying a japanese database row:

Correct: $myTableObject->select()->where('myColumn = ?', '漢字');

Fails: $myDatabase->query('SELECT * FROM myTable WHERE myColumn = ' . $myDatabase->quote('漢字')); As it's transformed to SELECT * FROM myTable WHERE myColumn = '漢字', which is not a Unicode string in Microsoft SQL Server way. Query should be SELECT * FROM myTable WHERE myColumn = N'漢字'

So missing binding for updates and deletes it's not possible to manipulate these rows without losing the option to replace the adapter and database system.

People, could you please update the documentation until this bug/feature request is fixed? I just wanted to use bind variables with the DB_ADAPTER->delete method and found out (by interpreting the source and searching the issue tracker), that this problem exists.

I also have problems with binding in wheres... and have 2 additional questions: 1. Why there is a difference between the "fetch" behavior between the adapter and the db_Table?? the first supports binding in "fetch" functions while the latter- does not.

  1. I see in the documentation that in the past there was a way to bind parameters to the where db select. see here: http://framework.zend.com/manual/en/…): and here is a sample of the code example: $rows = $table->fetchAll( $table->select() ->where('bug_status = :status') ->bind(array(':status'=>'NEW') ->order('bug_id ASC') ->limit(10, 0) ); But I don't want to use this old version....