[web] [SQL] Where-Statement problem

Is it really a problem? After all, you wouldn't really want to query an unsigned int column with a string parameter, right?

I seem to recall reading somewhere that MySQL - or perhaps, older versions of it - are quite lax in their type-checking, and what you're seeing here is probably a side-effect of that. It probably sees that you are comparing with an integer, makes an attempt to convert the string to an integer - yielding zero in this case - and then uses that to perform the match. Try using "two" or "2" as in the string and see if it returns the row with id=2, for example.

Hm, I'm not sure, if it's really a problem... I asked this because I have this SQL statement in a web application. The id is passed as a request parameter. To prevent SQL injections I use a prepared statement. If a user is now manipulating the id, e.g. trying a SQL injection attack, he will not succeed but will see the dataset where id equals 0. The page I encountered this problem does not contain any sensitive data at the moment but I'm still not feeling comfortable that my web application shows some data, which the user should not see.

I'm thinking about two possible solutions:
1) Inserting a dataset with id=0 which contains no more data.
2) Using the LIKE operator.

Is there any draftback using the LIKE operator? Or does anyone can think of a better solution?

@Kylotan:
I've tried to used "two" but this is not returning the dataset with id=2. If I use "2" or "2foo" the dataset with id=2 is returned. So characters behind the numbers a truncated and I receive the dataset where id equals the requested number. Are characters in front of the numbers I get the dataset with id=0.

The value is probably getting passed through C's atoi() function. I think you really need to check the value before you pass it to MySQL. A prepared statement can protect you from an injection attack but it can't protect you when the SQL statement is in the expected form. If there are values of id that you don't want a given user to be able to submit, then you absolutely must check them yourself between the browser and the database.

One thing to do is just through something in there to check what the value is being passed and if it's not a valid number then it'll just through up an error message instead of even proccessing the sql satement.

Thanks for the replies. Seems like I just have to change a 'little' bit of my code... Really, I don't understand this behaviour. It makes no sense to me. Anyway, learned again something. Thanks for the help.