FlawOpen

flawopen.com/sql-injection/Java

● CWE-89 · Critical
FlawOpen Security Research

Java SQL Injection: JDBC, JPA, MyBatis

Java SQL injection occurs when input is concatenated into query text. Bind values with parameters; allowlist SQL identifiers such as ORDER BY fields.[1], [2], [3], [7], [4], [6]

💡 Plain English Explainer (ELI5)

Imagine a factory that locks a metal stencil before any customer labels arrive. JDBC PreparedStatement builds that stencil with a ? socket first; setLong(1, userId) then places a value into the socket without changing the instructions. Concatenating text before preparation engraves it into the stencil. JdbcTemplate and JPQL setParameter keep values separate too. MyBatis makes the distinction visible: #{status} is a bound value, while ${status} is pasted SQL text. ORDER BY fields are identifiers, not values, so map them through an allowlist or an approved Sort.by property.[2], [3], [7], [4], [8]

Core Concepts & Subsystem Terms

PreparedStatement
A JDBC statement compiled from SQL with ? markers before any value is supplied. Values go in through typed setters such as setLong(1, id), so they can never alter the query. Using prepareStatement("..." + id) compiles the attacker's text and protects nothing.[2], [1]
JdbcTemplate query(sql, args)
Spring's queryForList(sql, Long.class, status) and update(sql, args...) bind trailing arguments to ? markers through a PreparedStatement. NamedParameterJdbcTemplate does the same with :name markers. Concatenating into the sql argument bypasses all of it.[3], [2]
JPQL / HQL injection
JPA and Hibernate queries are parsed from strings too. createQuery("... = '" + email + "'") lets input change the JPQL, which Hibernate then translates into SQL. Use :email with setParameter, or the Criteria API.[7], [1]
MyBatis #{} vs ${}
In a MyBatis mapper, #{status} becomes a bound ? parameter. ${status} is pasted into the SQL as text. ${} is only safe for values you chose yourself, such as a column from an allowlist.[4]
Spring Data Sort
Spring Data turns Sort.by("email") into ORDER BY against mapped properties and rejects unknown property names, so prefer it to building ORDER BY by hand. Still map request keys through an allowlist so users cannot sort on fields you did not intend to expose.[8]

Framework mappings

Step-by-Step Attack Flow

Step 1

Request value reaches the repository

A Spring @GetMapping handler takes @RequestParam String id and passes it to findEmail(). Because its declared type is String, Spring does not type-convert the value; validate it explicitly before relying on its format.[9]

Step 2

Concatenation builds the SQL

"... WHERE id = " + userId produces one string. With the benign test value 0 OR 1=1 it reads WHERE id = 0 OR 1=1.[1], [2], [6]

Step 3

The driver compiles the attacker's text

createStatement().executeQuery(sql) sends the whole string for parsing. There are no parameters, so the database treats OR 1=1 as part of the WHERE clause.[2], [1]

Step 4

Every row comes back

The condition is true for every row, so the lookup returns the table. The same happens through JPQL: Hibernate faithfully translates the altered query into SQL.[7], [1]

Source Code: Flaw vs. Secure Implementation

UNPATCHED FLAW
// UserRepository.java: every query below concatenates request data into the query text
public class UserRepository {
    private final DataSource dataSource;
    private final JdbcTemplate jdbc;
    private final EntityManager em;

    public UserRepository(DataSource dataSource, JdbcTemplate jdbc, EntityManager em) {
        this.dataSource = dataSource;
        this.jdbc = jdbc;
        this.em = em;
    }

    // JDBC: Statement parses the concatenated string, value and all
    public String findEmail(String userId) throws SQLException {
        try (Connection conn = dataSource.getConnection();
             Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT email FROM users WHERE id = " + userId)) {
            return rs.next() ? rs.getString(1) : null;
        }
    }

    // JdbcTemplate: concatenation happens before Spring ever sees the SQL
    public List<Long> findOrderIds(String status) {
        return jdbc.queryForList("SELECT id FROM orders WHERE status = '" + status + "'", Long.class);
    }

    // JPA: the JPQL itself is built from input, then translated to SQL
    public List<User> findByEmail(String email) {
        return em.createQuery("SELECT u FROM User u WHERE u.email = '" + email + "'", User.class)
                 .getResultList();
    }

    // ORDER BY field taken straight from the request
    public List<User> listUsers(String sortField) {
        return em.createQuery("SELECT u FROM User u ORDER BY u." + sortField, User.class)
                 .getResultList();
    }
}
HARDENED SECURE PATCH
// UserRepository.java: values are bound parameters, sort fields come from an allowlist
public class UserRepository {
    private static final Map<String, String> SORTED_QUERIES = Map.of(
            "id", "SELECT u FROM User u ORDER BY u.id",
            "email", "SELECT u FROM User u ORDER BY u.email",
            "created", "SELECT u FROM User u ORDER BY u.createdAt");

    private final DataSource dataSource;
    private final JdbcTemplate jdbc;
    private final EntityManager em;

    public UserRepository(DataSource dataSource, JdbcTemplate jdbc, EntityManager em) {
        this.dataSource = dataSource;
        this.jdbc = jdbc;
        this.em = em;
    }

    // JDBC: ? marker compiled first, value set through a typed setter
    public String findEmail(long userId) throws SQLException {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT email FROM users WHERE id = ?")) {
            ps.setLong(1, userId);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? rs.getString(1) : null;
            }
        }
    }

    // JdbcTemplate: trailing arguments are bound to the ? markers
    public List<Long> findOrderIds(String status) {
        return jdbc.queryForList("SELECT id FROM orders WHERE status = ?", Long.class, status);
    }

    // JPA: named parameter, value bound by setParameter
    public List<User> findByEmail(String email) {
        return em.createQuery("SELECT u FROM User u WHERE u.email = :email", User.class)
                 .setParameter("email", email)
                 .getResultList();
    }

    // allowlist lookup; only constant JPQL fragments reach the query
    public List<User> listUsers(String sortKey) {
        String jpql = SORTED_QUERIES.getOrDefault(sortKey, SORTED_QUERIES.get("id"));
        return em.createQuery(jpql, User.class)
                 .getResultList();
    }
}

Engineering & System Hardening Checklist

Sources