如何在搜索时用prepare方法编写MySQL查询

Question

如何使用prepare方法确保搜索查询的安全？

数据库连接

// connect to database
$con = new mysqli("localhost", "root", "", "chat");
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}

MySQL查询

if (isset($_POST['query'])) {

$search_string = trim($_POST['query']);
$query = mysqli_real_escape_string($con, $search_string);

//MySQL order by best match
$sql = "SELECT reply FROM question_answer WHERE question 
LIKE '%$query%'
ORDER BY CASE 
WHEN question = '$query' THEN 0  
WHEN question LIKE '$query%' THEN 1  
WHEN question LIKE '%$query%' THEN 2  
WHEN question LIKE '%$query' THEN 3  
ELSE 4
END, question ASC";
$res = mysqli_query($con, $sql);

if (mysqli_num_rows($res) > 0) {
$row = mysqli_fetch_assoc($res);
$html = $row['reply'];
} else {
$html = "Sorry not be able to understand you";
$added_on = date('Y-m-d h:i:s');
mysqli_query($con, "INSERT INTO unknown_question(question,added_on,type) VALUES('$query','$added_on','user')");
}
echo $html;
die();
}

Answer 1

在代码中使用mysqli准备好的查询的示例如下：

if (isset($_POST['query'])) {
$sql = "SELECT reply FROM question_answer WHERE question LIKE ?
ORDER BY CASE 
WHEN question = ? THEN 0  
WHEN question LIKE ? THEN 1  
WHEN question LIKE ? THEN 3
ELSE 2
END, question ASC";
$query = trim($_POST['query']);
$params = [
'%' . $query . '%',
$query,
$query . '%',
'%' . $query
];
$stmt = $con->prepare($sql);
$stmt->bind_param(str_repeat('s', count($params)), ...$params); 
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
if (!empty($row)) {
$html = $row['reply'];
} else {
$html = "Sorry not be able to understand you";
$added_on = date('Y-m-d h:i:s');
$sql = "INSERT INTO unknown_question(question,added_on,type) VALUES(?,?,'user')";
$stmt = $con->prepare($sql);
$stmt->bind_param('ss', $query, $added_on); 
$stmt->execute();
}
echo $html;
}