CiviCRM Community Forums (archive)

When logged in as an ACL restricted user, the Contribution and Membership Details report fails with the error message "Sorry but we are not able to provide this at the moment.  DB Error: syntax error".  See civicrm/report/member/contributionDetail?reset=1

We performed this on version 4.4.3 of CiviCRM

The error report shows a malformed SQL query:

INSERT INTO civireport_membership_contribution_detail (contribution_id, contact_id, membership_id)
          SELECT contribution.id, contact.id, m.id
          FROM civicrm_contribution contribution
          INNER JOIN civicrm_contact contact  INNER JOIN civicrm_acl_contact_cache aclContactCache ON contact_civireport.id = aclContactCache.contact_id
                ON contact.id = contribution.contact_id AND contribution.is_test = 0
           LEFT JOIN civicrm_membership_payment mp
                ON contribution.id = mp.contribution_id
           LEFT JOIN civicrm_membership m
                ON mp.membership_id = m.id AND m.is_test = 0  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaD
B server version for the right syntax to use near 'contact.id = contribution.contact_id AND contribution.is_test = 0
           LEF' at line 5]
Note how the INNER JOIN for the ACL related table seems to be placed right into the middle of the contact INNER JOIN clause.

The stack trace confirms that the calling function originates from CRM/Report/Form/Member/ContributionDetail.php(641)

Huh, that was a lot simpler than I thought it was.  Patch below (git diff)

diff --git a/CRM/Report/Form/Member/ContributionDetail.php b/CRM/Report/Form/Member/ContributionDetail.php
index 442c6f8..7972900 100644
--- a/CRM/Report/Form/Member/ContributionDetail.php
+++ b/CRM/Report/Form/Member/ContributionDetail.php
@@ -631,8 +631,9 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form {
           INSERT INTO civireport_membership_contribution_detail (contribution_id, contact_id, membership_id)
           SELECT contribution.id, contact.id, m.id
           FROM civicrm_contribution contribution
-          INNER JOIN civicrm_contact contact {$this->_aclFrom}
+          INNER JOIN civicrm_contact contact
                 ON contact.id = contribution.contact_id AND contribution.is_test = 0
+          {$this->_aclFrom}
            LEFT JOIN civicrm_membership_payment mp
                 ON contribution.id = mp.contribution_id
            LEFT JOIN civicrm_membership m