What SQL injection is
SQL injection (SQLi) is one of the oldest and most damaging web vulnerabilities. It happens when an attacker supplies input that the application concatenates directly into a SQL string — and the database executes the injected SQL as if the developer wrote it.
The consequences range from reading all rows in a table, bypassing login, to
destroying the entire database or running operating-system commands (via
xp_cmdshell in SQL Server).
A classic example
# Vulnerable Python code — string concatenation with user input
username = request.form['username'] # attacker enters: ' OR '1'='1
query = "SELECT * FROM users WHERE username = '" + username + "'"
# Resulting SQL:
# SELECT * FROM users WHERE username = '' OR '1'='1'
# → returns every user row; attacker is logged in as the first user
The attacker changed the SQL structure, not just the data. They closed the
string literal with ', injected their own condition, and commented out the
rest. The application thought it was passing data; the database received SQL.
Parameterised queries — the primary fix
A parameterised query (prepared statement) separates the SQL structure from the data. The query template is sent first; data values are sent separately as bound parameters. The database always treats bound parameters as data — never as SQL syntax, regardless of what they contain.
# Python + psycopg2 (Postgres) — SAFE
cur.execute(
"SELECT * FROM users WHERE username = %s AND password_hash = %s",
(username, password_hash)
)
# Even if username = "' OR '1'='1", it is treated as a literal string.
# The database looks for a user named exactly "' OR '1'='1" — finds none.
# Node.js + pg (Postgres) — SAFE
const result = await client.query(
'SELECT * FROM orders WHERE customer_id = $1 AND status = $2',
[customerId, 'pending']
);
# Java JDBC — SAFE
PreparedStatement stmt = conn.prepareStatement(
"SELECT * FROM products WHERE category = ? AND unit_price < ?"
);
stmt.setString(1, category);
stmt.setDouble(2, maxPrice);
ResultSet rs = stmt.executeQuery();
ORM queries are safe — raw SQL escapes are not
Most ORMs use parameterised queries internally. The danger comes from their raw SQL escape hatches.
# Django ORM — SAFE (parameterised internally)
orders = Order.objects.filter(customer_id=customer_id, status='pending')
# Django raw() — UNSAFE (string concatenation)
orders = Order.objects.raw(
f"SELECT * FROM orders WHERE customer_id = '{customer_id}'"
)
# Django raw() — SAFE (bound parameters)
orders = Order.objects.raw(
"SELECT * FROM orders WHERE customer_id = %s",
[customer_id]
)
# SQLAlchemy text() — SAFE with bindparam
from sqlalchemy import text
result = session.execute(
text("SELECT * FROM orders WHERE customer_id = :cid AND status = :st"),
{"cid": customer_id, "st": "pending"}
)
Stored procedures are not automatically safe
A stored procedure protects against injection at the call site — the parameters are bound. But if the procedure builds dynamic SQL internally via string concatenation, the injection surface moves inside the procedure.
-- SQL Server stored procedure — UNSAFE inside
CREATE PROCEDURE SearchProducts @SearchTerm NVARCHAR(100)
AS
BEGIN
EXEC('SELECT * FROM products WHERE name LIKE ''%' + @SearchTerm + '%''')
-- Attacker passes: '; DROP TABLE products; --
END;
-- SAFE: use sp_executesql with parameters
CREATE PROCEDURE SearchProducts @SearchTerm NVARCHAR(100)
AS
BEGIN
DECLARE @sql NVARCHAR(500) = N'SELECT * FROM products WHERE name LIKE @t';
EXEC sp_executesql @sql, N'@t NVARCHAR(102)', @t = '%' + @SearchTerm + '%';
END;
Second-order injection
Second-order injection is harder to spot. Malicious input is stored safely (the INSERT is parameterised) and later retrieved and used unsafely in a different query.
# Step 1: user registers with username = "admin'--"
# Safely stored (parameterised INSERT) — no injection here
cur.execute("INSERT INTO users (username) VALUES (%s)", (username,))
# Step 2: admin panel reads the username and uses it unsafely in a new query
row = db.fetchone("SELECT username FROM users WHERE id = %s", (user_id,))
admin_username = row['username'] # → "admin'--"
# UNSAFE: concatenates the stored value into a new query
cur.execute(f"SELECT * FROM audit_log WHERE actor = '{admin_username}'")
# → SELECT * FROM audit_log WHERE actor = 'admin'--'
# Dumps all audit log rows
The fix: parameterise every query, including those that use data fetched from the database. Data retrieved from the database must still be treated as untrusted.
Dynamic ORDER BY — the identifiers problem
Bind parameters work for values but not for SQL identifiers (column names, table names). Dynamic sort columns are a common injection vector.
# UNSAFE: attacker sends sort='; DROP TABLE orders; --
sort_col = request.args.get('sort')
cur.execute(f"SELECT * FROM orders ORDER BY {sort_col}")
# SAFE: allowlist of permitted column names
ALLOWED_SORT = {'id', 'created_at', 'total_amount', 'status'}
sort_col = request.args.get('sort', 'created_at')
if sort_col not in ALLOWED_SORT:
sort_col = 'created_at' # safe fallback
cur.execute(f"SELECT * FROM orders ORDER BY {sort_col} DESC")
# sort_col is guaranteed to be from the allowlist, not user-supplied
Defence in depth
Parameterised queries are the primary defence — they cannot be bypassed when used consistently. These layers reduce impact if a parameterisation is missed:
1. Parameterised queries — prevents injection (mandatory, non-negotiable)
2. Input validation/allowlists — reduces attack surface
3. Least-privilege DB user — limits blast radius (no DROP TABLE, no pg_read_file)
4. Error message suppression — hides schema info from error-based attackers
5. WAF — catches automated scans (easily bypassed by humans)
# Never return raw database errors to the client
try:
result = db.execute(query, params)
except Exception as e:
logging.error("DB error: %s", e, exc_info=True) # log internally
return {"error": "An internal error occurred"} # generic client message
Detecting injection vulnerabilities
-- Add injection payload tests to your test suite
-- A search for "' OR '1'='1" should return zero products
def test_search_not_injectable():
results = search_products("' OR '1'='1")
assert len(results) == 0 # not all products
CI tools: Bandit (Python SAST) flags unsafe DB calls; Semgrep has rules for SQL injection patterns; sqlmap probes live endpoints black-box.
Recap
SQL injection is 100% preventable with parameterised queries — use them everywhere, without exception. ORM query APIs are safe by default; their raw SQL escapes are not. Stored procedures are only safe if their internal dynamic SQL is also parameterised. Treat data retrieved from the database as untrusted when building new queries (second-order injection). Allowlist sort/filter column names — identifiers cannot be bound as parameters. Defence in depth (least privilege, error suppression, WAF) limits the blast radius if injection somehow occurs.