using a ParameterExpression versus a variable in JPA Criteria API

Question

When using the JPA Criteria API, what is the advantage of using a ParameterExpression over a variable directly? E.g. when I wish to search for a customer by name in a String variable, I could write something like

private List<Customer> findCustomer(String name) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Customer> criteriaQuery = cb.createQuery(Customer.class);
    Root<Customer> customer = criteriaQuery.from(Customer.class);
    criteriaQuery.select(customer).where(cb.equal(customer.get("name"), name));
    return em.createQuery(criteriaQuery).getResultList();
}

With parameters this becomes:

private List<Customer> findCustomerWithParam(String name) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Customer> criteriaQuery = cb.createQuery(Customer.class);
    Root<Customer> customer = criteriaQuery.from(Customer.class);
    ParameterExpression<String> nameParameter = cb.parameter(String.class, "name");
    criteriaQuery.select(customer).where(cb.equal(customer.get("name"), nameParameter));
    return em.createQuery(criteriaQuery).setParameter("name", name).getResultList();
}

For conciseness I would prefer the first way, especially when the query gets longer with optional parameters. Are there any disadvantages of using parameters like this, like SQL injection?

Exhilarative

I can't speak for JPA in general, but I found out that OpenJPA internally converts a Criteria query to JPQL and this can be printed by using OpenJPA-specific functionality (see openjpa.apache.org/builds/2.1.1/apache-openjpa/docs/…). The first query translates to "SELECT c FROM Customer c WHERE c.name = 'test Customer'". This means it does NOT use a parameter so if this gets further translated to SQL the corresponding prepared statement will NOT use a parameter. The second version translates to the JPQL "SELECT c FROM Customer c WHERE c.name = :name", so I will use parameters. – Exhilarative

After some more testing I found that writing the same query with JPQL and using name "' OR 'x'='x" injects JPQL. When using the criteria API, the generated JPQL that OpenJPA logs looks exactly same. However the actual SQL that is logged by OpenJPA then uses a prepared statement with a parameter of value "' OR 'x'='x" instead of '' in the JPQL case. This means SQL injection does not work here! Unfortunately I have no idea how reliable this is. It seems that this an undocumented feature. – Exhilarative

Tip: I just gave querydsl.com a try and it's syntax is much more concise and readable. It seems to guard against sql injection by using parameters by default. – Exhilarative

That is, if you do not use parameters, SQL injection is possible? – Schweiz

Answer 1

you can use ParameterExpression like this: assume that you have some input filter, an example could be this:

• in your query you have to check the value of a fiscal Code.

let's start: first of all create criteriaQuery and criteriaBuilder and root

        CriteriaBuilder cb = _em.getCriteriaBuilder();
        CriteriaQuery<Tuple> cq = cb.createTupleQuery();
        Root<RootEntity> soggettoRoot = cq.from(RootEntity.class);

1) inizialize a predicateList(use for where clause) and a paramList(use for param)

Map<ParameterExpression,String> paramList = new HashMap();
List<Predicate> predicateList = new ArrayList<>();

2 )check if the input is null and create predicateList and param

if( input.getFilterCF() != null){
            //create ParameterExpression
            ParameterExpression<String> cf = cb.parameter(String.class);


           //if like clause
            predicateList.add(cb.like(root.<String>get("cf"), cf));
            paramList.put(cf , input.getFilterCF() + "%");

           //if equals clause
           //predicateList.add(cb.equal(root.get("cf"), cf));   
           //paramList.put(cf,input.getFilterCF()());
        }

3) create the where clause

 cq.where(cb.and(predicateList.toArray(new   Predicate[predicateList.size()])));
TypedQuery<Tuple> q = _em.createQuery(cq);

4) set param value

        for(Map.Entry<ParameterExpression,String> entry : paramList.entrySet())
        {
            q.setParameter(entry.getKey(), entry.getValue());
        }

Imprinting

Will SQL injection be possible if parameters are not used? – Schweiz

Answer 2

When using a parameter, likely (dependent on JPA implementation, datastore in use, and JDBC driver) the SQL will be optimised to a JDBC parameter so if you execute the same thing with a different value of the parameter it uses the same JDBC statement.

SQL injection is always down to the developer as to whether they validate some user input that is being used as a parameter.

Caceres

That's a good thing to know. I prefer simpler code above optimized code until it is identified as a problem. The problem I have with using parameters for optional criteria in the where clause is that you need to have repeated code like "if (optionalParameter != null)" to declare and set the parameter. Your second answer confuses me. I always thought that (up to possible implementation bugs) parameters were guaranteed to not suffer from SQL injection attacks and I was wondering if my simpler first way can suffer from SQL injection. I am using OpenJPA by the way. – Exhilarative