Fix SQL injection in Java and Spring Boot

1. Find every JdbcTemplate, JdbcClient, EntityManager or @Query call whose SQL or JPQL is built with +, String.format, formatted() or StringBuilder from request data.
2. Make the SQL a constant with :name (NamedParameterJdbcTemplate, JdbcClient) or ? placeholders and pass the values separately: a MapSqlParameterSource, or the Object... args overloads of JdbcTemplate.
3. For the sort column and direction, look the request value up in a Map of fixed SQL fragments; throw IllegalArgumentException for anything else and map it to 400 with an @ExceptionHandler.
4. Add regression tests with this card's payloads (MockMvc against the real repository) next to functional tests for names such as O'Brien and non-ASCII text.
5. Do not return SQLException or DataAccessException messages to clients.

The three implementations share one API and one test suite; only the code below changes. The tests load the one that IMPL names.

@Override
public List<CustomerOrder> ordersForCustomer(String customerName, String status) {
    String sql = "SELECT o.id, o.status, o.total_cents, o.created_at"
            + " FROM orders AS o JOIN customers AS c ON c.id = o.customer_id"
            + " WHERE c.name = '" + customerName + "' AND o.status = '" + status + "'"
            + " ORDER BY o.id";
    return jdbc.query(sql, CUSTOMER_ORDER);
}

@Override
public List<CustomerOrder> ordersForCustomer(String customerName, String status) {
    String sql = """
            SELECT o.id, o.status, o.total_cents, o.created_at
            FROM orders AS o
            JOIN customers AS c ON c.id = o.customer_id
            WHERE c.name = :name AND o.status = :status
            ORDER BY o.id""";
    var params = new MapSqlParameterSource()
            .addValue("name", customerName)
            .addValue("status", status);
    return jdbc.query(sql, params, CUSTOMER_ORDER);
}

// A placeholder can only stand in for a value. Column names and ASC/DESC are
// part of the SQL text, so map the caller's choice onto fixed fragments.
private static final Map<String, String> SORT_COLUMNS = Map.of(
        "created_at", "o.created_at",
        "total_cents", "o.total_cents",
        "customer", "c.name");
private static final Map<String, String> SORT_DIRECTIONS = Map.of(
        "asc", "ASC",
        "desc", "DESC");

@Override
public List<AdminOrder> searchOrders(String status, String sort, String direction) {
    String column = SORT_COLUMNS.get(sort);
    String order = SORT_DIRECTIONS.get(direction);
    if (column == null || order == null) {
        throw new IllegalArgumentException("unsupported sort column or direction");
    }
    String sql = """
            SELECT o.id, c.name AS customer, o.status, o.total_cents, o.created_at
            FROM orders AS o
            JOIN customers AS c ON c.id = o.customer_id
            WHERE o.status = :status
            ORDER BY %s %s, o.id""".formatted(column, order);
    return jdbc.query(sql, new MapSqlParameterSource("status", status), ADMIN_ORDER);
}

private static String escapeSqlString(String value) {
    return value.replace("\\", "\\\\").replace("'", "\\'");
}

@Override
public List<CustomerOrder> ordersForCustomer(String customerName, String status) {
    String sql = "SELECT o.id, o.status, o.total_cents, o.created_at"
            + " FROM orders AS o JOIN customers AS c ON c.id = o.customer_id"
            + " WHERE c.name = '" + escapeSqlString(customerName) + "'"
            + " AND o.status = '" + escapeSqlString(status) + "'"
            + " ORDER BY o.id";
    return jdbc.query(sql, CUSTOMER_ORDER);
}

The exploit tests send the attack and check that it fails; functional tests next to them keep ordinary requests working, so a fix that rejects everything fails too.

// Exploit: each payload must stay data and leave the query's meaning unchanged.

@Test
void exploitTautologyDoesNotReturnOtherCustomersOrders() throws Exception {
    assertThat(orderIds("/orders", "customer", "' OR '1'='1", "status", "shipped")).isEmpty();
}

@Test
void exploitUnionCannotReadAnotherCustomersOrders() throws Exception {
    String customer = "' UNION SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 2 --";
    assertThat(orderIds("/orders", "customer", customer, "status", "shipped")).isEmpty();
}

@Test
void exploitCommentCannotCutOffTheStatusFilter() throws Exception {
    assertThat(orderIds("/orders", "customer", "Alice Martin'--", "status", "shipped")).isEmpty();
}

@Test
void exploitSortExpressionIsRejectedNotUsedAsAnOracle() throws Exception {
    // Row order would reveal whether each customer's hidden email starts with "b".
    String sort = "CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END";
    request("/admin/orders", "status", "shipped", "sort", sort, "dir", "asc")
            .andExpect(status().isBadRequest());
}

@Test
void exploitDirectionCannotAddASecondSortExpression() throws Exception {
    String dir = "ASC, CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END";
    request("/admin/orders", "status", "shipped", "sort", "created_at", "dir", dir)
            .andExpect(status().isBadRequest());
}

• JdbcTemplate.query(String sql, RowMapper) runs the text as static SQL; values are bound only by the overloads that also take bind arguments (Object... args) or a PreparedStatementSetter. Concatenating first and calling the two-argument form is the classic bug.
• JPA and Hibernate: createQuery("... '" + name + "'") is JPQL/HQL injection. Use :name with setParameter, or @Query with :name and @Param in Spring Data.
• Spring Data JPA checks Sort properties against the domain model and rejects function calls unless you wrap them in JpaSort.unsafe(...). Never pass request data to JpaSort.unsafe.
• IN lists: NamedParameterJdbcTemplate expands a java.util.List bound to IN (:ids) into one placeholder per element; plain JdbcTemplate with ? does not. Large lists hit database limits.
• LIKE: a bound % still matches every row. Escape \, % and _ in the value and add ESCAPE '\'; Spring Data JPA documents an escape(...) helper for this.
• ORDER BY ? binds a constant, so SQLite silently skips the sort. Identifiers need the allowlist.
• Allowlist only columns the caller may see: sorting by a hidden column such as email still leaks its order.
• Spring Boot 4 / Spring Framework 7 test support needs JUnit Jupiter 6 (the SpringExtension requires 6.0 or later), so this card uses JUnit 6.0.3 rather than JUnit 5.

1. Run the tests in all three modes: IMPL=fixed must pass; IMPL=vulnerable and IMPL=wrong must fail (node ../../run-all.mjs java-spring does this for you).
2. Run semgrep --config r/java.spring.security.audit.spring-sqli.spring-sqli src/main/java; expect findings only in VulnerableOrderRepository and WrongOrderRepository.
3. Against your own endpoint, send customer=' OR '1'='1 and expect an empty result, and send a CASE expression as the sort and expect 400.

What each rule reported when it was run on this card's code, or what its source shows it covers where it was not run. A rule that exists for the weakness does not always catch it.

Download the project, install its dependencies once (this step needs the network), then run the tests against each implementation from the sql-injection/java-springfolder. Maven runs with JDK 21 (set JAVA_HOME).

mvn -B -q test

# Expected: every test passes
IMPL=fixed mvn -B -o -q test

# Expected: an exploit test fails
IMPL=vulnerable mvn -B -o -q test

# Expected: a test fails
IMPL=wrong mvn -B -o -q test

Each run writes a JUnit report to target/surefire-reports/TEST-*.xml. From the top folder of the download, node run-all.mjs sql-injection/java-spring runs all three modes and checks the expected results.

Download the project ZIP, 22 KB

Verified

Machine

macOS 27.0 (Darwin 27.0.0, arm64)

Toolchain

• java OpenJDK 21.0.12.1 (Homebrew)
• maven 3.9.16
• springBoot 4.1.1
• tomcat 11.0.26
• jackson 3.1.7
• junitJupiter 6.0.3

Matrix

fixed: 11 of 11 tests pass; vulnerable: 7 of 11 tests fail (5 exploit tests); wrong: 6 of 11 tests fail (4 exploit tests)

Test results

fixed 11/11 pass; vulnerable 7/11 fail (5 exploit tests); wrong 6/11 fail (4 exploit tests), offline after install

Versions and results hold for the toolchain above. The card's tests are the check: run them with your own versions before relying on the fix.

References

1. OWASP SQL Injection Prevention Cheat Sheet
2. OWASP Query Parameterization Cheat Sheet
3. Spring Framework: Using NamedParameterJdbcTemplate
4. Spring Framework: Passing in lists of values for IN clause
5. Spring Framework API: JdbcTemplate
6. Spring Data JPA: Using Sort (JpaSort.unsafe)
7. Spring Framework: SpringExtension for JUnit Jupiter
8. Spring Boot 4.0 Migration Guide (starters and test starters)
9. xerial sqlite-jdbc
10. SQLite: SQL language expressions (string literals, parameters, LIKE ... ESCAPE)

Attack patterns:

• CAPEC-66 at MITRE