SQL Injection Protection - Cast from string to int

Question

We all know that parameterized SQL is the way to go when dealing with user input and dynamic SQL, but is casting from string to int (or double, or long, or whatever) as effective if the input you are seeking is numeric?

I guess what I am asking is if this technique alone is infallible in regards to SQL injection?

Answer 1

I'm no expert, but I'm reasonably sure that this would be safe.

But why take the chance? Use parameterised SQL and you don't ever need to worry about it.

Besides, parameterising your SQL has other advantages, not just injection-protection.

Answer 2

If the string was a valid number before you casted it to integer yes it is safe. But you must make sure that it is a valid integer before casting it to int.

I don't know what server side language you are using but in PHP you can use is_numeric() function. For instance:

$strYouExpectToBeInt = $_POST['id'];
try {
    if (false === is_numeric($strYouExpectToBeInt)) {
        throw new Exception('id is not a numeric string or a number');
    }
    $strYouExpectToBeInt = (int)$strYouExpectToBeInt;
    if (false === is_int($strYouExpectToBeInt)) {
        throw new Exception('id is not a valid integer');
    }

    // everything is ok, you can use $strYouExpectToBeInt
    // in SQL query now

} catch  (Exception $e) {
    echo $e->getMessage();
}