MySQL SELECT Statement

[HoboTraveler]

Hi All,

The first ID column in my database is for ID's only. The data set contains numbers from 1 - 10.

However, MySQL returns the first row, when I run the following SELECT statement:

SELECT *
FROM `table`
WHERE `ID` = '1alksjdf'

Why does MySQL return the first record when ID= '1alksjdf'?

Is '1alksjdf' understood as 1 by MySQL? How do I stop MySQL from assuming that '1alksjdf' is = 1?

TIA

[rocknbil]

I am presuming ID is primary key, auto_increment (as it should be) and an int data type.

It's doing this because you have "1" as the first character and quoted; it reads "1" and ignores the trailing non integer characters, so what you have left is 1. Example:

select * from test;

¦ id ¦ txt ¦ 
¦ 1 ¦ ai ¦ 
¦ 2 ¦ ue ¦ 

select * from test where id='1sdfsd';

¦ id ¦ txt ¦ 
¦ 1 ¦ ai ¦ 

select * from test where id='ds1fg';
empty set

select * from test where id='2rty';

¦ id ¦ txt ¦ 
¦ 2 ¦ ue ¦ 

select * from test where id='sdf2dsf';
empty set

select * from test where id=2sadasd;
ERROR: unknown column in where clause

select * from test where id=2;

¦ id ¦ txt ¦ 
¦ 2 ¦ ue ¦ 

So while it's a good idea to quote any input that comes from a public web page, to correctly query numeric columns, you need to have it unquoted. If you're working with something like this,

myscript.cgi?id=2

This is not a great idea as it exposes the field name and someone could modify that to inject a query,

select * from test where 1=1

Which reveals all records.

Thoroughly cleanse any integer data input from a web page and do some sort of filtering on it to verify it's a legitimate request.

[mark_roach]

I use the following regexp in perl to ensure I only accept a numeric argument for my ID column

if(!($id =~ /^[1-9][0-9]*$/))
{
$status="404 Not Found";
}