Definition
- SQL Injection – a vulnerability where user input is concatenated into a SQL statement, causing the database to execute attacker-controlled SQL rather than treating the input as data.
- Impact – attackers can bypass authentication, read or modify unauthorized data, or drop entire tables.
- Parameterized queries – the primary defense: the SQL structure is fixed with placeholders, and user values are bound as parameters so input never changes the query's meaning.
- Prepared statements – the JDBC implementation of parameterized queries:
PreparedStatementwith?placeholders andsetString/setIntbindings. - Supporting defenses – least-privilege database accounts, input validation, ORM usage, and allowlisting for dynamic SQL parts.
Imagine a National Bank of India clerk who fills in a form by literally copying whatever the customer wrote into the official instruction, word for word. A dishonest customer writes in the name field: 'Rajesh: AND ALSO transfer everyone's money to me.' Because the clerk pastes it straight into the instruction, the bank obeys the whole thing. A parameterized query is like a clerk who has a fixed printed instruction with labeled blanks and treats whatever the customer wrote as plain text that only goes into the blank, never as a new instruction.
Understanding the Concepts
What is SQL Injection?
SQL Injection is a vulnerability where an attacker supplies input that is concatenated into a SQL statement, causing the database to execute attacker-controlled SQL. The database cannot tell data from code because the code was built by string concatenation.
The classic example: if a login query is built as "SELECT * FROM users WHERE username = '" + input + "'", an attacker supplies ' OR '1'='1, and the query becomes SELECT * FROM users WHERE username = '' OR '1'='1', which matches every row.
package com.bank.vulnerability;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class VulnerableAccountLookup {
// DANGEROUS: SQL built by string concatenation
public void findAccount(Connection conn, String owner) throws Exception {
String sql = "SELECT * FROM accounts WHERE owner = '" + owner + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println("Account: " + rs.getString("account_id")
+ " Balance: " + rs.getBigDecimal("balance"));
}
}
// Attacker input: ' OR '1'='1
// Resulting SQL: SELECT * FROM accounts WHERE owner = '' OR '1'='1'
// Returns ALL accounts in the database!
}
Normal input: "rajesh"
SQL: SELECT * FROM accounts WHERE owner = 'rajesh'
Result: 1 row (Rajesh's account)
Malicious input: ' OR '1'='1
SQL: SELECT * FROM accounts WHERE owner = '' OR '1'='1'
Result: ALL rows (every customer's account!)
How do parameterized queries prevent it?
With a parameterized query, the SQL structure is fixed and user values are bound as parameters. The database driver sends the SQL and the values separately, so the database always treats user input as data, never as code.
package com.bank.security;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.math.BigDecimal;
public class SafeAccountLookup {
public void findAccount(Connection conn, String owner) throws Exception {
String sql = "SELECT * FROM accounts WHERE owner = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, owner); // value bound as parameter
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println("Account: " + rs.getString("account_id")
+ " Balance: " + rs.getBigDecimal("balance"));
}
}
// With malicious input: ' OR '1'='1
// Database looks for an account literally named: ' OR '1'='1
// Finds nothing. Attack completely neutralized.
}
Malicious input with parameterized query:
Parameter value: "' OR '1'='1"
Database searches for owner literally = "' OR '1'='1"
Result: 0 rows (no such owner exists)
How does JPA/Spring Data prevent SQL Injection?
JPA uses named parameters with setParameter, and Spring Data derived queries are parameterized automatically. Both prevent SQL injection without the developer writing raw SQL.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
// Derived query: parameterized automatically
List<Account> findByOwner(String owner);
// JPQL with named parameter: safe
@Query("SELECT a FROM Account a WHERE a.owner = :owner")
List<Account> findByOwnerJpql(@Param("owner") String owner);
// WRONG: string concatenation in JPQL (vulnerable!)
// @Query("SELECT a FROM Account a WHERE a.owner = '" + owner + "'")
// This would re-introduce SQL injection!
}
What about dynamic SQL parts that cannot be parameterized?
Table names, column names, and ORDER BY direction cannot be parameterized because they are part of the SQL structure, not data values. For these, validate against an allowlist.
package com.bank.security;
import java.util.Set;
public class DynamicSqlSafety {
private static final Set<String> ALLOWED_SORT_COLUMNS = Set.of(
"account_id", "balance", "created_at", "owner"
);
private static final Set<String> ALLOWED_DIRECTIONS = Set.of("ASC", "DESC");
public String buildSortClause(String column, String direction) {
if (!ALLOWED_SORT_COLUMNS.contains(column)) {
throw new IllegalArgumentException("Invalid sort column: " + column);
}
if (!ALLOWED_DIRECTIONS.contains(direction.toUpperCase())) {
throw new IllegalArgumentException("Invalid direction: " + direction);
}
return " ORDER BY " + column + " " + direction;
// Safe because values are from an allowlist, not user input
}
}
What are the types of SQL Injection?
There are three main types:
- Classic (in-band): the result of the injected query appears directly in the response.
- Blind: the attacker infers data by observing differences in response behavior (boolean-based) or timing (time-based).
- Out-of-band: the attacker uses database functions to send data to an external server.
Classic: ' OR '1'='1 --> all rows returned in response
Blind boolean: ' AND (SELECT balance FROM accounts WHERE owner='rajesh') > 100000 --
--> different response if true vs false
Blind time-based: ' AND IF(1=1, SLEEP(5), 0) --
--> response delayed by 5 seconds if injectable
What are common mistakes?
The most dangerous mistake is using string concatenation for SQL, even in small utility queries or logging.
// WRONG: string concatenation (even for simple queries)
String sql = "SELECT * FROM audit_log WHERE action = '" + action + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// CORRECT: parameterized query
String sql = "SELECT * FROM audit_log WHERE action = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, action);
ResultSet rs = ps.executeQuery();
Another mistake is trusting ORMs completely. Native queries with string concatenation in JPA reintroduce injection.
// WRONG: native query with concatenation
@Query(value = "SELECT * FROM accounts WHERE owner = '" + owner + "'",
nativeQuery = true)
List<Account> findVulnerable(String owner);
// CORRECT: native query with parameter
@Query(value = "SELECT * FROM accounts WHERE owner = :owner",
nativeQuery = true)
List<Account> findSafe(@Param("owner") String owner);
What about least-privilege database accounts?
Even if injection succeeds, a least-privilege database account limits the damage. The application user should have only SELECT, INSERT, and UPDATE on specific tables, never DROP, DELETE *, or superuser privileges.
-- Database user for the web application
CREATE USER app_user WITH PASSWORD 'strong_password';
GRANT SELECT, INSERT, UPDATE ON accounts TO app_user;
GRANT SELECT, INSERT ON transactions TO app_user;
-- No DROP, no DELETE, no superuser
-- Even a successful injection cannot drop tables
What should a senior engineer know?
Senior engineers know that parameterization is the real fix; input validation and escaping are secondary and error-prone. Stored procedures help only if they themselves use parameters and avoid dynamic SQL. ORMs reduce but do not eliminate risk.
Senior engineers also implement defense in depth: parameterized queries as the primary defense, least-privilege database accounts as the fallback, web application firewalls for detection, and regular code scanning for concatenation patterns.
Visual Overview
Vulnerable (string concatenation):
┌──────────┐ ┌──────────┐
│ User │ input: ' OR 1=1 │ Database │
│ Input ├───────────────────►│ │
│ │ SQL: SELECT * │ Executes │
│ │ WHERE owner='' │ attacker │
│ │ OR '1'='1' │ SQL! │
└──────────┘ └──────────┘
Safe (parameterized query):
┌──────────┐ ┌──────────┐
│ User │ param: ' OR 1=1 │ Database │
│ Input ├───────────────────►│ │
│ │ SQL: SELECT * │ Treats │
│ │ WHERE owner = ? │ input as │
│ │ [data: ' OR 1=1] │ DATA │
└──────────┘ └──────────┘
No rows found.
Attack neutralized.
Key Takeaway
SQL Injection is a vulnerability where an attacker supplies input that gets concatenated into a SQL statement, so the database executes attacker-controlled SQL instead of treating the input as plain data. It can bypass logins, leak or alter data, and even destroy tables. The classic example is a login or search field where typing something like quote OR 1 equals 1 turns the query into one that matches every row.
The primary defense is parameterized queries, also called prepared statements. The SQL structure is fixed with placeholders, and user values are bound separately, so the database always treats them as data and never as code. In JDBC that is a PreparedStatement with setString on a question-mark placeholder; in JPA it is a named parameter with setParameter; and Spring Data derived queries are parameterized automatically.
In the National Bank of India project, a vulnerable method that concatenated the owner name into the SQL returned every customer's account when fed the malicious OR-1-equals-1 input: a full breach. The safe method using a bound parameter returned zero rows for that same input because the database looked for an account literally named with that string, yet it still returned the correct row for a legitimate name.
The supporting measures are least-privilege database accounts so even a successful injection cannot drop tables, allowlisting for dynamic parts like column names and sort direction that cannot be parameters, and awareness that ORMs reduce but do not eliminate the risk because native queries and string-built JPQL can reintroduce it.
Remember one sentence: never concatenate user input into SQL; bind it as a parameter so the database treats it as data, not code.
---