Why MySQL fetches record even if it doesn't match the ID?

I just discovered this, I use MySQL as my database.

When i do something like:

$query = $this->db->where('id', '6rubbish')->get('posts', 1);

it generates and executes this SQL:

SELECT *
FROM (`posts`)
WHERE `id` =  '6rubbish'
LIMIT 1

The surprising thing is that it actually fetches the post with the ID 6.

I find this very vulnerable in same cases 开发者_StackOverflowbecause i'm trying to exactly match the ID, not to do a LIKE query.

Any ideas?

Yes.

• Read Type Conversion in Expression Evaluation
• Use intval() PHP function to extract the integer part of the variable
• Or use is_int to exclude any variable that is not a pure integer

But the origin of the problem is that your query generator library doesn't understand the variable types, PHP being a dynamic typed language doesn't help too.

I don't know what library you're using, maybe there is an option to tell that you're passing an int? It should protect you from SQL injection, I hope, try with:

$query = $this->db->where('id', "don't")->get('posts', 1);

and see if the generated SQL has the single quoted escaped (doubled or preceded by backslash).

That is because SELECT 6 = '6rubbish' will give you true, and your id is number type.

Your id field is, no doubt, a numeric data type.

When MySQL evaluates expressions, it converts operands to compatible types (see docs).

'6rubbish' (a string) gets converted to 6 (a number) and, hence, you get a match.