ACLs fail with syntax error when using nested API4 smart groups
Overview
Easiest to explain with specifics:
We have users who should only be able to access contacts in Northern Ireland. So we've got an ACL set up for this.
We have a regular group MembersAll, and a smart group MembersNorthernIreland. MembersNorthernIreland is an API4 smart group that finds anyone in the group MembersAll who lives in Northern Ireland.
Then we have a 'Northern Ireland' ACL that allows access to the MembersNorthernIreland smart group.
But the MembersNorthernIreland is throwing syntax errors for ACL users, because a long way down the chain the code finds itself not allowed to access details about MembersAll - because it's not a group the ACL'd user is allowed to access.
Essentially the GroupContactCache wants to build the SQL to generate the people in MembersNorthernIreland. It hands this over to API4, which parses the smart group criteria and finds MembersAll. API4 wants to get the ID for MembersAll and ultimately calls FormattingUtil::getPseudoconstantList() - which tries to return a list of all groups via a getfields call here. But at this point the ACL permissions themselves get in the way, and all it gets is a list of the groups the user is allowed to access.
So the function can't find an ID and returns nothing, ultimately resulting in a DB syntax error where the SQL tries to search an empty list:
[message] => DB Error: syntax error
[mode] => 16
[debug_info] => SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1]
Backtrace:
[...sql then error handling...]
#11 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(476): DB_DataObject->query("SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE paren...")
#12 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupNesting.php(201): CRM_Core_DAO->query("SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE paren...")
#13 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(671): CRM_Contact_BAO_GroupNesting::getDescendentGroupIds((Array:0))
#14 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php(75): CRM_Contact_BAO_GroupContactCache::populateTemporaryTableWithContactsInGroups((Array:0), "civicrm_tmp_e_dflt_a6ffaf8a8a00dea3b83483af9d0b4a95")
#15 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/Civi/Api4/Query/Api4SelectQuery.php(554): Civi\Api4\Service\Spec\Provider\ContactGetSpecProvider::getContactGroupSql((Array:32), "`a`.`id`", "IN", (Array:0), Object(Civi\Api4\Query\Api4SelectQuery), 0)
#16 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/Civi/Api4/Query/Api4SelectQuery.php(530): Civi\Api4\Query\Api4SelectQuery->createSQLClause("`a`.`id`", "IN", (Array:0), (Array:32), 0)
#17 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/Civi/Api4/Query/Api4SelectQuery.php(425): Civi\Api4\Query\Api4SelectQuery->composeClause((Array:3), "WHERE", 0)
#18 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/Civi/Api4/Query/Api4SelectQuery.php(298): Civi\Api4\Query\Api4SelectQuery->treeWalkClauses((Array:3), "WHERE")
#19 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/Civi/Api4/Query/Api4SelectQuery.php(150): Civi\Api4\Query\Api4SelectQuery->buildWhereClause()
#20 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(529): Civi\Api4\Query\Api4SelectQuery->getSql()
#21 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(808): CRM_Contact_BAO_GroupContactCache::getApiSQL((Array:14), 1177)
#22 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(654): CRM_Contact_BAO_GroupContactCache::insertGroupContactsIntoTempTable("civicrm_tmp_e_gccache_d3d3e139c670a183ea5d7ceab54118b6", 1177, 4808, NULL)
#23 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(360): CRM_Contact_BAO_GroupContactCache::buildGroupContactTempTable((Array:1), Object(CRM_Utils_SQL_TempTable))
#24 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/ACL/BAO/ACL.php(290): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
#25 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/ACL/API.php(117): CRM_ACL_BAO_ACL::whereClause(2, (Array:0), (Array:0), 290135)
#26 /data/vhosts/humanists.uk/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Contact/Permission.php(187): CRM_ACL_API::whereClause(2, (Array:0), (Array:0), 290135, FALSE, FALSE, TRUE)
#27 /data/vhosts/humanists.uk/wp-content/plugins/civicrm-wp-profile-sync/includes/civicrm/cwps-civicrm-contact.php(1099): CRM_Contact_BAO_Contact_Permission::allow("290135", 2)
#28 /data/vhosts/humanists.uk/wp-content/plugins/civicrm-wp-profile-sync/includes/civicrm/cwps-civicrm-contact.php(1058): CiviCRM_WP_Profile_Sync_CiviCRM_Contact->user_can_view("290135")
#29 /data/vhosts/humanists.uk/wp-includes/class-wp-hook.php(307): CiviCRM_WP_Profile_Sync_CiviCRM_Contact->menu_link_add(Object(WP_Admin_Bar))
#30 /data/vhosts/humanists.uk/wp-includes/class-wp-hook.php(331): WP_Hook->apply_filters(NULL, (Array:1))
#31 /data/vhosts/humanists.uk/wp-includes/plugin.php(524): WP_Hook->do_action((Array:1))
Reproduction steps
- Using search kit, create smart group x based on smart group y
- Create an ACL that permits users to access smart group x, but not smart group y
- Login as that user and try to access Civi
Environment information
- Browser: Firefox 59.0.1/Chrome 78.0.3904/Safari 13
- CiviCRM: 5.50.1
- PHP: 7.3
- CMS: WordPress 6.0
Comments
I've worked around it by removing the nested group. But I'm not sure of the best approach to fix it in the code.