PreparedStatement 与 SQL 注入防护

Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE name = '" + name + "'");

String sql = "SELECT * FROM user WHERE name = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery();

// 危险代码：字符串拼接
String sql = "SELECT * FROM user WHERE name = '" + name + "' AND password = '" + password + "'";
 
// 攻击输入
String name = "admin";
String password = "' OR '1'='1";
 
// 实际执行的 SQL
// SELECT * FROM user WHERE name = 'admin' AND password = '' OR '1'='1'
// 结果：绕过密码验证，返回所有用户

// 使用 PreparedStatement
String sql = "SELECT * FROM user WHERE name = ? AND password = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "admin");
pstmt.setString(2, "' OR '1'='1");
 
// 实际执行的 SQL（参数被转义）
// SELECT * FROM user WHERE name = 'admin' AND password = '\' OR \'1\'=\'1'
// 参数值作为整体字符串处理，不会改变 SQL 结构

1. 预编译阶段
   SQL: SELECT * FROM user WHERE name = ?
   ↓
   解析语法、生成执行计划、缓存

2. 参数绑定阶段
   setString(1, "张三")
   ↓
   参数值转义、类型转换

3. 执行阶段
   执行缓存的执行计划
   ↓
   返回结果

Statement stmt = connection.createStatement();
for (User user : users) {
    String sql = "INSERT INTO user(name, age) VALUES('" + user.getName() + "', " + user.getAge() + ")";
    stmt.addBatch(sql);
}
stmt.executeBatch();

String sql = "INSERT INTO user(name, age) VALUES(?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
 
for (User user : users) {
    pstmt.setString(1, user.getName());
    pstmt.setInt(2, user.getAge());
    pstmt.addBatch();
    
    // 每 1000 条执行一次，避免内存溢出
    if (i % 1000 == 0) {
        pstmt.executeBatch();
        pstmt.clearBatch();
    }
}
pstmt.executeBatch(); // 执行剩余

// 开启批量优化（MySQL）
connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
 
// 设置批量大小
pstmt.setFetchSize(1000);
 
// MySQL 需要开启 rewriteBatchedStatements
// jdbc:mysql://localhost:3306/db?rewriteBatchedStatements=true
// 将多条 INSERT 合并为一条：INSERT INTO t VALUES (...), (...), (...)

// 错误：PreparedStatement 不支持动态占位符数量
String sql = "SELECT * FROM user WHERE id IN (?)";
pstmt.setString(1, "1,2,3"); // 会被当作字符串 "1,2,3"，而不是三个值
 
// 正确方式1：动态构建占位符
List<Integer> ids = Arrays.asList(1, 2, 3);
String placeholders = ids.stream().map(i -> "?").collect(Collectors.joining(","));
String sql = "SELECT * FROM user WHERE id IN (" + placeholders + ")";
PreparedStatement pstmt = connection.prepareStatement(sql);
for (int i = 0; i < ids.size(); i++) {
    pstmt.setInt(i + 1, ids.get(i));
}
 
// 正确方式2：使用临时表或 FIND_IN_SET

// 错误：占位符不能加引号
String sql = "SELECT * FROM user WHERE name LIKE '%?%'"; // 错误！
 
// 正确：占位符包含完整模式
String sql = "SELECT * FROM user WHERE name LIKE ?";
pstmt.setString(1, "%" + name + "%");

// 错误：PreparedStatement 不能用于表名、列名
String sql = "SELECT * FROM ? WHERE ? = ?"; // 不支持！
 
// 只能使用字符串拼接（需要手动校验防止注入）
String tableName = validateTableName(inputTable);
String sql = "SELECT * FROM " + tableName + " WHERE name = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, name);

// 使用 try-with-resources 自动关闭
try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setString(1, name);
    ResultSet rs = pstmt.executeQuery();
    // 处理结果
} catch (SQLException e) {
    // 异常处理
}

<!-- 安全：参数值使用 #{} -->
<select id="getUserByName" resultType="User">
    SELECT * FROM user WHERE name = #{name}
</select>
 
<!-- 不安全：表名使用 ${}，需要校验 -->
<select id="getUserFromTable" resultType="User">
    SELECT * FROM ${tableName} WHERE name = #{name}
</select>
 
<!-- 排序字段需要 ${}，需严格校验 -->
<select id="getUsersOrderBy" resultType="User">
    SELECT * FROM user ORDER BY ${orderColumn} ${orderDirection}
</select>

// 白名单校验
public class OrderValidator {
    private static final Set<String> ALLOWED_COLUMNS = 
        Set.of("id", "name", "create_time", "update_time");
    
    public static String validateColumn(String column) {
        if (!ALLOWED_COLUMNS.contains(column)) {
            throw new IllegalArgumentException("Invalid column: " + column);
        }
        return column;
    }
}

攻击输入：' OR '1'='1
转义后：\' OR \'1\'=\'1（作为普通字符串）

PreparedStatement 核心要点：
1. 防注入：参数和 SQL 结构分离
2. 提性能：相同 SQL 预编译复用
3. 批量优化：rewriteBatchedStatements
4. 注意事项：IN、LIKE、表名场景处理