recaptime.dev
@recaptime-dev's working patches + fork for Phorge, a community fork of Phabricator. (Upstream dev and stable branches are at upstream/main and upstream/stable respectively.)
hq.recaptime.dev/wiki/Phorge
phorge
phabricator
1<?php
2
3/**
4 * Format an SQL query. This function behaves like `sprintf`, except that all
5 * the normal conversions (like "%s") will be properly escaped, and additional
6 * conversions are supported:
7 *
8 * %nd, %ns, %nf, %nB
9 * "Nullable" versions of %d, %s, %f and %B. Will produce 'NULL' if the
10 * argument is a strict null.
11 *
12 * %=d, %=s, %=f
13 * "Nullable Test" versions of %d, %s and %f. If you pass a value, you
14 * get "= 3"; if you pass null, you get "IS NULL". For instance, this
15 * will work properly if `hatID' is a nullable column and $hat is null.
16 *
17 * qsprintf($escaper, 'WHERE hatID %=d', $hat);
18 *
19 * %Ld, %Ls, %Lf, %LB
20 * "List" versions of %d, %s, %f and %B. These are appropriate for use in
21 * an "IN" clause. For example:
22 *
23 * qsprintf($escaper, 'WHERE hatID IN (%Ld)', $list_of_hats);
24 *
25 * %B ("Binary String")
26 * Escapes a string for insertion into a pure binary column, ignoring
27 * tests for characters outside of the basic multilingual plane.
28 *
29 * %C, %LC, %LK ("Column", "Key Column")
30 * Escapes a column name or a list of column names. The "%LK" variant
31 * escapes a list of key column specifications which may look like
32 * "column(32)".
33 *
34 * %K ("Comment")
35 * Escapes a comment.
36 *
37 * %Q, %LA, %LO, %LQ, %LJ ("Query Fragment")
38 * Injects a query fragment from a prior call to qsprintf(). The list
39 * variants join a list of query fragments with AND, OR, comma, or space.
40 *
41 * %Z ("Raw Query")
42 * Injects a raw, unescaped query fragment. Dangerous!
43 *
44 * %R ("Database and Table Reference")
45 * Behaves like "%T.%T" and prints a full reference to a table including
46 * the database. Accepts a AphrontDatabaseTableRefInterface.
47 *
48 * %P ("Password or Secret")
49 * Behaves like "%s", but shows "********" when the query is printed in
50 * logs or traces. Accepts a PhutilOpaqueEnvelope.
51 *
52 * %~ ("Substring")
53 * Escapes a substring query for a LIKE (or NOT LIKE) clause. For example:
54 *
55 * // Find all rows with $search as a substring of `name`.
56 * qsprintf($escaper, 'WHERE name LIKE %~', $search);
57 *
58 * See also %> and %<.
59 *
60 * %> ("Prefix")
61 * Escapes a prefix query for a LIKE clause. For example:
62 *
63 * // Find all rows where `name` starts with $prefix.
64 * qsprintf($escaper, 'WHERE name LIKE %>', $prefix);
65 *
66 * %< ("Suffix")
67 * Escapes a suffix query for a LIKE clause. For example:
68 *
69 * // Find all rows where `name` ends with $suffix.
70 * qsprintf($escaper, 'WHERE name LIKE %<', $suffix);
71 *
72 * %T ("Table")
73 * Escapes a table name. In most cases, you should use "%R" instead.
74 */
75function qsprintf(PhutilQsprintfInterface $escaper, $pattern /* , ... */) {
76 $args = func_get_args();
77 array_shift($args);
78 return new PhutilQueryString($escaper, $args);
79}
80
81function vqsprintf(PhutilQsprintfInterface $escaper, $pattern, array $argv) {
82 array_unshift($argv, $pattern);
83 return new PhutilQueryString($escaper, $argv);
84}
85
86/**
87 * @{function:xsprintf} callback for encoding SQL queries. See
88 * @{function:qsprintf}.
89 */
90function xsprintf_query($userdata, &$pattern, &$pos, &$value, &$length) {
91 $type = $pattern[$pos];
92
93 if (is_array($userdata)) {
94 $escaper = $userdata['escaper'];
95 $unmasked = $userdata['unmasked'];
96 } else {
97 $escaper = $userdata;
98 $unmasked = false;
99 }
100
101 $next = (strlen($pattern) > $pos + 1) ? $pattern[$pos + 1] : null;
102 $nullable = false;
103 $done = false;
104
105 $prefix = '';
106
107 if (!($escaper instanceof PhutilQsprintfInterface)) {
108 throw new InvalidArgumentException(pht('Invalid database escaper.'));
109 }
110
111 switch ($type) {
112 case '=': // Nullable test
113 switch ($next) {
114 case 'd':
115 case 'f':
116 case 's':
117 $pattern = substr_replace($pattern, '', $pos, 1);
118 $length = strlen($pattern);
119 $type = 's';
120 if ($value === null) {
121 $value = 'IS NULL';
122 $done = true;
123 } else {
124 $prefix = '= ';
125 $type = $next;
126 }
127 break;
128 default:
129 throw new Exception(
130 pht(
131 'Unknown conversion, try %s, %s, or %s.',
132 '%=d',
133 '%=s',
134 '%=f'));
135 }
136 break;
137
138 case 'n': // Nullable...
139 switch ($next) {
140 case 'd': // ...integer.
141 case 'f': // ...float.
142 case 's': // ...string.
143 case 'B': // ...binary string.
144 $pattern = substr_replace($pattern, '', $pos, 1);
145 $length = strlen($pattern);
146 $type = $next;
147 $nullable = true;
148 break;
149 default:
150 throw new XsprintfUnknownConversionException("%n{$next}");
151 }
152 break;
153
154 case 'L': // List of..
155 qsprintf_check_type($value, "L{$next}", $pattern);
156 $pattern = substr_replace($pattern, '', $pos, 1);
157 $length = strlen($pattern);
158 $type = 's';
159 $done = true;
160
161 switch ($next) {
162 case 'd': // ...integers.
163 $value = implode(', ', array_map('intval', $value));
164 break;
165 case 'f': // ...floats.
166 $value = implode(', ', array_map('floatval', $value));
167 break;
168 case 's': // ...strings.
169 foreach ($value as $k => $v) {
170 $value[$k] = "'".$escaper->escapeUTF8String((string)$v)."'";
171 }
172 $value = implode(', ', $value);
173 break;
174 case 'B': // ...binary strings.
175 foreach ($value as $k => $v) {
176 $value[$k] = "'".$escaper->escapeBinaryString((string)$v)."'";
177 }
178 $value = implode(', ', $value);
179 break;
180 case 'C': // ...columns.
181 foreach ($value as $k => $v) {
182 $value[$k] = $escaper->escapeColumnName($v);
183 }
184 $value = implode(', ', $value);
185 break;
186 case 'K': // ...key columns.
187 // This is like "%LC", but for escaping column lists passed to key
188 // specifications. These should be escaped as "`column`(123)". For
189 // example:
190 //
191 // ALTER TABLE `x` ADD KEY `y` (`u`(16), `v`(32));
192
193 foreach ($value as $k => $v) {
194 $matches = null;
195 if (preg_match('/\((\d+)\)\z/', $v, $matches)) {
196 $v = substr($v, 0, -(strlen($matches[1]) + 2));
197 $prefix_len = '('.((int)$matches[1]).')';
198 } else {
199 $prefix_len = '';
200 }
201
202 $value[$k] = $escaper->escapeColumnName($v).$prefix_len;
203 }
204
205 $value = implode(', ', $value);
206 break;
207 case 'Q':
208 // TODO: Here, and in "%LO", "%LA", and "%LJ", we should eventually
209 // stop accepting strings.
210 foreach ($value as $k => $v) {
211 if (is_string($v)) {
212 continue;
213 }
214 $value[$k] = $v->getUnmaskedString();
215 }
216 $value = implode(', ', $value);
217 break;
218 case 'O':
219 foreach ($value as $k => $v) {
220 if (is_string($v)) {
221 continue;
222 }
223 $value[$k] = $v->getUnmaskedString();
224 }
225 if (count($value) == 1) {
226 $value = '('.head($value).')';
227 } else {
228 $value = '(('.implode(') OR (', $value).'))';
229 }
230 break;
231 case 'A':
232 foreach ($value as $k => $v) {
233 if (is_string($v)) {
234 continue;
235 }
236 $value[$k] = $v->getUnmaskedString();
237 }
238 if (count($value) == 1) {
239 $value = '('.head($value).')';
240 } else {
241 $value = '(('.implode(') AND (', $value).'))';
242 }
243 break;
244 case 'J':
245 foreach ($value as $k => $v) {
246 if (is_string($v)) {
247 continue;
248 }
249 $value[$k] = $v->getUnmaskedString();
250 }
251 $value = implode(' ', $value);
252 break;
253 default:
254 throw new XsprintfUnknownConversionException("%L{$next}");
255 }
256 break;
257 }
258
259 if (!$done) {
260 qsprintf_check_type($value, $type, $pattern);
261 switch ($type) {
262 case 's': // String
263 if ($nullable && $value === null) {
264 $value = 'NULL';
265 } else {
266 $value = "'".$escaper->escapeUTF8String((string)$value)."'";
267 }
268 $type = 's';
269 break;
270
271 case 'B': // Binary String
272 if ($nullable && $value === null) {
273 $value = 'NULL';
274 } else {
275 $value = "'".$escaper->escapeBinaryString((string)$value)."'";
276 }
277 $type = 's';
278 break;
279
280 case 'Q': // Query Fragment
281 if ($value instanceof PhutilQueryString) {
282 $value = $value->getUnmaskedString();
283 }
284 $type = 's';
285 break;
286
287 case 'Z': // Raw Query Fragment
288 $type = 's';
289 break;
290
291 case '~': // Like Substring
292 case '>': // Like Prefix
293 case '<': // Like Suffix
294 $value = $escaper->escapeStringForLikeClause($value);
295 switch ($type) {
296 case '~': $value = "'%".$value."%'"; break;
297 case '>': $value = "'".$value."%'"; break;
298 case '<': $value = "'%".$value."'"; break;
299 }
300 $type = 's';
301 break;
302
303 case 'f': // Float
304 if ($nullable && $value === null) {
305 $value = 'NULL';
306 } else {
307 $value = (float)$value;
308 }
309 $type = 's';
310 break;
311
312 case 'd': // Integer
313 if ($nullable && $value === null) {
314 $value = 'NULL';
315 } else {
316 $value = (int)$value;
317 }
318 $type = 's';
319 break;
320
321 case 'T': // Table
322 case 'C': // Column
323 $value = $escaper->escapeColumnName($value);
324 $type = 's';
325 break;
326
327 case 'K': // Komment
328 $value = $escaper->escapeMultilineComment($value);
329 $type = 's';
330 break;
331
332 case 'R': // Database + Table Reference
333 $database_name = $value->getAphrontRefDatabaseName();
334 $database_name = $escaper->escapeColumnName($database_name);
335
336 $table_name = $value->getAphrontRefTableName();
337 $table_name = $escaper->escapeColumnName($table_name);
338
339 $value = $database_name.'.'.$table_name;
340 $type = 's';
341 break;
342
343 case 'P': // Password or Secret
344 if ($unmasked) {
345 $value = $value->openEnvelope();
346 $value = "'".$escaper->escapeUTF8String($value)."'";
347 } else {
348 $value = '********';
349 }
350 $type = 's';
351 break;
352
353 default:
354 throw new XsprintfUnknownConversionException($type);
355 }
356 }
357
358 if ($prefix) {
359 $value = $prefix.$value;
360 }
361
362 $pattern[$pos] = $type;
363}
364
365function qsprintf_check_type($value, $type, $query) {
366 switch ($type) {
367 case 'Ld':
368 case 'Ls':
369 case 'LC':
370 case 'LK':
371 case 'LB':
372 case 'Lf':
373 case 'LQ':
374 case 'LA':
375 case 'LO':
376 case 'LJ':
377 if (!is_array($value)) {
378 throw new AphrontParameterQueryException(
379 $query,
380 pht('Expected array argument for %%%s conversion.', $type));
381 }
382 if (empty($value)) {
383 throw new AphrontParameterQueryException(
384 $query,
385 pht('Array for %%%s conversion is empty.', $type));
386 }
387
388 foreach ($value as $scalar) {
389 qsprintf_check_scalar_type($scalar, $type, $query);
390 }
391 break;
392 default:
393 qsprintf_check_scalar_type($value, $type, $query);
394 break;
395 }
396}
397
398function qsprintf_check_scalar_type($value, $type, $query) {
399 switch ($type) {
400 case 'LQ':
401 case 'LA':
402 case 'LO':
403 case 'LJ':
404 // TODO: See T13217. Remove this eventually.
405 if (is_string($value)) {
406 phlog(
407 pht(
408 'UNSAFE: Raw string ("%s") passed to query ("%s") subclause '.
409 'for "%%%s" conversion. Subclause conversions should be passed '.
410 'a list of PhutilQueryString objects.',
411 $value,
412 $query,
413 $type));
414 break;
415 }
416
417 if (!($value instanceof PhutilQueryString)) {
418 throw new AphrontParameterQueryException(
419 $query,
420 pht(
421 'Expected a list of PhutilQueryString objects for %%%s '.
422 'conversion.',
423 $type));
424 }
425 break;
426
427 case 'Q':
428 // TODO: See T13217. Remove this eventually.
429 if (is_string($value)) {
430 phlog(
431 pht(
432 'UNSAFE: Raw string ("%s") passed to query ("%s") for "%%Q" '.
433 'conversion. %%Q should be passed a query string.',
434 $value,
435 $query));
436 break;
437 }
438
439 if (!($value instanceof PhutilQueryString)) {
440 throw new AphrontParameterQueryException(
441 $query,
442 pht('Expected a PhutilQueryString for %%%s conversion.', $type));
443 }
444 break;
445
446 case 'Z':
447 if (!is_string($value)) {
448 throw new AphrontParameterQueryException(
449 $query,
450 pht('Value for "%%Z" conversion should be a raw string.'));
451 }
452 break;
453
454 case 'LC':
455 case 'LK':
456 case 'T':
457 case 'C':
458 if (!is_string($value)) {
459 throw new AphrontParameterQueryException(
460 $query,
461 pht('Expected a string for %%%s conversion.', $type));
462 }
463 break;
464
465 case 'Ld':
466 case 'Lf':
467 case 'd':
468 case 'f':
469 if (!is_null($value) && !is_numeric($value)) {
470 throw new AphrontParameterQueryException(
471 $query,
472 pht('Expected a numeric scalar or null for %%%s conversion.', $type));
473 }
474 break;
475
476 case 'Ls':
477 case 's':
478 case 'LB':
479 case 'B':
480 case '~':
481 case '>':
482 case '<':
483 case 'K':
484 if (!is_null($value) && !is_scalar($value)) {
485 throw new AphrontParameterQueryException(
486 $query,
487 pht('Expected a scalar or null for %%%s conversion.', $type));
488 }
489 break;
490
491 case 'R':
492 if (!($value instanceof AphrontDatabaseTableRefInterface)) {
493 throw new AphrontParameterQueryException(
494 $query,
495 pht(
496 'Parameter to "%s" conversion in "qsprintf(...)" is not an '.
497 'instance of AphrontDatabaseTableRefInterface.',
498 '%R'));
499 }
500 break;
501
502 case 'P':
503 if (!($value instanceof PhutilOpaqueEnvelope)) {
504 throw new AphrontParameterQueryException(
505 $query,
506 pht(
507 'Parameter to "%s" conversion in "qsprintf(...)" is not an '.
508 'instance of PhutilOpaqueEnvelope.',
509 '%P'));
510 }
511 break;
512
513 default:
514 throw new XsprintfUnknownConversionException($type);
515 }
516}