如何防止使用动态表名进行SQL注入? [英] How can I prevent SQL injection with dynamic tablenames?

问题描述

我在这次讨论中享有很高的声誉 PHP 伙计:

I had this discussion with a high reputation PHP guy:

PDO 在这里没有用.以及mysql_real_escape_string. 质量极差.

PDO has no use here. as well as mysql_real_escape_string. extremely poor quality.

这当然很酷，但是老实说，我不知道建议使用mysql_real_escape_string或PDO来修复此代码是怎么回事:

This of course is cool, but I honestly don't know what's wrong with suggesting use of mysql_real_escape_string or PDO to fix this code:

<script type="text/javascript">
    var layer;

    window.location.href = "example3.php?layer="+ layer;

    <?php
        //Make a MySQL connection
        $query = "SELECT Category, COUNT(BUSNAME)
          FROM ".$_GET['layer']." GROUP BY Category";
        $result = mysql_query($query) or die(mysql_error());

进入

$layer = mysql_real_escape_string($_GET['layer']);
$query = "SELECT Category, COUNT(BUSNAME)
FROM `".$layer."` GROUP BY Category";

，考虑到 JavaScript 代码已发送到客户端.

, considering that the JavaScript code gets send client-side.

推荐答案

您的建议确实不正确.

mysql_real_escape_string()不适用于动态表名；它仅用于转义由引号分隔的字符串数据.它不会逃脱反引号字符.这是一个很小但至关重要的区别.

mysql_real_escape_string() will not work for dynamic table names; it is designed to escape string data, delimited by quotes, only. It will not escape the backtick character. It's a small but crucial distinction.

所以我可以在其中插入一个SQL注入，我只需要使用结束反引号即可.

So I could insert a SQL injection in this, I would just have to use a closing backtick.

PDO 也不提供动态表名称的卫生条件，或者.

这就是为什么最好不要使用动态表名，或者必须使用动态表名与有效值列表(例如，来自SHOW TABLES命令的表列表)进行比较的原因.

This is why it is good not to use dynamic table names, or if one has to, comparing them against a list of valid values, like a list of tables from a SHOW TABLES command.

我也没有真正意识到这一点，并且可能重复同样的坏建议也有罪恶感，直到在这里也由Shrapnel上校向我指出了.

I wasn't really fully aware of this either, and probably guilty of repeating the same bad advice, until it was pointed out to me here on SO, also by Col. Shrapnel.

这篇关于如何防止使用动态表名进行SQL注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！