Fix SQL Injection (Legacy & Modern) in Javalin

SQLi in Javalin is a classic 'dev-shot-self-in-foot' scenario. Because Javalin is unopinionated and lightweight, developers often revert to raw JDBC string splicing to 'save time.' If you aren't parameterizing your queries, you're handing the keys to your database to any script kiddie with a single quote. Whether you're using legacy JDBC or modern fluent APIs, the rule remains: Never trust user-supplied input from ctx.pathParam() or ctx.queryParam().

The Vulnerable Pattern

app.get("/users/{id}", ctx -> {
    String id = ctx.pathParam("id");
    Connection conn = dataSource.getConnection();
    Statement stmt = conn.createStatement();
    // CRITICAL: String concatenation creates an injectable sink
    String sql = "SELECT * FROM accounts WHERE id = '" + id + "'";
    ResultSet rs = stmt.executeQuery(sql);
    // Payload example: /users/1' OR '1'='1
});

The Secure Implementation

The vulnerability exists because the SQL engine cannot distinguish between the developer's intended query logic and the malicious data injected via string concatenation. By using 'PreparedStatements' or modern wrappers like JDBI/JOOQ, the query structure is pre-compiled on the database server. User input is then sent as a separate parameter block. The database engine treats the input as a literal value (a string, integer, etc.) rather than executable SQL commands, effectively neutralizing payloads like ' OR 1=1 --. In the modern stack, JDBI's named parameters (e.g., :id) are the gold standard for preventing injection while maintaining readable code.

// Legacy approach: PreparedStatements
app.get("/users/{id}", ctx -> {
    String id = ctx.pathParam("id");
    try (Connection conn = dataSource.getConnection();
         PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM accounts WHERE id = ?")) {
        pstmt.setString(1, id); // Input is treated strictly as data, not code
        ResultSet rs = pstmt.executeQuery();
        // ... process results
    }
});
// Modern approach: JDBI (Fluent API)
app.get(“/v2/users/{id}”, ctx -> {
String id = ctx.pathParam(“id”);
User user = jdbi.withHandle(handle ->
handle.createQuery(“SELECT * FROM accounts WHERE id = :id”)
.bind(“id”, id) // Named parameter binding
.mapToBean(User.class)
.first()
);
ctx.json(user);
});