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

Question

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.

Porush

I don't think so, he has list of parameters of type Long (which shouldn't be able to be used for injection wizardry ;) ) and @BindIn seems to use PreparedStatements which @Define (as far as I understood) does not. – Porush

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

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?)

Porush

Where are your queryValues keys coming from? If from the client, then you are still vulnerable to injection. e.g. "foo; drop table students; --". Also, have a look at the @BindIn annotation in JDBI--it combines both the define and the bindings into a single annotation. It might simplify your usage. – Nicolnicola

is the preparedString never used anywhere here? How would you call the "findThingsWhere" method. how to supply params here ? – Puppis

preparedString = "WHERE foo=:foo AND bar=:baz" should be passed as where in findThingsWhere method – Healthful

Does anyone have a JDBI3 version of this? – Rooster

Answer 2

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

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

You would 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've never used jdbi so I'm not 100% sure what you'd have to do, but it looks like the q.bind(...) method is exactly what you want.

Typeface

Answer 3

I know this is old but here is my solutions with JDBI3 version and DAO class.

public interface MyEntityDAO extends SqlObject {

    default List<MyEntity> searchEntitiesWithDynamicWhere(Map<String, Object> queryParams) {
        String whereClause = buildWhereClause(queryParams);
        return searchEntities(queryParams, whereClause);
    }
    
    @SqlQuery("SELECT * FROM my_table <whereClause>")
    List<MyEntity> searchEntities(@BindMap Map<String, Object> queryParams, @Define("whereClause") String whereClause);

    default String buildWhereClause(Map<String, Object> queryParams) {
        StringBuilder whereClause = new StringBuilder();

        if (!queryParams.isEmpty()) {
            whereClause.append(" WHERE ");

            for (Map.Entry<String, Object> entry : queryParams.entrySet()) {
                if (whereClause.length() > " WHERE ".length()) {
                    whereClause.append(" AND ");
                }

                whereClause.append(entry.getKey()).append(" = :").append(entry.getKey());
            }
        }

        return whereClause.toString();
    }
}

Missile

Answer 4

For JDBI 3, things look much simpler: See: Jdbi 3 Developer Guide, 6.4 Binding Arguments

Generate dynamic SQL query using bind variable references to avoid SQL injection vulnerability.

This example is not 100% realistic, because you would likely treat individual fields differently per data types. Also, modifying the input map is a bit dicey (side-effects == bad). But it is included here to demonstrate binding variables for paging parameters (OFFSET/LIMIT).

Caveat: still need to test this!

public List<SomeThing> getSomeThings(Map<String, String> filter, int start, int limit) throws SQLException {
    try ( Handle handle = jdbi.open() ) {
        return handle.createQuery(buildSql(filter, start, limit))
                     .bindMap(filter) // bind by name to keys used to generate SQL
                     .map(new SomeThingRowMapper()).list();
    }
}

static String buildSql( Map<String, String> filter, int start, int limit ) {
    StringBuilder sql = new StringBuilder(256);
    sql.append("SELECT * FROM SOME_TABLE");
    String conj = " WHERE ";
    for ( String key : filter.keySet() ) {
        sql.append(conj).append(key).append(" = :").append(key);
        conj = " AND ";
    }
    sql.append(" ORDER BY SOME_FIELD DESC");
    if ( start >= 0 ) {
        sql.append(" OFFSET :start");
        filter.put("start", ""+start);  // Needs to be in map for binding
    }
    if ( limit >= 0 ) {
        sql.append(" LIMIT :limit");
        filter.put("limit", ""+limit); // Needs to be in map for binding
    }
    return sql.toString();
}

Jail