php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #76639 PDO throws PDOException for no apparent reason
Submitted: 2018-07-18 16:22 UTC Modified: 2018-07-19 15:16 UTC
Votes:1
Avg. Score:3.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: janssen dot rob at gmail dot com Assigned:
Status: Not a bug Package: PDO Core
PHP Version: 7.2.7 OS: SMP Debian 4.9.110-1 (2018-07-05
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
21 - 5 = ?
Subscribe to this entry?

 
 [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


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-07-18 16:30 UTC] janssen dot rob at gmail dot com
My apologies; the correct test-script URL is:

https://gist.github.com/RobThree/4902595d5f8f096f98969e23520d0857
 [2018-07-18 16:33 UTC] danack@php.net
-Status: Open +Status: Feedback
 [2018-07-18 16:33 UTC] danack@php.net
That bug report is an essay. I've read it twice and can't extract what you're trying to report.

Please read http://sscce.org/ and then submit:

* a short script that shows the problem
* the expected/hoped for output>
* the actual output
 [2018-07-18 16:41 UTC] janssen dot rob at gmail dot com
> That bug report is an essay. I've read it twice and can't extract what you're trying to report.

Or... you value the time and effort your customers take to report a bug and take a second, maybe longer, look at the problem. I know it's kind of a long(er) story, it's just hard to summarize.

But hey, if you want the short of it: 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.

????
 [2018-07-18 18:24 UTC] requinix@php.net
Does PDO/pdo_mysql even support reusing named parameters in non-emulated queries? Does
  WHERE ((:v is null) or (value = :v))
work at all with an integer :v? I suspect not, but there's enough magic going on that I can't be sure.
 [2018-07-18 18:39 UTC] pmmaga@php.net
-Status: Feedback +Status: Not a bug
 [2018-07-18 18:39 UTC] pmmaga@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

From the PDO::prepare docs (http://php.net/manual/en/pdo.prepare.php):
You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

So this is expected behavior. Reproducing a comment found in http://paul-m-jones.com/archives/243#comment-740 :
"It was never technically supported. It just happened to work for the drivers that most people use. This is (not very clearly) documented, and something that I mention in my PDO talks.

The change was made for two reasons; first and foremost, if you re-use the same variable in a bind, it is possible to induce a crash when using some drivers. It?s not possible to guarantee to do the right thing, and having a way to trigger a crash can sometimes be used as an attack vector for a security exploit.

The second reason is that of portability. Some drivers would internally perform this check and error out. If you code against the drivers that don?t enforce this, then your code won?t work on those that don?t.

I?m sorry that this bit you; it?s hard to notice a change in the handling of ?bad? uses if you don?t use it that way."
 [2018-07-18 18:55 UTC] requinix@php.net
How about making PDO warn when it happens? Didn't look like that would be too difficult when I checked earlier.
 [2018-07-18 19:01 UTC] pmmaga@php.net
From this (https://github.com/php/php-src/blob/a76661a4a2b129eb4d098abfc45fbed46066636b/ext/pdo_mysql/tests/pdo_mysql_prepare_native_dup_named_placeholder.phpt#L135-L137) test, the execute seems to throw a warning. Unless you mean warning during the prepare.
 [2018-07-18 22:39 UTC] a at b dot c dot de
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 number
 [2018-07-19 05:16 UTC] requinix@php.net
-Package: PDO MySQL +Package: PDO Core
 [2018-07-19 05:16 UTC] requinix@php.net
> Unless you mean warning during the prepare.
Mostly I'm aiming for a clearer error message - "Invalid parameter number" is a bit confusing when they're named.
But yes, I think prepare() would be the best time for it: that's where the problem actually occurs and where it would be fixed by the user.
 [2018-07-19 11:09 UTC] pmmaga@php.net
That makes sense to me. But maybe it would be better to file a new request ticket? Otherwise feel free to reopen this.
 [2018-07-19 15:14 UTC] requinix@php.net
Just created the request. Decided on something separate so there's no need to read a few screenfuls of comments here to understand it.
 [2018-07-19 15:15 UTC] requinix@php.net
Looks like the automatic cross-referencing isn't working yet. Request #76647
 [2018-07-19 15:16 UTC] requinix@php.net
...actually it is working but it doesn't process the bug description. I had never noticed that.
 [2023-02-09 08:59 UTC] maaaddog at gmx dot at
PHP/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.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 07:01:28 2024 UTC