ZF-3379: Passing null value to Zend_Db_Select::where($cond, $value) leads to error in SQL

Issue Type: Bug Created: 2008-06-04T01:19:33.000+0000 Last Updated: 2012-11-21T08:09:55.000+0000 Status: Resolved Fix version(s): - 1.7.6 (02/Mar/09)

Reporter: Ji?í Tomek (katulus) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db_Select

Related issues: - ZF-5699

Attachments: - dirty_hack.diff


<pre class="highlight">
$select = new Zend_Db_Select($db);
$select->where('act_name = ?', null);

Code aboe raises Zend_Db_Statement_Exception with message "SQLSTATE[HY093]: Invalid parameter number: no parameters were bound".

Problem is in file Zend/Db/Select.php

<pre class="highlight">
785: if ($value !== null) {
786:     $condition = $this->_adapter->quoteInto($condition, $value, $type);
787: }

If null is passed as value, condition remains unchanged and so '?' remains there. This leads to SQL error above. It is quite a problem, because when using Zend_Db_Table::findby() and there is no matching row for some parent row, null value is supplied for it's primary key and it leads to above error.

This bug was not present in 1.5.0 but strated in 1.5.1.


Posted by Dmitry Pereslegin (dvp) on 2008-08-15T16:58:52.000+0000

This issue precludes the possibility of using "<=>" operator in MySQL


<pre class="highlight">
$param = $request->getParam('arg'); // some parameter

$select = $db->select()->where('column <=> ?', $param);

accounting write

<pre class="highlight">
$param = $request->getParam('arg'); // some parameter

$select = $db->select();

if (is_null($param)) {
    $select->where('column IS NULL');
} else {
    $select->where('column = ?', $param);

Posted by Ji?í Tomek (katulus) on 2008-09-06T04:30:15.000+0000

Your solution for using null value works, but it is not core of the problem. As I wrote, problem is with Zend_Db_Table::findby(). If there is no row for in parent table, null value is supplied for underlying statement. Solving this issue requires extra database statement to check, if parent table contains any rows and use Zend_Db_Table::findby() only if it contains something. Regarding your comment, this issue is probably related to Zend_Db_Table class where this should be checked.

Posted by Sylvain Filteau (sylvain) on 2009-02-10T10:19:51.000+0000

I don't know if it's a correct solution but when I tried to remove the if statement and it works.

But since I can't run the unit tests, I can't investigate more.

Posted by Sylvain Filteau (sylvain) on 2009-02-13T09:18:37.000+0000

I can't upgrade from ZendFramework 1.0 to ZendFramework 1.7 because of this issue.

I searched for a workaround but I use findDependentRowset with new a new row object too often to patch all my code base.

Maybe someone have seen a workaround that I didn't found ?

Posted by Sylvain Filteau (sylvain) on 2009-02-17T08:10:52.000+0000

I know it's a really dirty trick... but it work with my code in issue ZF-5699

Posted by Sylvain Filteau (sylvain) on 2009-02-25T11:34:14.000+0000

Added test case to the patch so everyone can test it

Posted by Sylvain Filteau (sylvain) on 2009-02-25T11:36:52.000+0000

When you pass the test case, the problem seems to be solved but there is another thing in the profiler that bugs but I can't track it. PHPUnit just tell me this :

<pre class="literal">
1) testProfilerPreparedStatementWithParams(Zend_Db_Profiler_Pdo_MysqlTest)
Undefined offset:  0

2) testProfilerPreparedStatementWithBoundParams(Zend_Db_Profiler_Pdo_MysqlTest)
Undefined offset:  0

I tested my patch with sqlite and pdo_mysql

Posted by Ralph Schindler (ralph) on 2009-02-25T13:10:53.000+0000

I'm not sure there is an issue here actually.

When you do this:

<pre class="highlight">
$select->where('something = ?', null);

its effectively the same as you doing this:

<pre class="highlight">
$select->where('something = ?');

and the latter is semantically wrong, and destined to fail. You are passing in a PHP null value, and expecting it to be used as the SQL null value. You are assuming that there is a direct mapping between PHP null and SQL NULL - which is not the case. The select object in this case cannot determin what you "mean" by the php null, thus, no replacement is made.

In SQL "something IS NULL" is not the same as "something = NULL" […], and technically, Zend_Db_Select shouldn't be inferring meaning and re-writing SQL unless its a vendor specific implementation issue (for example limit in MSSQL) and not a PHP -> SQL issue (what is null in php and what is null in SQL).

That said, I think the proper logic (in your code) here would be this:

<pre class="highlight">
if ($value === null){
   $select->where('something IS NULL');
} else {
   $select->where('something = ?', $value);

I am inclined to close this as "Not An Issue"

Posted by Sylvain Filteau (sylvain) on 2009-02-25T14:02:10.000+0000

Maybe the problem was not explained appropriately.

In the code I wrote in the issue ZF-5699 (a duplicate of this issue, I think), I use a newly created row instance of Zend_Db_Table_Row_Abstract to find a dependent rowset :

<pre class="highlight">




class Application {

        public static function setUp() {
                $db = Zend_Db::factory('pdo_mysql', array(
                        'dbname' => 'test',
                        'host' => 'localhost',
                        'username' => 'root',
                        'password' => ''

                $db->query('DROP TABLE IF EXISTS projects');
                $db->query('DROP TABLE IF EXISTS components');

                $db->query('CREATE TABLE projects (id INTEGER AUTO_INCREMENT, name VARCHAR(15), PRIMARY KEY (id));');
                $db->query('CREATE TABLE components (id INTEGER AUTO_INCREMENT, name VARCHAR(15), fk_parent INTEGER, PRIMARY KEY (id));');


class Projects extends Zend_Db_Table_Abstract {

        protected $_name = 'projects';

        protected $_dependentTables = array('Components');


class Components extends Zend_Db_Table_Abstract {

        protected $_name = 'components';

        protected $_referenceMap = array(
                'Parent' => array(
                        'columns' => 'fk_parent',
                        'refTableClass' => 'Projects',
                        'refColumns' => 'id'



$tbl_projects = new Projects();
$new_project = $tbl_projects->createRow();

$components = $new_project->findDependentRowset('Components');
echo count($components) . "\n";

This code fail because of the if statement in Zend_Db_Select.

The same problem happen for the same reason when using Zend_Db_Table::findby() as explained in the description of this issue.

Maybe my solution is not appropriate but this is an issue and I think it should not be closed.

Posted by Ralph Schindler (ralph) on 2009-02-25T14:33:11.000+0000

The above use case is not related to this issue. For that, I will leave my comments in issue ZF-5699.

As for this issue, for the reasons I described above in the comments, I am marking as "not an issue".

Posted by nike shox (nike shox) on 2012-11-21T08:09:55.000+0000

Med buzz Rundt utgivelsen, some folk stylte opp as dring as 24 timer. Jeg today limited to some few utvalgte amerikanske forhandlere, håpet many på "Miami Nights" would Baere lik long-term importance as already classic "South Beach" LeBron 8 Mid.Where it is a major time sneaker utgivelse, it is always kontrovers.

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.