本文目录导读：
在Java中，使用预编译SQL（PreparedStatement）是防止SQL注入最有效的方法,下面是完整的防注入案例和原理说明：
核心原理
预编译SQL的防注入原理：参数与SQL语句分离编译，参数值被当作纯数据处理,不会改变SQL语义。
正确案例（防注入）
1 基本PreparedStatement使用
public class SafeQueryExample {
// 正确：使用PreparedStatement
public User getUserById(Long id) {
String sql = "SELECT * FROM users WHERE id = ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// 参数绑定（自动处理转义）
pstmt.setLong(1, id);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return mapUser(rs);
}
}
} catch (SQLException e) {
log.error("查询用户失败", e);
}
return null;
}
// 多个参数示例
public List<User> searchUsers(String username, String email) {
String sql = "SELECT * FROM users WHERE username = ? AND email = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, username);
pstmt.setString(2, email);
try (ResultSet rs = pstmt.executeQuery()) {
List<User> users = new ArrayList<>();
while (rs.next()) {
users.add(mapUser(rs));
}
return users;
}
}
}
}
2 MyBatis框架示例
<!-- Mapper XML文件：使用#{}实现预编译 -->
<select id="getUser" resultType="User">
SELECT * FROM users
WHERE username = #{username}
AND password = #{password}
<!-- #{xxx} 会生成 PreparedStatement 的 ? 占位符 -->
</select>
// MyBatis Mapper接口
@Mapper
public interface UserMapper {
// 安全：使用#{}
@Select("SELECT * FROM users WHERE username = #{username}")
User findByUsername(@Param("username") String username);
// 安全：动态SQL也使用#{}
@Select("<script>" +
"SELECT * FROM users WHERE 1=1" +
"<if test='username != null'> AND username = #{username}</if>" +
"<if test='email != null'> AND email = #{email}</if>" +
"</script>")
List<User> findByCondition(UserQuery query);
}
3 JPA/Hibernate示例
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// 安全：JPQL使用参数绑定
@Query("SELECT u FROM User u WHERE u.username = :username")
User findByUsername(@Param("username") String username);
// 安全：Native SQL也支持参数绑定
@Query(value = "SELECT * FROM users WHERE username = :username",
nativeQuery = true)
User findByUsernameNative(@Param("username") String username);
}
// EntityManager方式
public User findUserByCondition(String username) {
String jpql = "SELECT u FROM User u WHERE u.username = :username";
return entityManager.createQuery(jpql, User.class)
.setParameter("username", username)
.getSingleResult();
}
错误案例（易注入）
// ❌ 错误1：使用字符串拼接SQL
public User findUserByUsername(String username) {
String sql = "SELECT * FROM users WHERE username = '" + username + "'";
// 如果username = "' OR '1'='1"，将查询所有用户！
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
}
// ❌ 错误2：MyBatis中使用${}
@Select("SELECT * FROM users WHERE username = ${username}")
// 使用${}不会预编译，会直接替换，存在注入风险
User findUserUnsafe(@Param("username") String username);
// ❌ 错误3：JPA中原生SQL拼接
@Query(value = "SELECT * FROM users WHERE username = '" + ":username" + "'",
nativeQuery = true)
// 错误的参数绑定方式
特殊场景处理
1 LIKE查询防注入
// 正确：手动处理通配符转义
public List<User> searchUsers(String keyword) {
String sql = "SELECT * FROM users WHERE username LIKE ? ESCAPE '/'";
// 转义特殊字符
String safeKeyword = keyword
.replace("!", "!!")
.replace("%", "!%")
.replace("_", "!_")
.replace("[", "
