将数组安全地转换为 SQL 查询（PHP7+PDO+SQLite）

Question

使用 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 替换上面示例中最里面的数组，则加分）。

或者（或另外），一种以可搜索的方式描述我的问题的简单方法可能会有所帮助，因为我无法对此进行太多研究，因为我无法很好地表达它。

Answer 1

这将是我已经在我的网站上教授的几种技术的有趣组合，即动态创建 WHERE 子句为 IN 子句创建准备好的语句白名单以防止SQL 注入最后会变成这样$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()，制作防弹解决方案，其中值通过参数保护，文件名通过白名单过滤保护不受保护我唯一的怀疑是问题中出现的案例可能过于简单化了。一旦您不仅需要精确匹配，还需要部分匹配或更大/更小的比较，代码的复杂性就会飙升。