MySQL LIKE 如何参数化查询避免注入？

这是一个非常重要且常见的问题，因为它直接关系到SQL 注入和查询性能。

可以参数化，但必须谨慎处理通配符（ 和 _）。

标准的参数化方法（使用 Prepared Statements）只能将 LIKE 搜索的值部分进行参数化，而通配符（ 和 _）必须作为字符串的一部分拼接到 SQL 语句中。

为什么需要参数化 LIKE？

1. 防止 SQL 注入：这是最主要的原因，如果用户输入直接拼接到 SQL 语句中，恶意用户可以输入 ' OR '1'='1 这样的内容，从而绕过认证或执行任意 SQL 命令，参数化查询将用户输入作为数据处理，而不是作为 SQL 代码执行,从而杜绝了注入风险。
2. 提高性能：对于频繁执行的查询，数据库可以缓存预编译的 SQL 语句（执行计划），当参数值改变时，直接使用缓存的计划，避免了每次都重新解析和优化 SQL 的开销。

正确的参数化方法

最佳实践是使用 Prepared Statements（预处理语句）,下面我将通过不同编程语言的示例来展示如何正确操作。

场景：搜索用户名以 "张" 开头的所有用户。

SQL 模板应该是：SELECT * FROM users WHERE username LIKE ?

这里的 是一个占位符，关键在于，我们如何将 "张%" 传递给这个占位符。

编程语言示例

Java (JDBC)

这是最标准的做法，你需要将通配符和用户输入的值拼接成一个完整的字符串,然后将这个完整字符串作为参数传入。

import java.sql.*;
public class LikeExample {
    public static void main(String[] args) {
        String searchTerm = "张"; // 用户输入
        String sql = "SELECT * FROM users WHERE username LIKE ?";
        // 使用 try-with-resources 确保连接和语句被正确关闭
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "user", "password");
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 将 "张" 和 "%" 拼接后，作为单个参数设置
            String likePattern = searchTerm + "%";
            pstmt.setString(1, likePattern);
            System.out.println("Executing SQL: " + pstmt.toString()); // 打印最终执行的SQL
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                // 处理结果集
                System.out.println(rs.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

关键点：

• sql 语句中的 LIKE ? 的 占位符，接收的是 likePattern 这个完整的字符串，即 "张%"。
• 数据库驱动不会对 传入的值中的 或 _ 进行特殊转义,它们会被当作普通字符处理。

Python (MySQL Connector/Python)

与 Java 类似，你需要先构建好完整的模式字符串,然后将其作为参数传递。

import mysql.connector
db = mysql.connector.connect(
    host="localhost",
    user="user",
    password="password",
    database="mydb"
)
search_term = "李"
# 1. 构建完整的 LIKE 模式
like_pattern = search_term + "%"
# 2. SQL 语句中使用 %s 作为占位符
sql = "SELECT * FROM users WHERE username LIKE %s"
try:
    cursor = db.cursor()
    # 3. 将构建好的模式作为元组传递
    # 注意：即使只有一个参数，也必须写成 (like_pattern,) 的形式
    cursor.execute(sql, (like_pattern,))
    results = cursor.fetchall()
    for row in results:
        print(row)
finally:
    cursor.close()
    db.close()

关键点：

• Python 的 cursor.execute() 的第二个参数是一个元组。
• 即使只有一个参数，也必须写成 (like_pattern,),否则会出错。

PHP (PDO)

PHP 的 PDO 扩展也支持预处理语句,方法与上述语言一致。

<?php
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$searchTerm = "王";
// 1. 构建 LIKE 模式
$likePattern = $searchTerm . "%";
// 2. SQL 语句中使用 :placeholder 或 ?
$sql = "SELECT * FROM users WHERE username LIKE :pattern";
$stmt = $pdo->prepare($sql);
// 3. 绑定参数
$stmt->bindParam(':pattern', $likePattern);
$stmt->execute();
// 获取结果
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}
?>

关键点：

• 先构建 $likePattern 字符串。
• 使用 prepare() 准备 SQL 语句。
• 使用 bindParam() 或 bindValue() 将构建好的字符串绑定到占位符上。

错误的做法（易受 SQL 注入）

千万不要这样做！

// 错误示例！
String userInput = "some_input'; DROP TABLE users; --";
String sql = "SELECT * FROM users WHERE username LIKE '%" + userInput + "%'";
// 恶意用户可以注入任意SQL代码

这种做法直接将用户输入拼接到 SQL 字符串中，是典型的 SQL 注入漏洞。

性能优化与注意事项

1. 避免前导通配符：

   • 高效：LIKE '张%'，数据库可以使用 username 列上的索引（如果存在）进行快速范围查找。
   • 低效：LIKE '%张' 或 LIKE '%张%'，这种写法会导致索引失效，数据库必须对整个表进行全表扫描（Full Table Scan）,在数据量大时性能极差。

2. 全文索引： 如果你的需求是进行复杂的文本搜索（搜索包含“数据库”和“性能”的文章，但不包含“MySQL”），LIKE 可能不是最佳选择，你应该考虑使用 MySQL 的 全文索引（FULLTEXT Index）。

   • 它专为文本搜索优化。
   • 支持更复杂的搜索逻辑（布尔模式、自然语言模式）。
   • 性能远高于 LIKE '%...%'。

   示例：

   -- 假设 content 列有全文索引
   SELECT * FROM articles 
   WHERE MATCH(content) AGAINST('数据库 性能' IN BOOLEAN MODE);

最终建议：

• 始终使用 Prepared Statements 来处理用户输入。
• 手动拼接通配符到用户输入的值上,然后将这个完整的字符串作为参数传入。
• 尽量避免在 LIKE 模式中使用前导 ,除非你明确知道数据量很小或者无法使用索引。
• 对于复杂的文本搜索需求，优先考虑全文索引。