<?php
/**
* Format an SQL query. This function behaves like `sprintf`, except that all
* the normal conversions (like "%s") will be properly escaped, and additional
* conversions are supported:
*
* %nd, %ns, %nf, %nB
* "Nullable" versions of %d, %s, %f and %B. Will produce 'NULL' if the
* argument is a strict null.
*
* %=d, %=s, %=f
* "Nullable Test" versions of %d, %s and %f. If you pass a value, you
* get "= 3"; if you pass null, you get "IS NULL". For instance, this
* will work properly if `hatID' is a nullable column and $hat is null.
*
* qsprintf($escaper, 'WHERE hatID %=d', $hat);
*
* %Ld, %Ls, %Lf, %LB
* "List" versions of %d, %s, %f and %B. These are appropriate for use in
* an "IN" clause. For example:
*
* qsprintf($escaper, 'WHERE hatID IN (%Ld)', $list_of_hats);
*
* %B ("Binary String")
* Escapes a string for insertion into a pure binary column, ignoring
* tests for characters outside of the basic multilingual plane.
*
* %C, %LC, %LK ("Column", "Key Column")
* Escapes a column name or a list of column names. The "%LK" variant
* escapes a list of key column specifications which may look like
* "column(32)".
*
* %K ("Comment")
* Escapes a comment.
*
* %Q, %LA, %LO, %LQ, %LJ ("Query Fragment")
* Injects a query fragment from a prior call to qsprintf(). The list
* variants join a list of query fragments with AND, OR, comma, or space.
*
* %Z ("Raw Query")
* Injects a raw, unescaped query fragment. Dangerous!
*
* %R ("Database and Table Reference")
* Behaves like "%T.%T" and prints a full reference to a table including
* the database. Accepts a AphrontDatabaseTableRefInterface.
*
* %P ("Password or Secret")
* Behaves like "%s", but shows "********" when the query is printed in
* logs or traces. Accepts a PhutilOpaqueEnvelope.
*
* %~ ("Substring")
* Escapes a substring query for a LIKE (or NOT LIKE) clause. For example:
*
* // Find all rows with $search as a substring of `name`.
* qsprintf($escaper, 'WHERE name LIKE %~', $search);
*
* See also %> and %<.
*
* %> ("Prefix")
* Escapes a prefix query for a LIKE clause. For example:
*
* // Find all rows where `name` starts with $prefix.
* qsprintf($escaper, 'WHERE name LIKE %>', $prefix);
*
* %< ("Suffix")
* Escapes a suffix query for a LIKE clause. For example:
*
* // Find all rows where `name` ends with $suffix.
* qsprintf($escaper, 'WHERE name LIKE %<', $suffix);
*
* %T ("Table")
* Escapes a table name. In most cases, you should use "%R" instead.
*/
function qsprintf(PhutilQsprintfInterface $escaper, $pattern /* , ... */) {
$args = func_get_args();
array_shift($args);
return new PhutilQueryString($escaper, $args);
}
function vqsprintf(PhutilQsprintfInterface $escaper, $pattern, array $argv) {
array_unshift($argv, $pattern);
return new PhutilQueryString($escaper, $argv);
}
/**
* @{function:xsprintf} callback for encoding SQL queries. See
* @{function:qsprintf}.
*/
function xsprintf_query($userdata, &$pattern, &$pos, &$value, &$length) {
$type = $pattern[$pos];
if (is_array($userdata)) {
$escaper = $userdata['escaper'];
$unmasked = $userdata['unmasked'];
} else {
$escaper = $userdata;
$unmasked = false;
}
$next = (strlen($pattern) > $pos + 1) ? $pattern[$pos + 1] : null;
$nullable = false;
$done = false;
$prefix = '';
if (!($escaper instanceof PhutilQsprintfInterface)) {
throw new InvalidArgumentException(pht('Invalid database escaper.'));
}
switch ($type) {
case '=': // Nullable test
switch ($next) {
case 'd':
case 'f':
case 's':
$pattern = substr_replace($pattern, '', $pos, 1);
$length = strlen($pattern);
$type = 's';
if ($value === null) {
$value = 'IS NULL';
$done = true;
} else {
$prefix = '= ';
$type = $next;
}
break;
default:
throw new Exception(
pht(
'Unknown conversion, try %s, %s, or %s.',
'%=d',
'%=s',
'%=f'));
}
break;
case 'n': // Nullable...
switch ($next) {
case 'd': // ...integer.
case 'f': // ...float.
case 's': // ...string.
case 'B': // ...binary string.
$pattern = substr_replace($pattern, '', $pos, 1);
$length = strlen($pattern);
$type = $next;
$nullable = true;
break;
default:
throw new XsprintfUnknownConversionException("%n{$next}");
}
break;
case 'L': // List of..
qsprintf_check_type($value, "L{$next}", $pattern);
$pattern = substr_replace($pattern, '', $pos, 1);
$length = strlen($pattern);
$type = 's';
$done = true;
switch ($next) {
case 'd': // ...integers.
$value = implode(', ', array_map('intval', $value));
break;
case 'f': // ...floats.
$value = implode(', ', array_map('floatval', $value));
break;
case 's': // ...strings.
foreach ($value as $k => $v) {
$value[$k] = "'".$escaper->escapeUTF8String((string)$v)."'";
}
$value = implode(', ', $value);
break;
case 'B': // ...binary strings.
foreach ($value as $k => $v) {
$value[$k] = "'".$escaper->escapeBinaryString((string)$v)."'";
}
$value = implode(', ', $value);
break;
case 'C': // ...columns.
foreach ($value as $k => $v) {
$value[$k] = $escaper->escapeColumnName($v);
}
$value = implode(', ', $value);
break;
case 'K': // ...key columns.
// This is like "%LC", but for escaping column lists passed to key
// specifications. These should be escaped as "`column`(123)". For
// example:
//
// ALTER TABLE `x` ADD KEY `y` (`u`(16), `v`(32));
foreach ($value as $k => $v) {
$matches = null;
if (preg_match('/\((\d+)\)\z/', $v, $matches)) {
$v = substr($v, 0, -(strlen($matches[1]) + 2));
$prefix_len = '('.((int)$matches[1]).')';
} else {
$prefix_len = '';
}
$value[$k] = $escaper->escapeColumnName($v).$prefix_len;
}
$value = implode(', ', $value);
break;
case 'Q':
// TODO: Here, and in "%LO", "%LA", and "%LJ", we should eventually
// stop accepting strings.
foreach ($value as $k => $v) {
if (is_string($v)) {
continue;
}
$value[$k] = $v->getUnmaskedString();
}
$value = implode(', ', $value);
break;
case 'O':
foreach ($value as $k => $v) {
if (is_string($v)) {
continue;
}
$value[$k] = $v->getUnmaskedString();
}
if (count($value) == 1) {
$value = '('.head($value).')';
} else {
$value = '(('.implode(') OR (', $value).'))';
}
break;
case 'A':
foreach ($value as $k => $v) {
if (is_string($v)) {
continue;
}
$value[$k] = $v->getUnmaskedString();
}
if (count($value) == 1) {
$value = '('.head($value).')';
} else {
$value = '(('.implode(') AND (', $value).'))';
}
break;
case 'J':
foreach ($value as $k => $v) {
if (is_string($v)) {
continue;
}
$value[$k] = $v->getUnmaskedString();
}
$value = implode(' ', $value);
break;
default:
throw new XsprintfUnknownConversionException("%L{$next}");
}
break;
}
if (!$done) {
qsprintf_check_type($value, $type, $pattern);
switch ($type) {
case 's': // String
if ($nullable && $value === null) {
$value = 'NULL';
} else {
$value = "'".$escaper->escapeUTF8String((string)$value)."'";
}
$type = 's';
break;
case 'B': // Binary String
if ($nullable && $value === null) {
$value = 'NULL';
} else {
$value = "'".$escaper->escapeBinaryString((string)$value)."'";
}
$type = 's';
break;
case 'Q': // Query Fragment
if ($value instanceof PhutilQueryString) {
$value = $value->getUnmaskedString();
}
$type = 's';
break;
case 'Z': // Raw Query Fragment
$type = 's';
break;
case '~': // Like Substring
case '>': // Like Prefix
case '<': // Like Suffix
$value = $escaper->escapeStringForLikeClause($value);
switch ($type) {
case '~': $value = "'%".$value."%'"; break;
case '>': $value = "'".$value."%'"; break;
case '<': $value = "'%".$value."'"; break;
}
$type = 's';
break;
case 'f': // Float
if ($nullable && $value === null) {
$value = 'NULL';
} else {
$value = (float)$value;
}
$type = 's';
break;
case 'd': // Integer
if ($nullable && $value === null) {
$value = 'NULL';
} else {
$value = (int)$value;
}
$type = 's';
break;
case 'T': // Table
case 'C': // Column
$value = $escaper->escapeColumnName($value);
$type = 's';
break;
case 'K': // Komment
$value = $escaper->escapeMultilineComment($value);
$type = 's';
break;
case 'R': // Database + Table Reference
$database_name = $value->getAphrontRefDatabaseName();
$database_name = $escaper->escapeColumnName($database_name);
$table_name = $value->getAphrontRefTableName();
$table_name = $escaper->escapeColumnName($table_name);
$value = $database_name.'.'.$table_name;
$type = 's';
break;
case 'P': // Password or Secret
if ($unmasked) {
$value = $value->openEnvelope();
$value = "'".$escaper->escapeUTF8String($value)."'";
} else {
$value = '********';
}
$type = 's';
break;
default:
throw new XsprintfUnknownConversionException($type);
}
}
if ($prefix) {
$value = $prefix.$value;
}
$pattern[$pos] = $type;
}
function qsprintf_check_type($value, $type, $query) {
switch ($type) {
case 'Ld':
case 'Ls':
case 'LC':
case 'LK':
case 'LB':
case 'Lf':
case 'LQ':
case 'LA':
case 'LO':
case 'LJ':
if (!is_array($value)) {
throw new AphrontParameterQueryException(
$query,
pht('Expected array argument for %%%s conversion.', $type));
}
if (empty($value)) {
throw new AphrontParameterQueryException(
$query,
pht('Array for %%%s conversion is empty.', $type));
}
foreach ($value as $scalar) {
qsprintf_check_scalar_type($scalar, $type, $query);
}
break;
default:
qsprintf_check_scalar_type($value, $type, $query);
break;
}
}
function qsprintf_check_scalar_type($value, $type, $query) {
switch ($type) {
case 'LQ':
case 'LA':
case 'LO':
case 'LJ':
// TODO: See T13217. Remove this eventually.
if (is_string($value)) {
phlog(
pht(
'UNSAFE: Raw string ("%s") passed to query ("%s") subclause '.
'for "%%%s" conversion. Subclause conversions should be passed '.
'a list of PhutilQueryString objects.',
$value,
$query,
$type));
break;
}
if (!($value instanceof PhutilQueryString)) {
throw new AphrontParameterQueryException(
$query,
pht(
'Expected a list of PhutilQueryString objects for %%%s '.
'conversion.',
$type));
}
break;
case 'Q':
// TODO: See T13217. Remove this eventually.
if (is_string($value)) {
phlog(
pht(
'UNSAFE: Raw string ("%s") passed to query ("%s") for "%%Q" '.
'conversion. %%Q should be passed a query string.',
$value,
$query));
break;
}
if (!($value instanceof PhutilQueryString)) {
throw new AphrontParameterQueryException(
$query,
pht('Expected a PhutilQueryString for %%%s conversion.', $type));
}
break;
case 'Z':
if (!is_string($value)) {
throw new AphrontParameterQueryException(
$query,
pht('Value for "%%Z" conversion should be a raw string.'));
}
break;
case 'LC':
case 'LK':
case 'T':
case 'C':
if (!is_string($value)) {
throw new AphrontParameterQueryException(
$query,
pht('Expected a string for %%%s conversion.', $type));
}
break;
case 'Ld':
case 'Lf':
case 'd':
case 'f':
if (!is_null($value) && !is_numeric($value)) {
throw new AphrontParameterQueryException(
$query,
pht('Expected a numeric scalar or null for %%%s conversion.', $type));
}
break;
case 'Ls':
case 's':
case 'LB':
case 'B':
case '~':
case '>':
case '<':
case 'K':
if (!is_null($value) && !is_scalar($value)) {
throw new AphrontParameterQueryException(
$query,
pht('Expected a scalar or null for %%%s conversion.', $type));
}
break;
case 'R':
if (!($value instanceof AphrontDatabaseTableRefInterface)) {
throw new AphrontParameterQueryException(
$query,
pht(
'Parameter to "%s" conversion in "qsprintf(...)" is not an '.
'instance of AphrontDatabaseTableRefInterface.',
'%R'));
}
break;
case 'P':
if (!($value instanceof PhutilOpaqueEnvelope)) {
throw new AphrontParameterQueryException(
$query,
pht(
'Parameter to "%s" conversion in "qsprintf(...)" is not an '.
'instance of PhutilOpaqueEnvelope.',
'%P'));
}
break;
default:
throw new XsprintfUnknownConversionException($type);
}
}