PDO query binding string instead of integer returns results

Question

I have a table with and auto increment id and was just testing a few scenarios when I stumbled across a problem whereby PDO or mysql seems to convert a string to an integer when in an array. Does anyone know why?

If my query is as follows:

$check = $db->prepare("SELECT * FROM tbl_test WHERE id=:id");
$check->execute(array(':id'=>1));

it retrieves 1 record - all fine, but if the query uses a string either by design or mistake as follows:

$check = $db->prepare("SELECT * FROM tbl_test WHERE id=:id");
$check->execute(array(':id'=>'1 OR id > 0'));

it still retrieves a record with id=1.

Surely nothing should be found? I appreciate I should never allow the 2nd scenario to happen but why is PDO / mysql converting the string to an integer and how is it doing it?

bobflux · Accepted Answer · 2017-03-31 10:55:10Z

4

This is a MySQL bug/oversight in string to integer conversion. Instead of raising an error when given an incorrect integer literal, it simply 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)

answered Mar 31, 2017 at 10:55

bobflux

11.6k3 gold badges31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2875878 Over a year ago

Many thanks. Interestingly even if I bind a parameter and use 'PDO::PARAM_INT' it makes no difference - just issues a warning.

CD001 Over a year ago

I was quite surprised to find bindParam doesn't throw an Exception when you attempt to bind a string with PDO::PARAM_INT as well... seems it follows PHPs general String > Int conversion rules.

CD001 Over a year ago

... actually bindParam doesn't even do what I thought: stackoverflow.com/questions/833510/… (did some further digging and deleted my incorrect answer when it was pointed out) @user2875878

bobflux Over a year ago

Weeelll... it's PHP. Anyone expecting PHP to do the right thing with types is on drugs...

Álvaro González Over a year ago

@peufeu However, in this precise situation it behaves exactly as MySQL does (demo) and it isn't more insane than JavaScript (demo).

Álvaro González · Accepted Answer · 2017-03-31 10:53:26Z

For good or bad, that's how MySQL is designed to behave:

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, though, it 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)

ponury-kostek · Accepted Answer · 2017-03-31 10:50:19Z

0

It's because execute create 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 just

$check = $db->prepare("SELECT * FROM tbl_test WHERE id>:id");
$check->execute(array(':id'=>'0'));

answered Mar 31, 2017 at 10:50

ponury-kostek

8,0835 gold badges27 silver badges33 bronze badges

Comments

Lorna Mitchell · Accepted Answer · 2017-03-31 10:53:28Z

0

With prepared statements and placeholders, the database knows to expect a value that suits the column type. I would expect that it sees your numeric id column and casts the '1 or id > 0' to a number - so you just get the 1.

answered Mar 31, 2017 at 10:53

Lorna Mitchell

2,00614 silver badges24 bronze badges

Collectives™ on Stack Overflow

PDO query binding string instead of integer returns results

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related