From 6a489c2fa1068464b6e6646d4729dea9c520c6cb Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Tue, 24 May 2016 18:01:18 -0700
Subject: [PATCH] CRM_Contact_BAO_Query::searchQuery - Preserve $order until
 last minute (EVIL)

Building on https://gist.github.com/mollux/7ffeb63f55ba0ba6180968d6d36f4745

This function does a lot of string-analysis on `$order`, and proper escaping
breaks this. However, the CRM_Utils_Type::escape() is quite clever about
validating and applying escaping.
---
 CRM/Contact/BAO/Query.php | 17 +++++++++++++----
 1 file changed, 13 insertions(+), 4 deletions(-)

diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index 235b3713ed..c0d45a0111 100644
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -4570,18 +4570,20 @@ civicrm_relationship.is_permission_a_b = 0
           else {
             $orderBy = trim($sort->orderBy());
           }
+          // Deliberately remove the backticks again, as they mess up the evil
+          // string munging below. This balanced by re-escaping before use.
+          $orderBy = str_replace('`', '', $orderBy);
+
           if (!empty($orderBy)) {
             // this is special case while searching for
             // change log CRM-1718
-            if (preg_match('/`sort_name`/i', $orderBy)) {
-              $orderBy = str_replace('`sort_name`', '`contact_a`.`sort_name`', $orderBy);
+            if (preg_match('/sort_name/i', $orderBy)) {
+              $orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy);
             }
 
-            $orderBy = CRM_Utils_Type::escape($orderBy, 'String');
             $order = " ORDER BY $orderBy";
 
             if ($sortOrder) {
-              $sortOrder = CRM_Utils_Type::escape($sortOrder, 'String');
               $order .= " $sortOrder";
             }
 
@@ -4641,6 +4643,13 @@ civicrm_relationship.is_permission_a_b = 0
         }
       }
 
+      // The above code relies on crazy brittle string manipulation of a peculiarly-encoded ORDER BY
+      // clause. But this magic helper which forgivingly reescapes ORDER BY.
+      // Note: $sortByChar implies that $order was hard-coded/trusted, so it can do funky things.
+      if ($order && !$sortByChar) {
+        $order = ' ORDER BY ' . CRM_Utils_Type::escape(preg_replace('/^\s*ORDER BY\s*/', '', $order), 'MysqlOrderBy');
+      }
+
       if ($rowCount > 0 && $offset >= 0) {
         $offset = CRM_Utils_Type::escape($offset, 'Int');
         $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
-- 
2.25.1