DB Error on smart group with form values stored as html entities
We're encountering DB errors on some sites for those who have smart group form values stored as <
or >
.
It seems these smart groups were created using search builder with >, < as the operator. Eg payment with amount < 30, etc.
To replicate, probably just try hitting Update Smart Group Counts
button on Manage group page. It errors if form_values has any saved_search entry with <
or >
in it.
SELECT * FROM `civicrm_saved_search` WHERE `form_values` LIKE '%<%' OR `form_values` LIKE '%>%';
If i try creating a new smart group using the operator, it correctly decodes the value before storing it in the database, so probably it just affects the existing groups.
Approach to fix:
-
Replace these characters while retrieving the SQL for group contact cache - https://github.com/civicrm/civicrm-core/blob/master/CRM/Contact/BAO/GroupContactCache.php#L633 -
return str_replace(['<', '>'], ['<', '>'], "$select {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['group_by']} {$sqlParts['having']}");
Might be Related - #1328 & https://github.com/civicrm/civicrm-core/pull/21556