使用 Zend_Db 类避免 MySQL 注入
我目前使用 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)
Zend_Db_Adapter_Abstract::insert( array('colname' => 'value') )
表名和列名是分隔的，除非你关闭AUTO_QUOTE_IDENTIFIERS.
Table name and column names are delimited, unless you turn off AUTO_QUOTE_IDENTIFIERS.
值被参数化为真正的查询参数(未插入).除非值是一个 Zend_Db_Expr 对象，在这种情况下它是逐字插入的，所以你可以插入表达式或 NULL 或其他什么.
Values are parameterized as true query parameters (not interpolated). Unless the value is a Zend_Db_Expr object, in which case it's interpolated verbatim, so you can insert expressions or NULL or whatever.
Zend_Db_Adapter_Abstract::update( array('colname' => 'value'), $where )
表名和列名是分隔的，除非你关闭AUTO_QUOTE_IDENTIFIERS.
Table name and column names are delimited, unless you turn off AUTO_QUOTE_IDENTIFIERS.
值是参数化的，除非它们是 Zend_Db_Expr 对象，如 insert() 方法.
Values are parameterized, unless they are Zend_Db_Expr objects, as in insert() method.
$where 参数根本没有被过滤，因此您应对该参数中的任何 SQL 注入风险负责.您可以使用 quoteInto() 方法来帮助更方便地引用.
The $where argument is not filtered at all, so you're responsible for any SQL injection risks in that one. You can make use of the quoteInto() method to help make quoting more convenient.
相关文章