Database, File, Deserialization, and Command Security

Use separate database identities with only required privileges.

11.1 Database permissions

Use separate database identities with only required privileges. An ordinary web runtime should not normally have database-owner or schema-administration privileges. Separate migration credentials from runtime credentials where practical.

11.2 Transactions and integrity

Use transactions for multi-step state changes. Add database constraints for invariants such as uniqueness and referential integrity. Application checks alone can fail under concurrency. Prevent race conditions by combining authorization, state checks, and writes atomically where needed.

Constraints catch what application checks miss Output (CPython 3.13.1):

Ben's insert succeeded but the seat update violated the CHECK, so the transaction rolled back both: there is no enrollment without a seat. The UNIQUE constraint stops duplicates even if two requests race past an application-level check.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE courses (id TEXT PRIMARY KEY, seats INTEGER NOT NULL CHECK (seats >= 0));
CREATE TABLE enrollments (student TEXT, course TEXT REFERENCES courses(id), UNIQUE (student, course));
INSERT INTO courses VALUES ('sec-101', 1);
""")

def enroll(student: str, course: str) -> str:
    try:
        with con:   # one transaction: both statements succeed or neither does
            con.execute("INSERT INTO enrollments VALUES (?, ?)", (student, course))
            con.execute("UPDATE courses SET seats = seats - 1 WHERE id = ?", (course,))
        return "enrolled"
    except sqlite3.IntegrityError as error:
        return f"refused ({error})"

print("ana:", enroll("ana", "sec-101"))
print("ana again:", enroll("ana", "sec-101"))
print("ben:", enroll("ben", "sec-101"))
print("seats left:", con.execute("SELECT seats FROM courses").fetchone()[0],
      "| enrollments:", con.execute("SELECT count(*) FROM enrollments").fetchone()[0])

ana: enrolled
ana again: refused (UNIQUE constraint failed: enrollments.student, enrollments.course)
ben: refused (CHECK constraint failed: seats >= 0)
seats left: 0 | enrollments: 1

11.3 ORM safety

An ORM does not automatically eliminate injection. Avoid raw query concatenation and unsafe raw-query APIs. Review generated queries, dynamic ordering/filtering, and bulk update behavior. Never bind arbitrary request objects to privileged model fields.

11.4 File upload and download

Enforce byte size, count, and processing-time limits.

Use allowlists based on business need; do not trust extensions or client-provided MIME types alone.

Inspect file signatures/content as appropriate.

Generate storage names on the server.

Store uploads outside executable/public roots.

Use malware scanning or content disarm/reconstruction where the threat model requires it.

Avoid parsing complex formats in highly privileged processes.

Re-authorize every download.

Protect archives against decompression bombs and path traversal.

Consider image/document parser vulnerabilities and patching.

11.5 Deserialization

Deserialization can trigger unexpected type construction, code paths, resource use, or gadget chains in some formats and ecosystems.

Simple data formats with explicit schemas Type and field allowlists Input size and nesting limits

No arbitrary class loading or object construction from untrusted data Versioned schemas and validation

Isolated processing for complex file formats

11.6 XML security

For XML parsers, disable external entity resolution and unnecessary DTD processing according to the library's documented secure configuration. Bound entity expansion, document size, depth, and processing time. Do not assume defaults are secure across versions.

11.7 Archive extraction

Prevent archive path traversal by validating every normalized entry path remains inside the intended extraction root. Reject absolute paths and unexpected links, limit entry count and total expanded size, and consider symlinks and race conditions.

Checking every entry before extracting Output (CPython 3.13.1):

The second archive is a test fixture built in memory for this check. The function declares the whole archive invalid instead of skipping bad entries, so a partly extracted archive never appears on disk. Python's zipfile.extractall also sanitises names, but an explicit policy (entry count, expanded size, path rules) belongs to your application.

import io
import posixpath
import zipfile

MAX_ENTRIES, MAX_TOTAL = 100, 10_000_000

def check_archive(data: bytes) -> list[str]:
    problems = []
    with zipfile.ZipFile(io.BytesIO(data)) as archive:
        entries = archive.infolist()
        if len(entries) > MAX_ENTRIES:
            problems.append("too many entries")
        if sum(e.file_size for e in entries) > MAX_TOTAL:
            problems.append("expanded size too large")
        for e in entries:
            name = e.filename
            normalized = posixpath.normpath(name)
            if name.startswith("/") or "\\" in name or normalized == ".." or normalized.startswith("../"):
                problems.append(f"unsafe path: {name}")
    return problems

def make_zip(names):
    buffer = io.BytesIO()
    with zipfile.ZipFile(buffer, "w") as z:
        for n in names:
            z.writestr(n, "test")
    return buffer.getvalue()

print("good archive:", check_archive(make_zip(["docs/a.txt", "docs/b.txt"])) or "ok")
print("test archive:", check_archive(make_zip(["docs/a.txt", "docs/../../outside.txt", "/abs.txt"])))

good archive: ok
test archive: ['unsafe path: docs/../../outside.txt', 'unsafe path: /abs.txt']

11.8 Shell and process execution

Avoid shell execution where a library API can do the job. When process execution is needed:

Use a fixed executable and argument arrays.

Do not construct shell strings from user input.

Set environment variables deliberately.

Use least privilege, timeouts, resource limits, and controlled working directories.

Validate file paths and outputs.

Avoid inheriting sensitive environment variables unnecessarily.

Lab

Build a safe file-import endpoint and write tests for oversized files, wrong content type, misleading extension, traversal paths, malformed archives, unauthorized download, and parser timeout.

Quiz

? Which privileges should an ordinary web application's database identity have?
+ Only the data operations it needs, with migrations run by a separate identity :: Least privilege limits the damage of a compromise.
- Database owner, so migrations run automatically :: Gives an attacker full control after any injection bug.
- Read-only access to everything :: Probably too much read access and too little write access.
- The same identity as the DBA :: Defeats separation of duties.

? An upload's file name ends in `.png` and its MIME type is `image/png`. What do you know?
- It is a PNG image. :: Both are client-supplied claims.
+ Very little — inspect the content and enforce your own policy. :: Extensions and declared types can be anything.
- It is safe to serve from the main origin. :: Uploaded content should be served carefully, ideally from a separate origin with `nosniff`.
- It is under the size limit. :: Size must be checked separately.

? What does the transaction in the enrollment example guarantee?
+ The enrollment row and the seat update succeed or fail together. :: Atomicity prevents half-done state.
- That no two students ever enroll at once. :: Concurrency is allowed; the co
…