php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #48251 Mysql selects/deletes rows it shouldn't when using IS NULL
Submitted: 2009-05-12 22:06 UTC Modified: 2009-05-21 01:00 UTC
From: dan at manifestwebdesign dot com Assigned:
Status: No Feedback Package: MySQL related
PHP Version: 5.2.9 OS: linux
Private report: No CVE-ID: None
 [2009-05-12 22:06 UTC] dan at manifestwebdesign dot com
Description:
------------
Mysql ignores the IS NULL in the WHERE statement when the query is executed from php.

In the example I give, the AddressID is auto_increment primary key.  As you can see from the output, the address ID gets a value of 65 for the inserted row.  I then DELETE WHERE AddressID IS NULL and the row is removed.  It will also return the row when I execute SELECT * FROM Address WHERE AddressID IS NULL.

It happens in with mysql_query, PDO::exec() or PDO::query() and with or without ` around the table name.

Reproduce code:
---------------
//PHP 5.2.9 on Hostmonster.com
mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db(DB_NAME);
mysql_query("INSERT INTO `Address` (StreetAddress) VALUES ('blah')");
$result = mysql_query("SELECT * FROM `Address`");
while($row = mysql_fetch_assoc($result)){
	print_r($row);
}

mysql_query("DELETE FROM `Address` WHERE `AddressID` IS NULL");

$result = mysql_query("SELECT * FROM `Address`");
while($row = mysql_fetch_assoc($result)){
	print_r($row);
}

Expected result:
----------------
Array ( [AddressID] => 65 [NameOrCompany] => [StreetAddress] => blah [City] => [State] => [Zip] => [Country] => [DateTimeCreated] => [DateTimeModified] => ) Array ( [AddressID] => 65 [NameOrCompany] => [StreetAddress] => blah [City] => [State] => [Zip] => [Country] => [DateTimeCreated] => [DateTimeModified] => ) 

Actual result:
--------------
Array ( [AddressID] => 65 [NameOrCompany] => [StreetAddress] => blah [City] => [State] => [Zip] => [Country] => [DateTimeCreated] => [DateTimeModified] => ) 

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-05-13 22:36 UTC] johannes@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc. If the script requires a 
database to demonstrate the issue, please make sure it creates 
all necessary tables, stored procedures etc.

Please avoid embedding huge scripts into the report.

PHP simply passes the query through to MySQL so the problem is somewhere there. PLease provide a complete example including all CREATE TABLE statements and version information (mysql server, mysql clien library, ...)
 [2009-05-21 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Dec 10 03:01:27 2024 UTC