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

  • $4 is the INT column, but the UUID column is the one with ther error
    – Jasen
    Commented Aug 5, 2018 at 20:35
  • 1
    it works here. please give an actual example.
    – Jasen
    Commented Aug 5, 2018 at 20:43
  • 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
    – E.E
    Commented Aug 6, 2018 at 21:08
  • 1
    can you make an example that can be executed, and that when executed demonstrates the problem you are encountering?
    – Jasen
    Commented Aug 7, 2018 at 22:11
  • 1
    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
    – E.E
    Commented Aug 8, 2018 at 20:06

2 Answers 2

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.

  • 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'];
    – E.E
    Commented Aug 4, 2018 at 23:44

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; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.