mysql automatically cast strings to integer

Question

I've just noticed that if I do a MySQL request like this one:

SELECT 1 FROM myTable WHERE id = 'asdf'

Then the string 'asdf' is casted to 0. It means that I have a record with id 0 this will match. The format of the id field is int(8).

What is the best way to proceed:

• I need to check (by PHP for example) that my value is numerical only?
• There is a MySQL way to do that?
• I must remove my record with id 0? (bad)

Answer 1

Just write your queries so that they don't use numeric fields as if they were textual ones.

If id is a numeric field, then your where clause can never be useful. Yes, it would be good if MySQL actively complained about it - but fundamentally you shouldn't be writing code which runs bad queries to start with.

How did that query enter your system? Is the 'asdf' part direct user input? Can you use parameterized SQL instead?

If you're genuinely intending to query a numeric field, you should make sure that your input is numeric first. Convert the text to an integer in your calling code, not in the database.

Answer 2

You must first sanitize your inputs via PHP.

$id = 'asdf';
if(is_numeric($id)){
    $query("SELECT 1 FROM myTable WHERE id = $id");
}else{
    die("ID is not numeric");
}

Or you can do:

    SELECT 1 FROM myTable WHERE id = 'asdf' AND 'asdf' REGEXP '^-?[0-9]+$'

This would cause the regex to = false, causing no rows to return.

Answer 3

Since pdo prepared statements binding with correct types will not raise any error (except if mysql strict mode is enabled), your only choice is to ensure and control the types of your variables within your php to "correct" the permissivity of these languages.

[thanks to commentators]