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

Description


$select = new Zend_Db_Select($db);
$select->from('f_actualities');
$select->where('act_name = ?', null);
$db->query($select)->fetchAll();

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


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.

Comments

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

instead


$param = $request->getParam('arg'); // some parameter

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

accounting write


$param = $request->getParam('arg'); // some parameter

$select = $db->select();

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

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.

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.

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 ?

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

Added test case to the patch so everyone can test it

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 :

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

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

When you do this:


$select->where('something = ?', null);

its effectively the same as you doing this:


$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" [http://dev.mysql.com/doc/refman/…], 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:


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

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

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 :


<?php

set_include_path('/home/sylvain/Documents/ZendFramework-1.7.4/library');

require_once('Zend/Loader.php');

Zend_Loader::registerAutoload();

class Application {

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

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

}

Application::setUp();

$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.

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".

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