SQL 92 🛢️ Preventing SQL Injection Vulnerabilities
SQL injection remains one of the most dangerous and persistent web vulnerabilities, yet it is also one of the most completely solvable. The root cause is always the same: an application builds a SQL query by concatenating user input into a string, and the database cannot distinguish the developer’s intended query structure from the attacker’s injected syntax . The fix is equally consistent: parameterized queries, also called prepared statements, separate the query structure from the data so that user input is always treated as a literal value, never as executable SQL .
What makes SQL injection still prevalent despite decades of documented solutions is not a tooling gap but a discipline gap . Every mainstream database driver in every mainstream language supports parameterized queries. ORMs parameterize by default when used as intended. The vulnerability appears at the edges: raw query escape hatches, dynamic identifiers that cannot be parameterized, stored procedures that build SQL internally, and second-order attacks where data retrieved from the database is trusted in a later query. This chapter covers the primary defense, the layered defenses that reduce blast radius when something slips through, and the specific patterns that reintroduce risk even in otherwise secure codebases.
Key point: Parameterized queries are the non-negotiable primary defense against SQL injection; input validation, least-privilege database accounts, and ORMs are defense-in-depth layers that do not substitute for parameterization.
Why SQL injection persists despite the known fix
The code-data confusion problem. SQL is a language with structure: keywords, operators, clauses, and literals. When a developer writes SELECT * FROM users WHERE email = ' + input + ', the input becomes part of the query text. If the input contains ' OR '1'='1, the query becomes SELECT * FROM users WHERE email = '' OR '1'='1', which returns every row . The database has no way to know that the OR '1'='1 was not intended by the developer, because by the time the query reaches the parser, it is all one string.
The string-formatting illusion. Every language offers convenient string formatting: f-strings in Python, template literals in JavaScript, String.format in Java, string interpolation in C#. Each of these feels clean and readable. Each of them is concatenation with nicer syntax . The illusion is that the code looks structured, but the query the database receives is a flat string where data and structure have already been merged.
The ORM false assurance problem. ORMs like SQLAlchemy, Sequelize, Hibernate, and Entity Framework parameterize automatically when you use their query builder methods. This leads teams to conclude that “we use an ORM” means “we are safe” . The problem is the raw query escape hatch. Every ORM provides a method to execute arbitrary SQL, and the moment user input is concatenated into that raw SQL, the ORM provides no protection whatsoever. Code reviewers frequently miss these because the ORM’s presence is treated as a blanket assurance .
The dynamic identifier problem. Parameters bind values, not identifiers. You cannot parameterize a table name or a column name. When an application needs dynamic sorting (ORDER BY column ASC) or dynamic table selection, the input must go through an allowlist rather than a parameter placeholder . Using user input directly for identifiers is a design smell that requires either a rewrite or a strict allowlist mapping.
The second-order problem. Data that was safely stored using parameterized queries can become dangerous when retrieved and used unsafely in a different query later. A username like admin'-- is stored safely in stage one. In stage two, if the application retrieves that username and concatenates it into a password-change query, the -- comments out the rest of the query, potentially updating every user’s password . The rule is absolute: every value that touches a SQL query must go through a parameter placeholder, regardless of where it came from .
a. Parameterized queries: the primary defense
A parameterized query writes the SQL template with placeholders and passes the values separately to the driver. The driver sends the query structure and the data on separate channels, and the database compiles the query once with the placeholders, then binds the values strictly as data .
The syntax varies by driver, but the principle is identical:
# Python with psycopg2 (PostgreSQL)
cursor.execute(
"SELECT * FROM users WHERE email = %s",
(user_email,)
)// Node.js with pg (PostgreSQL)
const result = await client.query(
'SELECT * FROM users WHERE email = $1',
[userEmail]
);// Node.js with mysql2 (MySQL)
const [rows] = await connection.execute(
'SELECT * FROM users WHERE email = ?',
[userEmail]
);// Java with JDBC
PreparedStatement stmt = conn.prepareStatement(
"SELECT * FROM users WHERE email = ?");
stmt.setString(1, userEmail);
ResultSet rs = stmt.executeQuery();In each case, the placeholder is the point. If the input contains ' OR '1'='1, the database looks for a user whose email is literally that string. It does not interpret the OR as an operator or the quotes as delimiters. The input is data, and only data .
A subtle but critical mistake is applying formatting to the query before passing it to execute(). cursor.execute(query % params) performs Python-level string substitution before the driver sees it, which is functionally identical to concatenation . The parameters must be passed as the second argument to execute(), not merged into the string.
b. Defense in depth: what adds protection without replacing parameterization
Input validation is a secondary layer. Allowlisting expected formats—a numeric ID must match ^\d+$, an email must contain @—blocks malformed input before it reaches the database . But validation is not a substitute for parameterization. Blocklisting specific characters or keywords fails because attackers have decades of encoding tricks, comment variations, and alternate syntax . The correct role for validation is narrowing the attack surface, not detecting malicious input.
Least-privilege database accounts limit the blast radius when an injection does occur. An application that only reads and writes its own tables should not connect as a superuser that can drop tables or read system catalogs . If an injection slips through, the account’s limited permissions constrain what the attacker can do. This is the control most teams skip and most regret skipping after an incident .
ORMs and query builders parameterize by default when used through their structured APIs. Sequelize’s User.findOne({ where: { email } }) and SQLAlchemy’s session.query(User).filter(User.email == email) are safe . The rule for ORMs is: trust the parameterized path, treat every raw-query call as a place to audit, and pass bind parameters even in raw queries .
Stored procedures can be as effective as parameterized queries when implemented safely—meaning the procedure does not build dynamic SQL internally through string concatenation . A stored procedure that concatenates its own inputs is just injection with extra steps . Calling a procedure from application code does not automatically make it safe.
Web application firewalls catch known, unsophisticated attack patterns in transit. They are a compensating control that buys time and catches noise, not a fix for the underlying vulnerability . Encoding variations and second-order injection can slip past generic rule sets.
c. Dynamic identifiers and the allowlist pattern
Parameter placeholders cannot bind table names, column names, or sort order indicators. These are identifiers that determine the structure of the query, and the database parser needs them at compile time. When an application genuinely needs dynamic identifiers, the defense is an allowlist: map the user-supplied value to a fixed set of permitted names and reject anything outside that set .
ALLOWED_TABLES = {"users", "products", "orders"}
ALLOWED_COLUMNS = {"name", "email", "created_at"}
def build_query(table, column, value):
if table not in ALLOWED_TABLES:
raise ValueError("Invalid table")
if column not in ALLOWED_COLUMNS:
raise ValueError("Invalid column")
query = f"SELECT * FROM {table} WHERE {column} = %s"
return query, (value,)The table and column values are interpolated into the query string, but only after they have been validated against a fixed set. The value is parameterized. This is the only safe pattern for dynamic identifiers. Using user input directly for an identifier, even with “sanitization,” is a symptom of poor design that warrants a rewrite if time allows .
Complete Example Session
The following session demonstrates a vulnerable query, its parameterized fix, and the layered defenses in Python with psycopg2.
# ============================================
# PART 1: THE VULNERABLE PATTERN
# ============================================
import psycopg2
def get_user_vulnerable(email):
cursor.execute(
"SELECT * FROM users WHERE email = '" + email + "'"
)
return cursor.fetchone()
# Input: "' OR '1'='1"
# Query becomes: SELECT * FROM users WHERE email = '' OR '1'='1'
# Returns every row
# ============================================
# PART 2: THE PARAMETERIZED FIX
# ============================================
def get_user_safe(email):
cursor.execute(
"SELECT * FROM users WHERE email = %s",
(email,)
)
return cursor.fetchone()
# Input: "' OR '1'='1"
# Database looks for a user with that literal email
# Returns None
# ============================================
# PART 3: INPUT VALIDATION (SECONDARY)
# ============================================
import re
def get_user_validated(email):
if not re.match(r'^[\w\.-]+@[\w\.-]+\.\w+$', email):
raise ValueError("Invalid email format")
cursor.execute(
"SELECT * FROM users WHERE email = %s",
(email,)
)
return cursor.fetchone()
# ============================================
# PART 4: LEAST-PRIVILEGE ACCOUNT
# ============================================
# SQL executed as DBA, not in application code:
# CREATE USER app_readonly WITH PASSWORD '...';
# GRANT SELECT ON users, orders TO app_readonly;
# Application connects as app_readonly, not as superuser
# ============================================
# PART 5: ORM SAFE USAGE
# ============================================
from sqlalchemy.orm import Session
def get_user_orm(session: Session, email):
return session.query(User).filter(User.email == email).first()
# SQLAlchemy parameterizes automatically
# ============================================
# PART 6: ORM RAW QUERY (STILL REQUIRES PARAMETERS)
# ============================================
from sqlalchemy import text
def get_user_raw_safe(session, email):
return session.execute(
text("SELECT * FROM users WHERE email = :email"),
{"email": email}
).fetchone()
# The text() construct uses bind parameters
# ============================================
# PART 7: DYNAMIC IDENTIFIER WITH ALLOWLIST
# ============================================
ALLOWED_SORT_COLUMNS = {"name", "created_at", "email"}
def get_users_sorted(sort_column):
if sort_column not in ALLOWED_SORT_COLUMNS:
raise ValueError("Invalid sort column")
query = f"SELECT * FROM users ORDER BY {sort_column}"
cursor.execute(query)
return cursor.fetchall()
# ============================================
# PART 8: SECOND-ORDER INJECTION (UNSAFE)
# ============================================
def change_password_unsafe(username, new_hash):
cursor.execute(
"UPDATE users SET password = '" + new_hash + "' WHERE username = '" + username + "'"
)
# If username was stored as "admin'--" in stage one,
# the WHERE clause is commented out, updating all rows
# ============================================
# PART 9: SECOND-ORDER INJECTION (SAFE)
# ============================================
def change_password_safe(username, new_hash):
cursor.execute(
"UPDATE users SET password = %s WHERE username = %s",
(new_hash, username)
)
# Every value is parameterized, regardless of source
# ============================================
# PART 10: STORED PROCEDURE AUDIT
# ============================================
# Safe stored procedure (no dynamic SQL):
# CREATE FUNCTION get_user(p_email TEXT) RETURNS SETOF users AS $$
# BEGIN
# RETURN QUERY SELECT * FROM users WHERE email = p_email;
# END;
# $$ LANGUAGE plpgsql;
#
# Unsafe stored procedure (concatenates internally):
# CREATE FUNCTION get_user_unsafe(p_email TEXT) RETURNS SETOF users AS $$
# BEGIN
# RETURN QUERY EXECUTE 'SELECT * FROM users WHERE email = ''' || p_email || '''';
# END;
# $$ LANGUAGE plpgsql;The ten parts cover the vulnerable pattern, the parameterized fix, input validation as a secondary layer, least-privilege account design, ORM safe usage, ORM raw query with bind parameters, dynamic identifiers with allowlist, second-order injection vulnerability, second-order injection fix, and stored procedure audit. What the session demonstrates is that parameterization is the consistent fix across every pattern, and the layered defenses reduce impact when a gap remains.
Quick Reference
The Primary Defense
| Pattern | Safe | Unsafe |
|---|---|---|
| Python (psycopg2) | cursor.execute("... %s", (val,)) | cursor.execute(f"... '{val}'") |
| Node.js (pg) | client.query('... $1', [val]) | client.query(`... '${val}'`) |
| Node.js (mysql2) | connection.execute('... ?', [val]) | connection.query(`... '${val}'`) |
| Java (JDBC) | stmt.setString(1, val) | "..." + val |
| PHP (PDO) | $stmt->execute([$val]) | "..." . $val |
Placeholder Syntax by Engine
| Engine | Placeholder |
|---|---|
| PostgreSQL | $1, $2 (pg), %s (psycopg2) |
| MySQL | ? |
| SQLite | ? or :name |
| SQL Server | @param |
Defense Layers
| Layer | Role |
|---|---|
| Parameterized queries | Primary defense |
| Input validation (allowlist) | Secondary, narrows surface |
| Least-privilege account | Limits blast radius |
| ORM structured API | Automatic parameterization |
| WAF | Compensating control, not a fix |
What Cannot Be Parameterized
| Element | Defense |
|---|---|
| Table name | Allowlist mapping |
| Column name | Allowlist mapping |
Sort order (ASC/DESC) | Convert to boolean, select safe value |
Best Practices
✅ Do This:
cursor.execute("SELECT * FROM t WHERE id = %s", (user_id,)) # ✅ parameterized
ALLOWED = {"name", "email"}; assert col in ALLOWED # ✅ allowlist identifier
GRANT SELECT ON users TO app_readonly # ✅ least privilege
session.query(User).filter(User.email == email) # ✅ ORM structured❌ Don’t Do This:
cursor.execute(f"SELECT * FROM t WHERE id = {user_id}") # ❌ f-string
cursor.execute("SELECT * FROM t WHERE id = " + user_id) # ❌ concatenation
cursor.execute("SELECT * FROM " + table + " WHERE id = %s") # ❌ unvalidated identifier
session.execute(text(f"... '{name}'")) # ❌ ORM raw bypassCommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| ORM raw query vulnerable | text() or .raw() with concatenation | Pass bind parameters |
| Dynamic identifier injection | Table/column from user input | Allowlist mapping |
| Second-order injection | DB data trusted in later query | Parameterize every query |
| Stored procedure vulnerable | Internal dynamic SQL concatenation | Audit for EXECUTE/sp_executesql |
execute(query % params) | Python formatting before driver | Pass params as second argument |
| Verbose errors leak schema | Stack traces returned to user | Generic errors, log detail server-side |
Real-World Examples
1. Python psycopg2 parameterized
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))2. Node.js pg parameterized
await client.query('SELECT * FROM users WHERE id = $1', [id]);3. Java PreparedStatement
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
stmt.setInt(1, id);4. SQLAlchemy ORM
session.query(User).filter(User.email == email).first()5. Sequelize ORM
User.findOne({ where: { email } })6. Dynamic sort allowlist
if sort not in {"name", "created_at"}: raise ValueError
query = f"SELECT * FROM users ORDER BY {sort}"7. Least-privilege grant
GRANT SELECT, INSERT ON orders TO app_user;8. Second-order safe
cursor.execute("UPDATE users SET password = %s WHERE username = %s", (new_hash, username))9. Stored procedure audit
-- Search for dynamic SQL in procedures
SELECT routine_name FROM information_schema.routines WHERE routine_definition LIKE '%EXECUTE%';10. ORM raw query with bind
session.execute(text("SELECT * FROM users WHERE email = :email"), {"email": email})Visual
Concatenation vs Parameterization
┌──────────────────────────────────────────────┐
│ CONCATENATION (VULNERABLE) │
│ "SELECT * FROM users WHERE email = '" + input + "'" │
│ input = "' OR '1'='1" │
│ │ │
│ ▼ │
│ "SELECT * FROM users WHERE email = '' OR '1'='1'" │
│ │ │
│ ▼ │
│ Database parses: WHERE email='' OR TRUE │
│ Returns ALL rows ❌ │
│ │
│ PARAMETERIZATION (SAFE) │
│ "SELECT * FROM users WHERE email = %s" │
│ params = ["' OR '1'='1"] │
│ │ │
│ ▼ │
│ Driver sends query and data separately │
│ │ │
│ ▼ │
│ Database: WHERE email = literal string │
│ Returns 0 rows ✅ │
└──────────────────────────────────────────────┘Defense Layers
┌──────────────────────────────────────────────┐
│ LAYER 1: PARAMETERIZED QUERIES │
│ The fix. Separates code from data. │
│ │ │
│ ▼ │
│ LAYER 2: INPUT VALIDATION │
│ Allowlist expected formats. │
│ Narrows surface, does not replace L1. │
│ │ │
│ ▼ │
│ LAYER 3: LEAST PRIVILEGE │
│ App account cannot DROP or read system. │
│ Limits blast radius if L1 fails. │
│ │ │
│ ▼ │
│ LAYER 4: WAF / LOGGING │
│ Catches known patterns, alerts on anomalies.│
│ Compensating control, not a fix. │
└──────────────────────────────────────────────┘Dynamic Identifier Decision
┌──────────────────────────────────────────────┐
│ Does the query need a dynamic identifier? │
│ (table name, column name, sort order) │
│ │ │
│ ┌────┴────┐ │
│ │ │ │
│ NO YES │
│ │ │ │
│ ▼ ▼ │
│ Parameterize │ Is it from user input? │
│ │ │
│ ┌────┴────┐ │
│ │ │ │
│ NO YES │
│ │ │ │
│ ▼ ▼ │
│ Safe Allowlist mapping │
│ (code) (reject non-allowed) │
└──────────────────────────────────────────────┘Second-Order Injection
┌──────────────────────────────────────────────┐
│ STAGE 1: Registration │
│ INSERT INTO users (username) VALUES (%s) │
│ Input: "admin'--" │
│ Stored safely as literal string │
│ │ │
│ ▼ │
│ STAGE 2: Password change │
│ SELECT username FROM users WHERE id = %s │
│ Retrieved: "admin'--" │
│ │ │
│ ▼ │
│ UNSAFE: "UPDATE users SET password='...' │
│ WHERE username = '" + username + "'"│
│ Becomes: WHERE username = 'admin'--' │
│ The -- comments out the rest → ALL rows │
│ updated ❌ │
│ │ │
│ ▼ │
│ SAFE: Parameterize every query, even with │
│ data from your own database. │
└──────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Root cause | User input concatenated into query string |
| Primary defense | Parameterized queries / prepared statements |
| Placeholder examples | ?, $1, %s, @param |
| Input validation | Secondary, allowlist expected formats |
| Least privilege | Limits blast radius |
| ORM caveat | Raw query methods bypass parameterization |
| Dynamic identifiers | Allowlist mapping, not parameters |
| Second-order | Parameterize DB-sourced values too |
| Stored procedures | Safe only without internal dynamic SQL |
Key takeaways:
- Parameterized queries are the fix. They send query structure and data on separate channels, so input is never interpreted as SQL .
- String formatting is concatenation. f-strings, template literals,
.format(), and%all produce a merged string before the driver sees it . - ORMs are safe until you use raw queries. The escape hatch bypasses parameterization entirely .
- Dynamic identifiers require allowlists. Table names, column names, and sort order cannot be parameterized .
- Second-order injection is real. Data stored safely can become dangerous when retrieved and concatenated later .
- Stored procedures are not automatically safe. Internal dynamic SQL reintroduces the vulnerability .
- Least privilege is the control most teams skip. It does not prevent injection, but it limits what an injection can do .
Remember: SQL injection is solved by one discipline applied consistently: never build a query by concatenating user input. Use parameterized queries everywhere, including in ORM raw queries and in queries that use data retrieved from the database. Use allowlists for the identifiers that cannot be parameterized. Apply least privilege to the database account so that a failure is contained. Validate input as a secondary layer, not as a substitute. The vulnerability persists not because the fix is unknown, but because the discipline is applied unevenly—and one unparameterized query in an otherwise secure codebase is enough.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!