PHP搜索结合MySQL CASE函数失效问题求助

我帮你揪出了两个核心问题，咱们一步步来解决：

第一个致命问题：搜索查询引用了不存在的列 #

你在搜索分支的SQL里写了WHERE Value1r = '" . $valueToSearch . "' OR Value2r = '" . $valueToSearch . "'，但Value1r和Value2r并不是gdata表中实际存储的字段——它们是你在默认查询里用CASE语句生成的临时计算列。数据库根本找不到这两个列，自然会抛出类似Unknown column 'Value1r' in 'where clause'的错误。

第二个高危问题：SQL注入漏洞 #

你直接把用户输入的$valueToSearch拼接到SQL语句里，没有做任何过滤或转义。恶意用户可以通过输入特殊字符执行任意SQL操作，比如删除你的整张表，这是非常危险的。

修复后的完整代码 #

<?php
// 初始化搜索结果变量
$search_result = null;

// 处理搜索请求
if(isset($_POST['search'])) {
    $valueToSearch = trim($_POST['valueToSearch']);
    
    // 使用预处理语句防止SQL注入，同时在WHERE子句中复用CASE的判断逻辑
    $query = "
        SELECT 
            Value1, 
            Value2,
            CASE 
                WHEN Value1 >= 90 AND Value1 <= 100 THEN 'A'
                WHEN Value1 >= 80 AND Value1 <= 89 THEN 'B'
                WHEN Value1 >= 70 AND Value1 <= 79 THEN 'C'
                WHEN Value1 >= 0 AND Value1 <= 69 THEN 'F'
                ELSE '' 
            END AS Value1r,
            CASE 
                WHEN Value2 >= 90 AND Value2 <= 100 THEN 'A'
                WHEN Value2 >= 80 AND Value2 <= 89 THEN 'B'
                WHEN Value2 >= 70 AND Value2 <= 79 THEN 'C'
                WHEN Value2 >= 0 AND Value2 <= 69 THEN 'F'
                ELSE '' 
            END AS Value2r
        FROM gdata 
        WHERE 
            Value1 = ? 
            OR Value2 = ?
            -- 复用CASE的逻辑来匹配等级（比如用户搜'A'时，匹配对应分数区间）
            OR (Value1 >= 90 AND Value1 <= 100 AND ? = 'A')
            OR (Value1 >= 80 AND Value1 <= 89 AND ? = 'B')
            OR (Value1 >= 70 AND Value1 <= 79 AND ? = 'C')
            OR (Value1 >= 0 AND Value1 <= 69 AND ? = 'F')
            OR (Value2 >= 90 AND Value2 <= 100 AND ? = 'A')
            OR (Value2 >= 80 AND Value2 <= 89 AND ? = 'B')
            OR (Value2 >= 70 AND Value2 <= 79 AND ? = 'C')
            OR (Value2 >= 0 AND Value2 <= 69 AND ? = 'F')
    ";
    
    $search_result = filterTable($query, [$valueToSearch, $valueToSearch, $valueToSearch, $valueToSearch, $valueToSearch, $valueToSearch, $valueToSearch, $valueToSearch, $valueToSearch]);
} else {
    // 默认查询，保持原有的CASE逻辑
    $query = "
        SELECT 
            Value1, 
            Value2,
            CASE 
                WHEN Value1 >= 90 AND Value1 <= 100 THEN 'A'
                WHEN Value1 >= 80 AND Value1 <= 89 THEN 'B'
                WHEN Value1 >= 70 AND Value1 <= 79 THEN 'C'
                WHEN Value1 >= 0 AND Value1 <= 69 THEN 'F'
                ELSE '' 
            END AS Value1r,
            CASE 
                WHEN Value2 >= 90 AND Value2 <= 100 THEN 'A'
                WHEN Value2 >= 80 AND Value2 <= 89 THEN 'B'
                WHEN Value2 >= 70 AND Value2 <= 79 THEN 'C'
                WHEN Value2 >= 0 AND Value2 <= 69 THEN 'F'
                ELSE '' 
            END AS Value2r
        FROM gdata
    ";
    $search_result = filterTable($query);
}

// 修改filterTable函数，支持预处理语句
function filterTable($query, $params = []) {
    $connect = mysqli_connect("localhost", "root", "", "db");
    // 检查连接错误
    if(mysqli_connect_error()) {
        die("数据库连接失败: " . mysqli_connect_error());
    }
    
    $stmt = mysqli_prepare($connect, $query);
    if($stmt && !empty($params)) {
        // 根据参数数量绑定类型（这里都是字符串，用's'）
        $types = str_repeat('s', count($params));
        mysqli_stmt_bind_param($stmt, $types, ...$params);
        mysqli_stmt_execute($stmt);
        $filter_Result = mysqli_stmt_get_result($stmt);
    } else {
        $filter_Result = mysqli_query($connect, $query);
    }
    
    return $filter_Result;
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Gdata</title>
</head>
<body>
    <form action="gdata.php" method="post">
        <input type="text" name="valueToSearch" placeholder="Search">
        <input type="submit" name="search" value="Filter">
    </form>
    <table>
        <tr>
            <th>G1</th>
            <th>G2</th>
            <th>R1</th>
            <th>R2</th>
        </tr>
        <?php 
        if($search_result) {
            while($row = mysqli_fetch_array($search_result)) { 
                echo "<tr>";
                echo "<td>" . htmlspecialchars($row['Value1']) . "</td>";
                echo "<td>" . htmlspecialchars($row['Value2']) . "</td>";
                echo "<td>" . htmlspecialchars($row['Value1r']) . "</td>";
                echo "<td>" . htmlspecialchars($row['Value2r']) . "</td>";
                echo "</tr>"; 
            }
        }
        ?>
    </table>
</body>
</html>

关键修改说明 #

1. 修复列不存在的问题：在搜索查询中不再引用Value1r/Value2r，而是直接复用CASE的逻辑——当用户搜索等级（比如'A'）时，匹配对应的分数区间；搜索分数时直接匹配Value1/Value2。
2. 解决SQL注入问题：改用mysqli预处理语句，将用户输入作为参数绑定，而不是直接拼接到SQL中，彻底避免注入风险。
3. 增加安全防护：用htmlspecialchars()输出数据库内容，防止XSS攻击；增加数据库连接错误检查，方便调试。
4. 代码可读性优化：将SQL语句格式化，拆分逻辑，让代码更易维护。

内容的提问来源于stack exchange，提问作者Max