mysql automatically cast strings to integer

mysql, php

Solution

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.

Problem

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)

Original source