避免在Spring JPA中发生SQL注入

huangapple go评论97阅读模式
英文:

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)还通过参数化查询和位置参数提供了避免此威胁的机制:

  1. 通过本地查询位置参数:
Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");
  1. 通过命名参数(JPQL):
Query q = em.createQuery("SELECT count(*) FROM mytable where username = :username");
q.setParameter("username", "test");

考虑 orderBy 参数:

  1. CriteriaQuery/Spring Specifications
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();
  1. 通过预先构建的 Spring 特定的 Sort 参数传递 orderBy(使用 spring-data 库):
Sort sort = Sort.by(Sort.Direction.ASC, "criteria");
em.createQuery(QueryUtils.applySorting(yourSqlQuery_withoutSorting, sort));
  1. 使用带有 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:

  1. via native query positional parameters:
Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");

  1. via named parameters(jplq)
Query q = em.createQuery("SELECT count(*) FROM mytable where username = :username");
q.setParameter("username", "test");

Considering orderBy parameters:

  1. CriteriaQuery/spring Specifications
 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

  1. Passing orderBy via spring specific Sort parameter built beforehand(using the spring-data library)
Sort sort = Sort.by(Sort.Direction.ASC, "criteria");
em.createQuery(QueryUtils.applySorting(yourSqlQuery_withoutSorting,sort));

  1. 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


huangapple
  • 本文由 发表于 2020年9月22日 02:25:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/63997976.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定