避免使用Zend_Db类进行MySQL注入 [英] avoiding MySQL injections with the Zend_Db class

问题描述

我目前使用Zend_Db来管理我的查询. 我已经编写了执行以下查询的代码:

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

我假设Zend_Db会做到这一点，但并没有清除输入. Zend会这样做吗?

另一个问题: Zend_Db是否清除insert('table', $data)和update查询?

谢谢.

解决方案

在我担任该项目的团队负责人时(在1.0版之前)，我在Zend Framework中编写了很多用于数据库参数和引用的代码.

我尽力鼓励最佳做法，但我必须在易用性之间取得平衡.

请注意，您始终可以检查Zend_Db_Select对象的字符串值，以了解它是如何决定引用的.

print $select; // invokes __toString() method

此外，您还可以使用Zend_Db_Profiler来检查Zend_Db代表您运行的SQL.

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

以下是您的具体问题的一些答案:

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

  值用适当的引号引起来.尽管"?"看起来像参数占位符，但在此方法中，实际上已对引号进行了适当的引用并进行了内插.因此，这不是一个真正的查询参数.实际上，以下两个语句产生的查询与上述用法完全相同:

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

  但是，如果您传递的参数是类型为Zend_Db_Expr的对象，则该参数不会被引用.您要对SQL注入风险负责，因为它是逐字插入的，以支持表达式值:

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

  该表达式的任何其他部分需要用引号引起来或定界，这是您的责任.例如，如果您将任何PHP变量插值到表达式中，则安全是您的责任.如果您具有作为SQL关键字的列名，则需要使用quoteIdentifier()自行分隔它们.示例:

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

• Zend_Db_Adapter_Abstract::insert( array('colname' => 'value') )

  表名和列名是有界的，除非您关闭AUTO_QUOTE_IDENTIFIERS.

  将值参数化为真正的查询参数(未插值).除非该值是Zend_Db_Expr对象，否则将逐字插入，因此您可以插入表达式或NULL或其他任何内容.

• Zend_Db_Adapter_Abstract::update( array('colname' => 'value'), $where )

  表名和列名是有界的，除非您关闭AUTO_QUOTE_IDENTIFIERS.

  值是参数化的，除非它们是Zend_Db_Expr对象，如在insert()方法中一样.

  $where参数根本没有被过滤，因此您应对其中的任何SQL注入风险负责.您可以使用quoteInto()方法来使引用更加方便.

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)

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

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

Thanks.

解决方案

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.

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

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) );
  

  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()'))
  

  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') )

  Table name and column names are delimited, unless you turn off AUTO_QUOTE_IDENTIFIERS.

  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 )

  Table name and column names are delimited, unless you turn off AUTO_QUOTE_IDENTIFIERS.

  Values are parameterized, unless they are Zend_Db_Expr objects, as in insert() method.

  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.

这篇关于避免使用Zend_Db类进行MySQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！