Java Spring JPA 本机查询 SQL 注入证明吗? [英] Is Java Spring JPA native query SQL injection proof?

问题描述

我写这个问题是因为我没有找到任何关于如何在 Spring Data JPA 中防止 SQL 注入的有用文章.所有教程都展示了如何使用这些查询，但他们没有提到这些可能的攻击.<​​/p>

我有以下查询:

@Repository公共接口 UserRepository 扩展了 CrudRepository{@Query(nativeQuery = true, value = "SELECT * FROM users WHERE email LIKE %:emailAddress%")公共响应列表<用户>getUsers(@Param("emailAddress") String emailAddress);}

发送请求的其余控制器:

@RequestMapping(value = "/get-users", method = RequestMethod.POST)公共响应getUsers(WebRequest 请求){return userService.getUsers(request.getParameter("email"));}

JPQL 或本机查询参数在执行之前是否进行了转义?

这是在我的 MySQL 控制台中执行的 SQL 注入查询，它删除了用户表:

SELECT * FROM users WHERE email LIKE '%';删除表用户；-- %';

我尝试通过向服务器发送 POST 请求来执行 SQL 攻击:

http://localhost:8080/get-usersPOST:键/值:电子邮件":'；删除表用户；--"

我启用了 Hibernate 的 sql 日志记录，这就是上述请求产生的结果:

[http-nio-8080-exec-8] DEBUG org.hibernate.SQL - SELECT * FROM users WHERE email LIKE ?Hibernate: SELECT * FROM users WHERE email LIKE ?[http-nio-8080-exec-8] 调试 org.hibernate.loader.Loader - bindNamedParameters() %';删除表用户；--%->电子邮件地址 [1][http-nio-8080-exec-8] TRACE o.h.type.descriptor.sql.BasicBinder - 绑定参数 [1] 为 [VARCHAR] - [%';删除表用户；--%]

表没有被删除(这很好)但是为什么参数没有被转义?

如果我不注释 @Param("emailAddress") 而我使用索引参数怎么办?:

@Query(nativeQuery = true, value = "SELECT * FROM users WHERE email LIKE ?1")公共响应列表<用户>getUsers(字符串电子邮件)；

解决方案

Spring Data JPA 的所有功能都使用绑定参数.很少有字符串连接，而且只有那些不是来自最终用户的内容.

因此 Spring Data JPA 可以防止 SQL 注入.

<块引用>

如果我不注释 @Param("emailAddress") 而我使用索引参数怎么办?

这与您使用索引或命名参数无关.

<块引用>

为什么参数没有转义?

由于绑定参数值不是在数据库中解析并转换为执行计划的 SQL 语句的一部分，因此无需对参数进行转义.这样做确实是错误的.

I'm writing this question because I didn't find any useful article about how to prevent SQL Injection in Spring Data JPA. All the tutorials are showing how to use these queries but they don't mentioned anything about these possible attacks.

I'm having the following query:

@Repository
public interface UserRepository extends CrudRepository<User, Integer> {

    @Query(nativeQuery = true, value = "SELECT * FROM users WHERE email LIKE %:emailAddress%")
    public ResponseList<User> getUsers(@Param("emailAddress") String emailAddress);
}

The rest controller to deliver the request:

@RequestMapping(value = "/get-users", method = RequestMethod.POST)
public Response<StringResponse> getUsers(WebRequest request) {
    return userService.getUsers(request.getParameter("email"));
}

Are JPQL or native query parameters escaped before executing them?

This is the query with SQL injection executed in my MySQL console which drops the users table:

SELECT * FROM users WHERE email LIKE '%'; DROP TABLE users; -- %';

I have tried to execute the SQL attack by sending a POST request to the server:

http://localhost:8080/get-users
POST: key/value: "email" : "'; DROP TABLE users; --"

I have enabled Hibernate's sql logging and this is what the above request produced:

[http-nio-8080-exec-8] DEBUG org.hibernate.SQL - SELECT * FROM users WHERE email LIKE ?
Hibernate: SELECT * FROM users WHERE email LIKE ?

[http-nio-8080-exec-8] DEBUG org.hibernate.loader.Loader - bindNamedParameters() %'; DROP TABLE users; -- % -> emailAddress [1]
[http-nio-8080-exec-8] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [%'; DROP TABLE users; -- %]

The table wasn't dropped (which is good) but why the parameter isn't escaped?

What if I don't annotate the @Param("emailAddress") and I use indexed parameters?:

@Query(nativeQuery = true, value = "SELECT * FROM users WHERE email LIKE ?1")
public ResponseList<User> getUsers(String email);

解决方案

Spring Data JPA uses bind parameters for all its features. There is very little String concatenation going on and that only with stuff that doesn't come from end users.

Therefore Spring Data JPA is secure against SQL injection.

What if I don't annotate the @Param("emailAddress") and I use indexed parameters?

This is independent of you using indexed or named parameters.

why the parameter isn't escaped?

Since the bind parameter values are not part of the SQL statement that gets parsed and converted into an execution plan in the database there is no need to escape the parameters. Indeed it would be a mistake to do so.

这篇关于Java Spring JPA 本机查询 SQL 注入证明吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！