PostgreSQL PHP – Parameterized Queries Fail to Handle Null Values

PHPpostgresqlpostgresql-9.6query

I'm using PHP and PostgreSQL on OpenBSD 6.1, with pg_prepare and pg_execute to effect parameterized queries. 

$rs = pg_prepare($con, base64_encode($query), $query);
$rs = pg_execute($con, base64_encode($query), $query_parameters);

It's working for quite a lot of queries, but raises PHP errors in some cases where I'm passing, or attempting to pass, a null value to a UUID field. In attempt to have some data validation, I'm specifying for each value its datatype.

insert into dummy(text_1,ipv4_2,uuid_3,int_4) values($1::varchar(32),$2::inet,$3::uuid,$4::integer);

This works just fine for all cases, except null values. The schema does allow for nulls in all fields. Integers were causing me problems until I cast the input to an integer, and actually assigned a null when there was no value. PHP:

$p_int_4 = (int)$api_arguments['int_from_form'];
if ($p_int_4 == '' || is_null($p_int_4)) $p_int_4 = null;

The parameters are passed as an array with variables being used:

$query_parameters = array($p_text_1,$p_ipv4_2,$p_uuid_3,$p_int_4);

When I try to set the UUID variable to null, I still get a pg_execute error:

pg_execute(): Query failed: ERROR:  invalid input syntax for uuid: ""

How can I still have the benefits of data validation, while also sending a null to the database? Two ways way I can see to get it to pass is to take away the casting to UUID, which seems to be a pretty good layer of defense against SQL injection attacks, and casting to varchar instead and hoping PostgreSQL can "handle it" and get the casting correct on its side.

** UPDATE **

I've been "paraphrasing" production code, which I cannot paste here obviously, and it works for everything except UUIDs. This is code I'm using for the UUID, with just the variable names changed:

$p_uuid = (string)$this->api_arguments['ADD_UUID'];
if ($p_uuid == '' || is_null($p_uuid)) $p_uuid = null;

The SQL statement is this simple:

insert into dummy(myuuid) values($1::uuid);

* Final Update *

It's more accurate to say that PHP doesn't handle empty values well for integer and UUID datatypes when passing them as parameters to pg_execute.

The following code sets all parameters to NULL where they are empty. 

for ($x = 0; $x < count($query_parameters); $x++) { 
  if ($query_parameters[$x] == '' || is_null($query_parameters[$x])) 
    query_parameters[$x] = null; 
}

Thanks Jasen. Constructing a working example led me to the bug 🙂

Best Answer

if ($p_int_4 = '' || is_null($p_int_4)) $p_int_4 = null;

The equality operator in PHP is ==. Your code uses =, and will always set $p_int_4 to an empty string as a result of this mistake.

Related Question