Bug #76639 [NEW]: PDO throws PDOException for no apparent reason

From: janssen dot rob at gmail dot com Operating system: SMP Debian 4.9.110-1 (2018-07-05 PHP version: 7.2.7 Package: PDO MySQL Bug Type: Bug Bug description:PDO throws PDOException for no apparent reason Description: ------------ See the test-script that reproduces the problem. Explanation (not sure how much formatting is allowed) below but can be found at the gist in formatted form as well. === 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 helpt 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. Test script: --------------- https://gist.github.com/RobThree/c61d782606c24a55f4d491c6f869d689 -- Edit bug report at https://bugs.php.net/bug.php?id=76639&edit=1 -- Try a snapshot (PHP 5.4): https://bugs.php.net/fix.php?id=76639&r=trysnapshot54 Try a snapshot (PHP 5.5): https://bugs.php.net/fix.php?id=76639&r=trysnapshot55 Try a snapshot (trunk): https://bugs.php.net/fix.php?id=76639&r=trysnapshottrunk Fixed in SVN: https://bugs.php.net/fix.php?id=76639&r=fixed Fixed in release: https://bugs.php.net/fix.php?id=76639&r=alreadyfixed Need backtrace: https://bugs.php.net/fix.php?id=76639&r=needtrace Need Reproduce Script: https://bugs.php.net/fix.php?id=76639&r=needscript Try newer version: https://bugs.php.net/fix.php?id=76639&r=oldversion Not developer issue: https://bugs.php.net/fix.php?id=76639&r=support Expected behavior: https://bugs.php.net/fix.php?id=76639&r=notwrong Not enough info: https://bugs.php.net/fix.php?id=76639&r=notenoughinfo Submitted twice: https://bugs.php.net/fix.php?id=76639&r=submittedtwice register_globals: https://bugs.php.net/fix.php?id=76639&r=globals PHP 4 support discontinued: https://bugs.php.net/fix.php?id=76639&r=php4 Daylight Savings: https://bugs.php.net/fix.php?id=76639&r=dst IIS Stability: https://bugs.php.net/fix.php?id=76639&r=isapi Install GNU Sed: https://bugs.php.net/fix.php?id=76639&r=gnused Floating point limitations: https://bugs.php.net/fix.php?id=76639&r=float No Zend Extensions: https://bugs.php.net/fix.php?id=76639&r=nozend MySQL Configuration Error: https://bugs.php.net/fix.php?id=76639&r=mysqlcfg