英文:
Avoid SQL Injection in Spring JPA
问题
我正在使用类似以下的 sql query
:
String query = "Select max(case when UPPER(key)='firstname' then value end) as firstNameName, , ... order by "+orderBy;
result = em.createNativeQuery(query).getResultList();
em.close();
由于某种原因,我必须使用动态的 +orderBy
。其中 orderBy =firstname ASC , lastname DESC
等。尝试过使用 .setParameter(1, orderedBy)
,但在这种情况下,我没有获得预期的排序结果。
英文:
I am using sql query
like
String query = "Select max(case when UPPER(key)='firstname' then value end) as firstNameName, , ... order by "+orderBy;
result = em.createNativeQuery(query).getResultList();
em.close();
for some reason i would have to use dynamic +orderBy
. Where orderBy =firstname ASC , lastname DESC
etc. Tried using .setParameter(1, orderedBy)
but in this case i am not getting expected ordered results.
答案1
得分: 9
为了避免 SQL 注入威胁,首先需要避免将参数直接附加到查询中。当您在应用程序中附加参数时,攻击者可以劫持您的 SQL 代码(例如使用撇号和其他方式)。
例如:
如果您的查询是 "select * from table where name="+id
攻击者可以向字段传递诸如以下值:
'John' or 1=1;
-> 可以看到此表中的所有记录
甚至
'John' or 1=1;Delete all from users;'
-> 可以删除用户表中的所有条目。
通过输入清理、输入白名单/黑名单(从输入中删除不需要的字符/定义允许或不允许的字符列表)等机制可以避免劫持查询。大多数现代框架,如JDBC/JPA/Hibernate,都可以提供对此威胁的保护。
在此基础上,我们应考虑以下情况:
考虑 SQL 参数:
例如,JDBC提供了预编译语句,您可以在其中定义一个变量,框架会替换它。
在您的情况下,JPA 实现(Hibernate)还通过参数化查询和位置参数提供了避免此威胁的机制:
- 通过本地查询位置参数:
Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");
- 通过命名参数(JPQL):
Query q = em.createQuery("SELECT count(*) FROM mytable where username = :username");
q.setParameter("username", "test");
考虑 orderBy 参数:
CriteriaQuery
/SpringSpecifications
:
CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
CriteriaQuery<RESULT> criteria = cb.createQuery(RESULT.class);
Root<RESULT> root = criteria.from(RESULT.class);
return this.entityManager.createQuery(
criteria.select(root).orderBy(cb.asc(root.get("ORDER_BY_FIELD"))))
.getResultList();
- 通过预先构建的 Spring 特定的
Sort
参数传递orderBy
(使用 spring-data 库):
Sort sort = Sort.by(Sort.Direction.ASC, "criteria");
em.createQuery(QueryUtils.applySorting(yourSqlQuery_withoutSorting, sort));
- 使用带有 Spring Data 库中
@Query
注解的方法:
您可以通过仅使用@Query
注解来实现相同的结果,减少了冗长的代码(无需注入 entityManager 并创建原生查询):
@Query("select u from User u where u.lastname like ?1%")
List<User> findByAndSort(String lastname, Sort sort);
注意:
原生 vs 非原生(JPQL):
JPQL 查询与数据库供应商(MySQL、PostgreSQL、Oracle、DB2)无关,而原生查询在需要使用特定于数据库的功能(在供应商之间不同的功能)时更为重要。
简单地说,JPQL 无法支持原生 [With]
2 子句 PLSQL 标准函数。
关于您的编辑:
您可以尝试应用以下 SQL 技巧以进行动态排序:
SELECT param1, param2 ...
FROM ...
ORDER BY case when :sortParam='name asc' then name asc END
case when :sortParam='name desc' then name desc END
....
else 0
英文:
For avoiding sql injection threats you firstly need to remove appending parameters to your query. When you're appending parameters in your app, the atacker can hijack your sql code (with apostrophes and other means for example)
For example:
If your query is "select * from table where name="+id
The attacker can pass to the field values such as:
'John' or 1=1;
->sees all your records in this table
Or even
'John' or 1=1;Delete all from users;'
-> deleting all entries from users table.
Hijacking queries can be avoided via mechanisms such as input sanitization, input whitelisting/blacklisting(removing unwanted characters from the input/ defining a list of allowed or unnalowed characters). Most modern framerowks such as JDBC/JPA/Hibernate can offer protection from this threat.
With all this stated we should take into consideration the following scenarios:
Considering sql where parameters:
JDBC for example offers prepared statements, where you define a variable in your sql, and the framework replaces it
In your case a JPA implementation(Hibernate) also has mechanisms for avoiding this threat, also via parameterized queries and positional paramaters:
- via native query positional parameters:
Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");
- via named parameters(jplq)
Query q = em.createQuery("SELECT count(*) FROM mytable where username = :username");
q.setParameter("username", "test");
Considering orderBy parameters:
CriteriaQuery
/springSpecifications
CriteriaBuilder cb = this.entityManager
.getCriteriaBuilder();
CriteriaQuery<RESULT> criteria = cb.createQuery(RESULT.class);
Root<RESULT> root = criteria.from(RESULT.class);
return this.entityManager.createQuery(
criteria.select(root).orderBy(cb.asc(root.get("ORDER_BY_FIELD"))))
.getResultList();
More on criteriaQueries usage and config here
- Passing
orderBy
via spring specificSort
parameter built beforehand(using the spring-data library)
Sort sort = Sort.by(Sort.Direction.ASC, "criteria");
em.createQuery(QueryUtils.applySorting(yourSqlQuery_withoutSorting,sort));
- annotate method with @Query with spring-data library:
You can achieve the same result with less boiler plate code(without injecting an entityManager and creating a nativeQuery) by just annotating a method with a @Query
annotation:
@Query("select u from User u where u.lastname like ?1%")
List<User> findByAndSort(String lastname, Sort sort);
Note:
Native vs non-native(jpql):
JOQL queries are independent of the database vendor(MySQL,PostGres,Oracle,DB2), nativeQueries usages are more focused when you need to use a database specific functionalities which differes accross vendors.
For a brief example jpql can not support native [With]
2 clause PLSQL standard functions
Regarding your edit:
You can try to apply the following sql trick for dynamic ordering:
SELECT param1, param2 ...
FROM ...
ORDER BY case when :sortParam='name asc' then name asc END
case when :sortParam='name desc' then name desc END
....
else 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论