php - 安全地将数组转换为 SQL 查询(PHP7+PDO+SQLite)
问题描述
使用 PHP7+PDO+SQLite,我正在寻找一种方法来过滤表中的条目,使用前端生成的数组形式的用户定义过滤器。为清楚起见,这是我正在寻找的示例:
示例:
数据库中的表:
ID Firstname Lastname Age
+--------------+--------------+--------------+-------------+
| 0 | John | Doe | 21 |
| 1 | John | Smith | 35 |
| 2 | Alice | Smith | 35 |
| 3 | Bob | Smith | 40 |
+--------------+--------------+--------------+-------------+
过滤器:
/*
Filters follow the structure:
[
"Table Column Name 1" => [Match1 OR Match2 OR ...],
AND
"TCN 2" ...
]
Any column name not provided should match any value.
*/
[
"Firstname" => ["John"]
]
// ^ fetchAll() returns rows with ID 0 and 1
[
"Firstname" => ["John", "Alice"],
"Lastname" => ["Smith"]
]
// ^ ID 1 and 2 (but not 3)
我需要找到一个可以将上面的数组转换为 SQL 查询的函数,以获取与过滤器匹配的所有行。出于性能原因,我想使用 SQL(我没有太多经验)来执行此操作,而不是获取所有行并使用 PHP 过滤它们。
我知道我可以按照以下方式做一些事情:
// Untested - for illustration purposes only
$filter = [
"Firstname" => ["John", "Alice"],
"Lastname" => ["Smith"]
];
$sql = "SELECT * FROM table_name WHERE ";
foreach ($filter as $column => $matching_values) {
foreach ($matching_values as $match) {
$sql .= $column . " == " . $match . " OR ";
}
// Ugly way of removing trailing ` OR `
$sql = substr($sql, 0, -4);
$sql .= " AND ";
}
// Ugly way of removing trailing ` AND `
$sql = substr($sql, 0, -5);
echo $sql;
但这引入了一个巨大的 SQL 注入安全漏洞。我想知道是否有一种简单而有效且安全的方法来实现这一目标。如果需要,过滤器的格式也可以更改,例如更改为 RegEx(如果有人可以用 RegEx 替换上面示例中最里面的数组,则可以加分)。
或者(或另外),以可搜索的方式描述我的问题的简单方法可能会有所帮助,因为我无法对此进行太多研究,因为我不能很好地表达它。
解决方案
这将是我已经在我的网站上教授的几种技术的有趣组合,即
最后会是这样的
$allowed = ["Firstname", "Lastname"];
$conditions = [];
$parameters = [];
foreach ($filter as $key => $values) {
if (array_search($key, $allowed, true) === false) {
throw new InvalidArgumentException("invalid field name!");
}
$conditions[] = "`$key` in (".str_repeat('?,', count($values) - 1) . '?'.")";
$parameters = array_merge($parameters, $values);
}
$sql = "SELECT * FROM table_name ";
if ($conditions)
{
$sql .= " WHERE ".implode(" AND ", $conditions);
}
将产生您正在寻找的查询,
SELECT * FROM table_name WHERE WHERE `Firstname` in (?,?) AND `Lastname` in (?)
以及要使用的值数组PDO::execute()
,这是一个防弹解决方案,其中值通过参数保护,文件名通过过滤白名单来保护
我唯一的疑问是问题中的案例可能过于简单化了。一旦您不仅需要精确匹配,还需要部分匹配或更大/更少的比较,代码的复杂性就会飙升。