防止JPQL查询SQL注入 [英] Prevent JPQL query sql injection

问题描述

我被告知下面的查询不安全，因为来自前端输入字段的参数:searchFor 可以用于SQL注入. Plase建议在下面的代码中防止SQL注入的最佳解决方案是什么?

I was advised that below query is not safe as parameter :searchFor coming from input field in front end can be used for SQL injection. Plase advise what is the best solution to prevent SQL injection in below code?

@Query("SELECT u FROM User u WHERE lower(u.username) LIKE %:searchFor% " +
        " OR lower(concat(u.firstname, ' ', u.lastname)) LIKE %:searchFor% " +
        " OR lower(u.email) LIKE %:searchFor%")
Page<User> findAllAndSearch(@Param(value = "searchFor") String searchFor, Pageable pageable);

我没有使用"+"来连接字符串，而是提供了参数(:searchFor).不知道这是否仍然不安全.

I am not using "+" to join strings, but provide parameter instead (:searchFor). Not sure if this still unsafe.

推荐答案

有人建议我以下查询作为参数:searchFor

I was advised that below query is not safe as parameter :searchFor

您应该挑战此建议.

当客户端传递的参数值可能传输其他查询逻辑(通常是不希望的)并且在已执行的查询中将允许的这种查询逻辑时，发生SQL注入.
例如，代替searchFor的简单foo文本值，参数值还可以在查询中包含其他逻辑，例如:foo OR ''=''.那就是SQL注入.

SQL injections occur when the parameter value passed by the client may transmit additional query logic (generally undesirable) and that that will be allowed in the executed query such as.
For example instead of a simple foo textual value for searchFor, the parameter value could contain also additional logic in the query, for example : foo OR ''=''. That is SQL injection.

在您的情况下，无法进行SQL注入，因为您没有手动设置参数，而是依靠一种安全的方式来绑定searchFor参数:Spring.
实际上，Spring就像JPA实现一样以安全的方式绑定参数值，即从JPA Query实例设置参数值，该实例被保护以防止对声明的参数进行SQL注入.

In your case, SQL injection is not possible because you don't set the parameter manually but rather you rely on a safe way to bind thesearchFor parameter : Spring.
Indeed, Spring binds the parameter values in a safe way as JPA implementations do, that is setting the parameter value from a JPA Query instance that is protected against SQL injections for declared parameters.

例如，以以下查询为例(为简化起见，我删除了%部分):

For example take this query (I removed the % part to simplify) :

"SELECT u FROM User u WHERE lower(u.username) LIKE :searchFor"

并尝试使用字符串"foo OR ''==''"设置searchFor的参数，以尝试注入始终为true的SQL条件.
如果打开JPA实现的日志以输出参数绑定(对于Hibernate:logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE)，您将看到类似

And try to set the param of searchFor with the String "foo OR ''==''" to try to inject a SQL condition that is always true.
If you turn on the log of your JPA implementation to output the paramaters binding (for Hibernate : logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE) you could see something like :

TRACE 11012-[nio-8080-exec-8] o.h.type.descriptor.sql.BasicBinder :将参数[1]绑定为[VARCHAR]-[foo OR''==​​'']

TRACE 11012 --- [nio-8080-exec-8] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [foo OR ''=='']

仅对参数值绑定执行绑定，而不作为添加新查询逻辑的方式.最终查询部分受保护的程度如下:

The binding is only performed for the parameter value and not as a way to add new query logic. The final query part protected is so protected as :

SELECT u FROM User u WHERE lower(u.username) LIKE "foo OR ''==''"

这篇关于防止JPQL查询SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！