CiviCRM Community Forums (archive)

Hi,

I'm using hook_civicrm_aclWhereClause to implement read-protection of VIP contacts (those with a specific tag) by unauthorized Drupal users (those whose role denies viewing/editing of contacts but are members of ACL groups which provide those permissions)

The code works on Civi 4.3.5 / Drupal 7.31. I cloned that site and then upgraded the clone to Civi 4.5 Beta7 / Drupal 7.31.

On either site, when a user without the necessary Drupal permissions performs a search, the hook is called as expected. But on the 4.5 Beta7 site an error occurs as shown below.

The message complains about "Unknown column 'tag_table.tag_id' in 'where clause'", although my hook adds an addition to $tables[] and $whereTables[] providing an appropriate LEFT JOIN and defining tag_table  as an alias of civicrm_entity_tag. That added join inexplicably does not appear in the query quoted by the error message however.

To recreate the problem:
1. Install the hook shown below (replacing "hook_" with an appropriate module name).
2. Log in as a user lacking the view/edit contacts permissions.
3. Perform a contact search: Search | Find Contacts | Click Search button.

Error message:
DB Error: no such field
Error Details
Database Error Code: Unknown column 'tag_table.tag_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 '%bob%' ) OR  ( civicrm_email.email LIKE '%bob%' )  )  )  )  AND  ( 1 )  AND (`tag_table`.tag_id IS NULL) AND (contact_a.is_deleted = 0)    ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc  [nativecode=1054 ** Unknown column 'tag_table.tag_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 '%bob%' ) OR  ( civicrm_email.email LIKE '%bob%' )  )  )  )  AND  ( 1 )  AND (`tag_table`.tag_id IS NULL) AND (contact_a.is_deleted = 0)    ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc  [nativecode=1054 ** Unknown column 'tag_table.tag_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 '%bob%' ) OR  ( civicrm_email.email LIKE '%bob%' )  )  )  )  AND  ( 1 )  AND (`tag_table`.tag_id IS NULL) AND (contact_a.is_deleted = 0)    ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc  [nativecode=1054 ** Unknown column 'tag_table.tag_id' in 'where clause']"]
)

Hook code:
function hook_civicrm_aclWhereClause($type, &$tables, &$whereTables, &$contactID, &$where) {
  CRM_Core_Session::setStatus('civicrm_aclWhereClause: type=' . $type, 'Debug info', 'info');
  if (! $contactID) {
    return false;
  }
  if ($type == 1 && count($where)==0) {
    return false;  //prevent an Edit link from appearing on the profile if the user does not have contact-edit permission
  }
  $tables['tag'] = $whereTables['tag'] = "\nLEFT JOIN civicrm_entity_tag `tag_table` ON contact_a.id = `tag_table`.entity_id AND `tag_table`.entity_table = 'civicrm_contact' AND `tag_table`.tag_id = 6"; // VIP tag
  if (strlen ( trim ( $where ) ) != 0) {
    $where .= ' AND ';
  }
  $where .= '(`tag_table`.tag_id IS NULL)';
  return true;
}


Hmm - I would bet this is related to CRM-15231 (https://issues.civicrm.org/jira/browse/CRM-15231), which I submitted earlier today. 

In both cases:
* ACLs are failing with an "unknown column in WHERE clause" error.
* There's obviously a JOIN missing in both cases from the SELECT but not from the WHERE.
* they weren't failing on an earlier version.

My use case (in the ticket) is substantially simpler, and doesn't involve extensions/hooks, which isolates the problem to core.

I'm going to post a link to this post on the ticket, because it seems like this is the same bug.

Problem solved by patch https://github.com/civicrm/civicrm-core/pull/4088.

Thanks Jon!

Bob