avoiding MySQL injections with the Zend_Db class
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 orNULL
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 ininsert()
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 thequoteInto()
method to help make quoting more convenient.
ZEND_DB_TABLE_ABSTRACT methods SQL INJECTION
Yes, it is possible, but in the usual uses of insert()
it's not probable. Unless you are using Zend_Db_Expr
, you should be safe, because insert()
uses prepared statements.
See this post from Bill Karwin for other methods and details.
Is the Zend_Db_Table_Abstract-insert() function safe?
When you need to use quoting (quote()
, quoteInto()
) with Zend_Db_Table
:
insert
(no)update
(yes)delete
(yes)- querying with SQL using the adapter directly (yes).
Use quotes with Zend_Db_Table_Select
(usually not); make sure you examine the output of the query.
Here's a great answer from one of the authors of Zend_Db (avoiding MySQL injections with the Zend_Db class).
Need help with Zend_db update
You approach only works with integer values, because the way you concat the where string does not escape the value. So if you do
'email = '.$email
It will product an sql string like this if you use the string "hello world"
WHERE email = hello world
This is an invalid SQL statement so the update does not happen. What you want to produce is a where clause like this
WHERE email = 'hello world'
There are multiple ways to do this, but the safest way to do that via Zend Framework is described in the reference manual under "Example #24 Updating Rows Using an Array of Arrays".
$data = array(
'password' => $password
);
$where['email = ?'] = $email;
$this->update($data, $where);
Zend sql injection prevention
Tim is correct if a bit terse. :)
The $data array in an update statment in Zend_Db is broken down into bound parameters. You can find the exact code in Zend_Db_Adapter_Abstract
.
There are a number of procedures involved but the array ultimately ends up in this statement.
$set[] = $this->quoteIdentifier($col, true) . ' = ' . $val;
where your original array was $col => $val
then the SQL is created:
$sql = "UPDATE "
. $this->quoteIdentifier($table, true)
. ' SET ' . implode(', ', $set)
. (($where) ? " WHERE $where" : '');
It looks reasonably secure against SQL injection.
However you can always employ Zend_Filter_Input
with Zend_Validate
and Zend_Filter
to really sanitize your input values.
Related Topics
Difference Between Set_Time_Limit() and Ini_Set('Max_Execution_Time', ...)
PHP Array Printing Using a Loop
How to Convert Utc Datetime to Another Timezone
How to Find Annotations in a PHP5 Object
How to Get Jquery Code Completion in Netbeans
How to Change the Array Key to Start from 1 Instead of 0
PHP Parsing Problem - &Nbsp; and Â
PHP Image Resize on the Fly VS Storing Resized Images
How to Convert a String to an Array in PHP
How to Strip Out the Domain Name from a Url in PHP
Checking If a String Contains an Integer
Laravel Eloquent Compare Date from Datetime Field