From bfd417c7fd7bc0e876c4ec9f113434aa12122536 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Sat, 7 Jul 2018 18:48:30 -0700 Subject: [PATCH] Implement CRM_Utils_SQL_Delete Before ------ * `CRM_Utils_SQL_Select` and `CRM_Utils_SQL_Insert` are query-building utilities. After ----- * `CRM_Utils_SQL_Select`, `CRM_Utils_SQL_Insert`, and `CRM_Utils_SQL_Delete` are query-building utilities. * `CRM_Utils_SQL_Select` and `CRM_Utils_SQL_Delete` extend a common base-class, which includes parameter-management/interpolation functions. * (*Note: Initially tried doing this as a trait, but traits aren't allowed to define constants.*) --- CRM/Utils/SQL/BaseParamQuery.php | 232 ++++++++++++++++++ CRM/Utils/SQL/Delete.php | 269 +++++++++++++++++++++ CRM/Utils/SQL/Select.php | 211 +--------------- tests/phpunit/CRM/Utils/SQL/DeleteTest.php | 39 +++ 4 files changed, 543 insertions(+), 208 deletions(-) create mode 100644 CRM/Utils/SQL/BaseParamQuery.php create mode 100644 CRM/Utils/SQL/Delete.php create mode 100644 tests/phpunit/CRM/Utils/SQL/DeleteTest.php diff --git a/CRM/Utils/SQL/BaseParamQuery.php b/CRM/Utils/SQL/BaseParamQuery.php new file mode 100644 index 0000000000..82fb91cfc3 --- /dev/null +++ b/CRM/Utils/SQL/BaseParamQuery.php @@ -0,0 +1,232 @@ +strict = $strict; + return $this; + } + + /** + * Given a string like "field_name = @value", replace "@value" with an escaped SQL string + * + * @param string $expr SQL expression + * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded: + * prefix '@' => escape SQL + * prefix '#' => literal number, skip escaping but do validation + * prefix '!' => literal, skip escaping and validation + * if a value is an array, then it will be imploded + * + * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string. + * + * @param string $activeMode + * + * @return string + */ + public function interpolate($expr, $args, $activeMode = self::INTERPOLATE_INPUT) { + if ($args === NULL) { + return $expr; + } + else { + if ($this->mode === self::INTERPOLATE_AUTO) { + $this->mode = $activeMode; + } + elseif ($activeMode !== $this->mode) { + throw new RuntimeException("Cannot mix interpolation modes."); + } + + $select = $this; + return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) { + if (isset($args[$m[2]])) { + $values = $args[$m[2]]; + } + elseif (isset($args[$m[1] . $m[2]])) { + // Backward compat. Keys in $args look like "#myNumber" or "@myString". + $values = $args[$m[1] . $m[2]]; + } + elseif ($select->strict) { + throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.'); + } + else { + // Unrecognized variables are ignored. Mitigate risk of accidents. + return $m[0]; + } + $values = is_array($values) ? $values : array($values); + switch ($m[1]) { + case '@': + $parts = array_map(array($select, 'escapeString'), $values); + return implode(', ', $parts); + + // TODO: ensure all uses of this un-escaped literal are safe + case '!': + return implode(', ', $values); + + case '#': + foreach ($values as $valueKey => $value) { + if ($value === NULL) { + $values[$valueKey] = 'NULL'; + } + elseif (!is_numeric($value)) { + //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($m[0] => $values), TRUE)); + throw new CRM_Core_Exception("Failed encoding non-numeric value (" . $m[0] . ")"); + } + } + return implode(', ', $values); + + default: + throw new CRM_Core_Exception("Unrecognized prefix"); + } + }, $expr); + } + } + + /** + * @param string|NULL $value + * @return string + * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) + */ + public function escapeString($value) { + return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; + } + + /** + * Set one (or multiple) parameters to interpolate into the query. + * + * @param array|string $keys + * Key name, or an array of key-value pairs. + * @param null|mixed $value + * The new value of the parameter. + * Values may be strings, ints, or arrays thereof -- provided that the + * SQL query uses appropriate prefix (e.g. "@", "!", "#"). + * @return $this + */ + public function param($keys, $value = NULL) { + if ($this->mode === self::INTERPOLATE_AUTO) { + $this->mode = self::INTERPOLATE_OUTPUT; + } + elseif ($this->mode !== self::INTERPOLATE_OUTPUT) { + throw new RuntimeException("Select::param() only makes sense when interpolating on output."); + } + + if (is_array($keys)) { + foreach ($keys as $k => $v) { + $this->params[$k] = $v; + } + } + else { + $this->params[$keys] = $value; + } + return $this; + } + + /** + * Has an offset been set. + * + * @param string $offset + * + * @return bool + */ + public function offsetExists($offset) { + return isset($this->params[$offset]); + } + + /** + * Get the value of a SQL parameter. + * + * @code + * $select['cid'] = 123; + * $select->where('contact.id = #cid'); + * echo $select['cid']; + * @endCode + * + * @param string $offset + * @return mixed + * @see param() + * @see ArrayAccess::offsetGet + */ + public function offsetGet($offset) { + return $this->params[$offset]; + } + + /** + * Set the value of a SQL parameter. + * + * @code + * $select['cid'] = 123; + * $select->where('contact.id = #cid'); + * echo $select['cid']; + * @endCode + * + * @param string $offset + * @param mixed $value + * The new value of the parameter. + * Values may be strings, ints, or arrays thereof -- provided that the + * SQL query uses appropriate prefix (e.g. "@", "!", "#"). + * @see param() + * @see ArrayAccess::offsetSet + */ + public function offsetSet($offset, $value) { + $this->param($offset, $value); + } + + /** + * Unset the value of a SQL parameter. + * + * @param string $offset + * @see param() + * @see ArrayAccess::offsetUnset + */ + public function offsetUnset($offset) { + unset($this->params[$offset]); + } + +} diff --git a/CRM/Utils/SQL/Delete.php b/CRM/Utils/SQL/Delete.php new file mode 100644 index 0000000000..b646d3b3e2 --- /dev/null +++ b/CRM/Utils/SQL/Delete.php @@ -0,0 +1,269 @@ +where('activity_type_id = #type', array('type' => 234)) + * ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3)) + * ->where('subject like @subj', array('subj' => '%hello%')) + * ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)')) + * ->where('!column = @value', array( + * 'column' => $customField->column_name, + * 'value' => $form['foo'] + * )) + * echo $del->toSQL(); + * @endcode + * + * Design principles: + * - Portable + * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString) + * - No knowledge of the underlying data model + * - SQL clauses correspond to PHP functions ($select->where("foo_id=123")) + * - Variable escaping is concise and controllable based on prefixes, eg + * - similar to Drupal's t() + * - use "@varname" to insert the escaped value + * - use "!varname" to insert raw (unescaped) values + * - use "#varname" to insert a numerical value (these are validated but not escaped) + * - to disable any preprocessing, simply omit the variable list + * - control characters (@!#) are mandatory in expressions but optional in arg-keys + * - Variables may be individual values or arrays; arrays are imploded with commas + * - Conditionals are AND'd; if you need OR's, do it yourself + * - Use classes/functions with documentation (rather than undocumented array-trees) + * - For any given string, interpolation is only performed once. After an interpolation, + * a string may never again be subjected to interpolation. + * + * The "interpolate-once" principle can be enforced by either interpolating on input + * xor output. The notations for input and output interpolation are a bit different, + * and they may not be mixed. + * + * @code + * // Interpolate on input. Set params when using them. + * $select->where('activity_type_id = #type', array( + * 'type' => 234, + * )); + * + * // Interpolate on output. Set params independently. + * $select + * ->where('activity_type_id = #type') + * ->param('type', 234), + * @endcode + * + * @package CRM + * @copyright CiviCRM LLC (c) 2004-2018 + */ +class CRM_Utils_SQL_Delete extends CRM_Utils_SQL_BaseParamQuery { + + private $from; + private $wheres = array(); + + /** + * Create a new DELETE query. + * + * @param string $from + * Table-name and optional alias. + * @param array $options + * @return CRM_Utils_SQL_Delete + */ + public static function from($from, $options = array()) { + return new self($from, $options); + } + + /** + * Create a new DELETE query. + * + * @param string $from + * Table-name and optional alias. + * @param array $options + */ + public function __construct($from, $options = array()) { + $this->from = $from; + $this->mode = isset($options['mode']) ? $options['mode'] : self::INTERPOLATE_AUTO; + } + + /** + * Make a new copy of this query. + * + * @return CRM_Utils_SQL_Delete + */ + public function copy() { + return clone $this; + } + + /** + * Merge something or other. + * + * @param CRM_Utils_SQL_Delete $other + * @param array|NULL $parts + * ex: 'wheres' + * @return CRM_Utils_SQL_Delete + */ + public function merge($other, $parts = NULL) { + if ($other === NULL) { + return $this; + } + + if ($this->mode === self::INTERPOLATE_AUTO) { + $this->mode = $other->mode; + } + elseif ($other->mode === self::INTERPOLATE_AUTO) { + // Noop. + } + elseif ($this->mode !== $other->mode) { + // Mixing modes will lead to someone getting an expected substitution. + throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode})."); + } + + $arrayFields = array('wheres', 'params'); + foreach ($arrayFields as $f) { + if ($parts === NULL || in_array($f, $parts)) { + $this->{$f} = array_merge($this->{$f}, $other->{$f}); + } + } + + $flatFields = array('from'); + foreach ($flatFields as $f) { + if ($parts === NULL || in_array($f, $parts)) { + if ($other->{$f} !== NULL) { + $this->{$f} = $other->{$f}; + } + } + } + + return $this; + } + + /** + * Limit results by adding extra condition(s) to the WHERE clause + * + * @param string|array $exprs list of SQL expressions + * @param null|array $args use NULL to disable interpolation; use an array of variables to enable + * @return CRM_Utils_SQL_Delete + */ + public function where($exprs, $args = NULL) { + $exprs = (array) $exprs; + foreach ($exprs as $expr) { + $evaluatedExpr = $this->interpolate($expr, $args); + $this->wheres[$evaluatedExpr] = $evaluatedExpr; + } + return $this; + } + + /** + * Set one (or multiple) parameters to interpolate into the query. + * + * @param array|string $keys + * Key name, or an array of key-value pairs. + * @param null|mixed $value + * The new value of the parameter. + * Values may be strings, ints, or arrays thereof -- provided that the + * SQL query uses appropriate prefix (e.g. "@", "!", "#"). + * @return \CRM_Utils_SQL_Delete + */ + public function param($keys, $value = NULL) { + // Why bother with an override? To provide better type-hinting in `@return`. + return parent::param($keys, $value); + } + + /** + * @param array|NULL $parts + * List of fields to check (e.g. 'wheres'). + * Defaults to all. + * @return bool + */ + public function isEmpty($parts = NULL) { + $empty = TRUE; + $fields = array( + 'from', + 'wheres', + ); + if ($parts !== NULL) { + $fields = array_intersect($fields, $parts); + } + foreach ($fields as $field) { + if (!empty($this->{$field})) { + $empty = FALSE; + } + } + return $empty; + } + + /** + * @return string + * SQL statement + */ + public function toSQL() { + $sql = 'DELETE '; + + if ($this->from !== NULL) { + $sql .= 'FROM ' . $this->from . "\n"; + } + if ($this->wheres) { + $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n"; + } + if ($this->mode === self::INTERPOLATE_OUTPUT) { + $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT); + } + return $sql; + } + + /** + * Execute the query. + * + * To examine the results, use a function like `fetch()`, `fetchAll()`, + * `fetchValue()`, or `fetchMap()`. + * + * @param string|NULL $daoName + * The return object should be an instance of this class. + * Ex: 'CRM_Contact_BAO_Contact'. + * @param bool $i18nRewrite + * If the system has multilingual features, should the field/table + * names be rewritten? + * @return CRM_Core_DAO + * @see CRM_Core_DAO::executeQuery + * @see CRM_Core_I18n_Schema::rewriteQuery + */ + public function execute($daoName = NULL, $i18nRewrite = TRUE) { + // Don't pass through $params. toSQL() handles interpolation. + $params = array(); + + // Don't pass through $abort, $trapException. Just use straight-up exceptions. + $abort = TRUE; + $trapException = FALSE; + $errorScope = CRM_Core_TemporaryErrorScope::useException(); + + // Don't pass through freeDAO. You can do it yourself. + $freeDAO = FALSE; + + return CRM_Core_DAO::executeQuery($this->toSQL(), $params, $abort, $daoName, + $freeDAO, $i18nRewrite, $trapException); + } + +} diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php index 23adc513d4..27eb13169f 100644 --- a/CRM/Utils/SQL/Select.php +++ b/CRM/Utils/SQL/Select.php @@ -47,7 +47,6 @@ * - Portable * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString) * - No knowledge of the underlying data model - * - Single file * - SQL clauses correspond to PHP functions ($select->where("foo_id=123")) * - Variable escaping is concise and controllable based on prefixes, eg * - similar to Drupal's t() @@ -81,38 +80,8 @@ * @package CRM * @copyright CiviCRM LLC (c) 2004-2018 */ -class CRM_Utils_SQL_Select implements ArrayAccess { +class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery { - /** - * Interpolate values as soon as they are passed in (where(), join(), etc). - * - * Default. - * - * Pro: Every clause has its own unique namespace for parameters. - * Con: Probably slower. - * Advice: Use this when aggregating SQL fragments from agents who - * maintained by different parties. - */ - const INTERPOLATE_INPUT = 'in'; - - /** - * Interpolate values when rendering SQL output (toSQL()). - * - * Pro: Probably faster. - * Con: Must maintain an aggregated list of all parameters. - * Advice: Use this when you have control over the entire query. - */ - const INTERPOLATE_OUTPUT = 'out'; - - /** - * Determine mode automatically. When the first attempt is made - * to use input-interpolation (eg `where(..., array(...))`) or - * output-interpolation (eg `param(...)`), the mode will be - * set. Subsequent calls will be validated using the same mode. - */ - const INTERPOLATE_AUTO = 'auto'; - - private $mode = NULL; private $insertInto = NULL; private $insertVerb = 'INSERT INTO '; private $insertIntoFields = array(); @@ -125,12 +94,8 @@ class CRM_Utils_SQL_Select implements ArrayAccess { private $orderBys = array(); private $limit = NULL; private $offset = NULL; - private $params = array(); private $distinct = NULL; - // Public to work-around PHP 5.3 limit. - public $strict = NULL; - /** * Create a new SELECT query. * @@ -349,22 +314,8 @@ class CRM_Utils_SQL_Select implements ArrayAccess { * @return \CRM_Utils_SQL_Select */ public function param($keys, $value = NULL) { - if ($this->mode === self::INTERPOLATE_AUTO) { - $this->mode = self::INTERPOLATE_OUTPUT; - } - elseif ($this->mode !== self::INTERPOLATE_OUTPUT) { - throw new RuntimeException("Select::param() only makes sense when interpolating on output."); - } - - if (is_array($keys)) { - foreach ($keys as $k => $v) { - $this->params[$k] = $v; - } - } - else { - $this->params[$keys] = $value; - } - return $this; + // Why bother with an override? To provide bett er type-hinting in `@return`. + return parent::param($keys, $value); } /** @@ -477,101 +428,6 @@ class CRM_Utils_SQL_Select implements ArrayAccess { return $empty; } - /** - * Enable (or disable) strict mode. - * - * In strict mode, unknown variables will generate exceptions. - * - * @param bool $strict - * @return CRM_Utils_SQL_Select - */ - public function strict($strict = TRUE) { - $this->strict = $strict; - return $this; - } - - /** - * Given a string like "field_name = @value", replace "@value" with an escaped SQL string - * - * @param string $expr SQL expression - * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded: - * prefix '@' => escape SQL - * prefix '#' => literal number, skip escaping but do validation - * prefix '!' => literal, skip escaping and validation - * if a value is an array, then it will be imploded - * - * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string. - * - * @param string $activeMode - * - * @return string - */ - public function interpolate($expr, $args, $activeMode = self::INTERPOLATE_INPUT) { - if ($args === NULL) { - return $expr; - } - else { - if ($this->mode === self::INTERPOLATE_AUTO) { - $this->mode = $activeMode; - } - elseif ($activeMode !== $this->mode) { - throw new RuntimeException("Cannot mix interpolation modes."); - } - - $select = $this; - return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) { - if (isset($args[$m[2]])) { - $values = $args[$m[2]]; - } - elseif (isset($args[$m[1] . $m[2]])) { - // Backward compat. Keys in $args look like "#myNumber" or "@myString". - $values = $args[$m[1] . $m[2]]; - } - elseif ($select->strict) { - throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.'); - } - else { - // Unrecognized variables are ignored. Mitigate risk of accidents. - return $m[0]; - } - $values = is_array($values) ? $values : array($values); - switch ($m[1]) { - case '@': - $parts = array_map(array($select, 'escapeString'), $values); - return implode(', ', $parts); - - // TODO: ensure all uses of this un-escaped literal are safe - case '!': - return implode(', ', $values); - - case '#': - foreach ($values as $valueKey => $value) { - if ($value === NULL) { - $values[$valueKey] = 'NULL'; - } - elseif (!is_numeric($value)) { - //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($m[0] => $values), TRUE)); - throw new CRM_Core_Exception("Failed encoding non-numeric value (" . $m[0] . ")"); - } - } - return implode(', ', $values); - - default: - throw new CRM_Core_Exception("Unrecognized prefix"); - } - }, $expr); - } - } - - /** - * @param string|NULL $value - * @return string - * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) - */ - public function escapeString($value) { - return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; - } - /** * @return string * SQL statement @@ -655,65 +511,4 @@ class CRM_Utils_SQL_Select implements ArrayAccess { $freeDAO, $i18nRewrite, $trapException); } - /** - * Has an offset been set. - * - * @param string $offset - * - * @return bool - */ - public function offsetExists($offset) { - return isset($this->params[$offset]); - } - - /** - * Get the value of a SQL parameter. - * - * @code - * $select['cid'] = 123; - * $select->where('contact.id = #cid'); - * echo $select['cid']; - * @endCode - * - * @param string $offset - * @return mixed - * @see param() - * @see ArrayAccess::offsetGet - */ - public function offsetGet($offset) { - return $this->params[$offset]; - } - - /** - * Set the value of a SQL parameter. - * - * @code - * $select['cid'] = 123; - * $select->where('contact.id = #cid'); - * echo $select['cid']; - * @endCode - * - * @param string $offset - * @param mixed $value - * The new value of the parameter. - * Values may be strings, ints, or arrays thereof -- provided that the - * SQL query uses appropriate prefix (e.g. "@", "!", "#"). - * @see param() - * @see ArrayAccess::offsetSet - */ - public function offsetSet($offset, $value) { - $this->param($offset, $value); - } - - /** - * Unset the value of a SQL parameter. - * - * @param string $offset - * @see param() - * @see ArrayAccess::offsetUnset - */ - public function offsetUnset($offset) { - unset($this->params[$offset]); - } - } diff --git a/tests/phpunit/CRM/Utils/SQL/DeleteTest.php b/tests/phpunit/CRM/Utils/SQL/DeleteTest.php new file mode 100644 index 0000000000..25d39722b4 --- /dev/null +++ b/tests/phpunit/CRM/Utils/SQL/DeleteTest.php @@ -0,0 +1,39 @@ +assertLike('DELETE FROM foo', $del->toSQL()); + } + + public function testWherePlain() { + $del = CRM_Utils_SQL_Delete::from('foo') + ->where('foo = bar') + ->where(array('whiz = bang', 'frob > nicate')); + $this->assertLike('DELETE FROM foo WHERE (foo = bar) AND (whiz = bang) AND (frob > nicate)', $del->toSQL()); + } + + public function testWhereArg() { + $del = CRM_Utils_SQL_Delete::from('foo') + ->where('foo = @value', array('@value' => 'not"valid')) + ->where(array('whiz > @base', 'frob != @base'), array('@base' => 'in"valid')); + $this->assertLike('DELETE FROM foo WHERE (foo = "not\\"valid") AND (whiz > "in\\"valid") AND (frob != "in\\"valid")', $del->toSQL()); + } + + /** + * @param $expected + * @param $actual + * @param string $message + */ + public function assertLike($expected, $actual, $message = '') { + $expected = trim((preg_replace('/[ \r\n\t]+/', ' ', $expected))); + $actual = trim((preg_replace('/[ \r\n\t]+/', ' ', $actual))); + $this->assertEquals($expected, $actual, $message); + } + +} -- 2.25.1