← DBMS

Database Security — SQL Injection, Encryption & Least Privilege

Watch: "Running an SQL Injection Attack - Computerphile" — Computerphile

Database security is not one feature, it is several independent layers that each assume the layer above will eventually fail: parameterized queries so user input cannot become code, least-privilege accounts so a compromised application can do limited damage, encryption so stolen disks or sniffed traffic are useless, row-level rules so one tenant cannot read another's data, and careful handling of credentials so the keys to all of it do not leak. Interviewers probe this because SQL injection has been a top web vulnerability class for two decades and the fix is well known, so what they are really testing is whether you think in layers and can say what each control does and does not protect against. For a Backend or AI engineer it matters doubly, since LLM-generated SQL, multi-tenant RAG stores and agent tools that touch a database all reintroduce injection and over-privilege problems in new clothes.

Deep Dive: SQL Injection and Why Parameterization Fixes It

Vulnerable: input is concatenated INTO the SQL text, then parsed.

  sql = "SELECT id, name, role FROM users WHERE name = '" + name + "'"

  name = bob
    -> SELECT ... WHERE name = 'bob'                      (intended)
  name = x' OR '1'='1
    -> SELECT ... WHERE name = 'x' OR '1'='1'             (condition is now always true)
  name = x' UNION SELECT id, name, password_hash FROM users --
    -> SELECT ... WHERE name = 'x'
       UNION SELECT id, name, password_hash FROM users --'  (data from another column leaks)

Parameterized: the SQL text is FIXED, the value travels separately.

  sql = "SELECT id, name, role FROM users WHERE name = ?"
  params = (name,)         -> the whole string is ONE value, never parsed as SQL

The root cause is that the database parses whatever text it receives, and concatenation lets attacker-controlled characters (a quote, a comma, a comment marker) change the structure of the statement. A parameterized query separates the two channels: the statement with placeholders is what gets parsed, and the values are bound afterwards as pure data, so no value can ever become a keyword or an operator. That is why parameterization is the primary defence and why "escape the quotes yourself" is not: escaping is a fragile attempt to keep both channels in one string, and it breaks on encodings, numeric contexts and the next unexpected character. One honest nuance is that some drivers do the binding on the client and interpolate a safely quoted value into the text, while others use true server-side binding; both are safe when implemented correctly, but the guarantee comes from the library, not from your own string formatting.

Deep Dive: What Parameters Cannot Protect

Placeholders bind VALUES only. They cannot stand in for:
   - table or column names        SELECT * FROM ?            (invalid)
   - ORDER BY column / direction  ORDER BY ?                 (sorts by a constant, not a column)
   - SQL keywords / operators

For those, choose from an allow-list in code, never from raw input:

   ALLOWED_SORT = {"name", "role"}
   if col not in ALLOWED_SORT: reject
   sql = f"SELECT name FROM users ORDER BY {col}"     -- only known-good text is inserted

Other places injection survives:
   - ORM "raw" query escape hatches built with string formatting
   - stored procedures that build dynamic SQL by concatenation inside the database
   - second-order injection: a malicious value is stored safely, then later
     concatenated into a query by a different code path
   - LLM-generated SQL: model output is untrusted input, run it with a read-only,
     tightly scoped role

Parameterization protects the value positions of a statement, so any part of the query that must vary structurally needs a different control: an allow-list, or a lookup from a user-supplied key to a hard-coded fragment. Second-order injection is the case that catches careful teams, because the insert was safe and the value looks trusted when it is read back; the rule is to parameterize every query regardless of where the data came from. Since no single control is perfect, injection is also the reason the next layer exists: if an attacker does find a hole, what they can do should be limited by the account the application connects as.

Deep Dive: Least Privilege

-- PostgreSQL (illustrative, not run here)
-- One role per purpose, each with only what it needs.

CREATE ROLE app_rw  LOGIN PASSWORD '...';   -- the web service
CREATE ROLE app_ro  LOGIN PASSWORD '...';   -- reporting / read-only tools
CREATE ROLE migrator LOGIN PASSWORD '...';  -- schema changes, used only in deploys

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_rw, app_ro;

GRANT SELECT, INSERT, UPDATE ON orders, order_items TO app_rw;   -- no DELETE, no DDL
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;           -- read only
-- app_rw is NOT a superuser, does NOT own the tables, cannot DROP or ALTER.

-- Sensitive columns: expose a subset through a view or column grants
GRANT SELECT (id, name, role) ON users TO app_ro;                -- no password_hash

Least privilege means an account gets only the permissions its job needs and nothing more, so the blast radius of any single bug or stolen credential is bounded. The application role usually needs data-manipulation rights on specific tables and no rights to create, alter or drop objects, and it certainly should not be a superuser or the object owner (owners can typically bypass some controls and change grants). Separating a migration role from the runtime role means a SQL injection in a web request cannot run DROP TABLE even if the flaw exists. The trade-off is operational: more roles and grants to manage, grant drift when new tables are added (default privileges help), and migrations that must use a different credential. It is cheap compared with the alternative of one all-powerful account shared by every service.

Deep Dive: Encryption in Transit, at Rest, and Row-Level Security

THREAT                              CONTROL                          LIMITS       
-----------------------------------------------------------------------------------------
Network sniffing / MITM             TLS to the database              a stolen disk, a malicious query
  (in transit)                      (verify server cert, e.g.
                                    sslmode=verify-full)

Stolen disk, snapshot, backup       Disk / volume encryption,        an attacker who can log in and
  (at rest)                         TDE where the engine has it,     run SELECT: the DB decrypts
                                    encrypted backups                transparently for them

Compromised DB admin or app         Application-level (field)        gives up server-side search,
  reading sensitive columns         encryption, keys held outside    indexing and sorting on that column
                                    the database

One tenant reading another's rows   Row-level security policy        superusers and (by default)
                                                                     the table owner bypass it

-- PostgreSQL row-level security (illustrative, not run here)
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;          -- apply to the table owner too
CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- per request, inside the transaction:
BEGIN;
SET LOCAL app.tenant_id = '5f0c...';    -- SET LOCAL lasts only until COMMIT/ROLLBACK
SELECT * FROM documents;                -- only this tenant's rows are visible
COMMIT;

Each control answers a specific threat and none of them answers all of them. TLS protects data on the wire; disk-level encryption protects the medium when it is lost or a snapshot leaks, but once the database is running and a legitimate login runs a query, the data is served in plain form, so at-rest encryption does nothing against SQL injection or stolen application credentials. PostgreSQL has no built-in transparent data encryption in its core, so at rest usually means volume or cloud-KMS encryption underneath it, whereas some other engines (for example SQL Server and MySQL/InnoDB) offer TDE or tablespace encryption. Row-level security pushes the "which rows may this caller see" rule into the database itself so a missing WHERE tenant_id = ... in application code fails closed. In PostgreSQL that is native (SQL Server has a comparable feature, while MySQL has no native equivalent and relies on views or application logic). Using SET LOCAL inside a transaction also keeps the tenant setting safe when connections are pooled in transaction mode, which is exactly where a plain session-level SET would leak between requests.

Deep Dive: Credentials and Secrets

Bad                                       Better
-----------------------------------------------------------------------------
password in source code / git history     secret manager (Vault, AWS Secrets Manager,
password in a Docker image layer          Kubernetes secrets with encryption) injected at runtime
same DB user shared by every service      one credential per service, least-privilege role
password never rotated                    scheduled rotation, or short-lived / dynamic credentials
                                          (IAM database auth, Vault dynamic secrets)
connection string written to logs         redact secrets in logs and error messages
user passwords stored encrypted/plain     user passwords stored as a slow salted hash (argon2, bcrypt, scrypt)

Once a credential is committed to a repository or baked into an image it should be treated as leaked, because history and layers are copied widely; the only fix is to rotate it. Short-lived or dynamically issued credentials shrink the window in which a stolen one is useful and make rotation routine instead of an outage. Note the separate rule for end-user passwords: they should be hashed with a deliberately slow, salted algorithm rather than encrypted, because encrypted data can be decrypted by whoever holds the key and a login system never needs to recover the original password. The demo in the next section shows the injection part of this page with real output.

Key Pointers

• SQL injection happens when untrusted input becomes part of the SQL text that is parsed; parameterized queries fix it by sending the values on a separate channel so they can never be parsed as SQL.
• Escaping quotes by hand is not a substitute for parameterization; it is fragile across encodings, numeric contexts and drivers.
• Placeholders bind values only. Table names, column names and ORDER BY targets need an allow-list, and dynamic SQL inside stored procedures and ORM raw-query escape hatches can still be injectable.
• Least privilege limits blast radius: the app role should not be superuser or owner, should not run DDL, and should have only the table and column privileges it needs; use a separate role for migrations and read-only tools.
• Encryption in transit (TLS with certificate verification) and at rest (disk, TDE, encrypted backups) protect different threats; neither stops an attacker who can run authorized queries through the app.
• Row-level security enforces per-row visibility inside the database (native in PostgreSQL and SQL Server); remember table owners and superusers bypass it unless forced, and pair it with SET LOCAL when using transaction-mode pooling.
• Keep secrets out of code, images and logs; use a secret manager, one credential per service, and rotation or short-lived credentials. Hash user passwords with a slow salted hash; never encrypt them reversibly.
• Treat LLM-generated SQL and any downstream tool input as untrusted: run it with a read-only, narrowly scoped role and constrained statements.

How It Works

1. The app receives untrusted input (a form field, query string, JSON body, or model output) that will influence a query.
2. The driver sends the statement text with placeholders, and the values separately (server-side binding, or client-side quoting done correctly by the library); the database parses the fixed text only.
3. The connection is authenticated as a role that holds only the privileges that service needs, so even a flaw that changes what a query does is confined to those grants.
4. The connection is protected by TLS, and the server, disk and backups sit on encrypted storage so interception or stolen media yield ciphertext.
5. Where multi-tenant isolation is required, a row-level policy filters every statement by the tenant identity set for that transaction, independent of what the application query says.
6. Credentials are fetched from a secret store at startup, rotated on a schedule, never logged, and audited, so compromise of one component does not hand out reusable master access.

Try It Yourself

# Python 3 (standard library only). SQL injection vs parameterized queries with sqlite3.
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, role TEXT, password_hash TEXT);
INSERT INTO users VALUES (1, 'alice', 'admin',  'hash_a1');
INSERT INTO users VALUES (2, 'bob',   'member', 'hash_b2');
INSERT INTO users VALUES (3, 'carol', 'member', 'hash_c3');
""")

def vulnerable(name):
    sql = "SELECT id, name, role FROM users WHERE name = '" + name + "'"   # string concatenation
    print("  SQL sent :", sql)
    return db.execute(sql).fetchall()

def safe(name):
    sql = "SELECT id, name, role FROM users WHERE name = ?"                # placeholder
    print("  SQL sent :", sql, " params =", (name,))
    return db.execute(sql, (name,)).fetchall()

benign  = "bob"
attack1 = "x' OR '1'='1"
attack2 = "x' UNION SELECT id, name, password_hash FROM users --"

for label, payload in [("benign", benign), ("tautology", attack1), ("UNION exfiltration", attack2)]:
    print(f"== {label}: input = {payload!r}")
    print("vulnerable:"); print("  rows     :", vulnerable(payload))
    print("parameterized:"); print("  rows     :", safe(payload))
    print()

# Placeholders bind VALUES only, never identifiers, so ORDER BY / table names need an allow-list
ALLOWED_SORT = {"name", "role"}
def sorted_users(col):
    if col not in ALLOWED_SORT:
        raise ValueError(f"sort column {col!r} not allowed")
    return db.execute(f"SELECT name FROM users ORDER BY {col}").fetchall()

print(sorted_users("name"))
try:
    sorted_users("name; DROP TABLE users")
except ValueError as e:
    print("rejected:", e)

# Output (real run):
# == benign: input = 'bob'
# vulnerable:
#   SQL sent : SELECT id, name, role FROM users WHERE name = 'bob'
#   rows     : [(2, 'bob', 'member')]
# parameterized:
#   SQL sent : SELECT id, name, role FROM users WHERE name = ?  params = ('bob',)
#   rows     : [(2, 'bob', 'member')]
#
# == tautology: input = "x' OR '1'='1"
# vulnerable:
#   SQL sent : SELECT id, name, role FROM users WHERE name = 'x' OR '1'='1'
#   rows     : [(1, 'alice', 'admin'), (2, 'bob', 'member'), (3, 'carol', 'member')]
# parameterized:
#   SQL sent : SELECT id, name, role FROM users WHERE name = ?  params = ("x' OR '1'='1",)
#   rows     : []
#
# == UNION exfiltration: input = "x' UNION SELECT id, name, password_hash FROM users --"
# vulnerable:
#   SQL sent : SELECT id, name, role FROM users WHERE name = 'x' UNION SELECT id, name, password_hash FROM users --'
#   rows     : [(1, 'alice', 'hash_a1'), (2, 'bob', 'hash_b2'), (3, 'carol', 'hash_c3')]
# parameterized:
#   SQL sent : SELECT id, name, role FROM users WHERE name = ?  params = ("x' UNION SELECT id, name, password_hash FROM users --",)
#   rows     : []
#
# [('alice',), ('bob',), ('carol',)]
# rejected: sort column 'name; DROP TABLE users' not allowed

-- PostgreSQL (cannot be run in sqlite; expected behaviour in words)
-- After the ENABLE/FORCE ROW LEVEL SECURITY and CREATE POLICY shown earlier, connect as app_rw and:
--   BEGIN; SET LOCAL app.tenant_id = '<tenant A uuid>'; SELECT count(*) FROM documents; COMMIT;
-- returns only tenant A's rows. Running the same SELECT without setting app.tenant_id errors,
-- because current_setting() fails on an unset setting (the missing-setting case fails closed
-- unless you use the two-argument form with missing_ok, which returns NULL and so matches no rows).

Interview Q&A

Q: What is SQL injection and how do parameterized queries prevent it?

A: SQL injection is when untrusted input is concatenated into SQL text, so characters like a quote or comment marker change the structure of the statement, letting an attacker bypass conditions, read other tables with UNION, or modify data. Parameterized queries send the statement with placeholders and the values separately, so the database parses only the fixed text and treats each value strictly as data. The trade-off is that placeholders cover values only, so dynamic identifiers or sort columns still need an allow-list, and parameterization does not help if the injectable string is built inside a stored procedure.

Q: What is the principle of least privilege for a database, and how do you apply it?

A: Every account gets only the permissions its job needs, so a bug or stolen credential has a bounded blast radius. In practice that means separate roles for the application, read-only reporting and schema migrations; the app role gets DML on specific tables (and column-level grants for sensitive columns), no DDL, and is never a superuser or the table owner. The cost is more roles and grants to maintain, but it means even a successful SQL injection cannot drop tables or read tables the service never needed.

Q: Encryption at rest versus in transit: what does each protect against, and what does neither protect against?

A: In transit (TLS, ideally verifying the server certificate) protects against eavesdropping and man-in-the-middle attacks on the network path. At rest (disk or volume encryption, TDE where the engine supports it, encrypted backups) protects against a stolen disk, snapshot or backup file. Neither helps against someone who can authenticate and run queries, including through SQL injection or leaked application credentials, because the database decrypts data transparently for legitimate sessions; for that you need least privilege, field-level encryption with keys held outside the database, and auditing.

Q: What is row-level security and when would you use it?

A: Row-level security attaches a policy to a table so the database itself filters which rows a caller can read or write, typically by tenant or owner, instead of relying on every query remembering a WHERE clause. It is valuable for multi-tenant SaaS and shared vector stores because a missing filter fails closed rather than leaking data. The costs are per-query policy overhead, subtler performance and debugging, and bypass paths: PostgreSQL superusers and, unless you use FORCE ROW LEVEL SECURITY, the table owner skip policies; and with pooled connections the tenant context must be set per transaction with SET LOCAL.

Q: How should an application manage database credentials and other secrets?

A: Keep them out of source control, images and logs; inject them at runtime from a secret manager, give each service its own credential with a least-privilege role, and rotate them, ideally using short-lived or dynamically issued credentials such as IAM database authentication. Anything ever committed to a repository should be treated as compromised and rotated. The trade-off is operational complexity, since rotation requires the app and pool to pick up new credentials without downtime, but it turns a leak from a permanent breach into a bounded incident.

Follow-up: Can you parameterize a table name or an ORDER BY column? What do you do instead?

A: No, placeholders bind values, not identifiers or keywords; ORDER BY ? would sort by a constant rather than a column. Map the user's choice to a hard-coded fragment or check it against an allow-list of known column names and directions before inserting it into the text, and quote identifiers with the database's own identifier-quoting function if you must generate them. Never pass raw user input through as an identifier.

Follow-up: Why not just escape quotes, or use a web application firewall, instead of parameterizing?

A: Escaping tries to keep code and data in one string and fails on unexpected contexts (numeric fields with no quotes, multi-byte encodings, second-order use of stored values), so one missed case is a vulnerability. A WAF is a useful detection layer and can block known patterns, but it is a heuristic filter in front of the app and attackers routinely find encodings that slip through. Parameterization removes the problem at its cause, and the other layers are defence in depth on top of it.

Follow-up: If you have row-level security, do you still need tenant checks in application code?

A: You should still design the application to scope queries and authenticate correctly, since RLS covers row visibility in the database but not business authorization such as "may this user issue a refund", and it can be bypassed by superuser or owner connections and by misconfigured policies. Treat RLS as a backstop that makes mistakes fail closed, not as the only access-control layer. It is also worth testing the policies explicitly, including the unset-tenant case, because the failure modes are subtle.

System Design Tie-in

In a design interview, database security shows up as a checklist of layers rather than one component. The service connects with its own least-privilege role and TLS, secrets come from a manager with rotation, all queries use parameter binding, and the schema separates sensitive fields (password hashes and personal data) into columns or tables with tighter grants and optional application-level encryption. Multi-tenant SaaS designs choose an isolation model deliberately: a shared schema with a tenant column and row-level security is cheapest to run but has the weakest blast-radius boundary, whereas separate schemas or databases per tenant isolate better at higher operational cost, and the same question applies to embeddings in a shared vector table for a RAG product, where the tenant filter has to be enforced below the application layer. Auditing (who read or changed what), backups that are encrypted and access-controlled, and a plan for credential rotation complete the picture. If an LLM agent can generate SQL, it should connect as a read-only role on a restricted set of views, with statement timeouts and result limits, because the prompt can be manipulated just like a web form.

Trade-offs

Every layer here buys safety and charges for it. Least privilege costs grant management and can block legitimate but unanticipated queries; row-level security adds planning overhead and subtle debugging, and gives a false sense of completeness if owners or superusers connect; encryption at rest adds key-management burden and, for field-level encryption, gives up server-side searching, indexing and range queries on the encrypted column; TLS adds handshake cost, which connection pooling largely amortises. None of these is the wrong choice in principle, but each is the wrong place to stop: at-rest encryption is not an answer to injection, a WAF is not an answer to concatenated SQL, and RLS is not an answer to leaked credentials. The practical approach is to apply the cheap, high-value controls everywhere (parameterization, TLS, a non-superuser application role, no secrets in code) and layer the heavier ones (field-level encryption, per-tenant isolation, dynamic credentials) where the data's sensitivity and the threat model justify the operational cost.