From a33b83c5cd9095cd2c1c9e2241bd9d5d4c141206 Mon Sep 17 00:00:00 2001 From: Mattias Michaux Date: Thu, 12 May 2016 09:42:08 +0200 Subject: [PATCH] Added support for both aliased and non-aliased column names. --- CRM/Core/Page/AJAX.php | 2 +- CRM/Utils/Rule.php | 16 +++++++--------- CRM/Utils/Sort.php | 2 +- CRM/Utils/Type.php | 20 +++++++++++--------- tests/phpunit/CRM/Utils/TypeTest.php | 26 ++++++++++++++++---------- 5 files changed, 36 insertions(+), 30 deletions(-) diff --git a/CRM/Core/Page/AJAX.php b/CRM/Core/Page/AJAX.php index 84185152f1..5830a624cf 100644 --- a/CRM/Core/Page/AJAX.php +++ b/CRM/Core/Page/AJAX.php @@ -222,7 +222,7 @@ class CRM_Core_Page_AJAX { $sortMapper = array(); if (isset($_GET['columns'])) { foreach ($_GET['columns'] as $key => $value) { - $sortMapper[$key] = CRM_Utils_Type::validate($value['data'], 'MysqlColumnName'); + $sortMapper[$key] = CRM_Utils_Type::validate($value['data'], 'MysqlColumnNameOrAlias'); }; } diff --git a/CRM/Utils/Rule.php b/CRM/Utils/Rule.php index efb64eb858..3e6dbd0c4e 100644 --- a/CRM/Utils/Rule.php +++ b/CRM/Utils/Rule.php @@ -88,24 +88,22 @@ class CRM_Utils_Rule { } /** - * Validate an acceptable column name for sorting results. + * Validate that a string is a valid MySQL column name or alias. * * @param $str * * @return bool */ - public static function mysqlColumnName($str) { + public static function mysqlColumnNameOrAlias($str) { // Check not empty. if (empty($str)) { return FALSE; } - // Ensure it only contains valid characters (alphanumeric, hyphens 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)) { + // Ensure the string contains only valid characters: + // For column names: alphanumeric and underscores + // For aliases: backticks, alphanumeric hyphens and underscores. + if (!preg_match('^((`[\w-]{1,64}`|\w{1,64})\.)?(`[\w-]{1,64}`|\w{1,64})$/i', $str)) { return FALSE; } @@ -139,7 +137,7 @@ class CRM_Utils_Rule { // 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))) { + if (!preg_match('/^((`[\w-]{1,64}`|\w{1,64})\.)?(`[\w-]{1,64}`|\w{1,64})( (asc|desc))?$/i', trim($part))) { return FALSE; } } diff --git a/CRM/Utils/Sort.php b/CRM/Utils/Sort.php index 2b5765f235..fec2d400e3 100644 --- a/CRM/Utils/Sort.php +++ b/CRM/Utils/Sort.php @@ -121,7 +121,7 @@ class CRM_Utils_Sort { foreach ($vars as $weight => $value) { $this->_vars[$weight] = array( - 'name' => CRM_Utils_Type::validate($value['sort'], 'MysqlColumnName'), + 'name' => CRM_Utils_Type::validate($value['sort'], 'MysqlColumnNameOrAlias'), 'direction' => CRM_Utils_Array::value('direction', $value), 'title' => $value['name'], ); diff --git a/CRM/Utils/Type.php b/CRM/Utils/Type.php index 44e8f17a07..43fa00a5b2 100644 --- a/CRM/Utils/Type.php +++ b/CRM/Utils/Type.php @@ -268,8 +268,9 @@ class CRM_Utils_Type { } break; - case 'MysqlColumnName': - if (CRM_Utils_Rule::mysqlColumnName($data)) { + case 'mysqlColumnNameOrAlias': + if (CRM_Utils_Rule::mysqlColumnNameOrAlias($data)) { + $data = str_replace('`', '', $data); $parts = explode('.', $data); $data = '`' . implode('`.`', $parts) . '`'; @@ -287,7 +288,7 @@ class CRM_Utils_Type { 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)); + $part = preg_replace_callback('/^(?:(?:((?:`[\w-]{1,64}`|\w{1,64}))(?:\.))?(`[\w-]{1,64}`|\w{1,64})(?: (asc|desc))?)$/i', array('CRM_Utils_Type', 'mysqlOrderByCallback'), trim($part)); } return implode(', ', $parts); } @@ -396,8 +397,8 @@ class CRM_Utils_Type { } break; - case 'MysqlColumnName': - if (CRM_Utils_Rule::mysqlColumnName($data)) { + case 'mysqlColumnNameOrAlias': + if (CRM_Utils_Rule::mysqlColumnNameOrAlias($data)) { return $data; } break; @@ -432,15 +433,16 @@ class CRM_Utils_Type { */ public static function mysqlOrderByCallback($matches) { $output = ''; + $matches = str_replace('`', '', $matches); - // Column or table name. + // Table name. if (isset($matches[1])) { - $output .= '`' . $matches[1] . '`'; + $output .= '`' . $matches[1] . '`.'; } - // Column name in case there is a table. + // Column name. if (isset($matches[2]) && $matches[2]) { - $output .= '.`' . $matches[2] . '`'; + $output .= '`' . $matches[2] . '`'; } // Sort order. diff --git a/tests/phpunit/CRM/Utils/TypeTest.php b/tests/phpunit/CRM/Utils/TypeTest.php index 367f08e299..781b2d16ab 100644 --- a/tests/phpunit/CRM/Utils/TypeTest.php +++ b/tests/phpunit/CRM/Utils/TypeTest.php @@ -37,21 +37,26 @@ 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('Home-street_address', 'MysqlColumnName', 'Home-street_address'), - 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('civicrm_column_name', 'mysqlColumnNameOrAlias', 'civicrm_column_name'), + array('table.civicrm_column_name', 'mysqlColumnNameOrAlias', 'table.civicrm_column_name'), + array('table.civicrm_column_name.toomanydots', 'mysqlColumnNameOrAlias', NULL), + array('Home-street_address', 'mysqlColumnNameOrAlias', NULL), + array('`Home-street_address`', 'mysqlColumnNameOrAlias', '`Home-street_address`'), + array('table.`Home-street_address`', 'mysqlColumnNameOrAlias', 'table.`Home-street_address`'), + array('`table-alias`.`Home-street_address`', 'mysqlColumnNameOrAlias', '`table-alias`.`Home-street_address`'), + array('`table-alias`.column', 'mysqlColumnNameOrAlias', '`table-alias`.column'), + array('column_name, sleep(5)', 'mysqlColumnNameOrAlias', NULL), + array(str_repeat('a', 64), 'mysqlColumnNameOrAlias', str_repeat('a', 64)), + array(str_repeat('a', 65), 'mysqlColumnNameOrAlias', NULL), + array(str_repeat('a', 64) . '.' . str_repeat('a', 64), 'mysqlColumnNameOrAlias', str_repeat('a', 64) . '.' . str_repeat('a', 64)), + array(str_repeat('a', 64) . '.' . str_repeat('a', 65), 'mysqlColumnNameOrAlias', NULL), + array(str_repeat('a', 65) . '.' . str_repeat('a', 64), 'mysqlColumnNameOrAlias', 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'), + array('table.`Home-street_address` asc, `table-alias`.`Home-street_address` desc,`table-alias`.column', 'MysqlOrderBy', 'table.`Home-street_address` asc, `table-alias`.`Home-street_address` desc,`table-alias`.column'), ); } @@ -100,6 +105,7 @@ class CRM_Utils_TypeTest extends CiviUnitTestCase { 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'), + array('table.`Home-street_address` asc, `table-alias`.`Home-street_address` desc,`table-alias`.column', 'MysqlOrderBy', '`table`.`Home-street_address` asc, `table-alias`.`Home-street_address` desc, `table-alias`.`column`'), ); } -- 2.25.1