PDO query binding string instead of return integer results
I have a table with id auto and auto and I was just testing a few scenarios when I stumbled upon a problem where PDO or mysql seems to convert a string to an integer when in an array. Does anyone know why?
If my request looks like this:
$check = $db->prepare("SELECT * FROM tbl_test WHERE id=:id");
$check->execute(array(':id'=>1));
it fetches 1 record - everything is fine, but if the query uses a string either by design or by mistake like this:
$check = $db->prepare("SELECT * FROM tbl_test WHERE id=:id");
$check->execute(array(':id'=>'1 OR id > 0'));
it still fetches the record with id = 1.
Should nothing be found? I am grateful that I should never admit the second scenario, but why does PDO / mysql convert the string to an integer and how does it do it?
source to share
This is a MySQL bug / oversight in integer conversion. Instead of throwing an error when specifying an invalid integer literal, it just issues a warning.
mysql> select '1'+0;
+-------+
| '1'+0 |
+-------+
| 1 |
+-------+
1 row in set (0,00 sec)
mysql> select '1 hello world'+0;
+-------------------+
| '1 hello world'+0 |
+-------------------+
| 1 |
+-------------------+
1 row in set, 1 warning (0,00 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1 hello world' |
+---------+------+---------------------------------------------------+
1 row in set (0,00 sec)
source to share
Good or bad that MySQL is meant to be maintained:
mysql> SELECT CASE
-> WHEN 123='123 pink elephants' THEN 'Equal'
-> ELSE 'Different' END
-> AS 'How are they?';
+---------------+
| How are they? |
+---------------+
| Equal |
+---------------+
1 row in set, 1 warning (0.00 sec)
As you can see, this triggers a warning:
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '123 pink elephants' |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
source to share
This is because it execute
creates somthing like:SELECT * FROM tbl_test WHERE id='1 OR id > 0'
$check = $db->prepare("SELECT * FROM tbl_test WHERE id=:id OR id>:id2");
$check->execute(array(':id'=>'1', ':id2' => 0));
or simply
$check = $db->prepare("SELECT * FROM tbl_test WHERE id>:id");
$check->execute(array(':id'=>'0'));
source to share