SQL Security & Injection Defense

Every senior backend screen asks about SQL injection defense — a genuinely must-know topic, not trivia.

SQL Injection — the Mechanism

# NEVER do this — user input concatenated directly into a query string
query = f"SELECT * FROM users WHERE id = '{user_input}'"

If user_input = "' OR '1'='1":

SELECT * FROM users WHERE id = '' OR '1'='1';   -- returns every row, not one

If user_input = "'; DROP TABLE users; --", the attacker terminates the intended statement and appends an entirely new one — the classic, catastrophic case.

Defense — Parameterized Queries

The driver sends the query template and parameters separately — parameter values are never parsed as SQL syntax at all, only bound as literal data.

cursor.execute("SELECT * FROM users WHERE id = %s", (user_input,))   # psycopg2/sqlite3

-- Node (pg):  client.query("SELECT * FROM users WHERE id = $1", [userInput])
-- Go:         db.Query("SELECT * FROM users WHERE id = ?", userInput)

The key rule: the placeholder (?, $1, %s) is not a string-formatting slot — the driver type-checks and escapes at the protocol level, so attacker-controlled input becomes inert data, never executable SQL, regardless of what characters it contains.

Defense-in-Depth (Parameterization Is Necessary, Not Sufficient Alone)

1. ORM/query builder — generates parameterized SQL under the hood; still vulnerable if raw string interpolation is used inside a raw-query escape hatch.
2. Least-privilege DB user — the application's DB user should have only SELECT/INSERT/UPDATE/DELETE on the tables it needs, never DROP/GRANT/superuser — this bounds the blast radius even if injection somehow occurs.
3. Allowlist for identifiers — parameters bind values, not table/column names; a dynamic ORDER BY column must be validated against a fixed allowlist, not parameterized directly:

ALLOWED_SORT = {"id", "created_at", "name"}
sort = user_sort if user_sort in ALLOWED_SORT else "id"
query = f"SELECT * FROM users ORDER BY {sort}"    # safe ONLY because sort is allowlist-validated first

What does NOT prevent injection (common false confidence): blocklisting characters like ', --, ; (bypassable via encoding, comments, alternate syntax); stripping keywords like DROP (useless against ' OR '1'='1, which contains no dangerous keyword at all); hiding error messages (only slows down an attacker's reconnaissance, doesn't close the hole).

Privileges (DCL) — Least Privilege in Practice

GRANT SELECT, INSERT, UPDATE ON sales.* TO 'app_user'@'%';
GRANT EXECUTE ON PROCEDURE sales.transfer_funds TO 'app_user'@'%';   -- exec without table access
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'%';

CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT read_only TO alice, bob;                                        -- role-based, easier to audit than per-user grants

Never let the running application connect as DB root/superuser — an injection against a least-privileged app user limits the attacker to reading/writing app tables; against a superuser connection, the same injection can drop the entire database or escalate further.

Connection & Encryption

• TLS in transit — encrypt traffic between application and database.
• No credentials in source code — a secrets manager or environment variable, never a hardcoded connection string.
• Encryption at rest (TDE) for the whole database; column-level encryption (pgcrypto in Postgres) for specific highly-sensitive fields the DBA itself shouldn't be able to read in plaintext.
• Passwords are hashed (bcrypt/argon2), never merely encrypted — hashing is one-way by design; encryption is reversible and therefore the wrong tool for credential storage.

Interview Drills

1. Why doesn't blocklisting characters like ' or keywords like DROP reliably prevent SQL injection? — Blocklists are bypassable through encoding, comment syntax, or attacks that use no dangerous keyword at all (' OR '1'='1 contains neither ' alone in a flaggable pattern nor any blocked keyword) — the only reliable defense is parameterized queries, where attacker input is never parsed as SQL syntax in the first place, regardless of its content.
2. Why is parameterization alone not "defense in depth"? — Parameterization defends against value-based injection, but doesn't protect dynamic identifiers (table/column names in an ORDER BY or dynamic table selection) — those need an explicit allowlist check, since a bound parameter can only ever represent a value, not a piece of SQL syntax.
3. An application connects to the database as the DB root/superuser "for convenience." Why is this a serious risk even if the application code has no known SQL injection bugs today? — It removes the blast-radius limit that least-privilege provides — any future injection vulnerability (or a compromised dependency executing arbitrary queries) would have unrestricted access to drop tables, alter schema, or escalate privileges, rather than being confined to the app's own data operations.

Cross-links

[[SQL Constraints & Schema DDL]] · [[SQL Views, Procedures & Triggers]] · [[SQL Transactions & ACID]] · [[API Design Principles]]