php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #62998 [irrelevant - misunderstanding]
Submitted: 2012-09-02 14:46 UTC Modified: 2014-05-18 23:19 UTC
From: php at richardneill dot org Assigned:
Status: Not a bug Package: PostgreSQL related
PHP Version: 5.3.16 OS: Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
6 + 48 = ?
Subscribe to this entry?

 
 [2012-09-02 14:46 UTC] php at richardneill dot org
Description:
------------
pg_query_params($conn,$sql,$params)  is supposed to correctly handle NULL->SQL_NULL. It doesn't do this (despite the documentation); instead it breaks the query.

Documentation
"If parameters are used, they are referred to in the query string as $1, $2, etc. params specifies the actual values of the parameters. A NULL value in this array means the corresponding parameter is SQL NULL. "


Test script:
---------------
Here's a simple example:


$result = pg_query_params ($conn, 
   "select CASE WHEN NULL is $1 then 'it is null' ELSE 'it is not null' END",
    array(NULL) );
echo pg_last_error($conn);
print_r (pg_fetch_assoc($result));

This query fails, because the parameter $1 is not actually passed as NULL, instead it seems to be the empty string.

My SQL is definitely right:
 pg_query_params ($conn, 
 "select CASE WHEN NULL is NULL then 'it is null' ELSE 'it is not null' END",
 array() );

works fine, to return the string "it is null".

Expected result:
----------------
It should be possible to pass SQL_NULL to the database in a parameter.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-06-05 14:16 UTC] mbeccati@php.net
-Status: Open +Status: Not a bug
 [2013-06-05 14:16 UTC] mbeccati@php.net
Looks like you got it backwards. "IS NULL" is a string literal and you can't use a parameter to construct it. Try this instead:

$result = pg_query_params ($conn,
   "select CASE WHEN $1::text is null then 'it is null' ELSE 'it is not null' END",
    array(NULL) );
echo pg_last_error($conn);
print_r (pg_fetch_assoc($result));
 [2014-05-18 02:37 UTC] php at richardneill dot org
Not sure if this is now an RFE, but pg_query_params() should IMHO, allow NULL.

For example, the following works fine.


$sql    = "INSERT into authors (name, age) VALUES ($1, $2)";
$params = "Austen, 250";
pg_query_params ($sql, $params);


However, what if we don't know the author's age, and want to insert a null value, using the same function? i.e.


$sql    = "INSERT into authors (name, age) VALUES ($1, $2)";
$params = "Dickens, NULL";
pg_query_params ($sql, $params);


This Ought to work, generating the SQL:
INSERT into authors (name, age) VALUES ('Dickens', NULL)";


However, it actually generates the invalid:
INSERT into authors (name, age) VALUES ('Dickens', 'NULL')";
 [2014-05-18 21:14 UTC] php at richardneill dot org
(Sorry, I appear to be unable to type. The example should of course read:)

$sql    = "INSERT into authors (name, age) VALUES ($1, $2)";
$params = array ("Dickens", NULL);
pg_query_params ($sql, $params);
 [2014-05-18 23:19 UTC] php at richardneill dot org
-Summary: pg_query_params() doesn't handle NULL +Summary: [irrelevant - misunderstanding]
 [2014-05-18 23:19 UTC] php at richardneill dot org
OK, so I appear to be having a day of complete mental aberration.
Please ignore everything I just wrote; sorry for wasting your time.

To confirm: it looks like pg_query_params() does handle input NULLS correctly, so long as they are not in a Where clause. In that case, this is really an SQL problem distinguishing "where X = 7" from "where X is null" from "where X = null"
from "where X = 'null'".
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Jun 22 06:01:31 2024 UTC