SQL Injection Prevention

SQL injection has been the top of the OWASP list for two decades, not because it is subtle but because one concatenated string in one forgotten query is enough. The defence is structural and cheap; the trick is knowing which patterns bypass it.

How it works#

// NEVER do this.
String sql = "SELECT * FROM users WHERE email = '" + email + "'";

With email set to ' OR '1'='1, the database receives:

SELECT * FROM users WHERE email = '' OR '1'='1'

The condition is always true and the query returns every user. The database is behaving correctly — it was handed a string containing a valid OR clause and had no way to know the author did not intend it.

That ordering is the whole defence. The database plans the query with placeholders, then binds values into the plan. A bound value is data by construction — there is no path by which it becomes an operator or a clause.

What attackers do with it#

Beyond dumping a table, the standard escalations are worth knowing because they shape what you test for.

UNION-based extraction appends a second query whose columns line up with the first, pulling data from a different table into the original result set.

Blind boolean injection works when nothing is displayed. The attacker asks yes/no questions — "is the first character of the admin password hash an a?" — and reads the answer from whether the page renders normally or errors.

Time-based blind injection works even when the response never varies. ' AND SLEEP(5)-- makes the database pause when a condition is true, and the response time carries one bit per request. Slowly, an attacker extracts an entire table through a channel you never considered output.

Second-order injection stores a payload harmlessly and triggers it later, when some other query concatenates the stored value. Input sanitised on the way in is not sanitised on the way out.

The safe patterns#

public interface UserRepository extends JpaRepository<User, Long> {
 
    // Derived query: Spring Data builds a parameterised query. Safe.
    Optional<User> findByEmailIgnoreCase(String email);
 
    // JPQL with named parameters. Safe.
    @Query("select u from User u where u.email = :email and u.active = true")
    Optional<User> findActiveByEmail(@Param("email") String email);
 
    // Native SQL is fine — what matters is binding, not the dialect.
    @Query(value = "SELECT * FROM users WHERE email = :email", nativeQuery = true)
    Optional<User> findNative(@Param("email") String email);
}
 
@Repository
public class ReportRepository {
 
    private final NamedParameterJdbcTemplate jdbc;
 
    public List<Row> byStatusAndRange(String status, LocalDate from, LocalDate to) {
        String sql = """
            SELECT o.id, o.reference, o.total
            FROM orders o
            WHERE o.status = :status
              AND o.created_at BETWEEN :from AND :to
            """;
        var params = new MapSqlParameterSource()
                .addValue("status", status)
                .addValue("from", from)
                .addValue("to", to);
        return jdbc.query(sql, params, rowMapper);
    }
}

For dynamic filters, the Criteria API builds the query as a structure rather than a string, so injection is impossible by construction:

public static Specification<Order> matching(OrderFilter filter) {
    return (root, query, cb) -> {
        var predicates = new ArrayList<Predicate>();
        if (filter.status() != null) {
            predicates.add(cb.equal(root.get("status"), filter.status()));
        }
        if (filter.customerReference() != null) {
            // Even LIKE patterns are bound as values, not spliced into SQL.
            predicates.add(cb.like(root.get("customerReference"),
                                   "%" + filter.customerReference() + "%"));
        }
        return cb.and(predicates.toArray(Predicate[]::new));
    };
}

The patterns that still bite#

// 1. Concatenated JPQL. The ORM does not save you here.
@Query("select u from User u where u.role = '" + "#{#role}" + "'")   // injectable
 
// 2. Dynamic ORDER BY. Identifiers cannot be bound as parameters.
String sql = "SELECT * FROM orders ORDER BY " + sortColumn;          // injectable
 
// 3. Dynamic IN list built by concatenation.
String sql = "SELECT * FROM orders WHERE id IN (" + String.join(",", ids) + ")";
 
// 4. A LIKE pattern built by concatenation into the SQL rather than the value.
String sql = "SELECT * FROM users WHERE name LIKE '%" + term + "%'";

The ORDER BY case is the one that catches careful teams, because there is genuinely no way to bind it. SQL parameters bind values, and a column name is an identifier. The only safe approach is an allowlist:

private static final Map<String, String> SORTABLE = Map.of(
        "createdAt", "o.created_at",
        "total",     "o.total",
        "status",    "o.status",
        "reference", "o.reference");
 
private String orderByClause(String requested, String direction) {
    // Look up a literal WE control. The user's string is a map key, never SQL.
    String column = SORTABLE.get(requested);
    if (column == null) throw new BadRequestException("cannot sort by " + requested);
    String dir = "desc".equalsIgnoreCase(direction) ? "DESC" : "ASC";
    return " ORDER BY " + column + " " + dir;
}

Note the map returns a value you wrote, not a sanitised version of the input. Validating that the input "looks safe" and then interpolating it is a weaker pattern that has failed many times; mapping to a known literal cannot fail.

For dynamic IN lists, NamedParameterJdbcTemplate expands a collection parameter correctly:

jdbc.query("SELECT * FROM orders WHERE id IN (:ids)",
           new MapSqlParameterSource("ids", ids), rowMapper);

Defence in depth#

Parameterisation is the fix. These reduce the impact if one query slips through.

Least privilege on the database user. The application account should not own the schema, should not have DROP, and in many designs should not have DELETE. An injection in a read endpoint then cannot escalate to destruction. Separate read-only and read-write accounts where the architecture allows.

Do not leak database errors. A stack trace containing a SQL fragment and a table name is reconnaissance. Map DataAccessException to a generic 500 and log the detail against a correlation id.

Validate input for shape. An order id that must match [A-Z]{3}-\d{4} should be rejected before it reaches any query. This is not the primary defence, but it removes a large class of payloads cheaply.

Rate limit and alert. Automated injection tools send hundreds of malformed requests. A spike in 400s from one client, or a sudden appearance of SQL keywords in parameters, is worth an alert.

Testing for it#

@ParameterizedTest
@ValueSource(strings = {
        "' OR '1'='1",
        "'; DROP TABLE users; --",
        "' UNION SELECT username, password FROM users--",
        "' AND SLEEP(5)--",
        "admin'--"
})
void injectionPayloadsAreTreatedAsData(String payload) {
    // The payload must return no rows, not an error and certainly not all rows.
    assertThat(userRepository.findByEmailIgnoreCase(payload)).isEmpty();
    assertThat(userRepository.count()).isEqualTo(3);
}

Add a scanner to CI for coverage a unit test cannot give. sqlmap against a staging instance finds parameters you forgot existed:

sqlmap -u "https://staging.acme.com/api/v1/orders?status=PENDING" \
       --headers="Authorization: Bearer $TOKEN" \
       --batch --level=2 --risk=1

Static analysis catches it earlier still. SpotBugs with the FindSecBugs plugin flags SQL_INJECTION_JDBC and SQL_INJECTION_JPA at build time, which is where you want to find it.

What to take away#

Bind every value; never concatenate. Remember that identifiers cannot be bound, so sorting and dynamic column selection need an allowlist that maps to literals you control. Give the database user the least privilege that works, keep SQL errors out of responses, and add a scanner to CI so a future concatenation fails the build.