用PDO转义列名 [英] escaping column name with 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.

这篇关于用PDO转义列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！