Issues

ZF-7969: Where clause with value '\? does not handle binding of parameters properly

Description

I also tried this on ZF 1.9.3 and the bug exists. I have this setup:

  1. A table object that extends Zend_Db_Table_Abstract
  2. A model using that table object
  3. A query that goes like this:

$table = $this->_getTable(); $select = $table->select() ->where('Data_Division = ?', $category) ->where('Name = ?', $name);

Where $name is a string, and should accept any kind of patterns. It we enter '\? for name

'\? '\? '\? Single Quote: Slash: Question Mark Single Quote: Slash: Question Mark Single Quote: Slash: Question Mark

Here is the error:

2009-09-28T18:47:44+08:00 EMERG (0): 112233@10.38.248.110: Application error: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

0 C:\www\web_projects\xxx\library\Zend\Db\Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)

1 C:\www\web_projects\xxx\library\Zend\Db\Adapter\Abstract.php(464): Zend_Db_Statement->execute(Array)

2 C:\www\web_projects\xxx\library\Zend\Db\Adapter\Pdo\Abstract.php(232): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Table_Select), Array)

3 C:\www\web_projects\xxx\library\Zend\Db\Table\Abstract.php(1393): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Table_Select))

4 C:\www\web_projects\xxx\library\Zend\Db\Table\Abstract.php(1257): Zend_Db_Table_Abstract->_fetch(Object(Zend_Db_Table_Select))

5 C:\www\web_projects\techtuit\application\models\NameMnt.php(67): Zend_Db_Table_Abstract->fetchRow(Object(Zend_Db_Table_Select))

6 C:\www\web_projects\xxx\application\controllers\Ts13NameMntController.php(474): Default_Model_NameMnt->nameExists(2, ''\?', 4)

7 C:\www\web_projects\xxx\application\controllers\Ts13NameMntController.php(150): Ts13NameMntController->_checkData(Array, 'ADD')

8 C:\www\web_projects\xxx\library\Zend\Controller\Action.php(512): Ts13NameMntController->ajaxAddAction()

9 C:\www\web_projects\xxx\library\Zend\Controller\Dispatcher\Standard.php(288): Zend_Controller_Action->dispatch('ajaxAddAction')

10 C:\www\web_projects\xxx\library\Zend\Controller\Front.php(945): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))

11 C:\www\web_projects\xxx\library\Zend\Application\Bootstrap\Bootstrap.php(77): Zend_Controller_Front->dispatch()

12 C:\www\web_projects\xxx\library\Zend\Application.php(328): Zend_Application_Bootstrap_Bootstrap->run()

13 C:\www\web_projects\xxx\public\index.php(39): Zend_Application->run()

14 {main}

It seems that binding parameters does not work properly of could be that input is not properly escaped. I have tried it also on login, still the same error occurs.

I tried on some ZF made websites like:

http://indianapolismotorspeedway.com/user/login/ Email: test@test.com Password: test'\?

Application error occurs

Is this a problem with PDO or with ZF?

// I'm sorry if I don't follow your Issue tracking rules, but I really have to post this.

Comments

Please, post sql creation table.

Try take the generated string, object Zend_Db_Select.

echo $select; exit;

Try running on a client bd, the generated string by Zend_Db_Select.

I have seen this error when data contains a single quote and a question mark somewhere after the single quote.

CREATE TABLE users ( id int(11) unsigned NOT NULL auto_increment, user_name varchar(64) NOT NULL, email varchar(255) NOT NULL, password varchar(64) NOT NULL, active tinyint(1) NOT NULL default '1', PRIMARY KEY (id), UNIQUE KEY user_name (user_name) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 ;

$user_name = "te'st?name"; $password = "bobo";

$select = $this->_table->select(); $select->from($this->_name, '*'); $select->where('user_name = ?', $user_name); $select->where('password = ?', md5($password));

$stmt = $select->query(); // Exception thrown at this point.

If I echo the $select I get the following:

SELECT users.* FROM users WHERE (user_name = 'te\'st?name') AND (password = 'ca2cd2bcc63c4d7c8725577442073dde')

The MySQL query log shows that this statement is never sent to the server. But the query is well-formed and MySQL will execute it properly if I run it manually.

Try call

$this->_table->fetchAll($select);

http://framework.zend.com/manual/en/…

Try call

$this->_table->fetchAll($select);

http://framework.zend.com/manual/en/…

I get the same error either way since they both use a Statement.

The initial exception is a PDOException being thrown by the PDOStatement object within the Zend_Db_Statement_Pdo::_execute() method. So I suppose this is a PDO bug. The query produced by the Zend_Db_Select object is valid SQL.

The servers on which I have seen this error are both using PHP 5.2.6. I have since tested on a server with 5.2.10, and this bug does not occur.

I have recently tried it on PHP 5.2.10 (Zend Server CE) and the error does not occur. So therefore it is a PDO issue. I have upgraded our server already. Tnx