CODEWITHSUNDEEP
mysqlsql
Michael Samuel
Michael Samuel

Reputation: 3920

MYSQL query with leading zero returns the same result as without

I have a simple MySQL table with an Auto-increment id as the primary key.

Supposed I want to select the first id, I run the following query:

SELECT * FROM table WHERE id = '1'

I tried by mistake the following query and it returned the same result although it should return empty result.

SELECT * FROM table WHERE id = '01'

This leads to duplicate URLs on my site and many problems like invalid file references and such. Is this a bug?

Upvotes: 2

Views: 580

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

As already mentioned, the reason for the result you get is implicit type conversion: the '1' or '01' is converted to an integer so that it matches the type of id field before the comparison takes place.

A quick and dirty solution around this problem is to CAST the field, like:

SELECT *
FROM mytable 
WHERE CAST(id AS CHAR(10)) = '1'

Of course the best approach would be to sanitize your data of your application.

Demo here

Upvotes: 1

Dylan Su
Dylan Su

Reputation: 6065

If id is a integer, it is as expected.

Implicit type conversion to compatible type is done here.

mysql> select 1 = '01', 1 = '1', '1' = '01';
+----------+---------+------------+
| 1 = '01' | 1 = '1' | '1' = '01' |
+----------+---------+------------+
|        1 |       1 |          0 |
+----------+---------+------------+
1 row in set (0.00 sec)

Upvotes: 2

Related Questions