Passing entire where clause to native spring jpa @Query

Question

Currently I'm unable to know the fields that will be within the SQL where clause (think adhoc) when creating the native SQL in my spring @Query. Therefore I'm passing in the entire where clause.

If I output the SQL to the console and paste it in to my sql editor I'm able to receive a valid resultset.

SELECT * FROM lorder WHERE order_id = 1196077

Last SQL output to the console was: Hibernate:

/* dynamic native SQL query */ SELECT
    * 
FROM
    lorder 
WHERE
    ?
and the where clause value being passed in to the @query is:
order_id = 1196077

This is what I am currently doing which is not working.

@Query(
    value = "SELECT * FROM lorder WHERE :where",
    nativeQuery = true)
List<OrderEntity> listSelected(@Param("where") String where);

Not sure if passing the entire where clause is possible but I'm expecting a list. However I'm currently getting an empty collection.

I can't understand what you're trying to achieve, and why you're using a native query instead of a JPQL query. If you're trying to execute a dynamically created query, then use the criteria API, or QueryDSL. That's what they're for. If you want to use a single method to execute several different static queries, just don't do that: it makes it impossible to test the method, to reuse it several times, makes the code less clear and the tests more brittle. — JB Nizet, Jul 02 '19 at 18:58
Thanks JB. I changed this to use Query query = entityManager.createQuery("Select...) — shadley, Jul 02 '19 at 20:03
Check out `JpaSpecificationExecutor` and `Specification` from Spring Data JPA. — t4dohx, Jul 03 '19 at 07:42

hc_dev · Answer 1 · 2019-07-02T19:46:52.607

6

Since using the @Query annotation you can only use named parameters (your :where) or ordinal parameters (e.g. ?1, ?2) of a specific Java-type, it's not possible to inject partial SQL-expressions.

However you could use a TypedQuery to add partial SQL to a query:

  public List<OrderEntity> getOrdersUsingWhereClause(EntityManager em, String whereClause) {
    TypedQuery<OrderEntity> query = em.createQuery(
        "SELECT o FROM lorders o WHERE " + whereClause,
        OrderEntity.class);
    return query.getResultList();
  }

See https://www.objectdb.com/java/jpa/query/parameter#Parameters_vs.Literals

edited Jul 02 '19 at 19:46

answered Jul 02 '19 at 19:33

hc_dev

8,389
1
26
38

1

I believe the requirement itself hence the answer are vulnerable to sql injection, unless you handle such attack in outer layers – Jayr Jul 02 '19 at 20:24
@Jayr Totally agree. That's the trade-off OP must be aware of: __security__ (threatened by potential sql-injection) VS __flexibility__ (gained by simply injecting user-defined sql-partials). Go ahead and comment the original post by asking for these further requirements worth considering. – hc_dev Jul 02 '19 at 20:40
My version passes the entity as a string and uses reflection to get the entity class. There is a limitation however. A whereClause with an embedded semi-colon will fail. For example, whereClause = "description like '%;%'" – Rick Apr 14 '23 at 23:07

score 0 · Answer 2 · answered Jul 02 '19 at 19:55

0

The JPA's criteria query gives you more power to control where clause along with many other supported functionalities.

List of functions : https://en.wikibooks.org/wiki/Java_Persistence/Criteria

Criteria Queries : https://www.baeldung.com/hibernate-criteria-queries

Learn more about criteria query and criteria builder here

answered Jul 02 '19 at 19:55

Avinash Gupta

208
5
18

1

Using _Criteria Queries_ is a viable approach which has a **security-benefit** (prohibit SQL injection) but __restricts flexibility__ at runtime by allowing only WHERE-conditions coded at design-time. Super that you provided these links to learn. Proceed and add some code trying to solve the concrete problem asked :-) – hc_dev Jul 02 '19 at 21:03

Passing entire where clause to native spring jpa @Query

2 Answers2