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';
39 protected $mode = NULL;
41 protected $params = array();
43 // Public to work-around PHP 5.3 limit.
44 public $strict = NULL;
47 * Enable (or disable) strict mode.
49 * In strict mode, unknown variables will generate exceptions.
54 public function strict($strict = TRUE) {
55 $this->strict
= $strict;
60 * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
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
69 * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
71 * @param string $activeMode
75 public function interpolate($expr, $args, $activeMode = self
::INTERPOLATE_INPUT
) {
80 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
81 $this->mode
= $activeMode;
83 elseif ($activeMode !== $this->mode
) {
84 throw new RuntimeException("Cannot mix interpolation modes.");
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]];
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]];
96 elseif ($select->strict
) {
97 throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.');
100 // Unrecognized variables are ignored. Mitigate risk of accidents.
103 $values = is_array($values) ?
$values : array($values);
106 $parts = array_map(array($select, 'escapeString'), $values);
107 return implode(', ', $parts);
109 // TODO: ensure all uses of this un-escaped literal are safe
111 return implode(', ', $values);
114 foreach ($values as $valueKey => $value) {
115 if ($value === NULL) {
116 $values[$valueKey] = 'NULL';
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] . ")");
123 return implode(', ', $values);
126 throw new CRM_Core_Exception("Unrecognized prefix");
133 * @param string|NULL $value
135 * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
137 public function escapeString($value) {
138 return $value === NULL ?
'NULL' : '"' . CRM_Core_DAO
::escapeString($value) . '"';
142 * Set one (or multiple) parameters to interpolate into the query.
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. "@", "!", "#").
152 public function param($keys, $value = NULL) {
153 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
154 $this->mode
= self
::INTERPOLATE_OUTPUT
;
156 elseif ($this->mode
!== self
::INTERPOLATE_OUTPUT
) {
157 throw new RuntimeException("Select::param() only makes sense when interpolating on output.");
160 if (is_array($keys)) {
161 foreach ($keys as $k => $v) {
162 $this->params
[$k] = $v;
166 $this->params
[$keys] = $value;
172 * Has an offset been set.
174 * @param string $offset
178 public function offsetExists($offset) {
179 return isset($this->params
[$offset]);
183 * Get the value of a SQL parameter.
186 * $select['cid'] = 123;
187 * $select->where('contact.id = #cid');
188 * echo $select['cid'];
191 * @param string $offset
194 * @see ArrayAccess::offsetGet
196 public function offsetGet($offset) {
197 return $this->params
[$offset];
201 * Set the value of a SQL parameter.
204 * $select['cid'] = 123;
205 * $select->where('contact.id = #cid');
206 * echo $select['cid'];
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. "@", "!", "#").
215 * @see ArrayAccess::offsetSet
217 public function offsetSet($offset, $value) {
218 $this->param($offset, $value);
222 * Unset the value of a SQL parameter.
224 * @param string $offset
226 * @see ArrayAccess::offsetUnset
228 public function offsetUnset($offset) {
229 unset($this->params
[$offset]);