Running a Contact Summary report as a limited access user granted gives DB Error: no such field
Overview
Running a report as a limited access user based on a group that has ACL permissions granted gives ...
Sorry, due to an error, we are unable (etc)... DB Error: no such field
My particular use case was a Contact Summary report based on a smartgroup being run by a locked-down user who has acl access to that group (ie not all groups).
Reproduction steps (simplified from my actual use case).
- Create a smart-group and set up ACL for a specific user that doesn't have global access to all contacts/groups.
- Login as that user, create the report, select a couple of fields AND the smart-group.
- Attempt to run the report.
Current behaviour
Report fails with DB Error: no such field as limited access user, but runs fine for full access user.
The following SQL was generated for the limited access user ...
SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name AS civicrm_contact_sort_name,
contact_civireport.id AS civicrm_contact_id,
email_civireport.email AS civicrm_email_email,
(address_civireport.street_number % 2) AS civicrm_address_address_odd_street_number,
address_civireport.postal_code AS civicrm_address_address_postal_code
FROM civicrm_contact contact_civireport
LEFT JOIN civicrm_address address_civireport ON
(contact_civireport.id = address_civireport.contact_id)
AND address_civireport.is_primary = 1
LEFT JOIN civicrm_email email_civireport ON
contact_civireport.id = email_civireport.contact_id
AND email_civireport.is_primary = 1
WHERE ((contact_civireport.contact_type IN ('Individual')))
AND contact_civireport.id IN
(SELECT DISTINCT cgroup_civireport.contact_id
FROM civicrm_group_contact cgroup_civireport
WHERE cgroup_civireport.group_id IN (11)
AND cgroup_civireport.status = 'Added'
UNION DISTINCT SELECT DISTINCT smartgroup_contact.contact_id
FROM civicrm_group_contact_cache smartgroup_contact
WHERE smartgroup_contact.group_id IN (11) )
AND (`contact_civireport`.`id` IS NULL
OR (`contact_civireport`.`id` IN
(SELECT contact_id FROM civicrm_acl_contact_cache WHERE user_id = 6)))
AND (`contact_civireport`.`is_deleted` IS NULL
OR (`contact_civireport`.`is_deleted` != 1))
AND (`cgroup_civireport`.`id` IS NULL
OR (`cgroup_civireport`.`id` IN (3, 7, 9, 11)))
ORDER BY contact_civireport.sort_name ASC
LIMIT 0, 50
The error is caused by the last where clause which is generated from buildPermissionClause(), which is trying to access 'contact_civireport' that only exists in the nested where/filter subquery.
The last 3 where clauses are not present for the full access user (ie buildPermissionsClause() return empty string).
Expected behaviour
The report should run correctly without the last where clause for limited access use.
Environment information
- __Browser: Firefox 108
- __CiviCRM: 5.57.0
- __PHP: 8.1
- __CMS: WordPress 6.1.1
- __Database: MariaDB 10.5
- __Web Server: Apache 2.4
Comments
The problem appears to be in civicrm/CRM/report/form.php.
From what I read ...
The last three where clauses in the above are generated by the function buildPermissionClause() which uses a BAO call to generate a user-specific clause for each table in the query where the user does not have full access.
This relies on function selectedTables() to generate the list of tables. The last part of selectedTables function searches all the filters an adds them to the list of tables, however for filters using $filterop 'in' or 'notin', the tables referenced by these clauses may not exist in the main query. It would seem likely that the selected tables list should not include tables where the filter uses these two constructs.
I am not sure apart from 'group' filter (which has specific handling), if any report filters would use 'in/not in (select...)' rather than 'in/out (value list)' so I am not sure the best way to fix this as I have only investigated my usage.
The solution may be to explicitly excluding contact group table (cgroup_civireport), or by updating to selecttables to exclude all 'in' and 'notin' filters as there is no guarantee the table exists in the main query for these types of filters.
Change in function selecttables (line ~4388) with
$this->_selectedTables[] = $tableName;"
to:
if ( ! in_array($filterOp, ["in","notin"] )) $this->_selectedTables[] = $tableName;
If the intent is to restrict access to acl enabled groups this would need the same clause added in function joinGroupTempTable(). There may be other places where this should apply.
(On a side issue as I am new here - in my sql query, civicrm_address_address_odd_street_number is not a column that is selectable - it seems to be preset as required and hidden for some reason and limited to a 'Walking Report' template. Having this would cause an unnecessary join to the address table if no address fields are being reported - only a little performance thing but should this be another problem or improvement request?).