From e5be7043d4c106b0c93ca1ce05ea0a3bf859096b Mon Sep 17 00:00:00 2001
From: demeritcowboy <demeritcowboy@hotmail.com>
Date: Tue, 17 Nov 2020 18:56:00 -0500
Subject: [PATCH] for logging compare as case-sensitive and accent-sensitive

---
 CRM/Logging/Schema.php                   | 13 ++++++-
 tests/phpunit/CRM/Logging/SchemaTest.php | 45 ++++++++++++++++++++++++
 2 files changed, 57 insertions(+), 1 deletion(-)

diff --git a/CRM/Logging/Schema.php b/CRM/Logging/Schema.php
index 6a75405d4c..cd7dfd5b82 100644
--- a/CRM/Logging/Schema.php
+++ b/CRM/Logging/Schema.php
@@ -948,6 +948,12 @@ COLS;
       }
       $columns = $this->columnsOf($table, $force);
 
+      // Use utf8mb4_bin or utf8_bin, depending on what's in use.
+      $charset = 'utf8';
+      if (stripos(CRM_Core_BAO_SchemaHandler::getInUseCollation(), 'utf8mb4') !== FALSE) {
+        $charset = 'utf8mb4';
+      }
+
       // only do the change if any data has changed
       $cond = [];
       foreach ($columns as $column) {
@@ -958,7 +964,12 @@ COLS;
         $excludeColumn = in_array($column, $tableExceptions) ||
           in_array(str_replace('`', '', $column), $tableExceptions);
         if (!$excludeColumn) {
-          $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
+          // The empty string needs charset signalling to avoid errors.
+          // Note that it is not a cast/convert. It just tells mysql
+          // that there isn't a conflict when your system/connection defaults
+          // happen to be different from $charset.
+          // See https://dev.mysql.com/doc/refman/5.7/en/charset-literal.html
+          $cond[] = "IFNULL(OLD.$column,_{$charset}'') <> IFNULL(NEW.$column,_{$charset}'') COLLATE {$charset}_bin";
         }
       }
       $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
diff --git a/tests/phpunit/CRM/Logging/SchemaTest.php b/tests/phpunit/CRM/Logging/SchemaTest.php
index 5356c14e6d..b2f0bea21d 100644
--- a/tests/phpunit/CRM/Logging/SchemaTest.php
+++ b/tests/phpunit/CRM/Logging/SchemaTest.php
@@ -375,6 +375,51 @@ class CRM_Logging_SchemaTest extends CiviUnitTestCase {
     $this->assertStringContainsString("`{$custom_field['column_name']}` varchar(768)", $dao->Create_Table);
   }
 
+  /**
+   * Test that logging records changes in upper/lower-case and accents.
+   * e.g. changing e to E or e to é
+   * @dataProvider loggingSensitivityProvider
+   *
+   * @param array $input
+   */
+  public function testLoggingSensitivity(array $input) {
+    $schema = new CRM_Logging_Schema();
+    $schema->enableLogging();
+
+    // create a contact with all lower case/no accents
+    $contact_id = $this->individualCreate(['first_name' => 'pierre']);
+
+    $query_params = [
+      1 => [$contact_id, 'Integer'],
+      2 => [$input['first_name'], 'String'],
+    ];
+
+    // Clear out anything that api did twice during initial creation so that
+    // spurious update records don't give false results.
+    CRM_Core_DAO::executeQuery("DELETE FROM log_civicrm_contact WHERE id = %1 AND log_action = 'update'", $query_params);
+
+    // Change the first name
+    CRM_Core_DAO::executeQuery("UPDATE civicrm_contact SET first_name = %2 WHERE id = %1", $query_params);
+    // check the log
+    $dao = CRM_Core_DAO::executeQuery("SELECT first_name FROM log_civicrm_contact WHERE id = %1 AND log_action = 'update'", $query_params);
+    $first_name = NULL;
+    if ($dao->fetch()) {
+      $first_name = $dao->first_name;
+    }
+    $this->assertSame($input['first_name'], $first_name);
+  }
+
+  /**
+   * Dataprovider for testLoggingSensitivity
+   * @return array
+   */
+  public function loggingSensitivityProvider():array {
+    return [
+      'upper' => [['first_name' => 'Pierre']],
+      'accent' => [['first_name' => 'pièrre']],
+    ];
+  }
+
   /**
    * Test creating a table with SchemaHandler::createTable when logging
    * is enabled.
-- 
2.25.1