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 | ||
39 | protected $mode = NULL; | |
40 | ||
41 | protected $params = array(); | |
42 | ||
43 | // Public to work-around PHP 5.3 limit. | |
44 | public $strict = NULL; | |
45 | ||
46 | /** | |
47 | * Enable (or disable) strict mode. | |
48 | * | |
49 | * In strict mode, unknown variables will generate exceptions. | |
50 | * | |
51 | * @param bool $strict | |
52 | * @return self | |
53 | */ | |
54 | public function strict($strict = TRUE) { | |
55 | $this->strict = $strict; | |
56 | return $this; | |
57 | } | |
58 | ||
59 | /** | |
60 | * Given a string like "field_name = @value", replace "@value" with an escaped SQL string | |
61 | * | |
62 | * @param string $expr SQL expression | |
63 | * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded: | |
64 | * prefix '@' => escape SQL | |
65 | * prefix '#' => literal number, skip escaping but do validation | |
66 | * prefix '!' => literal, skip escaping and validation | |
67 | * if a value is an array, then it will be imploded | |
68 | * | |
69 | * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string. | |
70 | * | |
71 | * @param string $activeMode | |
72 | * | |
73 | * @return string | |
74 | */ | |
75 | public function interpolate($expr, $args, $activeMode = self::INTERPOLATE_INPUT) { | |
76 | if ($args === NULL) { | |
77 | return $expr; | |
78 | } | |
79 | else { | |
80 | if ($this->mode === self::INTERPOLATE_AUTO) { | |
81 | $this->mode = $activeMode; | |
82 | } | |
83 | elseif ($activeMode !== $this->mode) { | |
84 | throw new RuntimeException("Cannot mix interpolation modes."); | |
85 | } | |
86 | ||
87 | $select = $this; | |
88 | return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) { | |
89 | if (isset($args[$m[2]])) { | |
90 | $values = $args[$m[2]]; | |
91 | } | |
92 | elseif (isset($args[$m[1] . $m[2]])) { | |
93 | // Backward compat. Keys in $args look like "#myNumber" or "@myString". | |
94 | $values = $args[$m[1] . $m[2]]; | |
95 | } | |
96 | elseif ($select->strict) { | |
97 | throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.'); | |
98 | } | |
99 | else { | |
100 | // Unrecognized variables are ignored. Mitigate risk of accidents. | |
101 | return $m[0]; | |
102 | } | |
103 | $values = is_array($values) ? $values : array($values); | |
104 | switch ($m[1]) { | |
105 | case '@': | |
106 | $parts = array_map(array($select, 'escapeString'), $values); | |
107 | return implode(', ', $parts); | |
108 | ||
109 | // TODO: ensure all uses of this un-escaped literal are safe | |
110 | case '!': | |
111 | return implode(', ', $values); | |
112 | ||
113 | case '#': | |
114 | foreach ($values as $valueKey => $value) { | |
115 | if ($value === NULL) { | |
116 | $values[$valueKey] = 'NULL'; | |
117 | } | |
118 | elseif (!is_numeric($value)) { | |
119 | //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($m[0] => $values), TRUE)); | |
120 | throw new CRM_Core_Exception("Failed encoding non-numeric value (" . $m[0] . ")"); | |
121 | } | |
122 | } | |
123 | return implode(', ', $values); | |
124 | ||
125 | default: | |
126 | throw new CRM_Core_Exception("Unrecognized prefix"); | |
127 | } | |
128 | }, $expr); | |
129 | } | |
130 | } | |
131 | ||
132 | /** | |
133 | * @param string|NULL $value | |
134 | * @return string | |
135 | * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) | |
136 | */ | |
137 | public function escapeString($value) { | |
138 | return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; | |
139 | } | |
140 | ||
141 | /** | |
142 | * Set one (or multiple) parameters to interpolate into the query. | |
143 | * | |
144 | * @param array|string $keys | |
145 | * Key name, or an array of key-value pairs. | |
146 | * @param null|mixed $value | |
147 | * The new value of the parameter. | |
148 | * Values may be strings, ints, or arrays thereof -- provided that the | |
149 | * SQL query uses appropriate prefix (e.g. "@", "!", "#"). | |
150 | * @return $this | |
151 | */ | |
152 | public function param($keys, $value = NULL) { | |
153 | if ($this->mode === self::INTERPOLATE_AUTO) { | |
154 | $this->mode = self::INTERPOLATE_OUTPUT; | |
155 | } | |
156 | elseif ($this->mode !== self::INTERPOLATE_OUTPUT) { | |
157 | throw new RuntimeException("Select::param() only makes sense when interpolating on output."); | |
158 | } | |
159 | ||
160 | if (is_array($keys)) { | |
161 | foreach ($keys as $k => $v) { | |
162 | $this->params[$k] = $v; | |
163 | } | |
164 | } | |
165 | else { | |
166 | $this->params[$keys] = $value; | |
167 | } | |
168 | return $this; | |
169 | } | |
170 | ||
171 | /** | |
172 | * Has an offset been set. | |
173 | * | |
174 | * @param string $offset | |
175 | * | |
176 | * @return bool | |
177 | */ | |
178 | public function offsetExists($offset) { | |
179 | return isset($this->params[$offset]); | |
180 | } | |
181 | ||
182 | /** | |
183 | * Get the value of a SQL parameter. | |
184 | * | |
185 | * @code | |
186 | * $select['cid'] = 123; | |
187 | * $select->where('contact.id = #cid'); | |
188 | * echo $select['cid']; | |
189 | * @endCode | |
190 | * | |
191 | * @param string $offset | |
192 | * @return mixed | |
193 | * @see param() | |
194 | * @see ArrayAccess::offsetGet | |
195 | */ | |
196 | public function offsetGet($offset) { | |
197 | return $this->params[$offset]; | |
198 | } | |
199 | ||
200 | /** | |
201 | * Set the value of a SQL parameter. | |
202 | * | |
203 | * @code | |
204 | * $select['cid'] = 123; | |
205 | * $select->where('contact.id = #cid'); | |
206 | * echo $select['cid']; | |
207 | * @endCode | |
208 | * | |
209 | * @param string $offset | |
210 | * @param mixed $value | |
211 | * The new value of the parameter. | |
212 | * Values may be strings, ints, or arrays thereof -- provided that the | |
213 | * SQL query uses appropriate prefix (e.g. "@", "!", "#"). | |
214 | * @see param() | |
215 | * @see ArrayAccess::offsetSet | |
216 | */ | |
217 | public function offsetSet($offset, $value) { | |
218 | $this->param($offset, $value); | |
219 | } | |
220 | ||
221 | /** | |
222 | * Unset the value of a SQL parameter. | |
223 | * | |
224 | * @param string $offset | |
225 | * @see param() | |
226 | * @see ArrayAccess::offsetUnset | |
227 | */ | |
228 | public function offsetUnset($offset) { | |
229 | unset($this->params[$offset]); | |
230 | } | |
231 | ||
232 | } |