I'm looking at a Database ORM that uses an array to define the WHERE clause, e.g.
$articles->find('all', [
'OR' => [
'category_id IS NULL',
'category_id' => $id,
],
]);
The "array keys" become part of the SQL, so they must be a developer defined string (aka a literal-string), otherwise you can have mistakes like this:
$articles->find('all', [
'OR' => [
'category_id IS NULL',
'category_id = ' . $id, // INSECURE, SQLi
],
]);
If the "array values" simply contained the values to be parameterised (i.e. user values), then I could specify the parameter type as array<literal-string, int|string>.
But, as you will notice with the 'OR' key, the parameter can contain nested arrays, and can be many levels deep.
Is it possible to get Static Analysis tools like Psalm or PHPStan to work with this?
I can use the CakePHP implementation as an example of how this works:
<?php
class orm {
/**
* @param array<int, literal-string|array<mixed>>|array<literal-string, int|string|array<mixed>> $conditions
*/
public function find(string $finder, array $conditions): void {
print_r($this->_addConditions($conditions));
}
/**
* @param array<int, literal-string|array<mixed>>|array<literal-string, int|string|array<mixed>> $conditions
* @param literal-string $conjunction
* @return array{literal-string, array<int, mixed>}
*/
private function _addConditions(array $conditions, string $conjunction = 'AND'): array {
// https://github.com/cakephp/cakephp/blob/ab052da10dc5ceb2444c29aef838d10844fe5995/src/Database/Expression/QueryExpression.php#L654
$operators = ['and', 'or', 'xor'];
$sql = [];
$parameters = [];
foreach ($conditions as $k => $c) {
if (is_numeric($k)) {
if (is_array($c)) {
/** @var array<int, array<mixed>> $sub_conditions */
$sub_conditions = $c;
list($new_sql, $new_parameters) = $this->_addConditions($sub_conditions, 'AND');
$sql[] = $new_sql;
$parameters = array_merge($parameters, $new_parameters);
} else if (is_string($c)) {
$sql[] = $c; // $c must be a literal-string
}
} else {
$operatorId = array_search(strtolower($k), $operators);
if ($operatorId !== false) {
/** @var array<literal-string, int|string|array<mixed>> $sub_conditions */
$sub_conditions = $c;
list($new_sql, $new_parameters) = $this->_addConditions($sub_conditions, $operators[$operatorId]);
$sql[] = $new_sql;
$parameters = array_merge($parameters, $new_parameters);
} else {
$sql[] = $k . ' = ?'; // $k must be a literal-string
$parameters[] = $c;
}
}
}
/** @var literal-string $sql */
$sql = '(' . implode(' ' . $conjunction . ' ', $sql) . ')';
return [$sql, $parameters];
}
}
$articles = new orm();
?>
