白名单对SQL注入的有效性 [英] White listing effectiveness against SQL Injection

问题描述

假设我有以下内容:

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

我们假设$ id是一个动态变量，可以由恶意用户(通常是系统生成的，但可能被恶意删除)更改.如果$ id应该始终是一个五位数的整数，并使用正则表达式和重定向，是否有任何可能的方式使注入仅通过"whiltelisting"(仅针对五位数的整数).因此，例如，如果不是将PDO与参数化查询一起使用，则有:

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";

这对SQL注入的潜力有什么影响，因为重定向不会让任何不是5位整数的东西过去吗?我不是在做这个，而是建议这个问题或考虑这个问题(上面的查询中没有参数化查询).我不需要一堆答案来谈论PDO或类似的东西对于停止SQL注入的重要性.我试图以更深的方式理解安全性的逻辑.因此，这个问题实际上与SQL注入无关，而与上述方式中的白名单/清理的有效性有关.

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.

我的总体问题是:是否有人将多余的代码注入$ id变量中，所以会用五位数之外的其他值通过正则表达式重定向吗?

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:

我不是这样做，不是建议这个问题还是考虑这个问题(上面的查询中没有参数化查询).我不需要一堆答案来谈论PDO的重要性或停止SQL注入的类似方法.我是我试图更深入地了解安全的逻辑."

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

我真的在寻找我列出的白名单/敏感正则表达式的有效性.我对此的好奇心完全超出了SQL查询范围，但我认为这可能是一个很好的示例.显然不是.维克托实际上回答了我的问题.大多数其他人都对SQL注入有所了解(由常识大肆宣传-该主题的自任命银河专家).任何其他对我的真实问题的答复将不胜感激.

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.

关于

推荐答案

好吧，这个问题很模棱两可，很难说出您真正在问什么.但至少我可以回答以下问题之一:

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.

为什么要打扰所有这些事情?为什么不学习如何使用PDO预备语句呢?使用PDO，您可以以相同的方式处理所有参数-只需将它们分别发送给查询即可:

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.

通过手动格式化/清理，开发人员将对安全负责.我可以向您保证-这是链中最不可靠的链接.正确的格式化必须由DB驱动程序完成-PDO也会这样做.这就是为什么您应该始终使用绑定的原因.

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.

这篇关于白名单对SQL注入的有效性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！