ID '1a' matches '1' in table

[GregL83]

I have a query where the variable id is pulled from the page url.

 

$query = "SELECT id FROM table WHERE id = '".$_GET['id']."'";

 

The table's id column is auto incrementing.

 

My problem is that 1a returns the results for a record with the id 1.

How is this possible when I am surrounding the variable in single quotes?  My expected results would be returning a row for 1 an NOT 1a...

 

Do I need to do anything special?

[PFMaBiSmAd]

When strings get evaluated where a number is expected, all the characters up to the first non-numeric character are converted to a number.

[GregL83]

does that mean validation before the query or is there an mysql command that will take care of this?

[PFMaBiSmAd]

If you want '1a' to result in nothing returned, you would need to validate it before the query.

 

If you use your query without the quotes around the value, the results should be the same as well. Anything you can do with CAST(), BINARY, or REGEXP... in the query will produce the same results, because the parsing of a number with non-numeric character in it or at the end of it will return the numeric value up to the first non-numeric character.

[PFMaBiSmAd]

Edit to the above:

 

Here is the actual rule used -

For inserts of strings into numeric columns, conversion from string to number is handled as follows if the string has non-numeric contents:

 

A string that does not begin with a number cannot be used as a number and produces an error in strict mode, or a warning otherwise. This includes the empty string.

 

A string that begins with a number can be converted, but the trailing non-numeric portion is truncated. If the truncated portion contains anything other than spaces, this produces an error in strict mode, or a warning otherwise.

 

Without quotes you get an error: Unknown column '1a' error returned (at least in the mysql query browser.) With quotes, you get a mysql warning: Truncated incorrect DOUBLE value: '1a' (for servers not running in strict mode.)