| 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) { |
| 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 | public function offsetGet($offset) { |
| 206 | return $this->params[$offset]; |
| 207 | } |
| 208 | |
| 209 | /** |
| 210 | * Set the value of a SQL parameter. |
| 211 | * |
| 212 | * ``` |
| 213 | * $select['cid'] = 123; |
| 214 | * $select->where('contact.id = #cid'); |
| 215 | * echo $select['cid']; |
| 216 | * ``` |
| 217 | * |
| 218 | * @param string $offset |
| 219 | * @param mixed $value |
| 220 | * The new value of the parameter. |
| 221 | * Values may be strings, ints, or arrays thereof -- provided that the |
| 222 | * SQL query uses appropriate prefix (e.g. "@", "!", "#"). |
| 223 | * @see param() |
| 224 | * @see ArrayAccess::offsetSet |
| 225 | */ |
| 226 | public function offsetSet($offset, $value) { |
| 227 | $this->param($offset, $value); |
| 228 | } |
| 229 | |
| 230 | /** |
| 231 | * Unset the value of a SQL parameter. |
| 232 | * |
| 233 | * @param string $offset |
| 234 | * @see param() |
| 235 | * @see ArrayAccess::offsetUnset |
| 236 | */ |
| 237 | public function offsetUnset($offset) { |
| 238 | unset($this->params[$offset]); |
| 239 | } |
| 240 | |
| 241 | } |