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.
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.
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?
Ask the $id is it an integer and is it equal to or greater than 0. Or else a user input injection attempt will most likely be at play.
Example:
$id = ( false !== ( int )$_GET[ 'id' ] >= 0 ) ? ( int )$_GET[ 'id' ] : die( header( "Location: ./index.php" ) );
function mysql_prep( $value ) {
$magic_quotes_active = get_magic_quotes_gpc();
$new_enough_php = function_exists( "mysql_real_escape_string" ); // i.e. PHP >= v4.3.0
if( $new_enough_php ) { // PHP v4.3.0 or higher
// undo any magic quote effects so mysql_real_escape_string can do the work
if( $magic_quotes_active ) { $value = stripslashes( $value ); }
$value = mysql_real_escape_string( $value );
} else { // before PHP v4.3.0
// if magic quotes aren't already on then add slashes manually
if( !$magic_quotes_active ) { $value = addslashes( $value ); }
// if magic quotes are active, then the slashes already exist
}
return $value;
}
$username = trim(mysql_prep($_POST['username']));
Use that function to be damn safe!!! :D
$value = $id + 0and then pass$valueto the query. Anything to ensure that the user's raw data is not treated like a string when you want it to be a number.