构建动态SQL查询的技巧与安全实践

本文旨在解决PHP中构建动态SQL查询时常见的逻辑错误，特别是当需要根据多个条件筛选数据时，如何避免查询语句被意外覆盖的问题。我们将深入探讨如何通过增量构建WHERE子句来确保所有筛选条件生效，并重点强调使用预处理语句来防范SQL注入攻击，从而提升代码的安全性与健壮性。

动态SQL查询中的常见陷阱

在开发web应用时，根据用户输入或其他条件动态构建sql查询是常见的需求。然而，不当的实现方式可能导致查询逻辑错误，例如某些筛选条件被意外覆盖。考虑以下php函数，其目的是根据分类id ($cat_id) 或动物id ($animal_id) 获取动物信息，并默认只显示状态为1的动物：

function get_animals($cat_id='', $animal_id='')
{
global $con;
$query = "SELECT * FROM animals WHERE status= 1"; // 初始查询
if($cat_id!='') {
$query = "SELECT * FROM FROM animals WHERE category_name='$cat_id'"; // 问题：覆盖了status=1条件
}
if ($animal_id!='') {
$query = "SELECT * FROM animals WHERE id=$animal_id"; // 问题：再次覆盖了之前的条件
}
return mysqli_query($con,$query);
}

上述代码的问题在于，一旦$cat_id或$animal_id被设置，原始的$query变量（包含status=1条件）就会被完全覆盖。这意味着，如果用户请求特定分类的动物，status=1的筛选条件将失效，导致所有状态的动物都可能被返回。

正确构建动态WHERE子句

要解决上述问题，我们不应该在每个条件分支中重新定义整个$query字符串，而是应该在原始查询的基础上，通过追加AND操作符来添加额外的筛选条件。这样可以确保所有条件都协同工作。

以下是修正后的get_animals函数逻辑：

function get_animals($cat_id='', $animal_id='')
{
global $con;
// 初始查询，包含所有查询都应满足的基本条件
$query = "SELECT * FROM animals WHERE status = 1";
// 根据条件追加WHERE子句
if (!empty($cat_id)) {
// 使用 .= 操作符追加条件，并用 AND 连接
$query .= " AND category_name = '$cat_id'";
}
if (!empty($animal_id)) {
// 再次追加条件
$query .= " AND id = $animal_id";
}
return mysqli_query($con, $query);
}

通过这种方式，$query字符串会根据传入的参数逐步构建。例如：

• 如果只传入$cat_id，查询将变为 SELECT * FROM animals WHERE status = 1 AND category_name = ‘$cat_id’。
• 如果只传入$animal_id，查询将变为 SELECT * FROM animals WHERE status = 1 AND id = $animal_id。
• 如果两者都传入，查询将变为 SELECT * FROM animals WHERE status = 1 AND category_name = ‘$cat_id’ AND id = $animal_id。
• 如果两者都未传入，查询保持 SELECT * FROM animals WHERE status = 1。

关键：使用预处理语句防止SQL注入

虽然上述修正解决了查询逻辑问题，但直接将变量拼接到SQL查询字符串中仍然存在严重的安全漏洞——SQL注入。恶意用户可以通过在$cat_id或$animal_id中注入SQL代码来执行未经授权的数据库操作。

为了确保应用程序的安全性，强烈推荐使用预处理语句（Prepared Statements）。mysqli扩展提供了预处理语句的功能。

以下是使用预处理语句重构get_animals函数的示例：

function get_animals($cat_id = null, $animal_id = null)
{
global $con;
// 初始查询和条件数组
$sql = "SELECT * FROM animals WHERE status = 1";
$params = [];
$types = ""; // 存储参数类型字符串，如 "is" (integer, string)
if ($cat_id !== null && $cat_id !== '') {
$sql .= " AND category_name = ?";
$params[] = $cat_id;
$types .= "s"; // 's' for string
}
if ($animal_id !== null && $animal_id !== '') {
$sql .= " AND id = ?";
$params[] = $animal_id;
$types .= "i"; // 'i' for integer
}
// 准备语句
if ($stmt = mysqli_prepare($con, $sql)) {
// 如果有参数，绑定参数
if (!empty($params)) {
// 使用 call_user_func_array 动态绑定参数
// 第一个参数是 $stmt，后面是 $types 和 $params 数组的引用
mysqli_stmt_bind_param($stmt, $types, ...$params);
}
// 执行语句
mysqli_stmt_execute($stmt);
// 获取结果
$result = mysqli_stmt_get_result($stmt);
// 关闭语句
mysqli_stmt_close($stmt);
return $result;
} else {
// 错误处理
error_log("Error preparing statement: " . mysqli_error($con));
return false;
}
}

代码解释：

1. 初始化 $sql, $params, $types: $sql存储基础查询，$params存储要绑定的参数值，$types存储参数的类型字符串（’i’代表整数，’s’代表字符串，’d’代表双精度浮点数，’b’代表BLOB）。
2. 条件判断与参数收集: 根据$cat_id和$animal_id是否为空，向$sql追加AND条件和占位符?，并同时将对应的参数值添加到$params数组，将参数类型字符添加到$types字符串。
3. mysqli_prepare(): 准备SQL语句。这会将SQL语句发送到数据库服务器进行预编译，提高效率并防止SQL注入。
4. mysqli_stmt_bind_param(): 如果有参数需要绑定，则使用此函数将变量绑定到预处理语句中的占位符。call_user_func_array结合…$params（PHP 5.6+）或&$params[0], …（旧版本）可以动态处理可变数量的参数。
5. mysqli_stmt_execute(): 执行预处理语句。
6. mysqli_stmt_get_result(): 获取查询结果集。
7. mysqli_stmt_close(): 关闭预处理语句，释放资源。

animals.php文件的调用示例

在animals.php文件中，调用更新后的get_animals函数保持不变，但其内部处理已经变得更加安全和健壮：

<?php
// 假设 $con 已经在其他地方定义并连接
// include 'db_connection.php'; // 确保数据库连接可用
$cat_id = null; // 初始化为null或空字符串
if(isset($_GET['id'])) {
// 对于GET参数，不需要在这里使用mysqli_real_escape_string，因为get_animals会使用预处理语句
$cat_id = $_GET['id'];
}
// 调用更新后的函数
$particular_animal = get_animals($cat_id);
?>
<!-- HTML 部分保持不变 -->
<div class="container mt-5 ">
<div class="row">
<?php
if($particular_animal && mysqli_num_rows($particular_animal)) {
while($row = mysqli_fetch_assoc($particular_animal)) {
?>
<div class="col-md-4 product-grid">
<div class="row">
<div class="image border border-info bg-light">
<a href="https://www.php.cn/faq/animal_details.php?a_id=<?php echo $row['id'] ?>">
@@##@@" class="w-100" >
</a>
<h4 class="text-center mt-2 text-info font-weight-bold"><?php echo $row['name'] ?></h4>
<p class="text-center mt-2"><?php echo $row['gender'] ?></p>
</div>
</div>
</div>
<?php
}
} else {
echo "未找到相关记录或查询失败。"; // 改进错误提示
}
?>
</div>
</div>

总结

构建动态SQL查询时，避免查询语句被覆盖是确保所有筛选条件生效的关键。通过增量地向WHERE子句添加条件，可以灵活地构建满足多重筛选需求的查询。更重要的是，始终采用预处理语句来处理用户输入，这是防止SQL注入攻击、保障应用程序安全性的核心实践。遵循这些原则，将使您的数据库交互代码更加健壮、安全和易于维护。