From f82f7ac94a68732798ea87b7180bf3080481cd7e Mon Sep 17 00:00:00 2001
From: Mattias Michaux <mattias.michaux@gmail.com>
Date: Tue, 3 May 2016 22:53:59 +0200
Subject: [PATCH] Added extra Mysql types and rules + tests similar to 4.7

---
 CRM/Utils/Rule.php                   | 22 +++++++++++++
 CRM/Utils/Type.php                   | 48 +++++++++++++++++++++++++++-
 tests/phpunit/CRM/Utils/TypeTest.php | 25 +++++++++++++++
 3 files changed, 94 insertions(+), 1 deletion(-)

diff --git a/CRM/Utils/Rule.php b/CRM/Utils/Rule.php
index 9c59136f9a..32cbd75919 100644
--- a/CRM/Utils/Rule.php
+++ b/CRM/Utils/Rule.php
@@ -103,6 +103,9 @@ class CRM_Utils_Rule {
     }
 
     // Ensure it only contains valid characters (alphanumeric and underscores).
+    //
+    // MySQL permits column names that don't match this (eg containing spaces),
+    // but CiviCRM won't create those ...
     if (!preg_match('/^\w{1,64}(\.\w{1,64})?$/i', $str)) {
       return FALSE;
     }
@@ -126,6 +129,25 @@ class CRM_Utils_Rule {
     return TRUE;
   }
 
+  /**
+   * Validate that a string is valid order by clause.
+   *
+   * @param $str
+   * @return bool
+   */
+  public static function mysqlOrderBy($str) {
+    // Making a regex for a comma separated list is quite hard and not readable
+    // at all, so we split and loop over.
+    $parts = explode(',', $str);
+    foreach ($parts as $part) {
+      if (!preg_match('/^((\w{1,64})((\.)(\w{1,64}))?( (asc|desc))?)$/i', trim($part))) {
+        return FALSE;
+      }
+    }
+
+    return TRUE;
+  }
+
   /**
    * @param $str
    *
diff --git a/CRM/Utils/Type.php b/CRM/Utils/Type.php
index e299b6eae0..730797d64e 100644
--- a/CRM/Utils/Type.php
+++ b/CRM/Utils/Type.php
@@ -269,7 +269,17 @@ class CRM_Utils_Type {
 
       case 'MysqlOrderByDirection':
         if (CRM_Utils_Rule::mysqlOrderByDirection($data)) {
-          return $data;
+          return strtolower($data);
+        }
+        break;
+
+      case 'MysqlOrderBy':
+        if (CRM_Utils_Rule::mysqlOrderBy($data)) {
+          $parts = explode(',', $data);
+          foreach ($parts as &$part) {
+            $part = preg_replace_callback('/(?:([\w]+)(?:(?:\.)([\w]+))?(?: (asc|desc))?)/i', array('CRM_Utils_Type', 'mysqlOrderByCallback'), trim($part));
+          }
+          return implode(', ', $parts);
         }
         break;
 
@@ -376,6 +386,22 @@ class CRM_Utils_Type {
         }
         break;
 
+      case 'MysqlColumnName':
+        if (CRM_Utils_Rule::mysqlColumnName($data)) {
+          return $data;
+        }
+        break;
+      case 'MysqlOrderByDirection':
+        if (CRM_Utils_Rule::mysqlOrderByDirection($data)) {
+          return strtolower($data);
+        }
+        break;
+      case 'MysqlOrderBy':
+        if (CRM_Utils_Rule::mysqlOrderBy($data)) {
+          return $data;
+        }
+        break;
+
       default:
         CRM_Core_Error::fatal("Cannot recognize $type for $data");
         break;
@@ -389,4 +415,24 @@ class CRM_Utils_Type {
     return NULL;
   }
 
+  /**
+   * preg_replace_callback for MysqlOrderBy escape.
+   */
+  public static function mysqlOrderByCallback($matches) {
+    $output = '';
+    // Column or table name.
+    if (isset($matches[1])) {
+      $output .= '`' . $matches[1] . '`';
+    }
+    // Column name in case there is a table.
+    if (isset($matches[2]) && $matches[2]) {
+      $output .= '.`' . $matches[2] . '`';
+    }
+    // Sort order.
+    if (isset($matches[3]) && $matches[3]) {
+      $output .= ' ' . $matches[3];
+    }
+    return $output;
+  }
+
 }
diff --git a/tests/phpunit/CRM/Utils/TypeTest.php b/tests/phpunit/CRM/Utils/TypeTest.php
index e9235565cc..9c46b671b2 100644
--- a/tests/phpunit/CRM/Utils/TypeTest.php
+++ b/tests/phpunit/CRM/Utils/TypeTest.php
@@ -38,6 +38,21 @@ class CRM_Utils_TypeTest extends CiviUnitTestCase {
       array(-10, 'Positive', NULL),
       array('-10', 'Positive', NULL),
       array('-10foo', 'Positive', NULL),
+      array('civicrm_column_name', 'MysqlColumnName', 'civicrm_column_name'),
+      array('table.civicrm_column_name', 'MysqlColumnName', 'table.civicrm_column_name'),
+      array('table.civicrm_column_name.toomanydots', 'MysqlColumnName', NULL),
+      array('invalid-column-name', 'MysqlColumnName', NULL),
+      array('column_name, sleep(5)', 'MysqlColumnName', NULL),
+      array(str_repeat('a', 64), 'MysqlColumnName', str_repeat('a', 64)),
+      array(str_repeat('a', 65), 'MysqlColumnName', NULL),
+      array(str_repeat('a', 64) . '.' . str_repeat('a', 64), 'MysqlColumnName', str_repeat('a', 64) . '.' . str_repeat('a', 64)),
+      array(str_repeat('a', 64) . '.' . str_repeat('a', 65), 'MysqlColumnName', NULL),
+      array(str_repeat('a', 65) . '.' . str_repeat('a', 64), 'MysqlColumnName', NULL),
+      array('asc', 'MysqlOrderByDirection', 'asc'),
+      array('DESC', 'MysqlOrderByDirection', 'desc'),
+      array('DESCc', 'MysqlOrderByDirection', NULL),
+      array('table.civicrm_column_name desc', 'MysqlOrderBy', 'table.civicrm_column_name desc'),
+      array('table.civicrm_column_name desc,other_column, another_column desc', 'MysqlOrderBy', 'table.civicrm_column_name desc,other_column, another_column desc'),
     );
   }
 
@@ -76,6 +91,16 @@ class CRM_Utils_TypeTest extends CiviUnitTestCase {
       array('-3', 'ContactReference', NULL),
       // Escape function is meant for sql, not xss
       array('<p onclick="alert(\'xss\');">Hello</p>', 'Memo', '<p onclick=\\"alert(\\\'xss\\\');\\">Hello</p>'),
+      array('civicrm_column_name', 'MysqlColumnName', '`civicrm_column_name`'),
+      array('table.civicrm_column_name', 'MysqlColumnName', '`table`.`civicrm_column_name`'),
+      array('table.civicrm_column_name.toomanydots', 'MysqlColumnName', NULL),
+      array('invalid-column-name', 'MysqlColumnName', NULL),
+      array('column_name, sleep(5)', 'MysqlColumnName', NULL),
+      array('asc', 'MysqlOrderByDirection', 'asc'),
+      array('DESC', 'MysqlOrderByDirection', 'desc'),
+      array('DESCc', 'MysqlOrderByDirection', NULL),
+      array('table.civicrm_column_name desc', 'MysqlOrderBy', '`table`.`civicrm_column_name` desc'),
+      array('table.civicrm_column_name desc,other_column,another_column desc', 'MysqlOrderBy', '`table`.`civicrm_column_name` desc, `other_column`, `another_column` desc'),
     );
   }
 
-- 
2.25.1