CODEWITHSUNDEEP
javarestdropwizardjdbi
unSinn
unSinn

Reputation: 351

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.

Upvotes: 10

Views: 9375

Answers (4)

Charlie Reitzel
Charlie Reitzel

Reputation: 925

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();
}

Upvotes: -1

Andres Felipe
Andres Felipe

Reputation: 4330

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();
    }
}

Upvotes: 1

unSinn
unSinn

Reputation: 351

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

Upvotes: 8

Jean-Bernard Pellerin
Jean-Bernard Pellerin

Reputation: 12670

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.

Upvotes: 1

Related Questions