php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79132 PDO re-uses parameter values from earlier calls to execute()
Submitted: 2020-01-16 15:34 UTC Modified: 2020-01-17 12:41 UTC
From: love at sickpeople dot se Assigned:
Status: Verified Package: PDO related
PHP Version: 7.4.1 OS:
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2020-01-16 15:34 UTC] love at sickpeople dot se
Description:
------------
When executing the same statement, missing parameter values are filled in with values from earlier executions instead of throwing an error.

This requires emulated PREPARE to be disabled.

Test script:
---------------
$host = '';
$db = '';
$user = '';
$pass = '';

$options = [
    PDO::ATTR_EMULATE_PREPARES => false, /* required */
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  ];

$pdo = new PDO("mysql:host=$host; dbname=$db; charset=utf8mb4", $user, $pass, $options);

$stmt = $pdo->prepare('select ? a, ? b');

$set = [
    ['a', 'b'],
    [1 => 'y'], /* first parameter is missing. Note the array key */
    ['x'], /* second parameter is missing */
  ];

foreach ($set as $params) {

    try {
        var_dump($stmt->execute($params), $stmt->fetchAll(PDO::FETCH_ASSOC));
      }
    catch (Throwable $error) {
        echo $error->getMessage() . "\n";
      }

  }



Expected result:
----------------
When emulated PREPARE is enabled, an error "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens" is thrown. The same error should be thrown instead of values being re-used.

Actual result:
--------------
bool(true)
array(1) {
  [0]=>
  array(2) {
    ["a"]=>
    string(1) "a"
    ["b"]=>
    string(1) "b"
  }
}
bool(true)
array(1) {
  [0]=>
  array(2) {
    ["a"]=>
    string(1) "a"
    ["b"]=>
    string(1) "y"
  }
}
bool(true)
array(1) {
  [0]=>
  array(2) {
    ["a"]=>
    string(1) "x"
    ["b"]=>
    string(1) "y"
  }
}


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-01-16 18:00 UTC] cmb@php.net
-Assigned To: +Assigned To: cmb
 [2020-01-17 12:25 UTC] cmb@php.net
-Status: Assigned +Status: Verified -Assigned To: cmb +Assigned To:
 [2020-01-17 12:25 UTC] cmb@php.net
I can confirm the reported behavior for PDO_MySQL (using mysqlnd).
If emulated prepares are active (these are not supported by all
drivers), the behavior seems to be correct; however, executing the
prepared statement again without passing $input_parameters for
consecutive calls, will also reuse the parameters which have been
bound for the first call.  It's not totally clear to me, whether
this is done deliberately.  If it is, this issue would not be an
issue of PDO_Core.

Anyhow, the problem with PDO_MySQL native prepares is that
`params_given` is increased for each parameter, but never reset.
Actually, that counter might not be needed at all, since we could
just use the number of elements in the `bound_params` hashtable to
check for a sufficient number of parameters[1].

For PDO_SQLite, which does not support emulated prepares, we would
have to call sqlite3_clear_bindings() before re-binding (i.e. on
PDO_PARAM_EVT_FREE); however, the behavior of SQLite3 wrt. unbound
parameters is somewhat special anyway, since it assumes NULL has
been bound.  To be consistent with (most?) other drivers, the case
of unbound parameters would have to be caught explicitly.

While I agree that these inconsistencies should be fixed, this
might better be postponed to PHP 8 for BC reasons.  After all, a
lot of code might deliberately use the automatic rebinding of
formerly passed values.

[1] <https://github.com/php/php-src/blob/php-7.3.13/ext/pdo_mysql/mysql_statement.c#L483>
 [2020-01-17 12:41 UTC] cmb@php.net
> […] we could just use the number of elements in the
> `bound_params` hashtable to check for a sufficient number of
> parameters.

That would not solve the issue reported as bug #79131, though.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Wed Oct 28 15:01:23 2020 UTC