CRM/Utils/SQL/BaseParamQuery.php

   1 <?php
   2
   3 /**
   4  * Class CRM_Utils_SQL_BaseParamQuery
   5  *
   6  * Base class for query-building which handles parameter interpolation.
   7  */
   8 class CRM_Utils_SQL_BaseParamQuery implements ArrayAccess {
   9
  10   /**
  11    * Interpolate values as soon as they are passed in (where(), join(), etc).
  12    *
  13    * Default.
  14    *
  15    * Pro: Every clause has its own unique namespace for parameters.
  16    * Con: Probably slower.
  17    * Advice: Use this when aggregating SQL fragments from agents who
  18    *   maintained by different parties.
  19    */
  20   const INTERPOLATE_INPUT = 'in';
  21
  22   /**
  23    * Interpolate values when rendering SQL output (toSQL()).
  24    *
  25    * Pro: Probably faster.
  26    * Con: Must maintain an aggregated list of all parameters.
  27    * Advice: Use this when you have control over the entire query.
  28    */
  29   const INTERPOLATE_OUTPUT = 'out';
  30
  31   /**
  32    * Determine mode automatically. When the first attempt is made
  33    * to use input-interpolation (eg `where(..., array(...))`) or
  34    * output-interpolation (eg `param(...)`), the mode will be
  35    * set. Subsequent calls will be validated using the same mode.
  36    */
  37   const INTERPOLATE_AUTO = 'auto';
  38
  39   /**
  40    * @var mixed
  41    */
  42   protected $mode = NULL;
  43
  44   /**
  45    * @var array
  46    */
  47   protected $params = [];
  48
  49   /**
  50    * Public to work-around PHP 5.3 limit.
  51    * @var bool
  52    */
  53   public $strict = NULL;
  54
  55   /**
  56    * Enable (or disable) strict mode.
  57    *
  58    * In strict mode, unknown variables will generate exceptions.
  59    *
  60    * @param bool $strict
  61    * @return self
  62    */
  63   public function strict($strict = TRUE) {
  64     $this->strict = $strict;
  65     return $this;
  66   }
  67
  68   /**
  69    * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
  70    *
  71    * @param string $expr SQL expression
  72    * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
  73    *   prefix '@' => escape SQL
  74    *   prefix '#' => literal number, skip escaping but do validation
  75    *   prefix '!' => literal, skip escaping and validation
  76    *   if a value is an array, then it will be imploded
  77    *
  78    * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
  79    *
  80    * @param string $activeMode
  81    *
  82    * @return string
  83    */
  84   public function interpolate($expr, $args, $activeMode = self::INTERPOLATE_INPUT) {
  85     if ($args === NULL) {
  86       return $expr;
  87     }
  88     else {
  89       if ($this->mode === self::INTERPOLATE_AUTO) {
  90         $this->mode = $activeMode;
  91       }
  92       elseif ($activeMode !== $this->mode) {
  93         throw new RuntimeException("Cannot mix interpolation modes.");
  94       }
  95
  96       $select = $this;
  97       return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) {
  98         if (array_key_exists($m[2], $args)) {
  99           $values = $args[$m[2]];
 100         }
 101         elseif (array_key_exists($m[1] . $m[2], $args)) {
 102           // Backward compat. Keys in $args look like "#myNumber" or "@myString".
 103           $values = $args[$m[1] . $m[2]];
 104         }
 105         elseif ($select->strict) {
 106           throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.');
 107         }
 108         else {
 109           // Unrecognized variables are ignored. Mitigate risk of accidents.
 110           return $m[0];
 111         }
 112         $values = is_array($values) ? $values : [$values];
 113         switch ($m[1]) {
 114           case '@':
 115             $parts = array_map([$select, 'escapeString'], $values);
 116             return implode(', ', $parts);
 117
 118           // TODO: ensure all uses of this un-escaped literal are safe
 119           case '!':
 120             return implode(', ', $values);
 121
 122           case '#':
 123             foreach ($values as $valueKey => $value) {
 124               if ($value === NULL) {
 125                 $values[$valueKey] = 'NULL';
 126               }
 127               elseif (!is_numeric($value)) {
 128                 //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($m[0] => $values), TRUE));
 129                 throw new CRM_Core_Exception("Failed encoding non-numeric value (" . $m[0] . ")");
 130               }
 131             }
 132             return implode(', ', $values);
 133
 134           default:
 135             throw new CRM_Core_Exception("Unrecognized prefix");
 136         }
 137       }, $expr);
 138     }
 139   }
 140
 141   /**
 142    * @param string|null $value
 143    * @return string
 144    *   SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
 145    */
 146   public function escapeString($value) {
 147     return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
 148   }
 149
 150   /**
 151    * Set one (or multiple) parameters to interpolate into the query.
 152    *
 153    * @param array|string $keys
 154    *   Key name, or an array of key-value pairs.
 155    * @param null|mixed $value
 156    *   The new value of the parameter.
 157    *   Values may be strings, ints, or arrays thereof -- provided that the
 158    *   SQL query uses appropriate prefix (e.g. "@", "!", "#").
 159    * @return $this
 160    */
 161   public function param($keys, $value = NULL) {
 162     if ($this->mode === self::INTERPOLATE_AUTO) {
 163       $this->mode = self::INTERPOLATE_OUTPUT;
 164     }
 165     elseif ($this->mode !== self::INTERPOLATE_OUTPUT) {
 166       throw new RuntimeException("Select::param() only makes sense when interpolating on output.");
 167     }
 168
 169     if (is_array($keys)) {
 170       foreach ($keys as $k => $v) {
 171         $this->params[$k] = $v;
 172       }
 173     }
 174     else {
 175       $this->params[$keys] = $value;
 176     }
 177     return $this;
 178   }
 179
 180   /**
 181    * Has an offset been set.
 182    *
 183    * @param string $offset
 184    *
 185    * @return bool
 186    */
 187   public function offsetExists($offset): bool {
 188     return isset($this->params[$offset]);
 189   }
 190
 191   /**
 192    * Get the value of a SQL parameter.
 193    *
 194    * ```
 195    *   $select['cid'] = 123;
 196    *   $select->where('contact.id = #cid');
 197    *   echo $select['cid'];
 198    * ```
 199    *
 200    * @param string $offset
 201    * @return mixed
 202    * @see param()
 203    * @see ArrayAccess::offsetGet
 204    */
 205   #[ReturnTypeWillChange]
 206   public function offsetGet($offset) {
 207     return $this->params[$offset];
 208   }
 209
 210   /**
 211    * Set the value of a SQL parameter.
 212    *
 213    * ```
 214    *   $select['cid'] = 123;
 215    *   $select->where('contact.id = #cid');
 216    *   echo $select['cid'];
 217    * ```
 218    *
 219    * @param string $offset
 220    * @param mixed $value
 221    *   The new value of the parameter.
 222    *   Values may be strings, ints, or arrays thereof -- provided that the
 223    *   SQL query uses appropriate prefix (e.g. "@", "!", "#").
 224    * @see param()
 225    * @see ArrayAccess::offsetSet
 226    */
 227   public function offsetSet($offset, $value): void {
 228     $this->param($offset, $value);
 229   }
 230
 231   /**
 232    * Unset the value of a SQL parameter.
 233    *
 234    * @param string $offset
 235    * @see param()
 236    * @see ArrayAccess::offsetUnset
 237    */
 238   public function offsetUnset($offset): void {
 239     unset($this->params[$offset]);
 240   }
 241
 242 }