在SQL查询中安全地使用多个WHERE条件与PHP会话变量

SELECT
    user.username, books.bid, name, authors, edition,
    status, approve, issue, issue_book.return
FROM user
INNER JOIN issue_book ON user.username = issue_book.username
INNER JOIN books ON issue_book.bid = books.bid
WHERE issue_book.approve = '$exp'
ORDER BY `issue_book`.`return` DESC;

SELECT
    user.username, books.bid, name, authors, edition,
    status, approve, issue, issue_book.return
FROM user
INNER JOIN issue_book ON user.username = issue_book.username
INNER JOIN books ON issue_book.bid = books.bid
WHERE issue_book.approve = '$exp' AND user.username = '{$_SESSION['login_user']}'
ORDER BY `issue_book`.`return` DESC;

<?php
// 假设 $db 已经是一个有效的 mysqli 数据库连接对象
if (isset($_SESSION['login_user'])) {
    $exp_status = 'EXPIRED'; // 使用更具描述性的变量名
    $current_username = $_SESSION['login_user'];

    // 1. 准备SQL查询模板，使用问号 (?) 作为参数占位符
    // 为表使用别名 (u, b, ib) 以提高可读性
    $sql = "
        SELECT
            u.username, b.bid, b.name, b.authors, b.edition,
            b.status, ib.approve, ib.issue, ib.return
        FROM user u
        INNER JOIN issue_book ib ON u.username = ib.username
        INNER JOIN books b ON ib.bid = b.bid
        WHERE ib.approve = ? AND u.username = ?
        ORDER BY ib.return DESC
    ";

    // 2. 准备预处理语句
    if ($stmt = mysqli_prepare($db, $sql)) {
        // 3. 绑定参数
        // 'ss' 表示绑定两个字符串类型参数 (s = string)
        mysqli_stmt_bind_param($stmt, "ss", $exp_status, $current_username);

        // 4. 执行预处理语句
        mysqli_stmt_execute($stmt);

        // 5. 获取结果集
        $res = mysqli_stmt_get_result($stmt);

        if (mysqli_num_rows($res) == 0) {
            echo "您没有逾期书籍。";
        } else {
            ?>
            <table class='table table-bordered'>
                <tr style='background-color: #abb79b; color: white;'>
                    <th>用户名</th>
                    <th>书籍ID</th>
                    <th>书名</th>
                    <th>作者</th>
                    <th>版本</th>
                    <th>状态</th>
                    <th>归还日期</th>
                </tr>
                <?php
                while ($row = mysqli_fetch_assoc($res)) {
                    ?>
                    <tr style='background-color: white;'>
                        <td><?php echo htmlspecialchars($row['username']); ?></td>
                        <td><?php echo htmlspecialchars($row['bid']); ?></td>
                        <td><?php echo htmlspecialchars($row['name']); ?></td>
                        <td><?php echo htmlspecialchars($row['authors']); ?></td>
                        <td><?php echo htmlspecialchars($row['edition']); ?></td>
                        <td><?php echo htmlspecialchars($row['status']); ?></td>
                        <td><?php echo htmlspecialchars($row['return']); ?></td>
                    </tr>
                    <?php
                }
                ?>
            </table>
            <?php
        }
        // 6. 关闭语句
        mysqli_stmt_close($stmt);
    } else {
        // 错误处理：在生产环境中应记录日志而非直接显示
        echo "数据库语句准备失败: " . mysqli_error($db);
    }
} else {
    echo "</br></br></br>";
    echo "<h2><b>";
    echo "请先登录。";
    echo "</b></h2>";
}
?>