SQL LIKE查询绑定参数后丢失模糊匹配效果，求解决方法

问题场景 #

原本通过直接拼接$_POST['keyword']实现SKU模糊匹配的SQL查询，改成mysqli预处理绑定参数后，只能匹配完全一致的SKU，丢失了原有的模糊匹配效果。

原有代码（直接拼接方式）：

$keyword = $_POST['keyword'];
$query = mysqli_query($conn, "SELECT p.name AS productname, p.manufacturer, p.productid, p.sku AS productsku, p.ean, p.weight, p.categories, s.name AS stockname, s.sku AS stocksku, s.qty, s.sellprice, s.costprice, d.distyname, d.lastupdated, m.image FROM stock s LEFT OUTER JOIN products p ON p.productid = s.prodid INNER JOIN distributor d ON d.distyid = s.distributorid LEFT OUTER JOIN media m ON s.sku = m.sku WHERE s.sku LIKE '%$keyword%' ORDER BY `stocksku`");
while($fetch = mysqli_fetch_array($query)){
    // 处理逻辑
}

修改后的预处理代码（失效版本）：

$query = 'SELECT p.name AS productname, p.manufacturer, p.productid, p.sku AS productsku, p.ean, p.weight, p.categories, s.name AS stockname, s.sku AS stocksku, s.qty, s.sellprice, s.costprice, d.distyname, d.lastupdated, m.image FROM stock s LEFT OUTER JOIN products p ON p.productid = s.prodid INNER JOIN distributor d ON d.distyid = s.distributorid LEFT OUTER JOIN media m ON s.sku = m.sku WHERE s.sku LIKE ? ORDER BY `stocksku`';    
$stmt = $conn->prepare($query);    
$stmt->bind_param('s', $keyword);
$stmt->execute();
$result = $stmt->get_result();
$fetch = $result->fetch_assoc();

问题原因 #

原来的直接拼接方式中，SQL语句里的LIKE '%$keyword%'是给关键词前后加了通配符%，实现包含匹配；但改成预处理后，仅将纯$keyword绑定到?占位符，相当于执行了LIKE 'xxx'（xxx为用户输入的关键词），没有通配符的作用，自然只能匹配完全一致的SKU。

解决办法 #

有两种可靠的修正方式，都能保留预处理防注入的优势：

方式1：给绑定的参数添加通配符

在绑定参数前，给关键词前后拼接%，再传入预处理语句：

$keyword = "%{$_POST['keyword']}%"; // 给关键词前后加通配符
$query = 'SELECT p.name AS productname, p.manufacturer, p.productid, p.sku AS productsku, p.ean, p.weight, p.categories, s.name AS stockname, s.sku AS stocksku, s.qty, s.sellprice, s.costprice, d.distyname, d.lastupdated, m.image FROM stock s LEFT OUTER JOIN products p ON p.productid = s.prodid INNER JOIN distributor d ON d.distyid = s.distributorid LEFT OUTER JOIN media m ON s.sku = m.sku WHERE s.sku LIKE ? ORDER BY `stocksku`';    
$stmt = $conn->prepare($query);    
$stmt->bind_param('s', $keyword);
$stmt->execute();
$result = $stmt->get_result();
// 循环遍历所有匹配结果
while($fetch = $result->fetch_assoc()){
    // 你的业务逻辑
}

方式2：在SQL语句中用CONCAT拼接通配符

直接在SQL里通过CONCAT('%', ?, '%')把通配符和参数拼接，保持绑定的是纯关键词：

$keyword = $_POST['keyword'];
$query = 'SELECT p.name AS productname, p.manufacturer, p.productid, p.sku AS productsku, p.ean, p.weight, p.categories, s.name AS stockname, s.sku AS stocksku, s.qty, s.sellprice, s.costprice, d.distyname, d.lastupdated, m.image FROM stock s LEFT OUTER JOIN products p ON p.productid = s.prodid INNER JOIN distributor d ON d.distyid = s.distributorid LEFT OUTER JOIN media m ON s.sku = m.sku WHERE s.sku LIKE CONCAT(\'%\', ?, \'%\') ORDER BY `stocksku`';    
$stmt = $conn->prepare($query);    
$stmt->bind_param('s', $keyword);
$stmt->execute();
$result = $stmt->get_result();
// 循环遍历所有匹配结果
while($fetch = $result->fetch_assoc()){
    // 你的业务逻辑
}

另外注意：修改后的原代码缺少循环获取所有结果的逻辑，需要补上while遍历才能拿到全部匹配数据。

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