Passing numeric variable and preventing SQL injection

Question

I have a query like this:

SELECT name FROM mytable WHERE id = $id

where $id is given from user. I do add slashed for input variable. Is it enough to only use (int)$id to prevent SQL injection? Or do I have to check $id with is_numeric before passing it to the query?

Edited: the script language is PHP.

Answer 1

Yes, casting a variable with (int) or intval() will ensure that it the result is only a number, and has no other characters. This is a good method to defend against SQL injection attacks, but it only works for numeric variables of course.

For more detail on methods of SQL injection defense, see my presentation SQL Injection Myths and Fallacies, or the chapter in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Answer 2

I'd give up on the idea of figuring out how to safely append a number to your query string, and instead simply move on to the idea of always using prepared statements. They are slighly more verbose and tedious to write, but they are much safer -- and if you get into the habit, you won't have to worry about whether you did it right in this or that case, maybe sometimes it's a number, other times it's a string, did you use the right escaping mechanism?