From a047568869892f9af65002c1709af1931f5f4b0d Mon Sep 17 00:00:00 2001
From: "deb.monish" <monish.deb@jmaconsulting.biz>
Date: Tue, 26 Jun 2018 16:03:17 +0530
Subject: [PATCH] dev/core#210: Regex filter broken in Search Builder

---
 CRM/Contact/BAO/Query.php                  |  5 ++-
 tests/phpunit/CRM/Contact/SelectorTest.php | 47 ++++++++++++++++++++++
 2 files changed, 51 insertions(+), 1 deletion(-)

diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index eda7792aa7..61a76fe274 100644
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -2341,7 +2341,7 @@ class CRM_Contact_BAO_Query {
         $this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($fieldName, $value, $type);
       }
       else {
-        if (!strpos($op, 'IN')) {
+        if (!self::caseImportant($op)) {
           $value = $strtolower($value);
         }
         if ($wildcard) {
@@ -5669,6 +5669,9 @@ SELECT COUNT( conts.total_amount ) as cancel_count,
         $clause = ($dataType == 'Date') ? " $field IS NOT NULL " : " (NULLIF($field, '') IS NOT NULL) ";
         return $clause;
 
+      case 'RLIKE':
+        return " {$clause} BINARY '{$value}' ";
+
       case 'IN':
       case 'NOT IN':
         // I feel like this would be escaped properly if passed through $queryString = CRM_Core_DAO::createSqlFilter.
diff --git a/tests/phpunit/CRM/Contact/SelectorTest.php b/tests/phpunit/CRM/Contact/SelectorTest.php
index a000c70e1c..6394847a5b 100644
--- a/tests/phpunit/CRM/Contact/SelectorTest.php
+++ b/tests/phpunit/CRM/Contact/SelectorTest.php
@@ -367,6 +367,53 @@ class CRM_Contact_Form_SelectorTest extends CiviUnitTestCase {
     $this->assertEquals('test@test.com', $rows[$contactID]['Non_ASCII_Location_Type-email']);
   }
 
+  /**
+   * Test the value use in where clause if it's case sensitive or not against each MySQL operators
+   */
+  public function testWhereClauseByOperator() {
+    $contactID = $this->individualCreate(['first_name' => 'Adam']);
+
+    $filters = [
+      'IS NOT NULL' => 1,
+      '=' => 'Adam',
+      'LIKE' => '%Ad%',
+      'RLIKE' => '^A[a-z]{3}$',
+      'IN' => ['IN' => ['Adam']],
+    ];
+    $filtersByWhereClause = [
+      'IS NOT NULL' => '( contact_a.first_name IS NOT NULL )', // doesn't matter
+      '=' => "( contact_a.first_name = 'Adam' )", // case sensitive check
+      'LIKE' => "( contact_a.first_name LIKE '%ad%' )", // case insensitive check
+      'RLIKE' => "(  contact_a.first_name RLIKE BINARY '^A[a-z]{3}$'  )", // case sensitive check
+      'IN' => '( contact_a.first_name IN ("Adam") )', // case sensitive check
+    ];
+    foreach ($filters as $op => $filter) {
+      $selector = new CRM_Contact_Selector(
+        'CRM_Contact_Selector',
+        ['first_name' => [$op => $filter]],
+        [[
+          0 => 'first_name',
+          1 => $op,
+          2 => $filter,
+          3 => 1,
+          4 => 0,
+        ]],
+        [],
+        CRM_Core_Action::NONE,
+        NULL,
+        FALSE,
+        'builder'
+      );
+
+      $sql = $selector->getQueryObject()->query();
+      $this->assertEquals(TRUE, strpos($sql[2], $filtersByWhereClause[$op]));
+
+      $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, TRUE, NULL);
+      $this->assertEquals(1, count($rows));
+      $this->assertEquals($contactID, key($rows));
+    }
+  }
+
   /**
    * Test if custom table is added in from clause when
    * search results are ordered by a custom field.
-- 
2.25.1