SQL Security

Privileges + injection prevention. Interview must-know — every senior screen asks about SQLi defense.

SQL Injection

User input concatenated into a query, attacker breaks out of the literal and runs arbitrary SQL.

Vulnerable:

# NEVER do this
query = f"SELECT * FROM users WHERE id = '{user_input}'"
cursor.execute(query)

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

SELECT * FROM users WHERE id = '' OR '1'='1';
-- returns all rows

If user_input = "'; DROP TABLE users; --":

SELECT * FROM users WHERE id = ''; DROP TABLE users; --';
-- table gone

Defense — Parameterized Queries

The DB driver sends query and parameters separately; parameters never parsed as SQL.

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

# Node — pg
client.query("SELECT * FROM users WHERE id = $1", [userInput]);

# Go — database/sql
db.Query("SELECT * FROM users WHERE id = ?", userInput)

# Java — PreparedStatement
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
ps.setInt(1, userInput);

Key rule: parameter ? / $1 / %s is NOT a string format. The driver type-checks and escapes — attacker input becomes data, not SQL.

Other defenses (defense-in-depth)

1. ORM/query builder — generates parameterized SQL. (Still vulnerable if you use raw string interpolation inside.)
2. Stored procedures — only if parameters used internally (procs concatenating input are still injectable).
3. Input validation — allowlist (e.g., column name from a fixed enum). Don't rely solely on regex/blocklist.
4. Least privilege DB user — app user has only SELECT/INSERT/UPDATE/DELETE on needed tables, no DROP/GRANT.
5. Escape output — protects against second-order injection (stored data later concatenated).

What does NOT prevent injection:

• Blocklist of ', --, ; — bypassable with encoding, comments, Unicode
• Stripping keywords like DROP — useless against ' OR '1'='1
• Hiding error messages — only slows attacker

Identifier injection: Parameters bind values, not identifiers (table/column names). For dynamic identifiers use a strict allowlist:

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}"

---

Privileges (DCL)

Grant minimum needed. Audit periodically.

Privilege types

• System privileges — CREATE TABLE, CREATE USER, SHUTDOWN, RELOAD. Server-wide.
• Object privileges — SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, TRIGGER. On specific table/view/proc.

GRANT

GRANT SELECT ON sales.orders TO 'analyst'@'10.0.%';
GRANT SELECT, INSERT, UPDATE ON sales.* TO 'app_user'@'%';
GRANT ALL PRIVILEGES ON sales.* TO 'admin'@'localhost';
GRANT EXECUTE ON PROCEDURE sales.transfer_funds TO 'app_user'@'%';

-- Column-level
GRANT SELECT (id, name, email) ON customers TO 'support'@'%';

-- WITH GRANT OPTION — recipient can re-grant
GRANT SELECT ON sales.* TO 'team_lead'@'%' WITH GRANT OPTION;

REVOKE

REVOKE INSERT ON sales.* FROM 'analyst'@'10.0.%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'%';

Roles (Postgres / MySQL 8+)

Group privileges, assign role to users. Easier than per-user GRANTs.

CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT read_only TO alice, bob;

Show what a user has

-- MySQL
SHOW GRANTS FOR 'analyst'@'%';

-- Postgres
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'analyst';

---

Least Privilege Patterns

Never let the app run as DB root. SQLi against a least-privileged user limits blast radius — attacker can read/write app tables but can't drop the database or escalate.

---

Connection Security

• TLS — REQUIRE SSL on user grants. Encrypt traffic.
• No password in code — secrets manager / env / Vault.
• Network isolation — DB in private subnet, no public IP.
• Audit logging — log connection attempts, privilege changes, sensitive table access.

---

Encryption

• At rest — TDE (Transparent Data Encryption) — disk-level, transparent to queries.
• In transit — TLS to client.
• Column-level — encrypt sensitive cells (AES_ENCRYPT in MySQL, pgcrypto in Postgres). App holds key, DBA can't read.
• Hashing for passwords — bcrypt/argon2, never reversible encryption.

---

Cross-refs

• [[DDL DML DCL TCL]] — GRANT/REVOKE syntax
• [[Views, Procedures, Triggers]] — views as security layer
• [[Transactions]] — isolation level + concurrent-update bugs