Bug #73234 [Com]: emulated statements let value dictate parameter type
Edit report at https://bugs.php.net/bug.php?id=73234&edit=1
ID: 73234
Comment by: asherkin at limetech dot io
Reported by: adambaratz@php.net
Summary: emulated statements let value dictate parameter type
Status: Closed
Type: Bug
Package: PDO Core
PHP Version: Irrelevant
Assigned To: adambaratz
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2017-01-12 09:12:46] 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
------------------------------------------------------------------------
[2016-10-10 22:16:30] 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-03 15:19:52] 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
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=73234&edit=1
Thread (3 messages)