php - White listing effectiveness against SQL Injection

Question

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,

Answer 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.