Level 2 · 25 min

SQL Injection: Anatomy and Defenses

SQL Injection has been in the OWASP Top 10 since the list was first published in 2003. It persists because the unsafe pattern (string concatenation) is the default, and the safe pattern (parameterized statements) requires the developer to know the difference. A single SQLi in an authentication endpoint can leak the entire user table.

Anatomy of a Classic SQLi

Consider: String sql = "SELECT * FROM users WHERE email='" + email + "' AND password='" + pwd + "'". An attacker submits email=admin@x.com'-- and any password. The query becomes SELECT * FROM users WHERE email=''admin@x.com''-- AND password=''anything'' — the -- comments out the rest, and the attacker logs in as admin. Variants include UNION-based extraction (UNION SELECT username, password FROM users), stacked queries (; DROP TABLE...), and second-order injection (the payload is stored in the DB and triggers when read back unsafely later).

Parameterized Statements: The Real Fix

PreparedStatement separates the query structure from the data. The DB receives ''SELECT * FROM users WHERE email=? AND password=?'' once, plans it once, and any ? value is treated strictly as data — not parsed as SQL. There is no encoding to get wrong, no escape function to bypass; the parser simply never sees the user input. The exception is identifiers (table/column names) which cannot be parameterized — those need an allowlist. Modern frameworks (JPA/Hibernate, Spring Data, sqlc, Prisma) generate parameterized queries by default; the danger is when developers drop down to entityManager.createNativeQuery(''SELECT ... '' + dynamicFilter) for a ''quick custom report''. ORM is not a defense — concatenation inside an ORM is just as exploitable.

Blind SQLi and Defense in Depth

Modern apps usually catch DB exceptions and return a generic 500. This kills error-based SQLi but enables blind SQLi: boolean-based (the attacker sends id=1 AND substring(password,1,1)=''a'' and observes whether the response differs) or time-based (id=1; SELECT pg_sleep(5) — if the response takes 5 seconds extra, the injection works). sqlmap automates extraction at ~1 character/second. Defenses beyond parameterization: (1) least-privilege DB account — the application user should have GRANT SELECT/INSERT/UPDATE on specific tables, never DROP, ALTER, or access to system catalogues; (2) WAF rules as a backup, not a primary defense; (3) input validation as defense in depth (a numeric ID is rejected if not numeric) but never as the only defense.

Key Takeaways

Parameterized statements (PreparedStatement, $1, named bindings) are the only reliable defense — escaping is fragile, validation is incomplete.
ORMs do not protect against concatenation inside createNativeQuery / raw() / $queryRaw — those are just as exploitable as plain JDBC.
Run the application as a low-privilege DB user. If the app role lacks DROP, even a successful SQLi can not destroy the schema.

Code example

// VULNERABLE — string concatenation
String sql = "SELECT * FROM users WHERE email='" + email + "'";
ResultSet rs = stmt.executeQuery(sql);

// FIXED — PreparedStatement with placeholders
String sql = "SELECT * FROM users WHERE email = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
  ps.setString(1, email);
  try (ResultSet rs = ps.executeQuery()) {
    // ...
  }
}

// VULNERABLE — ORM is not a magic shield
String jpql = "SELECT u FROM User u WHERE u.role = '" + role + "'";
em.createQuery(jpql, User.class).getResultList();

// FIXED — named parameter, ORM-aware
em.createQuery("SELECT u FROM User u WHERE u.role = :role", User.class)
  .setParameter("role", role)
  .getResultList();