Commit | Line | Data |
---|---|---|
bfd417c7 TO |
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 | ||
6714d8d2 SL |
39 | /** |
40 | * @var mixed | |
41 | */ | |
bfd417c7 TO |
42 | protected $mode = NULL; |
43 | ||
6714d8d2 SL |
44 | /** |
45 | * @var array | |
46 | */ | |
be2fb01f | 47 | protected $params = []; |
bfd417c7 | 48 | |
6714d8d2 SL |
49 | /** |
50 | * Public to work-around PHP 5.3 limit. | |
51 | * @var bool | |
52 | */ | |
bfd417c7 TO |
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) { | |
3808d8b1 | 98 | if (array_key_exists($m[2], $args)) { |
bfd417c7 TO |
99 | $values = $args[$m[2]]; |
100 | } | |
3808d8b1 | 101 | elseif (array_key_exists($m[1] . $m[2], $args)) { |
bfd417c7 TO |
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 | } | |
be2fb01f | 112 | $values = is_array($values) ? $values : [$values]; |
bfd417c7 TO |
113 | switch ($m[1]) { |
114 | case '@': | |
be2fb01f | 115 | $parts = array_map([$select, 'escapeString'], $values); |
bfd417c7 TO |
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 | /** | |
2024d5b9 | 142 | * @param string|null $value |
bfd417c7 TO |
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 | * | |
0b882a86 | 194 | * ``` |
bfd417c7 TO |
195 | * $select['cid'] = 123; |
196 | * $select->where('contact.id = #cid'); | |
197 | * echo $select['cid']; | |
0b882a86 | 198 | * ``` |
bfd417c7 TO |
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 | * | |
0b882a86 | 212 | * ``` |
bfd417c7 TO |
213 | * $select['cid'] = 123; |
214 | * $select->where('contact.id = #cid'); | |
215 | * echo $select['cid']; | |
0b882a86 | 216 | * ``` |
bfd417c7 TO |
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 | } |