Parameterized Queries in PostgreSQL using PHP fail to handle null values

Question

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 :)

Answer 1

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.

Answer 2

The problem ended up being an unexpected empty value in one of the other UUIDs. Simply didn't notice it was referring to a different UUID after explictly setting one of them to null.

The following code sets all parameters to NULL where they are empty. I believe that parameterized query code in PHP handles empty by replacing it with '', when it explicitly needs NULL.

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