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:

  1. Lack or IDE support
  2. Syntax is evaluated at runtime. (affects performance and stability)
  3. 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: 

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


You could chain methods, which looks like this:


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:

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.


NEW MUC Airport Workshop: Migrating Java Client (Swing / Java FX) to Web Standards

Airport MUC workshops: Web (SPA, PWAs, Offline, Desktop, Mobile) Applications Essentials and Effective Web Applications. No migrations. #usetheplatform

Podcast: airhacks.fm and newsletter: airhacks.news

A book about rethinking Java EE Patterns

Comments:

Hi,
sounds good !!
Thanks.

could you plz point to the source files. I can't find anything here :
http://qlb.dev.java.net

Thanks

Posted by TechieExchange on November 06, 2007 at 01:34 PM CET #

Nice, we are nearly as good as .net-LinQ - except for the String "name" :-(
We could omit those Strings, if the Java-Language would give us the ability to access reflection fields. Until now we can only access classes with the ".class"-operator (e.g. Customer.class). If we would have the same for fields (.field-operator), we could write something like this: Customer.field.name (which could be checked by the compiler)
Is there a JSR for such a feature?

There are some more use cases for this.
E.g.: A comfortable Swing-GUI should mark fields that are affected by a failing validation. If the validation was on the server side operating on Entities, the message returned to the client must contain the names of the affected fields. So the Entities need a kind of Meta-Model for all their fields.

Posted by LarsFiedler on June 30, 2008 at 06:28 PM CEST #

The variable SEPARATOR and BLANK , what would this?
Have you full source code?

multipleColumns?

thanks?

Posted by Gustavo on July 10, 2011 at 09:07 AM CEST #

http://qlb.dev.java.net is dead - any new link for the project?

Posted by Name on February 27, 2018 at 02:29 PM CET #

Post a Comment:
  • HTML Syntax: NOT allowed
realworldpatterns.com
Online Workshops
...the last 150 posts
...the last 10 comments
License