SQL Injection and Prepared Statements in Java

1. Overview

SQL injection is a top security vulnerability in the OWASP list, stemming from manually concatenating SQL strings with user input. This article explains the attack mechanism, shows why PreparedStatement blocks it, and guides you through applying it to JDBC, JPA/Hibernate, and MyBatis. After reading, you’ll write secure queries and know how to write tests to catch vulnerabilities early.

2. How SQL Injection Works

The error occurs when you directly splice input values into an SQL string, rather than parameterizing them.

// DON'T DO IT THIS WAY
public Customer findByUsername(String username) throws SQLException {
    String sql = "SELECT * FROM customers WHERE username = '" + username + "'";
    try (Statement stmt = connection.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        return rs.next() ? mapRow(rs) : null;
    }
}

If username is a valid value like "minh", the generated SQL is:

SELECT * FROM customers WHERE username = 'minh'

But if an attacker passes username = "' OR '1'='1", the SQL becomes:

SELECT * FROM customers WHERE username = '' OR '1'='1'

The condition '1'='1' is always true, so the query returns all rows from the customers table — completely bypassing authentication logic. Even worse, an attacker could append ; DROP TABLE customers; -- to delete data, or use UNION SELECT to read other tables (like a users table with password hashes).

Note: Input doesn’t only come from HTML forms. HTTP headers, cookies, query parameters, and data from file uploads are all potential attack vectors.

3. How Prepared Statement Fixes the Problem

3.1. Parameterization Mechanism

PreparedStatement separates SQL structure (sent first, parsed and compiled by the DB into an execution plan) from parameter values (sent later, treated as pure data only). Because of this, attacker input is never interpreted as SQL syntax by the database.

public Customer findByUsername(String username) throws SQLException {
    String sql = "SELECT * FROM customers WHERE username = ?";
    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
        stmt.setString(1, username);
        try (ResultSet rs = stmt.executeQuery()) {
            return rs.next() ? mapRow(rs) : null;
        }
    }
}

• The ? is a placeholder; the DB pre-compiles the statement with this placeholder.
• stmt.setString(1, username) binds the value to the first parameter (1-indexed, not 0).
• Even if username contains ' OR '1'='1, the DB treats it as a single literal string to match, not SQL syntax. The query returns empty because no user has that odd name.

3.2. Named Parameters with Spring JdbcTemplate

If you’re using Spring, NamedParameterJdbcTemplate makes code more readable than numbered ? placeholders.

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
</dependency>

@Repository
public class CustomerRepository {

    private final NamedParameterJdbcTemplate jdbc;

    public CustomerRepository(NamedParameterJdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    public Optional<Customer> findByUsername(String username) {
        String sql = "SELECT id, username, email FROM customers WHERE username = :username";
        Map<String, Object> params = Map.of("username", username);
        List<Customer> result = jdbc.query(sql, params, this::mapRow);
        return result.stream().findFirst();
    }

    private Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Customer(rs.getLong("id"), rs.getString("username"), rs.getString("email"));
    }
}

:username is a named placeholder; Spring internally converts it to ? and binds the value safely. This approach avoids mixing up parameter order when SQL has multiple conditions.

4. Applying JPA/Hibernate

JPA by default generates SQL using prepared statements, but you can still shoot yourself in the foot if you use the API incorrectly.

// SAFE — JPQL with named parameters
public List<Order> findOrdersByStatus(String status) {
    return entityManager.createQuery(
            "SELECT o FROM Order o WHERE o.status = :status", Order.class)
        .setParameter("status", status)
        .getResultList();
}

// DANGEROUS — concatenating strings into JPQL
public List<Order> findOrdersByStatusUnsafe(String status) {
    String jpql = "SELECT o FROM Order o WHERE o.status = '" + status + "'";
    return entityManager.createQuery(jpql, Order.class).getResultList();
}

• setParameter("status", status) in the safe example causes Hibernate to translate JPQL to SQL with ?, then bind the value via PreparedStatement — attackers can’t manipulate the statement structure.
• In the dangerous example, Hibernate still parses JPQL, but the JPQL string is already “dirty” before parsing, so attackers can inject JPQL injection (similar to SQL injection, just at a different syntax level).

With Spring Data JPA, @Query also needs parameters instead of string concatenation:

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o WHERE o.customerEmail = :email AND o.status = :status")
    List<Order> findByEmailAndStatus(@Param("email") String email, @Param("status") String status);
}

5. When Parameterization Isn’t Possible: Table Names, Column Names

PreparedStatement can only parameterize values, not identifiers (table names, column names, ORDER BY clauses). This is a situation many developers overlook.

// Dynamic sorting by a column chosen by the user
public List<Order> findAllSorted(String sortColumn) {
    Set<String> allowedColumns = Set.of("id", "createdAt", "totalAmount");
    if (!allowedColumns.contains(sortColumn)) {
        throw new IllegalArgumentException("Invalid sort column: " + sortColumn);
    }
    String sql = "SELECT * FROM orders ORDER BY " + sortColumn; // safe because whitelisted
    // ... execute with PreparedStatement as usual
    return jdbc.query(sql, this::mapRow);
}

• Since you can’t write ORDER BY ?, you must whitelist valid values before concatenation.
• Never use blacklisting (blocking keywords like DROP, ;) because attackers always find ways around it via encoding or SQL syntax variations.

6. Writing Tests to Detect Vulnerabilities Early

A simple test helps you confirm an endpoint defends against injection correctly.

@SpringBootTest
@AutoConfigureMockMvc
class CustomerSearchSecurityTest {

    @Autowired
    private MockMvc mockMvc;

    @Test
    void searchByUsername_withInjectionPayload_returnsEmptyNotAllRows() throws Exception {
        String payload = "' OR '1'='1";

        mockMvc.perform(get("/api/customers").param("username", payload))
               .andExpect(status().isOk())
               .andExpect(jsonPath("$").isEmpty());
    }

    @Test
    void sortBy_withInvalidColumn_rejectsRequest() throws Exception {
        mockMvc.perform(get("/api/orders").param("sortBy", "id; DROP TABLE orders"))
               .andExpect(status().isBadRequest());
    }
}

class CustomerRepositoryTest {

    @Test
    void findByUsername_withSqlInjectionPayload_returnsEmptyOptional() {
        CustomerRepository repo = new CustomerRepository(jdbcTemplate);

        Optional<Customer> result = repo.findByUsername("nonexistent' OR '1'='1");

        assertThat(result).isEmpty();
    }
}

• The first test sends a classic injection payload, expecting an empty result (because no username matches that literal string), not all rows.
• The second test checks that the endpoint rejects input outside the whitelisted sort columns — returning 400 Bad Request instead of executing dangerous SQL.
• assertThat(result).isEmpty() uses AssertJ to confirm the Optional is empty, proving the query wasn’t bypassed.

7. Common Mistakes

• Using Statement.executeQuery(String) for dynamic input: works fine for demos but is a real backdoor in production.
• Using String.format to “simplify” SQL: still string concatenation, no different from using +.
• Trusting your ORM 100%: JPQL/HQL/Criteria can still be injected if you concatenate strings before calling createQuery.
• Manual escaping instead of parameterization: writing your own escape function (like replace("'", "\\'")) easily misses edge cases (Unicode encoding, multi-byte characters) and is less trustworthy than the driver’s native mechanism.
• Handling dynamic IN clauses incorrectly: when building WHERE id IN (?, ?, ?) with variable parameter count, you must generate the correct number of ? placeholders matching the list size, not concatenate values directly.

Note: Prepared statements protect against SQL injection but not against injection at other layers like command injection, LDAP injection, or NoSQL injection. Each data layer needs its own parameterization mechanism.

8. When You Don’t Need PreparedStatement

If your SQL is completely static with no dynamic values from user input (e.g., SELECT COUNT(*) FROM customers), using a regular Statement is safe and sometimes slightly faster since it skips parsing/binding. However, in practice, most queries have dynamic conditions, so make it a habit to always use PreparedStatement or equivalent (JPQL with setParameter, MyBatis with #{} instead of ${}) to avoid having to remember which cases are “safe”.

9. Conclusion

SQL injection stems from mixing code and data in a single SQL string. PreparedStatement solves it at the root by separating the two: the statement structure is compiled first, parameter values are only bound later as pure data. Apply this consistently across JDBC, JPQL, and MyBatis. Always whitelist dynamic identifiers like column/table names, and write tests with classic injection payloads to ensure your endpoint never executes SQL controlled by an attacker.