php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #73234 emulated statements let value dictate parameter type
Submitted: 2016-10-03 15:19 UTC Modified: 2016-10-07 14:28 UTC
From: adambaratz@php.net Assigned: adambaratz (profile)
Status: Closed Package: PDO Core
PHP Version: Irrelevant OS:
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: adambaratz@php.net
New email:
PHP Version: OS:

 

 [2016-10-03 15:19 UTC] adambaratz@php.net
Description:
------------
See test script. I would expect both statements to return NULL. Given the looseness of PHP's type system, this feels appropriate and safer. There's a related issue where numbers can end up treated as strings.

Test script:
---------------
$db = new PDO(...);

$stmt = $db->prepare("SELECT :null");
$stmt->bindValue(':null', null, PDO::PARAM_NULL);
$stmt->execute();
var_dump($stmt->fetchAll()); // NULL

$stmt = $db->prepare("SELECT :null");
$stmt->bindValue(':null', 0, PDO::PARAM_NULL);
$stmt->execute();
var_dump($stmt->fetchAll()); // 0


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-10-07 14:28 UTC] adambaratz@php.net
-Assigned To: +Assigned To: adambaratz
 [2016-10-10 22:16 UTC] adambaratz@php.net
Automatic comment on behalf of adambaratz
Revision: http://git.php.net/?p=php-src.git;a=commit;h=32b6154a61fae820386527f3019f8c5937fc5d27
Log: Fix #73234: Emulated statements let value dictate parameter type
 [2016-10-10 22:16 UTC] adambaratz@php.net
-Status: Assigned +Status: Closed
 [2017-01-12 09:12 UTC] krakjoe@php.net
Automatic comment on behalf of adambaratz
Revision: http://git.php.net/?p=php-src.git;a=commit;h=32b6154a61fae820386527f3019f8c5937fc5d27
Log: Fix #73234: Emulated statements let value dictate parameter type
 [2018-08-10 21:29 UTC] asherkin at limetech dot io
Just as a note for any other archaeologists, this "safer" change caused near-total data loss in production thanks to a faulty query that had been masked by the previous behaviour here (thankfully, backups).

In a DB with a string ID column, and a query that did "DELETE FROM table WHERE id = ?", the parameter was incorrectly being bound with PARAM_INT.

As this code was before, it worked fine as the type of the parameter was indeed a string, but after the change it got coerced to 0, which caused MySQL to coerce every id in the table to an integer as well (most of them also ending up as 0), and thus caused the condition to match almost every row.

(Queries very simplified as an example, the original was an IN clause, hence no defensive LIMIT 1.)

The new behaviour is definitely saner, but with the chance of catastrophic data loss thanks to MySQL's odd type casting priority, in hindsight it probably should've been opt-in.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Nov 10 12:01:30 2024 UTC