Magento – Magento 1.9.2.2 use SQL expression in addFieldToFilter

ce-1.9.2.2MySQLsupee-6788

I'm using Shop by Brand extension by Magestore guys and it stopped working when I upgraded to 1.9.2.2. I investigated the issue and found out the problem was with new security patch. So I've investigated more and figured out that in APPSEC-1063, addressing possible SQL injection Magento guys explained that the following code:

$collection->addFieldToFilter('(field1-field2)', array('eq'=>3));

Should be changed to:

$expression = '(field1-field2)';
$condition = $this->_getConditionSql($expression, array('eq'=>3));
$this->_select->where(condition);

So we can't use SQL expressions as a direct parameter of addFieldToFilter anymore.

In the extension, there's this piece of code:

// Magestore_Shopbybrand_Model_Mysql4_Brand_Collection:153
return parent::addFieldToFilter("IF(brand_$field.value IS NULL, main_table.$field, brand_$field.value)", $condition);

So the problem is right here because when I changed the code to following it works:

return parent::addFieldToFilter("main_table.$field", $condition);

Yeah, I know I should open a ticket about this (and I will) but I was curious how can I fix something like this?

Best Answer

not sure if this will work, but you can try to use addExpressionFieldToSelect and then having on the expression you use.

Something like 

$collection->addExpressionFieldToSelect(
    'expression_alias',
    '{{field1}} - {{field2}}',
    array('field1' => 'real_field1_name', 'field2' => 'real_field2_name')
);

and then

$collection->getSelect()->having('expression_alias >= 3');
Related Topic