CRM-15231 Fatal error on search when ACLs are enabled

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.5
    • Fix Version/s: 4.5
    • Component/s: Core CiviCRM
    • Labels:
      None
    • Documentation Required?:
      None

      Description

      Tested this problem on 4.5b6 and 4.5b8. I don't have the permissions to replicate this on the sandbox server.

      In Civi 4.4, I had an ACL group that worked well. On upgrade to 4.5, if I'm logged in as a user in that ACL group (who does NOT have "View all CiviCRM Contacts", I get the error below when I try to do any searches. Users who can view all contacts are unaffected. Also note that QuickSearch DOES work as intended, unless I opt to run a search rather than select a single contact from the dropdown widget.

      Please let me know if there's any other info/testing I can provide!

      ------

      Sorry but we are not able to provide this at the moment.
      DB Error: no such field
      Error Details
      Database Error Code: Unknown column 'civicrm_group_contact_cache-ACL.group_id' in 'where clause', 1054
      Additional Details:

      Array
      (
      [callback] => Array
      (
      [0] => CRM_Core_Error
      [1] => handle
      )

      [code] => -19
      [message] => DB Error: no such field
      [mode] => 16
      [debug_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) WHERE ( ( ( ( contact_a.sort_name LIKE '%a%' ) OR ( contact_a.nick_name LIKE '%a%' ) OR ( civicrm_email.email LIKE '%a%' ) ) ) ) AND ( `civicrm_group_contact_cache-ACL`.group_id IN (24) ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown column 'civicrm_group_contact_cache-ACL.group_id' in 'where clause']
      [type] => DB_Error
      [user_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) WHERE ( ( ( ( contact_a.sort_name LIKE '%a%' ) OR ( contact_a.nick_name LIKE '%a%' ) OR ( civicrm_email.email LIKE '%a%' ) ) ) ) AND ( `civicrm_group_contact_cache-ACL`.group_id IN (24) ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown column 'civicrm_group_contact_cache-ACL.group_id' in 'where clause']
      [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) WHERE ( ( ( ( contact_a.sort_name LIKE '%a%' ) OR ( contact_a.nick_name LIKE '%a%' ) OR ( civicrm_email.email LIKE '%a%' ) ) ) ) AND ( `civicrm_group_contact_cache-ACL`.group_id IN (24) ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown column 'civicrm_group_contact_cache-ACL.group_id' in 'where clause']"]
      )

        Attachments

          Activity

          [CRM-15231] Fatal error on search when ACLs are enabled
          Jon K Goldberg added a comment -

          This forum post seems to be describing the same bug - in both cases, an ACL-related SQL statement is missing a JOIN from the SELECT that appears in the WHERE portion of the SQL. Also in both cases, the problem didn't appear in an earlier version, just in 4.5beta.
          http://forum.civicrm.org/index.php/board,41.0.html

          Jon K Goldberg added a comment -
          Eileen McNaughton added a comment -

          Do API contact.get calls work as the non-permissioned user (e.g in firebug console CRM.api('contact', 'get', {}): will call the api with ACLs

          There is actually a test class for the API ACLs

          https://github.com/civicrm/civicrm-core/blob/master/tests/phpunit/api/v3/ACLPermissionTest.php

          But, for some reason the api calls the query object in a different way to the way in which search calls it

          Atif Shaikh added a comment -
          Bob Silvern added a comment -

          OP of the above mentioned forum thread here. Patch #4088 solved my problem. Thank you all!

          Eileen McNaughton added a comment -

          I added this comment on the PR too

          hmm - that's pretty deep into the code to be checking a $_GET variable - how does that get into the query object? Can it be pulled out closer to source? (As a rule the BAO should never care about $_REQUEST type vars)

          Do we know if API calls are affected? This fix seems like it would work from a particular form but not it the query object were accessed in different ways

          Atif Shaikh added a comment -

          Updated the PR

          Monish Deb added a comment -

          PR change looks good to me. Tested and confirmed. Jon/Bob can you please verify with the latest PR again ? Thank you

          Eileen McNaughton added a comment -

          yes - the change addresses the concern I had

          Monish Deb added a comment -

          Well on closer look there is a regression on api tests according to related test build https://test.civicrm.org/job/CiviCRM-Core-PR/509/#showFailuresLink. Please fix the regression spotted. Thanks

          Atif Shaikh added a comment -

          Fixed the regression, this is the latest related test build https://test.civicrm.org/job/CiviCRM-Core-PR/513/

          Eileen McNaughton added a comment -

          looks like the API tests worked as they are supposed to - good to see (although I haven't looked at the fix to see what they were catching)

          Bob Silvern added a comment -

          Latest PR 4088 (commit ac4985d) works for me.

          Monish Deb added a comment -

          Confirmed and merged.

            People

            • Assignee:
              Monish Deb
              Reporter:
              Jon K Goldberg

              Dates

              • Created:
                Updated:
                Resolved: