Fix SQL Injection (Legacy & Modern) in Dropwizard

Dropwizard typically wraps JDBI or Hibernate for data access. SQL injection in these environments occurs when developers bypass the abstraction layer to use raw string concatenation for 'dynamic' queries. In a legacy context, this often manifests as manual JDBC calls; in modern Dropwizard, it is usually a misuse of JDBI @SqlQuery or handle.createQuery() templates. The objective is to force the database driver to treat user input as data, not executable logic.

The Vulnerable Pattern

// LEGACY: Raw JDBC concatenation inside a Resource
String query = "SELECT * FROM accounts WHERE owner = '" + ownerName + "'";
ResultSet rs = connection.createStatement().executeQuery(query);
// MODERN: JDBI String Template misuse
@SqlQuery(“SELECT * FROM users WHERE id = ” + id)
User findById(@Bind(“id”) String id);

The Secure Implementation

Stop building queries with string builders. Dropwizard's JDBI integration is designed to use Prepared Statements by default. Use the ':variable' syntax in your SQL strings and the @Bind annotation in your DAOs. This ensures the JDBC driver handles the escaping and type-checking at the protocol level. If you must use dynamic table names or column names (which cannot be parameterized), implement a strict allow-list (whitelist) to validate the identifier against a set of known-good strings before it touches the query template.

// MODERN: JDBI Named Parameters (Prepared Statements)
@SqlQuery("SELECT * FROM users WHERE id = :id")
User findById(@Bind("id") String id);
// FLUENT API: Handle Binding
public List search(String name) {
return jdbi.withHandle(handle ->
handle.createQuery(“SELECT * FROM users WHERE name LIKE :name”)
.bind(“name”, ”%” + name + ”%”)
.mapToBean(User.class)
.list());
}