PHP :: Bug #76639 PDO throws PDOException for no apparent reason (https://bugs.php.net/bug.php?id=76639)

demo.php

<?php
class Result {
  public $id;
  public $value;
}

$pdo = new PDO('mysql:host=localhost;dbname=mydatabase;','myuser','mypass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

$args = [':v'=>null];

$st = $pdo->prepare('select * FROM (
	SELECT 1 as id, 103 as value
	UNION
	SELECT 2 as id, 556 as value
) as faketable
WHERE ((:v is null) or (value = :v))');

$st->setFetchMode(PDO::FETCH_CLASS, 'Result');
$st->execute($args);
var_dump($st->fetchAll());

How to use:

We have a 'table' (named faketable) with 2 rows:

| id | value |
| -- | ----- |
|  1 |   103 |
|  2 |   556 |

We want to be able to select something by specifically it's value (e.g. 103, 556 or 283 of which the latter won't return any results ofcourse) OR select all values simply by specifying the argument as null to signify we don't care.

To be clear; the code above may be confusing but this is what's actually happening:

select *
from faketable
where ((:arg is null) or (value = :arg))

When :arg is 103, 556 in both cases 1 row is returned. And, consequently, when arg is 283 no rows are returned. And when null is passed into :arg then the 'filter' is effectively disabled. I use this all the time in more complicated situations:

select *
from customers
where ((:name is null) or (name = :name))
  and ((:city is null) or (city = :city))
  and ((:minbalance is null) or (balance > :minbalance))
  -- etc...

This has some advantages (like: only 1 queryplan in the cache) and not having to construct the query with lots of if-else statements. Any or all of the arguments :name, :city and :balance can have a value or can be null and the query will return the desired results.

Back to our example code above. You can change the value of :v on line 11 to anything you want it to be (103, 556, null, whatever) and the correct results will be returned.

Now... if you look closely at the output you'll notice that all properties of the returned objects are of type string:

array(2) {
  [0]=>
  object(Result)#4 (2) {
    ["id"]=>
    string(1) "1"
    ["value"]=>
    string(3) "103"
  }
  [1]=>
  object(Result)#5 (2) {
    ["id"]=>
    string(1) "2"
    ["value"]=>
    string(3) "556"
  }
}

That's because by default PDO "stringifies" stuff (apparently). There's a remedy for that.

• Make sure we use PHP >= 5.3 (I'm using 7.2.7-2+0~20180714182139.1+stretch~1.gbp3fcba8)
• Make sure we use mysqlnd (I'm using mysqlnd 5.0.12-dev - 20150407)
• PDO::ATTR_STRINGIFY_FETCHES should be false (though some suggest it's not MySQL related...)
• PDO::ATTR_EMULATE_PREPARES should be set to false to stop PDO emulating prepared statements but force it to let MySQL do the 'preparing'. This will be at the cost of an extra round-trip to MySQL but, hey, at least PHP will then know the types of the fields. Right?

Now, if we uncomment line 8 we get:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number

If we now change the

WHERE ((:v is null) or (value = :v))

to

WHERE (value = :v)

and we pass any integer value into :v we're golden. If you look closely at the results we even see that the types are now correctly int:

array(1) {
  [0]=>
  object(Result)#4 (2) {
    ["id"]=>
    int(1)
    ["value"]=>
    int(103)
  }
}

We can even pass null into :v but that won't return all rows (as expected, since we removed the or-part of the clause). As soon as we change it back to WHERE ((:v is null) or (value = :v)) it all breaks.

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number

As suggested by someone this doesn't help either. Binding the parameters one-by-one and specifying PDO::PARAM_NULL explicitly doesn't help at all. *sigh*

If you ask me (but what do I know) PDO uses the arguments and their types to determine if they are compatible with the mysql field types (or can be cast to be compatible). And since the argument passed is null PDO, ofcourse, can't determine the type. Again, if you ask me, PDO should use the mysql field types to determine the desired type and then see if the passed argument can be cast to that. But that's just my $0.02.

Apparently, this is "not a bug". While I agree with it being documented and I could've somehow found this (the exception happens at the execute() stage, not the prepare() stage so I didn't read the prepare documentation that closely) I strongly think a (much) better error message would have been much more helpful.

PHP documentation and I often, if not always always, don't seem to get along. Much of it is, IMHO, half-assed, people talking rubbish in the comments or giving excellent tips that should've been in the docs in the first place *, everything is always documented in another place than where I'd expect it and last but not least it looks like a unicorn and a hippo had a baby that shat itself. I guess I'm a spoiled little MSDN brat.

_{* And when I'm in the docs I clearly need help on something. The docs saying A but people saying B or C isn't very helpful; how do I decide which is correct(er) when I'm there for help in the first place?}

But besides that rant (which, naturally, will all be my problem and fault to begin with)...where was I? Oh, right, a helpful error message. How about PDO throws: "Named argument cannot be reused" or "Named argument cannot be used more than once" or something along those lines. That would've saved me hours of debugging, trips to the documentation, building a testcase, reporting it as a bug, getting scolded by some devs for "writing an essay" instead of them appreciating people taking the time to walk them trough the problem etc. and I would've been on my merry-fuckin'-way in 3 seconds after reading such error message.

You pick:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Named argument cannot be used more than once

You know what would be even better? If PDO allowed this behaviour. I don't see any reason why a named argument cannot be reused. Just so you know: MySQL supports it just fine:

set @x = null;

select * FROM (
	SELECT 1 as id, 103 as value
	UNION
	SELECT 2 as id, 556 as value
) as faketable
WHERE ((@x is null) or (value = @x));

Works fine. Try 103, 556 or 283 for @x. Works, again, as expected.

So, in summary, the workarounds:

1. Switch the code to positional instead of named arguments.

$v = null;
$st = $pdo->prepare('select ... WHERE ((? is null) or (value = ?))');
$st->execute([$v, $v]);

2. Pass the arg twice using 2 named arguments:

$v = null;
$args = [':v1'=>$v,':v2'=>$v];
$st = $pdo->prepare('select ... WHERE ((:v1 is null) or (value = :v2))');
$st->execute($args);

Either way; at the very least the error message could be improved. And, wishful thinking here, the best solution would simply be PDO supporting this scenario. It's not that outlandish.

But PAAMAYIM_NEKUDOTAYIM it. Bug is closed as "Not a bug" and the PHP devs are on their merry way again. Fine. At least this comment in my code now makes sense:

// Can't use :sourceid twice (https://bugs.php.net/bug.php?id=76639) so we use positional here instead of named