CiviCRM Community Forums (archive)

When trying to implement ACLs, I ran into a problem when testing a user who didn't have permissions to edit a given contact.  It looks like the IN statements on groups are not enclosed in quotes:
SELECT
                    civicrm_group_contact.id as civicrm_group_contact_id,
                    civicrm_group.title as group_title,
                    civicrm_group.visibility as visibility,
                    civicrm_group_contact.status as status,
                    civicrm_group.id as group_id,
                    civicrm_subscription_history.date as date,
                    civicrm_subscription_history.method as method FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id  LEFT JOIN civicrm_group ON civicrm_group.id =  civicrm_group_contact.group_id  LEFT JOIN civicrm_subscription_history
                                   ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id
                                  AND civicrm_group_contact.group_id   =  civicrm_subscription_history.group_id WHERE contact_a.id = 119 AND civicrm_group.is_active = 1  AND civicrm_group_contact.status = 'Added' AND  (  ( civicrm_group_contact.group_id IN (*Constituents of your organization, *Grasstops (non-constituent contacts), *Legislators, 4 of em, Admins, dd, Deleted contacts, doer, doera, doh, New TESTEMENT, newer testement, newer testement2, once more, once more 2, ryan email test group, Sen District 5 & 8 (smart), SMART Sen District 12, SMART Sen District 4,6,&8, SMART test group, Smarty, swing moratorium legislators, Test Group, Test Legislators, Test-43, tester-7, TestGroup, TestLegislators, woah ) AND civicrm_group_contact.status = 'Added' )  )   ORDER BY civicrm_group.title  LIMIT 0, 3 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*Constituents of your organization, *Grasstops (non-constituent contacts), *Legi' at line 10]
    [type] => DB_Error


Thanks,
Jacob

Got the same problem with the latest stable CiviCRM (civicrm-1.7.9821-drupal-php4.tar.gz), and wrote a patch for it (below)
By the way: the group_id values are all integers.

BUT!!!
restricted roles can't see most groups in the search then.

Any ideas?

My patch for contact display:
--- /tmp/civicrm/CRM/Core/Permission/Drupal.php   2007-06-08 01:29:09.000000000 -0400
+++ CRM/Core/Permission/Drupal.php   2007-06-13 12:30:03.000000000 -0400
@@ -142,8 +142,8 @@
             } else {
                 $clauses = array( );
                 $groups = implode( ', ', $GLOBALS['_CRM_CORE_PERMISSION_DRUPAL']['_editPermissionedGroups'] );
-                $clauses[] = ' ( civicrm_group_contact.group_id IN ( ' . implode( ', ', array_keys( $GLOBALS['_CRM_CORE_PERMISSION_DRUPAL']['_editPermissionedGroups'] ) ) .
-                    " ) AND civicrm_group_contact.status = 'Added' ) ";
+                $clauses[] = ' ( civicrm_group_contact.group_id IN ( "' . implode( '", "', array_keys( $GLOBALS['_CRM_CORE_PERMISSION_DRUPAL']['_editPermissionedGroups'] ) ) .
+                    "\" ) AND civicrm_group_contact.status = 'Added' ) ";
                 $tables['civicrm_group_contact'] = 1;
                 $whereTables['civicrm_group_contact'] = 1;
                 
@@ -166,8 +166,8 @@
             } else {
                 $clauses = array( );
                 $groups = implode( ', ', $GLOBALS['_CRM_CORE_PERMISSION_DRUPAL']['_viewPermissionedGroups'] );
-                $clauses[] = ' ( civicrm_group_contact.group_id IN (' . implode( ', ', array_keys( $GLOBALS['_CRM_CORE_PERMISSION_DRUPAL']['_viewPermissionedGroups'] ) ) .
-                    " ) AND civicrm_group_contact.status = 'Added' ) ";
+                $clauses[] = ' ( civicrm_group_contact.group_id IN ("' . implode( '", "', array_keys( $GLOBALS['_CRM_CORE_PERMISSION_DRUPAL']['_viewPermissionedGroups'] ) ) .
+                    "\" ) AND civicrm_group_contact.status = 'Added' ) ";
                 $tables['civicrm_group_contact'] = 1;
                 $whereTables['civicrm_group_contact'] = 1;
 
@@ -239,4 +239,4 @@
 
 }
 
-?>
\ No newline at end of file
+?>

To Donald Lobo: Thanks in advance!