3

Consider the following example:

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$email_address = $_GET['email'];

$sql = 'SELECT * FROM `foo` WHERE `email_address` = ?';
$bar = $connection->fetchAll($sql, $email_address);

Does Magento handle escaping automatically, based on the bound parameters — or would this code be susceptible to SQL injection?

Source: How to mysql escape in magento?

I think Magento uses a DB Access layer based on PDO, which handles escaping automatically provided you use bound parameters.

When looking at Mage_Core_Model_Resource, the getConnection method states:

/**
 * Creates a connection to resource whenever needed
 *
 * @param string $name
 * @return Varien_Db_Adapter_Interface
 */

When looking at lib/Varien/Db/Adapter/Interface.php, the following comment is visible for the fetchOne method:

 /**
 * Safely quotes a value for an SQL statement.
 *
 * If an array is passed as the value, the array values are quoted
 * and then returned as a comma-separated string.
 *
 * @param mixed $value The value to quote.
 * @param mixed $type  OPTIONAL the SQL datatype name, or constant, or null.
 * @return mixed An SQL-safe quoted value (or string of separated values).
 */

This seems to imply that the parameters are safely escaped. The same comments aren't visible on the other fetch methods (i.e fetchRow, fetchAll etc).

Thus, would parsing user input values into the SQL statement using the fetchOne, fetchRow, fetchAll methods be susceptible to SQL injection — or would the parameters be safely escaped by the Variant Interface?

  • 1
    I think you can simply test this with $email_address = "email-with-single'quote". – Marius Jan 14 at 9:47
2
+50

Based on the comment.
I think it's safe to assume the parameters are escaped properly.
Tried these 3 cases:

Case 1:

$email_address = "email-with-single'quote";
$sql = 'SELECT * FROM `customer_entity` WHERE `email` = ?';
$bar = $connection->fetchAll($sql, $email_address);
echo "<pre>"; print_r($bar);exit;
exit;

Result is an empty array.

Case 2:

$email_address = "email-with-single'quote";

$sql = 'SELECT * FROM `customer_entity` WHERE `email` = '.$email_address;
$bar = $connection->fetchAll($sql);
echo "<pre>"; print_r($bar);exit;
exit;

Result:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-single'quote' at line 1 in /[ROOT]/lib/Zend/Db/Statement/Pdo.php on line 234

Case 3:

$email_address = "email-with-single'quote";

$sql = 'SELECT * FROM `customer_entity` WHERE `email` = \''.$email_address.'\'';
$bar = $connection->fetchAll($sql);
echo "<pre>"; print_r($bar);exit;

Result: Error:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'quote'' at line 1 in /[ROOT]/lib/Zend/Db/Statement/Pdo.php on line 234

In other words...
When using the unescaped email address directly, I get an error because of the single quote.
when using the email address as a parameter the query executes properly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.