Definition
- String concatenation – untrusted input is concatenated into a SQL statement, letting an attacker alter the query's structure.
- Consequences – authentication bypass, unauthorized data access, data deletion, privilege escalation, and potentially host command execution.
- Prepared statements – the SQL text with placeholders is compiled first, then values are bound separately, so input can never become code.
- Secondary defenses – input validation, allow-listing, correct ORM usage, stored procedures with bound parameters.
- Least privilege – the application's database account has only the rights it needs, containing damage if injection succeeds.
Imagine a National Bank of India form where the teller types a customer's name into a search slip read literally as a command. If a malicious "name" contains extra instructions like "and also show everyone's balances," the system obeys the whole slip. The fix is to keep the instruction and the typed value strictly separate: the command says "find the customer named [value]" and the value is always treated as just a name.
Understanding the Concepts
What is SQL injection?
SQL injection occurs when an application builds a query by concatenating user input, so the input can change the query's meaning.
package com.bank.repository;
// WRONG: string concatenation
public class UnsafeAccountLookup {
public void findAccount(String userInput) {
String sql = "SELECT * FROM accounts WHERE name = '" + userInput + "'";
// If userInput = "' OR '1'='1"
// Query becomes: SELECT * FROM accounts WHERE name = '' OR '1'='1'
// Returns ALL accounts!
}
}
What are the common injection techniques?
| Technique | Example Payload | Effect |
|---|---|---|
| Tautology | ' OR '1'='1 | Always-true WHERE, returns all rows |
| UNION SELECT | ' UNION SELECT id,password FROM users-- | Reads other tables |
| Stacked queries | '; DROP TABLE accounts;-- | Executes destructive commands |
| Comment truncation | admin'-- | Bypasses password check |
| Blind/time-based | ' AND SLEEP(5)-- | Extracts data by timing |
-- Classic login bypass
SELECT * FROM users WHERE name = '' OR '1'='1' AND password = '';
-- '1'='1' is always true: returns all users, bypasses authentication
-- UNION SELECT to read sensitive data
SELECT name, email FROM accounts
WHERE id = '1' UNION SELECT username, password FROM admin_users--';
What are the consequences?
For a bank, injection consequences are severe.
┌──────────────────────┬──────────────────────────────────────┐ │ Consequence │ Impact for National Bank of India │ ├──────────────────────┼──────────────────────────────────────┤ │ Auth bypass │ Attacker accesses any account │ │ Data theft │ Account numbers, balances, PII leaked │ │ Data tampering │ Balances modified, transactions forged│ │ Data deletion │ DROP TABLE destroys records │ │ Privilege escalation │ Attacker gains admin rights │ │ Host command exec │ xp_cmdshell (SQL Server) runs OS cmds│ └──────────────────────┴──────────────────────────────────────┘
How do prepared statements prevent injection?
The SQL text with placeholders is compiled first, then values are bound separately. Input can never alter the query structure.
package com.bank.repository;
import java.sql.*;
import java.math.BigDecimal;
public class SafeAccountLookup {
// CORRECT: parameterized query
public void findAccount(Connection conn, String name) throws SQLException {
String sql = "SELECT id, name, balance FROM accounts WHERE name = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, name); // value is ALWAYS data, never code
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString("name") + ": "
+ rs.getBigDecimal("balance"));
}
}
}
// If name = "' OR '1'='1"
// The database searches for a literal name "' OR '1'='1'"
// No injection possible
}
}
How do ORMs like JPA/Hibernate prevent injection?
JPA and Hibernate bind parameters by default when used correctly.
package com.bank.repository;
import jakarta.persistence.*;
import java.util.List;
public class AccountJpaRepository {
@PersistenceContext
private EntityManager em;
// Safe: named parameter binding
public List<Object[]> findByName(String name) {
return em.createQuery(
"SELECT a.id, a.balance FROM Account a WHERE a.name = :name")
.setParameter("name", name)
.getResultList();
}
// WRONG: string concatenation in JPQL
public List<Object[]> findByNameUnsafe(String name) {
return em.createQuery(
"SELECT a.id, a.balance FROM Account a WHERE a.name = '" + name + "'")
.getResultList();
// Injection possible even with JPA!
}
}
How do you handle dynamic column or table names?
Parameterized queries cannot bind identifiers. Use a strict allow-list.
package com.bank.repository;
import java.sql.*;
import java.util.Set;
public class DynamicQueryBuilder {
private static final Set<String> ALLOWED_COLUMNS =
Set.of("name", "email", "city", "balance");
public void sortAccounts(Connection conn, String sortColumn)
throws SQLException {
// Validate against allow-list, never interpolate raw input
if (!ALLOWED_COLUMNS.contains(sortColumn)) {
throw new IllegalArgumentException("Invalid sort column: " + sortColumn);
}
String sql = "SELECT * FROM accounts ORDER BY " + sortColumn;
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
// process results
}
}
}
What is the principle of least privilege?
The application's database account should have only the rights it needs.
-- Create a restricted application role
CREATE ROLE bank_app_role;
GRANT SELECT, INSERT, UPDATE ON accounts TO bank_app_role;
GRANT SELECT, INSERT ON transactions TO bank_app_role;
-- No DELETE, no DROP, no access to admin tables
-- Even a successful injection is contained
What is a defense-in-depth checklist?
Pankaj uses layered defenses at National Bank of India.
1. Parameterized queries / prepared statements (non-negotiable core)
2. Input validation and allow-listing
3. ORM with correct parameter binding
4. Least privilege on the database account
5. Web application firewall (WAF) as a perimeter layer
6. Security testing: automated SAST/DAST and manual pen testing
7. Code review: flag any string concatenation into SQL
What are common interview follow-up questions?
- Can stored procedures prevent injection? (Only if they use bound parameters internally and do not build dynamic SQL by concatenation.)
- Does an ORM guarantee safety? (No; concatenating into JPQL or HQL is still injectable.)
- What is second-order injection? (Data stored safely but later concatenated into a query when read back.)
Visual Overview
Unsafe (string concatenation):
Input: ' OR '1'='1
│
▼
SELECT * FROM accounts WHERE name = '' OR '1'='1'
──▶ Always TRUE ──▶ returns ALL accounts
Safe (prepared statement):
SQL template: SELECT * FROM accounts WHERE name = ?
│
▼
Compiled query (fixed structure)
│
▼
Bind value: "' OR '1'='1" (treated as literal data)
──▶ Searches for name literally equal to "' OR '1'='1"
──▶ Returns 0 rows (no such name)
Key Takeaway
SQL injection is a vulnerability where untrusted input is concatenated into a SQL statement, letting an attacker change the query's structure. The classic case is entering ' OR '1'='1 to make a WHERE clause always true, but worse payloads can read other tables with UNION SELECT, delete data with stacked queries, or extract information through blind timing attacks.
The non-negotiable core defense is parameterized or prepared statements: the SQL text with placeholders is compiled first and values are bound separately, so input is always data and can never become code. In Java that means PreparedStatement with ? placeholders. JPA and Hibernate also bind parameters by default, but only if you use named parameters, not string concatenation into JPQL.
Around that core, add input validation and allow-listing for identifiers that cannot be parameterized, and the principle of least privilege so the application's database account has only the rights it needs, with no DROP and no access to admin tables.
At the National Bank of India a penetration test on a legacy account-lookup screen that concatenated the account id returned every customer's account when fed ' OR '1'='1. Rajesh fixed it by switching to a parameterized query and tightening database privileges.
Remember one sentence: SQL injection happens when user input becomes part of the SQL code; prevent it by binding input as parameters so data can never be executed as code.
---