問題描述

我有一個類似的功能

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 *" 因為這不會返回更大的結果集并使用更多內(nèi)存嗎?

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 沒有用于分隔標識符(如表名和列名)的內(nèi)置函數(shù).PDO::quote() 函數(shù)僅適用于字符串文字和日期文字.

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 上工作時，我實現(xiàn)了一個 quoteIdentifier() 函數(shù).

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

SELECT * 獲取所有列是對的，這可能會使用更多內(nèi)存并破壞覆蓋索引的好處.

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

我的建議是將列名稱列入白名單.也就是說，確保 $info 實際上命名了 table 的一列.然后您無需擔心列名稱不存在，或包含奇怪字符或任何內(nèi)容.您可以控制可以合法放入查詢中的列集.

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 保留字，則需要分隔標識符.請參閱不同的數(shù)據(jù)庫是否使用不同的名稱引用?

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 轉義列名的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！