Security Advisory: ZF2016-03

ZF2016-03: Potential SQL injection in ORDER and GROUP functions of ZF1

The implementation of ORDER BY and GROUP BY in Zend_Db_Select remained prone to SQL injection when a combination of SQL expressions and comments were used. This security patch provides a comprehensive solution that identifies and removes comments prior to checking validity of the statement to ensure no SQLi vectors occur.

The implementation of ORDER BY and GROUP BY in Zend_Db_Select of ZF1 is vulnerable by the following SQL injection:

$db = Zend_Db::factory(/* options here */);
$select = new Zend_Db_Select($db);
$select->from('p');
$select->order("MD5(\"a(\");DELETE FROM p2; #)"); // same with group()

The above $select will render the following SQL statement:

SELECT `p`.* FROM `p` ORDER BY MD5("a(");DELETE FROM p2; #) ASC

instead of the correct one:

SELECT "p".* FROM "p" ORDER BY "MD5(""a("");DELETE FROM p2; #)" ASC

This security fix can be considered an improvement of the previous ZF2016-02 and ZF2014-04 advisories.

As a final consideration, we recommend developers either never use user input for these operations, or filter user input thoroughly prior to invoking Zend_Db. You can use the Zend_Db_Select::quoteInto() method to filter the input data, as shown in this example:

$db    = Zend_Db::factory(...);
$input = "MD5(\"a(\");DELETE FROM p2; #)"; // user input can be an attack
$order = $db->quoteInto("SQL statement for ORDER", $input);

$select = new Zend_Db_Select($db);
$select->from('p');
$select->order($order); // same with group()

Action Taken

We fixed the reported SQL injection by removing comments from the SQL statement before passing it to either the order() or group() methods; this patch effectively solves any comment-based SQLi vectors.

We used the following regex to remove comments from a SQL statement:

const REGEX_SQL_COMMENTS = '@
    (([\'"]).*?[^\\\]\2) # $1 : Skip single & double quoted expressions
    |(                   # $3 : Match comments
        (?:\#|--).*?$    # - Single line comments
        |                # - Multi line (nested) comments
         /\*             #   . comment open marker
            (?: [^/*]    #   . non comment-marker characters
                |/(?!\*) #   . ! not a comment open
                |\*(?!/) #   . ! not a comment close
                |(?R)    #   . recursive case
            )*           #   . repeat eventually
        \*\/             #   . comment close marker
    )\s*                 # Trim after comments
    |(?<=;)\s+           # Trim after semi-colon
    @msx';

The patch is available starting in Zend Framework 1.12.20.

Other Information

This SQL injection attack does not affect Zend Framework 2 and 3 versions because the implementations of Zend\Db\Sql\Select::order() and Zend\Db\Sql\Select::group() do not manage parenthetical expressions.

Acknowledgments

The Zend Framework team thanks the following for identifying the issues and working with us to help protect its users:

• Hiroshi Tokumaru (HASH Consulting Corp.), who discovered the issue;
• Masanobu Katagi (Japan Computer Emergency Response Team Coordination Center), who reported the issue;
• Enrico Zimuel, who provided the patch.

Released 2016-09-08

Back to advisories

• Overview
• Advisories
• Feed