Search code examples
javajpajpql

JPA query - how to mix arbitrary query strings and CriteriaBuilder


I need to perform a query of the form

SELECT dontcare FROM Bar b WHERE x = y AND expr
  • x is the name of any field in Bar, y is a specific value. These come via a Servlet and cannot be trusted.
  • expr is a project specific filter to change the scope of the query involving other fields - this is trusted and comes from a configuration file and could be quite complex.

An unsafe approach would be

String qStr = "SELECT foo FROM Bar b WHERE " + x " = " + y " AND " + expr

However x and y are not trusted source so leaves open to injection attack. I've found CriteriaBuilder, and made a programmatic build of the "x = y" part that works fine, but cannot find how to append the "AND expr".

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaBuilder<Bar> q = cb.createQuery(Foo.class);
Root<Bar> root = q.from(Bar.class);
Predicate p1 = cb.equals(root.get(x), y);
Predicate p2 = <====== help needed here, how can I use expr
q.where(cb.and(p1, p2));
Query query = em.createQuery(q);

Any ideas? I've spent a good amount of time looking through tutorials and the javadocs for Expression, Predicate, Query etc.


Solution

  • With the criteria api you can create conjunctions, which contain a number of predicates that all have to be true.

    If you want OR, you can use disjunctions.

    something like:

    Criteria crit = session.createCriteria(Product.class);
    Conjunction conjunction = Restrictions.conjunction();
    conjunction.add(Restrictions.isNull("location"));
    conjunction.add(Restrictions.eq("location", ""));
    crit.add(conjunction);