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)

The usual way to go is indeed to check the variable beforehand and to see whether it's numeric. (Many coding examples I've seen do a simple (int) cast though, which also results in 0) — Pekka
– Pekka, Commented Jun 11, 2012 at 15:52
You should always check inputs, especially when executing commands against your database and even more so if part of the input is coming from an external user. This is just good practice (for example to avoid injection attacks). PHP has an isNumeric function php.net/manual/en/function.is-numeric.php — Girish Rao
– Girish Rao, Commented Jun 11, 2012 at 15:55
it depends if you mind catching characters in a integer field. mySQL will allow it, if you don't care that the string is not an integer, you can truncate the input record so it doesn't overflow your buffers. LINK — Hituptony
– Hituptony, Commented Jun 11, 2012 at 16:00
please reword title of question. I came here looking to know how to cast int as string, but it wasn't even answered. not user friendly. — Henley Wing Chiu
– Henley Wing Chiu, Commented Nov 16, 2012 at 22:40

Jon Skeet · Accepted Answer · 2012-06-11 16:20:15Z

4

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.

edited Jun 11, 2012 at 16:20

answered Jun 11, 2012 at 15:56

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

2 Comments

Jon Skeet Over a year ago

@MarkByers: That doesn't mean you should be using non-integers in a query for integer input. Will edit to clarify.

Yann Sagon Over a year ago

I don't get your point when you say that if id is a numeric field, your where clause can never be useful

Mike Mackintosh · Accepted Answer · 2012-06-11 15:58:48Z

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.

answered Jun 11, 2012 at 15:58

Mike Mackintosh

14.3k7 gold badges64 silver badges87 bronze badges

2 Comments

Yann Sagon Over a year ago

just another method to validate an int in php: $id = filter_input(INPUT_GET | INPUT_POST, "varName", FILTER_VALIDATE_INT);

Mike Over a year ago

I would rather use is_int($id) instead, because is_numeric($id) would also go with "+0123.45e6". That of course depends on the Type of your Database Integer and its options.

Sebas · Accepted Answer · 2012-06-11 17:30:44Z

1

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]

edited Jun 11, 2012 at 17:30

answered Jun 11, 2012 at 15:58

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

1 Comment

Carlos Campderrós Over a year ago

In this case using prepared statements won't work, because the library will cast 'asdf' to 0, so it will return incorrect rows. The point about checking the type in php is totally correct though.

Collectives™ on Stack Overflow

mysql automatically cast strings to integer

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related