White listing effectiveness against SQL Injection

问题:

Let's say I had something like the following:

function return_some_info($db, $id){

    if (! preg_match("/^\d{5}$/",$id)) {
      header("Location: safepage.php");
      exit;
    }

    $query="SELECT `column1`, `columns2` FROM `table` WHERE `columnId`=:id ORDER BY `column1` ASC";
    $query_params = array(
        ':id' => $id
    );

    $stmt = $db->prepare($query);
    $stmt->execute($query_params);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $infoArr[]=$row;
    };
    return $infoArr;
}

And let's say $id is a dynamic variable that could be changed by a malicious user (normally system generated, but potentially malipulated). If $id should always be a five digit integer, with the regex and redirect, is there any possible way that injection could ever get past the "whiltelisting" for a five digit integer only. So for instance if instead of using PDO with paramterized queries, there was:

$query="SELECT `column1`, `columns2` FROM `table` WHERE `columnId`=".$id." ORDER BY `column1` ASC";

Would that make any difference as far as the potential for SQL injection since the redirect isn't going to let anything that isn't a five digit integer past? I'm not doing this, suggesting this or considering this (the above query without paramterized queries). I don't need a bunch of answers talking about the importance of PDO or something similar for stopping SQL injection. I'm trying to understand in a deeper way the logic of security. So this question isn't really so much about the SQL injection but about the effectiveness of whitelisting/sanitization in the above manner.

My overall question is this: Is there anyway that someone injecting extra code into the $id variable would ever get past the regex redirect with something other than a five digit integer?

Further clarification: Apparently, as hard as I tried, I didn't explain what I wanted. Let me copy some text from above:

"I'm not doing this, suggesting this or considering this (the above query without paramterized queries). I don't need a bunch of answers talking about the importance of PDO or something similar for stopping SQL injection. I'm trying to understand in a deeper way the logic of security."

I'm really looking for the effectiveness of the whitelisting/sensitization regex I've listed. My curiosity on this goes completely outside of SQL queries but I thought maybe this would be a good example to use. Apparently not. Wiktor actually answered my question. Most everyone else just got worked up about SQL injection (hyped up by Your Common Sense -- the self appointed galaxy expert on the subject). Any other replies to my real question would be appreciated.

Regards,

回答1:

Well, yes... if

1. your whitelist code is correct and
2. your whitelist code is bug free and
3. you know exactly how all possible values will be parsed and treated in all possible situations and
4. you have not forgotten any specific special case which might slip through

... then just maybe a whitelist approach can be secure.

The point is that this is a lot if ifs, and you'll change your code in the future and you'll make mistakes in the future and you'll forget to validate something somewhere somehow and some unsanitized values will slip through one day...

The big great advantage of a prepared statement API is that you cannot break it. Using value placeholders and passing values separately and doing this all the way to the database using the native prepared statement API cannot break no matter how stressed or drunk or forgetful or stupid you were when you wrote that code.

Yes, it's physically possible to secure database queries in alternative ways, but it's humanly much much harder and hence not worth bothering with.

回答2:

Well, this question is quite ambiguous and it's hard to tell what you're actually asking. But at least I can answer one of the questions:

So this question is ... about the effectiveness of whitelisting/sanitization in the above manner.

It's apparently ineffective, as it will require special attention for each particular variable. Here you need an integer and there you need a boolean, and somewhere you need a string. And in time you will need to change an integer for a string. And so on.

Why bother yourself with all these matters? Why not to learn how to use PDO prepared statements instead? With PDO you can treat all the parameters the same way - just by sending them to the query separately:

function return_some_info($db, $id) {

    $query = "SELECT column1, columns2 FROM table WHERE columnId= ? ORDER BY column1";
    $stmt = $db->prepare($query);
    $stmt->execute([$id]);
    return $stmt->fetchAll();
}

Look, with proper usage it takes less writing than your approach and works with any data the same way.

The logic of security is extremely simple: the database should be doing it for you. And this is true for any other medium/destination. Manual sanitization should be avoided in favor of automated formatting.

With your manual formatting/sanitization, a developer become responsible for the safety. And I can assure you - it's the most unreliable link in the chain. Proper formatting have to be done by the DB driver - so PDO does. That's why you should always use binding.

While whitelisting is recommended when you cannot use binding - for the field names, for example.