javarestdropwizardjdbi

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


I want to dynamically filter a JDBI query.

The a list of parameters is passed from the UI via REST e.g.

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

Which is (clumsily) built (Jersey @Context UriInfo::getQueryParameters -> StringBuilder) to 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 the column names but not the values. 1

There must be a more elegant and SQL Injection proof way of doing this.


Solution

  • 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"
    }
    

    Which is bound via a custom Binder BindWhereClause:

    @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 a 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 injection proof. (is it?)