Bug #79132 [Ver]: PDO re-uses parameter values from earlier calls to execute()

Edit report at https://bugs.php.net/bug.php?id=79132&edit=1 ID: 79132 Updated by: cmb@php.net Reported by: love at sickpeople dot se Summary: PDO re-uses parameter values from earlier calls to execute() Status: Verified Type: Bug Package: PDO related PHP Version: 7.4.1 Block user comment: N Private report: N New Comment: > […] 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. Previous Comments: ------------------------------------------------------------------------ [2020-01-17 12:25:58] 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-16 15:34:09] 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" } } ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=79132&edit=1