从表单输入数组-选择语句MySQLi参数化

Question

使用MySQLi将Form输入中输入的短语转换为数组，以传递到MySQL select语句where子句中。我的php代码实现了这一点，但我无法训练如何参数化查询以防止sql注入攻击。我已经看了这个网站上的一些问题，但我很难将其与我的代码联系起来。

if(!empty($_POST['Message']))
{
$searchStr = get_post($con,'Message');
$aKeyword = explode(" ", $searchStr);
$query ="SELECT m.ID, m.MessageText FROM MessageMain m LEFT OUTER JOIN Likes l on m.ID = l.PostID WHERE MessageText LIKE '%" . $aKeyword[0] . "%'";
for($i = 1; $i < count($aKeyword); $i++) {
if(!empty($aKeyword[$i])) {
$query .= " OR MessageText like '%" . $aKeyword[$i] . "%'";
}
}
$query .= " GROUP BY m.ID, m.MessageText ORDER BY count(m.id) desc";
$result = $con->query($query);
$rowcount=mysqli_num_rows($result);

Answer 1

如果你想根据匹配的关键字数量动态构建WHERE子句，你可以这样做：

if (!empty($_POST['Message'])) {
$searchStr = get_post($con, 'Message');
$aKeyword = explode(" ", $searchStr);
$whereClauseArr = [];
foreach ($aKeyword as $keyword) {
if ($keyword) {
$whereClauseArr[] = "MessageText LIKE ?";
$whereValues[] = '%'.$keyword.'%';
}
}
$stmt = $con->prepare(
'SELECT m.ID, m.MessageText 
FROM MessageMain m 
LEFT OUTER JOIN Likes l on m.ID = l.PostID 
WHERE '.implode(' OR ', $whereClauseArr).'
GROUP BY m.ID, m.MessageText ORDER BY count(m.id) desc'
);
$stmt->bind_param(str_repeat('s', count($whereValues)), ...$whereValues);
$stmt->execute();
$result = $stmt->get_result();
}

尽管在您的情况下，使用正则表达式检查同一列的多个值可能会更好。这将使您的查询更简单，也可能更快，具体取决于您拥有的关键字数量。

if (!empty($_POST['Message'])) {
$searchStr = get_post($con, 'Message');
$aKeyword = explode(" ", $searchStr);
$aKeyword = array_filter($aKeyword); // Remove empty values
$stmt = $con->prepare(
'SELECT m.ID, m.MessageText 
FROM MessageMain m 
LEFT OUTER JOIN Likes l on m.ID = l.PostID 
WHERE MessageText REGEXP ?
GROUP BY m.ID, m.MessageText ORDER BY count(m.id) desc'
);
$regexString = implode('|', $aKeyword);
$stmt->bind_param('s', $regexString);
$stmt->execute();
$result = $stmt->get_result();
}