avoiding MySQL injections with the Zend_Db class

Question

Hi -

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.

Answer 1

Yes. See http://framework.zend.com/manual/en/zend.db.select.html . Don't worry. You're right to be skeptical.

Answer 2

The bit that should make you feel safe is the ? marks in the where clauses. These are parameters, which are safely replaced with the second argument by the database system.

Answer 3

When you need it somewhere else (like in join) or you are unsure if it will be escaped then you can always use $this->getAdapter()->quoteInto('type = ?',1);

Answer 4

by default when you use value binding in your SQL queries like this:

where('first_name=?', $id);

Zend_Db uses appropriate quoting of values to prevent SQL injection. although it is strongly recommended (by books, articles, manuals and self experience) to sanitize/filter user input. Zend_Filter can be very helpful.

Answer 5

Filtering input is always good, because likely it'll be going somewhere other than just the DB, and you at least want sane data in your database at some level.

• Zend_Filter_Input on the way in
• Prepared statements (or quoteInto if not in a prepared)
• Escape filters on the way out (htmlentities, etc).

Answer 6

One thing about this, when value is NULL, you can achive not valid query

$value = NULL;
$select->where('prop=?', $value);

Result: SQL error

Answer 7

I wrote a lot of the code for database parameters and quoting in Zend Framework when I participated in the project. 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.