"DB Error: no such field" due to hook_civicrm_aclWhereClause

[bsilvern]

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:

Code: [Select]

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:

Code: [Select]

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;
}


[bsilvern]

<crickets>
OK, so it's been 2 weeks and I've gotten no response. I'd like to know if I should submit this as a bug report, hopefully before 4.5 is released. Would someone please advise?

Thx,
Bob

[JonGold]

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.

[bsilvern]

Thank you for your response, JonGold. Yes, that does seem to be a related issue. I guess I'll just wait and see if the next release solves my problem. BTW, the link you provided in your bug report to this thread is incorrect so you might want to update that.

Thx,
Bob

[bsilvern]

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

Thanks Jon!

Bob