|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2018-07-18 16:22 UTC] janssen dot rob at gmail dot com
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
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Mon Dec 30 14:01:28 2024 UTC |
Well, you just copy-pasted your description from your gist, without editing it for the bug database. One paragraph in particular really gets messed up in the process: "As suggested by someone this doesn't help either." With the link gone, the "this" no longer has a referent. Everyone reading the report is left to ask "This? What's this? What doesn't help?" A better report of the problem could be: Summary: -------- PDO throws PDOException for no apparent reason [This assumes you didn't notice details that you later mentioned noticing: otherwise "for no apparent reason" could be replaced by, say, "when named parameter is bound to NULL."] Description: ------------ Setting PDO::ATTR_EMULATE_PREPARES to false causes PDO to throw when a null-parameter is passed. If it's not set (or defaults to true) it doesn't. But then all returned properties of a class are of type string which I don't want. Test Script: ------------ <?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); // If set to true, all results are found but values are all returned as strings $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // I don't _want_ them to be returned as strings. $pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false); // If bound to an integer there is no problem $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()); Expected result: ---------------- array(2) { [0]=> object(Result)#4 (2) { ["id"]=> int(1) ["value"]=> int(103) } [1]=> object(Result)#5 (2) { ["id"]=> int(2) ["value"]=> int(556) } } Actual result: -------------- Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter numberPHP/PDO's fetchAll() OVERWRITES result index when query has a fixed NUMBER as table row. DESCRIPTION =========== PHP/PDO fetchAll() extends the resulting array from a query by its column name. But when a column name is a NUMBER it OVERWRITES contents on that index number! When do problems occur? If you are faced with multiple queries unified by "UNION" sometimes it is necessary to set a row to a fixed number (e.g. to mark its origin): "SELECT Pid,0 FROM Person UNION SELECT Pid,1 FROM Company". EXAMPLE ======== Now when you put such a query (here just one) in PHP/PDO you may have: $stmt = $this->dbh->prepare("SELECT Pid,0 FROM Person"); $stmt->execute(); $rows = $stmt->fetchAll(); if (!empty($rows)) { foreach($rows as $row){ $pid = $row[0]; $num = $row[1]; } } When you debug the output by "print_r($rows)" you see that PHP/PDO extends the array retrieved by fetchAll() by its column names. So one can retrieve the data from it either by $row[i] or by $row[COLUMNNAME]. Now, when the column is a NUMBER it OVERWRITES the index an THAT number. In this case the second column is set to "0" and therefor it OVERWRITES row[0] where pids are stored. EXPECTED BEHAVIOR ================= 1,0 2,0 3,0 ... ACTUAL BEHAVIOR =============== 0,0 0,0 0,0 ... I have tested this on PHP 8.1.15 CGI/FastCGI (API:20210902) environment. But I have also tested it on my backup server (with other settings) where this also occurred.