php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #47199 pg_delete fails on NULL
Submitted: 2009-01-23 11:46 UTC Modified: 2010-12-09 19:08 UTC
From: andrew at labyrinth-it dot co dot uk Assigned: felipe
Status: Closed Package: PostgreSQL related
PHP Version: 5.3.5 OS: Linux (Fedora)
Private report: No CVE-ID:
 [2009-01-23 11:46 UTC] andrew at labyrinth-it dot co dot uk
Description:
------------
pg_delete uses incorrect syntax for NULL columns. The code generated compares values with "col = NULL" instead of "col IS NULL". As a result, the row is not matched so is not deleted.

Reproduce code:
---------------
<?php

$db = pg_connect("host=localhost dbname=andrew user=andrew password=andrew");

$sql = 'SELECT * FROM demo';
$qry = pg_query($sql);
while ($row = pg_fetch_array($qry, null, PGSQL_ASSOC)) {
  print_r($row);
  pg_delete($db,'demo',$row);  //1
  print(pg_delete($db,'demo',$row,PGSQL_DML_STRING))
}
pg_free_result($qry);

$qry = pg_query($sql);
while ($row = pg_fetch_array($qry, null, PGSQL_ASSOC)) {
  print_r($row);   //2
}
pg_free_result($qry);

pg_close($db);


Expected result:
----------------
The first loop should read and display all rows in the table, and then delete the rows. The second loop should not display any data at all.

---
Array
(
    [id] => 1
    [col1] => 
)
DELETE FROM demo WHERE id=1 AND col1 IS NULL;


Actual result:
--------------
When this runs, the second loop displays results for tables that have NULL columns at the start of the run.

---
Array
(
    [id] => 1
    [col1] => 
)
DELETE FROM demo WHERE id=1 AND col1=NULL;
Array
(
    [id] => 1
    [col1] => 
)


Patches

bug47199_patch.diff (last revision 2010-04-12 03:01 UTC) by ewgraf at gmail dot com)

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-05-19 15:31 UTC] andrew at labyrinth-it dot co dot uk
I am using:
PHP 5.2.10-dev (cli) (built: May 19 2009 15:40:36) 
and still get the same error result.

This error is also in the pg_update function, where NULl values are compared with "= NULL" rather than "IS NULL" in the WHERE part of the generated SQL.
 [2009-05-19 15:34 UTC] andrew at labyrinth-it dot co dot uk
For completeness, I get the error running on Fedora 10 with Postgres 8.3.5
 [2009-05-31 06:27 UTC] andrew at labyrinth-it dot co dot uk
Sorry, I think I posted my reply in the wrong place, so let me try again.

I have just downloaded and tested the latest PHP version:
PHP 5.2.10RC2-dev (cli) (built: May 31 2009 07:16:36)

With this version I still get the same error. The Postgresql version I am testing against is:
PostgreSQL 8.3.5 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.3.2 20081007 (Red Hat 4.3.2-6)

The call to:
print(pg_delete($db,'demo',$row,PGSQL_DML_STRING)) 
still prints out:
DELETE FROM demo WHERE id=1 AND col1=NULL;

The final test should use the SQL language "IS NULL" test rather than "=NULL" which never evaluates to true.

The same problem exists if using pg_update, which produces:
UPDATE demo SET id=2,col1='Two' WHERE id=1 AND col1=NULL;

Again, "col1=NULL" will never evaluate to true using SQL, and the test col1 IS NULL should be used instead.
 [2010-02-10 16:40 UTC] ewgraf at gmail dot com
Patch for this bug:

http://news.php.net/php.internals/46974
 [2010-11-06 18:43 UTC] felipe@php.net
Automatic comment from SVN on behalf of felipe
Revision: http://svn.php.net/viewvc/?view=revision&amp;revision=305130
Log: - Fixed bug #47199 (pg_delete() fails on NULL)
  patch by: ewgraf at gmail dot com
 [2010-11-06 18:44 UTC] felipe@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: felipe
 [2010-11-06 18:44 UTC] felipe@php.net
This bug has been fixed in SVN.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

Thanks for the patch!
 [2010-12-09 18:52 UTC] andrew at labyrinth-it dot co dot uk
Hi,
I have just downloaded the latest 5.2 version from SVN, and tested this again. 
It is still 
not working. I have created a more complete test case that can be used.

===========Start===========
<?php

$linenum=0;
function _print($str) {
  global $linenum;
  print($linenum++."\t".$str."\n");
}

_print('PHP_VERSION : '.PHP_VERSION);
_print('PHP_OS : '.PHP_OS);

$db = pg_connect("dbname=test");
if (! $db) {
  die("Not connected");
}

//Create a test table
$create = 'create table testcase (id integer, col1 varchar(20))';
$result = pg_query($db,$create);

//Insert some test data
$row = array('id'=>1, 'col1'=>'I');
_print(pg_insert($db,'testcase',$row,PGSQL_DML_STRING));
pg_insert($db,'testcase',$row);
$row = array('id'=>2, 'col1'=>'');
_print(pg_insert($db,'testcase',$row,PGSQL_DML_STRING));
pg_insert($db,'testcase',$row);
$row = array('id'=>3, 'col1'=>null);
_print(pg_insert($db,'testcase',$row,PGSQL_DML_STRING));
pg_insert($db,'testcase',$row);

//Selects
$search = array('col1'=>'I');
_print(pg_select($db,'testcase',$search,PGSQL_DML_STRING));
$result = pg_select($db,'testcase',$search);
if ($result) {
  _print($result[0]['id']."\t".$result[0]['col1']);
} else {
  _print('Not found');
}
$search = array('col1'=>'');
_print(pg_select($db,'testcase',$search,PGSQL_DML_STRING));
$result = pg_select($db,'testcase',$search);
if ($result) {
  _print($result[0]['id']."\t".$result[0]['col1']);
} else {
  _print('Not found');
}
$search = array('col1'=>null);
_print(pg_select($db,'testcase',$search,PGSQL_DML_STRING));
$result = pg_select($db,'testcase',$search);
if ($result) {
  _print($result[0]['id']."\t".$result[0]['col1']);
} else {
  _print('Not found');
}

//Updates
$data = array('id'=>5);
$condition = array('col1'=>'I');
_print(pg_update($db,'testcase',$data,$condition,PGSQL_DML_STRING));
$condition = array('col1'=>'');
_print(pg_update($db,'testcase',$data,$condition,PGSQL_DML_STRING));
$condition = array('col1'=>null);
_print(pg_update($db,'testcase',$data,$condition,PGSQL_DML_STRING));

//Deletes
$condition = array('col1'=>'I');
_print(pg_delete($db,'testcase',$condition,PGSQL_DML_STRING));
$condition = array('col1'=>'');
_print(pg_delete($db,'testcase',$condition,PGSQL_DML_STRING));
$condition = array('col1'=>null);
_print(pg_delete($db,'testcase',$condition,PGSQL_DML_STRING));

//clean up
$drop = 'drop table testcase';
$result = pg_query($db,$drop);

=============End===============

The generated output from this script is:

0       PHP_VERSION : 5.2.16-dev
1       PHP_OS : Linux
2       INSERT INTO testcase (id,col1) VALUES (1,'I');
3       INSERT INTO testcase (id,col1) VALUES (2,'');
4       INSERT INTO testcase (id,col1) VALUES (3,NULL);
5       SELECT * FROM testcase WHERE col1='I';
6       1       I
7       SELECT * FROM testcase WHERE col1='';
8       2
9
10      Not found
11      UPDATE testcase SET id=5 WHERE col1='I';
12      UPDATE testcase SET id=5 WHERE col1='';
13      UPDATE testcase SET id=5 WHERE col1=NULL;
14      DELETE FROM testcase WHERE col1='I';
15      DELETE FROM testcase WHERE col1='';
16      DELETE FROM testcase WHERE col1=NULL;


Notes:
Line 0: php version 5.2.16-dev - latest version just downloaded.
Line 9: No SELECT string is generated if the parameter contains NULL
Line 10: No data is found when searching for NULL
Line 13: Still using col1=NULL instead of col1 IS NULL
Line 16: Still using col1=NULL instead of col1 IS NULL

Has the patch been merged with the version at snaps.php.net?
 [2010-12-09 18:58 UTC] felipe@php.net
Hello, the fix was committed to 5.3.x branch and trunk, not to 5.2.x.
 [2010-12-09 19:08 UTC] andrew at labyrinth-it dot co dot uk
-PHP Version: 5.2.10 +PHP Version: 5.3.5
 [2010-12-09 19:08 UTC] andrew at labyrinth-it dot co dot uk
OK, Sorry. I have downloaded and tested 5.3.5 and this is working perfectly! 
Thanks for fixing this.
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Wed Apr 23 14:02:33 2014 UTC