[Respot]Preventing injection in jpa query language - sample app and project

SQL and QL injection can be effectively prevented with the use of JPA Named Queries. In the contrary to the CMP 2.X spec, JPA QL are in general more flexible and can be parameterized.

You can cover almost 90% of all cases with named queries. However, named queries only works in case the structure of the query is stable, and the parameters vary.

Sometimes more flexibility is needed. Building the queries with Strings has several drawbacks:

Lack or IDE support
Syntax is evaluated at runtime. (affects performance and stability)
QL/SQL injection is possible.
With a little “hack” and builder pattern, it is possible to use almost the old syntax more conveniently.

Instead of writing a something like this:

1
String expected = "SELECT e FROM Customer e WHERE e.name = :name";

You could chain methods, which looks like this:

1
EntityQuery query = new EntityQuery.SELECT().ENTITY().FROM(Customer.class).WHERE().attribute("name").build();

A static inner class with the name SELECT implements the builder pattern and takes the responsibility for building the queries:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public class EntityQuery {

private String query;
//attribute declaration
public static class SELECT<T>{

public SELECT(){
this.sqlQuery = new StringBuilder();
this.sqlQuery.append("SELECT");
}

public SELECT column(String name){
if(!multipleColumns) {
multipleColumns = true;
}else{
this.sqlQuery.append(SEPARATOR);
}
this.sqlQuery.append(name);
return this;
}

public SELECT FROM(Class entity){
this.sqlQuery.append(BLANK).append(FROM).append(BLANK);
}
}
}

The Entity Query Builder, as well as the unit tests are available from http://qlb.dev.java.net. I’m working now on the EntityManager integration. First samples should be available in few days.