Saturday, July 7, 2012

Incorrect replacement of prepared statements with numeric values in PDO

1. Returned by tester

Found interesting bug yesterday: absolutely correct query show correct result when run it without preparing and incorrect, if use prepared statements.
SELECT `operator`,`region` FROM `phone_code` WHERE `code` = :code AND `from_num` <= :from_num AND `to_num` >= :to_num
Replacement by strtr function shows that all is correct in replacements pairs array. It's unbelievable, but  it's true. Conditions <= and => just doesn't work with prepare.
Thanks to Mikhail Khmelev, tester in our team, he found differences in results (both results looks almost correct in particularly case).

I've tried to change names of statements, change SQL query, and even change spaces in query - I've spent around hour to find reason of this bug.

And, as you read in title, bug is in PHP: PDO numeric values of prepared statements replaces as strings, with quotation:

SELECT `operator`,`region` FROM `phone_code` WHERE `code` = '921' AND `from_num` <= '1234567' AND `to_num` >= '1234567'
So it's became obviously, why this query doesn't work correctly - numeric fields in DB can't be compared with text values by <= or => operators.


2. We have to fix it 

How to fix this behavior of PHP? I don't want to write my own "prepare" function - native should work faster and correctly and test much more heavily. But I have to find workaround, because program should work.

First solution was replace all numeric values of statements by strtr or str_ireplace, but tests shows that names of statements can be replaced incorrectly, when we have :statement and :statement_something_other. So I decide to replace by strtr with replacement pairs array and in add in that array all statements, not only numeric - not numeric statements will be replaced by itself and this replacement will exclude sub-strings replacement.
Final code you can find here: https://github.com/jamm/DataMapper/blob/master/MySQL/Gateway.php#L233 (function getCorrectPreparedQuery).

This bug exists in PHP since 2008: https://bugs.php.net/bug.php?id=44639
Thanks to PHP for this interesting bug and wasted time. Hope it will be fixed in next ten years.

No comments:

Post a Comment