动态SQL编写技巧与安全方法

在文章实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《构建动态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="animal_details.php?a_id=<?php echo $row['id'] ?>">
                                    <img src="admin/image/<?php echo $row['img']?>" class="w-100" alt="">
                                </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注入攻击、保障应用程序安全性的核心实践。遵循这些原则，将使您的数据库交互代码更加健壮、安全和易于维护。

本篇关于《动态SQL编写技巧与安全方法》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于文章的相关知识，请关注golang学习网公众号！