4 * Class CRM_Utils_SQL_BaseParamQuery
6 * Base class for query-building which handles parameter interpolation.
8 class CRM_Utils_SQL_BaseParamQuery
implements ArrayAccess
{
11 * Interpolate values as soon as they are passed in (where(), join(), etc).
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.
20 const INTERPOLATE_INPUT
= 'in';
23 * Interpolate values when rendering SQL output (toSQL()).
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.
29 const INTERPOLATE_OUTPUT
= 'out';
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.
37 const INTERPOLATE_AUTO
= 'auto';
42 protected $mode = NULL;
47 protected $params = [];
50 * Public to work-around PHP 5.3 limit.
53 public $strict = NULL;
56 * Enable (or disable) strict mode.
58 * In strict mode, unknown variables will generate exceptions.
63 public function strict($strict = TRUE) {
64 $this->strict
= $strict;
69 * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
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
78 * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
80 * @param string $activeMode
84 public function interpolate($expr, $args, $activeMode = self
::INTERPOLATE_INPUT
) {
89 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
90 $this->mode
= $activeMode;
92 elseif ($activeMode !== $this->mode
) {
93 throw new RuntimeException("Cannot mix interpolation modes.");
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]];
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]];
105 elseif ($select->strict
) {
106 throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.');
109 // Unrecognized variables are ignored. Mitigate risk of accidents.
112 $values = is_array($values) ?
$values : [$values];
115 $parts = array_map([$select, 'escapeString'], $values);
116 return implode(', ', $parts);
118 // TODO: ensure all uses of this un-escaped literal are safe
120 return implode(', ', $values);
123 foreach ($values as $valueKey => $value) {
124 if ($value === NULL) {
125 $values[$valueKey] = 'NULL';
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] . ")");
132 return implode(', ', $values);
135 throw new CRM_Core_Exception("Unrecognized prefix");
142 * @param string|null $value
144 * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
146 public function escapeString($value) {
147 return $value === NULL ?
'NULL' : '"' . CRM_Core_DAO
::escapeString($value) . '"';
151 * Set one (or multiple) parameters to interpolate into the query.
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. "@", "!", "#").
161 public function param($keys, $value = NULL) {
162 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
163 $this->mode
= self
::INTERPOLATE_OUTPUT
;
165 elseif ($this->mode
!== self
::INTERPOLATE_OUTPUT
) {
166 throw new RuntimeException("Select::param() only makes sense when interpolating on output.");
169 if (is_array($keys)) {
170 foreach ($keys as $k => $v) {
171 $this->params
[$k] = $v;
175 $this->params
[$keys] = $value;
181 * Has an offset been set.
183 * @param string $offset
187 public function offsetExists($offset) {
188 return isset($this->params
[$offset]);
192 * Get the value of a SQL parameter.
195 * $select['cid'] = 123;
196 * $select->where('contact.id = #cid');
197 * echo $select['cid'];
200 * @param string $offset
203 * @see ArrayAccess::offsetGet
205 public function offsetGet($offset) {
206 return $this->params
[$offset];
210 * Set the value of a SQL parameter.
213 * $select['cid'] = 123;
214 * $select->where('contact.id = #cid');
215 * echo $select['cid'];
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. "@", "!", "#").
224 * @see ArrayAccess::offsetSet
226 public function offsetSet($offset, $value) {
227 $this->param($offset, $value);
231 * Unset the value of a SQL parameter.
233 * @param string $offset
235 * @see ArrayAccess::offsetUnset
237 public function offsetUnset($offset) {
238 unset($this->params
[$offset]);