JDBI How can I dynamically create a WHERE clause without SQL Injection?

Question

I want to dynamically filter a JDBI request.

The list of parameters is passed from the user interface via REST, for example.

http://localhost/things?foo=bar&baz=taz http://localhost/things?foo=buz 

What (awkwardly) built (Jersey @Context UriInfo :: getQueryParameters → StringBuilder) is something like this:

 WHERE foo=bar AND baz=taz 

And passed to JDBI, which looks like this:

 @UseStringTemplate3StatementLocator public interface ThingDAO { @SqlQuery("SELECT * FROM things <where>) List<Thing> findThingsWhere(@Define("where") String where); } 

As far as I understand, the current implementation is vulnerable to SQL injection. I can obviously sanitize column names, but not values. 1

To do this, there must be a more elegant and reliable way of SQL Injection.

Answer 1

Inspired by Jean-Bernard, I came up with this:

 public class WhereClause { public HashMap<String, String> queryValues; // [<"foo","bar">, <"baz","taz">] public String preparedString; // "WHERE foo=:foo AND bar=:baz" } 

What is connected with the custom BindWhereClause middleware:

 @BindingAnnotation(BindWhereClause.WhereClauseBinderFactory.class) @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.PARAMETER}) public @interface BindWhereClause { class WhereClauseBinderFactory implements BinderFactory { public Binder build(Annotation annotation) { return new Binder<BindWhereClause, WhereClause>() { public void bind(SQLStatement q, BindWhereClause bind, WhereClause clause) { clause.queryValues .keySet() .forEach(s -> q.bind(s, clause.queryValues.get(s))); } }; } } } 

And the combination of @Define and @Bind :

 @UseStringTemplate3StatementLocator public interface ThingDAO { @SqlQuery("SELECT * FROM things <where>) List<Thing> findThingsWhere(@Define("where") String where, @BindWhereClause() WhereClause whereClause); } 

This should be proof of injection. (this is?)

Answer 2

Use a parameterized query. Here is the jdbi page for them.
Parameterized queries are a way to prevent SQL injection in most settings.

You can dynamically create a where statement, but leave parameter names instead of values, they will be connected later in a safe way.

You will probably be interested in this bit specifically, since your parameters are dynamic:

 @BindingAnnotation(BindSomething.SomethingBinderFactory.class) @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.PARAMETER}) public @interface BindSomething { public static class SomethingBinderFactory implements BinderFactory { public Binder build(Annotation annotation) { return new Binder<BindSomething, Something>() { public void bind(SQLStatement q, BindSomething bind, Something arg) { q.bind("ident", arg.getId()); q.bind("nom", arg.getName()); } }; } } } 

I never used jdbi, so I'm not 100% sure what you need to do, but it looks like the q.bind (...) method is exactly what you want.