CodeIgniter中如何转义LIKE表达式值且不向相邻通配符内加多余引号

我最近在做查询功能的测试，特意用随机字符串来验证系统能不能正确返回匹配结果，其中我选的测试关键词是cal，对应的基础SQL语句其实很简单：

SELECT c.`ID` AS id, 
       CONCAT(p.`Name`, ' > ', c.`Name`) AS name, 
       'cal' AS q 
FROM `cq_provice` p 
JOIN `cq_city` c ON c.`provinceID` = p.`ID` 
WHERE (CONCAT(p.`Name`, ' > ', c.`Name`) LIKE '%cal%') 
ORDER BY p.`Name`, c.`Name` 
LIMIT 0, 50

为了兼顾查询的安全性（避免SQL注入）和代码的可维护性，我尝试了两种预准备SQL的实现方式：

• CodeIgniter原生预准备查询方式：
  CodeIgniter的数据库类自带了安全的预准备语句支持，不用手动处理占位符的转义，写法大概是这样：

  $search_term = 'cal';
  $like_pattern = "%{$search_term}%";
  $query = $this->db->query("
      SELECT c.`ID` AS id, 
             CONCAT(p.`Name`, ' > ', c.`Name`) AS name, 
             ? AS q 
      FROM `cq_provice` p 
      JOIN `cq_city` c ON c.`provinceID` = p.`ID` 
      WHERE CONCAT(p.`Name`, ' > ', c.`Name`) LIKE ? 
      ORDER BY p.`Name`, c.`Name` 
      LIMIT 0, 50
  ", array($search_term, $like_pattern));
  

  这里要注意，通配符%最好是在绑定参数的时候加上，而不是直接写在SQL语句里，这样能保证参数的安全性。

• 另一种预准备查询方式（比如原生PDO）：
  如果不用框架的话，原生PDO的预准备语句也是个可靠的选择，示例代码如下：

  $search_term = 'cal';
  $like_pattern = "%{$search_term}%";
  $pdo = new PDO('mysql:host=localhost;dbname=your_db', 'user', 'pass');
  $stmt = $pdo->prepare("
      SELECT c.`ID` AS id, 
             CONCAT(p.`Name`, ' > ', c.`Name`) AS name, 
             :q AS q 
      FROM `cq_provice` p 
      JOIN `cq_city` c ON c.`provinceID` = p.`ID` 
      WHERE CONCAT(p.`Name`, ' > ', c.`Name`) LIKE :like_pattern 
      ORDER BY p.`Name`, c.`Name` 
      LIMIT 0, 50
  ");
  $stmt->bindParam(':q', $search_term);
  $stmt->bindParam(':like_pattern', $like_pattern);
  $stmt->execute();
  $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
  

在测试过程中，我发现不管用哪种方式，只要占位符和参数绑定正确，都能和原始的SQL语句返回一致的结果，而且能有效避免SQL注入风险。

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