php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #65421 'field=NULL' should be 'field IS NULL'
Submitted: 2013-08-08 13:26 UTC Modified: 2013-08-12 16:29 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: schwalenberg1013 at gmail dot com Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.5.1 OS: Linux
Private report: No CVE-ID: None
 [2013-08-08 13:26 UTC] schwalenberg1013 at gmail dot com
Description:
------------
When using PDO bindParam when it receives a NULL value the ultimate result that 
it sends to MySql is field=NULL instead of field IS NULL.

Test script:
---------------
                $editStmt = $db->prepare("UPDATE employee SET startDate=:startDate,department=:department,jobTitle=:jobTitle WHERE name=:emplName AND startDate=:oldStartDate AND department=:oldDepartment AND jobTitle=:oldJobTitle LIMIT 1");
                $editStmt->bindParam('startDate',$startDate);
                $editStmt->bindParam('department',$department);
                $editStmt->bindParam('jobTitle',$jobTitle);
                $editStmt->bindParam('emplName',$emplName);
                $editStmt->bindParam('oldStartDate',$oldStartDate);
                $editStmt->bindParam('oldDepartment',$oldDepartment);
                $editStmt->bindParam('oldJobTitle',$oldJobTitle);
// catchException($editStmt,$db);
                $editStmt->execute();
                $editResult = $editStmt->fetchAll(PDO::FETCH_ASSOC);

/* If any one of these variables happens to be NULL then the whole statement fails because it will output, for example, 'AND department=NULL' instead of 'AND department IS NULL' */

Expected result:
----------------
MySql will not except field=NULL properly it should be field IS NULL

Actual result:
--------------
I can do this in MySql however when using PDO I have to create an entirely 
different statement myself for the exceptions of NULL values which becomes very 
tedious a problem I never had using the depreciated mysql functions or when using 
mysqli.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-08-12 16:29 UTC] ab@php.net
-Status: Open +Status: Not a bug
 [2013-08-12 16:29 UTC] ab@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

This won't work as you describe, a binding method will always affect only the 
defined container, so the question mark or :someval. Say if you have x=:y, 
bindParam will never touch the x= part. But what you can do is build the statement 
string dynamically and use the third bindParam argument on condition. Also you 
could try to explicitly cast string if that's ok with your database design.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Sun Mar 07 16:01:23 2021 UTC