ZF-1.12.7 breaks code when ordering on FULLTEXT results

[bbrala]

Hi,

I've upgraded to 1.12.7 and a lot of our sites are breaking locally. It seems the fix for some security issue is not backward compatiple with certain ORDER statements.

I have a search table with fulltext search and try to order by a result.

// Using MySQL database.
$sBoolQuery = 'database search';
$oSelect    ->from(array('s' => 'search_index'))
                        ->where('MATCH (s.titel, s.content) AGAINST (? IN BOOLEAN MODE)', $sBoolQuery)
                        ->order('(1.5 * (MATCH (s.titel) AGAINST (' . $oDb->quote($sQuery) .  ')) +
                                 MATCH (s.titel, s.content) AGAINST (' . $oDb->quote($sQuery) .  ')) DESC')
                        ->limit('100');

This is no longer working. The resulting query is now:

SELECT `s`.* 
FROM `search_index` AS `s` 
WHERE (MATCH (s.titel, s.content) AGAINST ('+database search' IN BOOLEAN MODE)) 
ORDER BY `(1`.`5 * (MATCH (s`.`titel) AGAINST ('database search')) + 
    MATCH (s`.`titel, s`.`content) AGAINST ('database search'))` DESC LIMIT 100

For convenience the table create SQL

CREATE TABLE `search_index` (
    `id` INT(10) UNSIGNED NOT NULL,
    `module` VARCHAR(255) NOT NULL,
    `titel` VARCHAR(255) NOT NULL,
    `content` TEXT NOT NULL,
    `lang` VARCHAR(50) NULL DEFAULT NULL,
    INDEX `lang` (`lang`),
    FULLTEXT INDEX `titel` (`titel`),
    FULLTEXT INDEX `titel_content` (`titel`, `content`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

I feel like this is not an expected difference.

[akrabat]

I believe that the security fix is intended to pick up order statements like yours. The solution is to wrap in a Zend_Db_Expr.

Ping @ezimuel for confirmation.

[ezimuel]

@bbrala, I confirm what @akrabat said, you need to use Zend_Db_Expr in the order statement:

order->(new Zend_Db_Expr('...'))

Basically, ZF2014-04 added a constraint in the order statement to prevent possible security issue.

[bbrala]

Thanks for the reply. I understand it has to be this way :)