具有使用串联的预准备语句的 SQL 注入漏洞

Question

以这种方式使用串联的参数化代码是否存在SQL注入漏洞？ 我假设它会，但我不确定什么 POST 数据会利用它。

foreach ($_POST as $key => $value) {
$columns .= ($columns == "") ? "" : ", ";
$columns .= $key;
$holders .= ($holders == "") ? "" : ", ";
$holders .= ":".$value;
}
$sql = "INSERT INTO request ($columns) VALUES ($holders)";
$stmt = $this->pdo->prepare($sql);
foreach($_POST as $key => $value) {
$field = ":".$key;
$stmt->bindValue($field, $value);
}
$stmt->execute();

Answer 1

你需要一个数组来存储请求表的所有列，并检查数组中是否存在 post 键。

PHP代码：

$request_columns = array('column1','column2');// all the columns of request table
foreach ($_POST as $key => $value) {
if(in_array($key,$request_columns)){
$columns .= ($columns == "") ? "" : ", ";
$columns .= $key;
$holders .= ($holders == "") ? "" : ", ";
$holders .= ":".$key;
}
}
$sql = "INSERT INTO request ($columns) VALUES ($holders)";
$stmt = $this->pdo->prepare($sql);
foreach($_POST as $key => $value) {
if(in_array($key,$request_columns)){
$field = ":".$key;
$stmt->bindValue($field, $value);
}
}
$stmt->execute();

Answer 2

@KetanYekale是正确的，您需要过滤 $_POST 的已知列名。

这是另一种方法，使用一些PHP内置函数。

$request_columns = array('column1','column2');// all the columns of request table
# get a subset of $_POST, only those that have keys matching the known request columns
$post_only_columns = array_intersect_key(
$_POST,
array_flip($request_column)
);
# make sure columns are delimited like `name` in case they are SQL reserved words
$columns = implode(array_map(function ($col) { return "`$col`"; }, array_keys($post_only_columns), ', ';
# use ? positional holders, not named holders. it's easier in this case
$holders = implode(array_fill(1, count($post_only_columns), '?'), ', ');
$sql = "INSERT INTO request ($columns) VALUES ($holders)";
$stmt => $this->pdo->prepare($sql);
# no need to bindValue() or use a loop, just pass the values to execute()
$stmt->execute( array_values($post_only_columns) );

PHP 有许多 Array 函数，您可以在不同的场景中使用这些函数来使您的代码更快、更简洁。可以使用这些函数来避免编写某些类型的foreach循环代码。