Is filter_var enough to sanitize integer input for PHP based MySQL queries?
I never liked to wrap
mysql_real_escape_string
Function around input I expect to be an integer to include in a MySQL query. I recently came across
filter_var
function. Nice!
I am currently using the code:
if (isset($idUserIN)
&& filter_var($idUserIN, FILTER_VALIDATE_INT)
&& 0 < filter_var($idUserIN, FILTER_SANITIZE_NUMBER_INT)
) {
$idUser = filter_var($idUserIN, FILTER_SANITIZE_NUMBER_INT);
$sql = 'SELECT * FROM TABLE_NAME WHERE idUser = '.$idUser;
} else {
// handle invalid data
}
Are the holes left to open?
('> 0' instead of '> = 0' as the auto_increment field of the table, so 0 won't be a normal value)
source to share
I will create a function for this task myself, maybe somewhere in a static class,
public static function escape_int($i) {
$sanitised = intval($i);
if( '_' . $sanitised . '_' === '_' . $i . '_' && $sanitised > 0 ) {
return $sanitised;
}
throw new IntegerEscapeException( $i, $sanitised );
return "ENOINT"; # Wont Run This, but I prepare for the impossible.
}
try {
$sql = 'SELECT * FROM TABLE_NAME WHERE idUser = ' . DB::escape_int( $userid );
DB::query($sql);
...etc...
} catch( IntegerEscapeException $e ) {
die ( "You shot the sherif!" ); # bad example.
}
This is good because if I find that my sanitation method stinks, I can fix it later.
source to share
Much simpler and easier to read the method I'm using:
$sql = 'SELECT * FROM TABLE_NAME WHERE idUser = ' . intval($idUser);
It tries to convert $ idUser to integer and on unsuccessful return 0 which none of my tables have as valid id. (So I know the input was invalid if it evaluated the value 0.)
To answer your real question, no that leaves no holes open. I suggest getting rid of the duplicate code:
$idUserIN_filtered = filter_var($idUserIN, FILTER_VALIDATE_INT);
if (isset($idUserIN)
&& $idUserIN_filtered
&& 0 < $idUserIN_filtered
) {
$sql = 'SELECT * FROM TABLE_NAME WHERE idUser = '.$idUser_filtered;
} else {
// handle invalid data
}
source to share
That's all you need
if ($idUser = filter_var($idUserIN, FILTER_VALIDATE_INT)) {
$sql = 'SELECT * FROM TABLE_NAME WHERE idUser = '.$idUser;
} else {
// handle invalid data
}
or
if ($idUser = filter_input(INPUT_POST, 'userId', FILTER_VALIDATE_INT)) {
Also, you can check if it is invalid in MVC.
if (!$idUser = filter_var($idUserIN, FILTER_VALIDATE_INT)) {
throw new InputParameterException('UserId');
}
//else its valid
source to share