正确构建PHP/MySQL多条件WHERE查询与SQL注入防护

SELECT * FROM customers WHERE customer_name OR id LIKE '%".$search."%' LIMIT 5;

SELECT * FROM customers 
WHERE customer_name LIKE '%".$search."%' 
OR id LIKE '%".$search."%' 
LIMIT 5;

<?php
// 假设 $search 变量来自用户输入，例如 $_GET['q']
$search = $_GET['q']; 

// 注意：直接拼接字符串存在SQL注入风险，不推荐在生产环境使用
$query = "SELECT * FROM customers 
          WHERE customer_name LIKE '%" . $search . "%' 
          OR id LIKE '%" . $search . "%' 
          LIMIT 5";

// 示例：执行查询（使用mysqli）
$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->connect_errno) {
    echo "连接失败: " . $mysqli->connect_error;
    exit();
}

$result = $mysqli->query($query);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row['id'] . " - Name: " . $row['customer_name'] . "<br>";
    }
    $result->free();
} else {
    echo "查询失败: " . $mysqli->error;
}

$mysqli->close();
?>

<?php
// 假设 $search 变量来自用户输入
$search_term = $_GET['q'] ?? ''; // 使用null合并运算符提供默认值，防止未设置

// 数据库连接信息
$db_host = "localhost";
$db_user = "username";
$db_pass = "password";
$db_name = "database";

// 建立数据库连接
$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);

// 检查连接
if ($mysqli->connect_errno) {
    echo "数据库连接失败: " . $mysqli->connect_error;
    exit();
}

// 准备 SQL 查询语句
// 注意：使用占位符 (?) 代替直接拼接变量
$query = "SELECT id, customer_name FROM customers 
          WHERE customer_name LIKE ? 
          OR id LIKE ? 
          LIMIT 5";

// 创建预处理语句对象
$stmt = $mysqli->prepare($query);

if ($stmt === false) {
    echo "预处理语句失败: " . $mysqli->error;
    $mysqli->close();
    exit();
}

// 准备绑定参数的值
// 为LIKE操作符添加通配符 '%'
$param_search = "%" . $search_term . "%";

// 绑定参数
// 'ss' 表示两个参数都是字符串类型 (s = string)
$stmt->bind_param("ss", $param_search, $param_search);

// 执行预处理语句
$stmt->execute();

// 获取结果集
$result = $stmt->get_result();

if ($result) {
    if ($result->num_rows > 0) {
        echo "<h3>搜索结果:</h3>";
        while ($row = $result->fetch_assoc()) {
            echo "ID: " . htmlspecialchars($row['id']) . 
                 " - 名称: " . htmlspecialchars($row['customer_name']) . "<br>";
        }
    } else {
        echo "没有找到匹配的客户。";
    }
    $result->free(); // 释放结果集
} else {
    echo "获取结果失败: " . $stmt->error;
}

// 关闭语句和数据库连接
$stmt->close();
$mysqli->close();
?>