SQL Injection, Prepared Statements, and Trust Boundaries

Learning outcomes

SQLite does not make string-built SQL safe simply because the database is local. If an application lets untrusted text become SQL syntax, a harmless search box can change the meaning of the statement. The durable rule is simple: SQL structure is written by the program; data values are bound separately. This lesson makes that rule concrete without relying on manual escaping tricks.

The trust boundary is not “internet versus local”

A value is untrusted when the application cannot safely assume what characters or meaning it contains. It can come from an HTTP form, a desktop search box, a CSV import, a QR code, another process, a plugin, or a synchronized device. SQL injection happens when that value is concatenated into SQL text and SQLite parses some of it as syntax.

A harmless local demonstration

The following test database contains three FieldNotes users. The vulnerable code is intentionally shown only to explain the failure. It does not modify data, touch external systems, or target a real service.

DROP TABLE IF EXISTS demo_user;CREATE TABLE demo_user(    user_id INTEGER PRIMARY KEY,    username TEXT NOT NULL UNIQUE,    display_name TEXT NOT NULL);INSERT INTO demo_user(username, display_name) VALUES ('alice','Alice'), ('bob','Bob'), ('carol','Carol');

Imagine an application constructs this query by concatenating a search value:

# Python anti-pattern: data becomes part of SQL textterm = input("username: ")sql = "SELECT user_id, username FROM demo_user WHERE username = '" + term + "'"rows = con.execute(sql).fetchall()

For the ordinary value alice, the resulting SQL is what the programmer expected. For the local test value ' OR 1=1 --, the generated predicate becomes true for every row and the comment marker removes the remaining quote. The problem is not that this string is magical; the problem is that the application allowed value bytes to become SQL grammar.

-- ordinary inputSELECT user_id, username FROM demo_user WHERE username = 'alice';-- hostile-looking local test input after unsafe concatenationSELECT user_id, username FROM demo_user WHERE username = '' OR 1=1 --';

Prepared statements keep syntax and values separate

A prepared/parameterized statement is parsed as SQL with placeholders. The application then binds values through the driver/API. Bound values do not become new keywords, operators, quotes, comments, or subqueries.

term = input("username: ")rows = con.execute(    "SELECT user_id, username FROM demo_user WHERE username = ?",    (term,),).fetchall()

Now the same malicious-looking string is simply searched as a username. Unless a row literally has that username, the result is empty. You can also store strings containing quotes, semicolons, comments, SQL keywords, or JSON punctuation as ordinary data.

payload = "Robert'); DROP TABLE demo_user;--"con.execute(    "INSERT INTO demo_user(username, display_name) VALUES (?, ?)",    ("literal-test", payload),)print(con.execute(    "SELECT display_name FROM demo_user WHERE username=?",    ("literal-test",)).fetchone()[0])# The table still exists because payload was never parsed as SQL.print(con.execute("SELECT count(*) FROM demo_user").fetchone()[0])

Parameters bind values, not arbitrary SQL syntax

Placeholders represent literal values in places where the SQL grammar permits a value expression. They are not a general text-substitution facility. Trying to bind a table or column name does not turn the bound string into an identifier.

Allowlist dynamic structure

Suppose the UI lets the user sort notes by time or severity. The application owns two legal choices and maps a small external token to hard-coded SQL. This is fundamentally different from “escape whatever the user typed.”

SORT_COLUMNS = {    "time": "occurred_at",    "severity": "severity",}SORT_DIRECTIONS = {"asc": "ASC", "desc": "DESC"}column = SORT_COLUMNS.get(requested_sort, "occurred_at")direction = SORT_DIRECTIONS.get(requested_direction, "DESC")sql = f"""SELECT note_id, occurred_at, severity, note_text          FROM maintenance_note          WHERE device_id = ?          ORDER BY {column} {direction}, note_id DESC          LIMIT ?"""rows = con.execute(sql, (device_id, page_size)).fetchall()

The only interpolated text comes from program-owned constants. The actual data values remain bound.

Why manual escaping is a poor primary defense

Hand-written quote replacement is easy to apply inconsistently, easy to forget in one code path, and does not solve dynamic identifiers or every API/type situation. SQLite itself exposes prepared/bind APIs precisely so programs do not need to rebuild a SQL lexer. Escaping/quoting helpers still have legitimate uses when generating trusted administrative SQL, but they should not replace bound parameters for application values.

Map the rule across application languages

Lab: attack the boundary, not a real system

Use a temporary database. First show that unsafe concatenation returns all three rows for the demonstration string. Then run the same value through a parameterized query and verify it returns zero rows. Finally insert the “DROP TABLE” looking display name through a parameter and verify the table remains intact.

import sqlite3, tempfilefrom pathlib import Pathwith tempfile.TemporaryDirectory() as td:    path = Path(td) / "injection.sqlite"    con = sqlite3.connect(path)    con.executescript("""      CREATE TABLE demo_user(        user_id INTEGER PRIMARY KEY,        username TEXT NOT NULL UNIQUE,        display_name TEXT NOT NULL      );      INSERT INTO demo_user(username,display_name)      VALUES ('alice','Alice'),('bob','Bob'),('carol','Carol');    """)    attack = "' OR 1=1 --"    unsafe_sql = "SELECT username FROM demo_user WHERE username='" + attack + "'"    assert len(con.execute(unsafe_sql).fetchall()) == 3    assert con.execute(        "SELECT username FROM demo_user WHERE username=?", (attack,)    ).fetchall() == []    scary = "Robert'); DROP TABLE demo_user;--"    con.execute(        "INSERT INTO demo_user(username,display_name) VALUES (?,?)",        ("literal-test", scary),    )    con.commit()    assert con.execute("SELECT count(*) FROM demo_user").fetchone()[0] == 4    con.close()print("parameter-bound values remained data")

Secure data-access checklist

[ ] All data values are bound through driver/API parameters.[ ] No user/import/network value is concatenated into SQL text.[ ] Dynamic identifiers/directions come from explicit allowlists.[ ] Multi-statement execution APIs are not used for ordinary request data.[ ] Transaction ownership is explicit and errors cause known rollback/retry behavior.[ ] Sensitive bound values are redacted from logs/telemetry where necessary.[ ] Driver/library SQLite version and feature set are recorded at runtime.[ ] Tests include quotes, semicolons, comment markers, Unicode, NULL, and long values.[ ] Security review distinguishes SQL structure from data before code review approval.

Checkpoint

Bridge to file-level authority

Prepared statements defend the SQL-text boundary. They do not stop another process that can read or replace the database file, copy a backup, inspect plaintext pages, or run its own SQLite connection. Lesson 2 moves outward to the operating-system identity and filesystem boundary that SQLite deliberately relies on.

Authoritative references

• SQLite C API: prepared statement compilation
• SQLite C API: binding values
• SQLite security guidance