問題描述

我目前使用 Zend_Db 來管理我的查詢.我已經編寫了可以執行如下查詢的代碼:

I currently use Zend_Db to manage my queries. I've written already code that preforms queries like the one below:

$handle->select()->from('user_id')
                   ->where('first_name=?', $id)
                   ->where('last_name=?', $lname)

假設 Zend_Db 會，我在沒有清理輸入的情況下完成了這項工作.Zend 會這樣做嗎?

I've done this without sanitizing the input, assuming Zend_Db will. Does Zend do this?

另一個問題:Zend_Db 是否清理 insert('table', $data) 和 update 查詢?

Another question: Does Zend_Db sanitize insert('table', $data) and update queries?

謝謝.

推薦答案

當我擔任該項目的團隊負責人(直到 1.0 版)時，我在 Zend Framework 中編寫了大量用于數據庫參數和引用的代碼.

I wrote a lot of the code for database parameters and quoting in Zend Framework while I was the team lead for the project (up to version 1.0).

我盡可能鼓勵最佳實踐，但我必須在易用性和易用性之間取得平衡.

I tried to encourage best practices where possible, but I had to strike a balance with ease of use.

請注意，您始終可以檢查 Zend_Db_Select 對象的字符串值，以查看它如何決定進行引用.

Note that you can always examine the string value of a Zend_Db_Select object, to see how it has decided to do quoting.

print $select; // invokes __toString() method

您也可以使用 Zend_Db_Profiler 檢查由 Zend_Db 代表您運行的 SQL.

Also you can use the Zend_Db_Profiler to inspect the SQL that is run on your behalf by Zend_Db.

$db->getProfiler()->setEnabled(true);
$db->update( ... );
print $db->getProfiler()->getLastQueryProfile()->getQuery(); 
print_r $db->getProfiler()->getLastQueryProfile()->getQueryParams(); 
$db->getProfiler()->setEnabled(false);

以下是您的具體問題的一些答案:

Here are some answers to your specific questions:

• Zend_Db_Select::where('last_name=?', $lname)

值被適當引用.雖然?"看起來像一個參數占位符，但在這個方法中，參數實際上被適當地引用和插入.所以它不是一個真正的查詢參數.實際上，以下兩條語句產生的查詢與上述用法完全相同:

Values are quoted appropriately. Although the "?" looks like a parameter placeholder, in this method the argument is actually quoted appropriately and interpolated. So it's not a true query parameter. In fact, the following two statements produce exactly the same query as the above usage:

$select->where( $db->quoteInto('last_name=?', $lname) );
$select->where( 'last_name=' . $db->quote($lname) );

然而，如果您傳遞的參數是 Zend_Db_Expr 類型的對象，則它不會被引用.您應對 SQL 注入風險負責，因為它是逐字插入的，以支持表達式值:

However, if you pass a parameter that is an object of type Zend_Db_Expr, then it's not quoted. You're responsible for SQL injection risks, because it's interpolated verbatim, to support expression values:

$select->where('last_modified < ?', new Zend_Db_Expr('NOW()'))

該表達式的任何其他需要引用或分隔的部分是您的責任.例如，如果您將任何 PHP 變量插入到表達式中，安全是您的責任.如果您的列名是 SQL 關鍵字，您需要自己用 quoteIdentifier() 分隔它們.示例:

Any other part of that expression that needs to be quoted or delimited is your responsibility. E.g., if you interpolate any PHP variables into the expression, safety is your responsibility. If you have column names that are SQL keywords, you need to delimit them yourself with quoteIdentifier(). Example:

$select->where($db->quoteIdentifier('order').'=?', $myVariable)