使用 PDO 转义列名

2021-12-26 00:00:00 php pdo

我有一个类似的功能

function getInfoById($id, $info) {

}

这个想法是有一个查询 "SELECT $info FROM table WHERE id = $id"

the idea is to have a query be "SELECT $info FROM table WHERE id = $id"

这不适用于 PDO，因为您无法对列名进行转义.我也真的不想使用 "SELECT *" 因为这不会返回更大的结果集并使用更多内存吗?

This doesn't work with PDO because you can't escape column names. I also don't really want to use "SELECT *" because doesn't that return a bigger result set and use more memory?

推荐答案

是的，PDO 没有用于分隔标识符(如表名和列名)的内置函数.PDO::quote() 函数仅适用于字符串文字和日期文字.

Yes, PDO does not have a builtin function for delimiting identifiers like table names and column names. The PDO::quote() function is only for string literals and date literals.

无论如何，当我在 Zend Framework 上工作时，我实现了一个 quoteIdentifier() 函数.

For what it's worth, when I worked on Zend Framework, I implemented a quoteIdentifier() function.

SELECT * 获取所有列是对的，这可能会使用更多内存并破坏覆盖索引的好处.

You're right that SELECT * fetches all columns, likely using more memory and spoiling the benefit of covering indexes.

我的建议是将列名称列入白名单.也就是说，确保 $info 实际上命名了 table 的一列.然后您无需担心列名称不存在，或包含奇怪字符或任何内容.您可以控制可以合法放入查询中的列集.

My recommendation is to whitelist column names. That is, make sure $info actually names a column of table. Then you don't need to worry about the column name not existing, or containing a strange character, or anything. You get to control the set of columns that are legitimate to put in the query.

无论如何，您还应该分隔列名称.如果列名称包含标点符号、空格、国际字符或匹配 SQL 保留字，则需要分隔标识符.请参阅不同的数据库是否使用不同的名称引用?

You should also delimit the column name anyway. Delimited identifiers are necessary if the column name contains punctuation, whitespace, international characters, or matches an SQL reserved word. See Do different databases use different name quote?

function getInfoById($id, $info) {
    // you can make this a literal list, or query it from DESC or INFORMATION_SCHEMA
    $cols = array('col1', 'col2', 'col3');

    if (array_search($info, $cols) === false) {
      return false;
    }
    $sql = "SELECT `$info` FROM table WHERE id = :id";
    $stmt = $pdo->prepare($sql);
    if ($stmt === false) {
      return false;
    }
    . . .
}

我在演示文稿中展示了更多白名单示例 SQL 注入神话和谬论.

I show more examples of whitelisting in my presentation SQL Injection Myths and Fallacies.

相关文章