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

• Jasen: 4ドル is the INT column, but the UUID column is the one with ther error
• Jasen: it works here. please give an actual example.
• E.E: I'm sorry, I cannot copy in actual production code and post it here. It must be "paraphrased". I assure you the PHP code above for the integer works just fine. I'll add the code that deals with the UUID
• Jasen: can you make an example that can be executed, and that when executed demonstrates the problem you are encountering?
• E.E: I did make an example, and it didn't reproduce the problem. Looking into the values being passed again showed a different UUID being passed empty. Ended up looping through all parameters and setting empty to NULL. This solved the problem

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.

• E.E: It's production code for a client, so I was giving an example. Sorry for the typo. I did triple check and it was ==. That's also for the integer, which is no longer causing me any problems. The UUID lines are exactly the same, except the variable has a casted assignment from a different input: $p_uuid_3 = (string)$api_arguments['the_uuid']

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; 
}