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

Its safe with regards to sql injection prevention but not really a good idea as you get an exception, which you always want to avoid if possible as exceptions are expensive. You should really properly sanitize the input. And of course the user can still alter the value to any range within an int32 value.

Answer 2

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 3

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();
}

Answer 4

Probably, but worth testing.

Regarding Richard's answer, I've sometimes had trouble with IsNumeric() being a little more liberal in what it will accept as a valid number than the actual CAST to numeric (depends on localization settings, actually). Stuff like "-0", "3E-5", "5.000.000" sometimes satisfies IsNumeric, but doesn't cast properly. So I usually do a full try, catch around the actual cast statement instead.