Defensive Configuration, trusted_schema, Extension Loading, and Untrusted Databases

Learning outcomes

A SQLite database file contains more than passive rows. Its schema can contain views, triggers, CHECK expressions, generated columns, expression/partial indexes, and virtual-table declarations. If your process registers privileged application-defined functions or virtual tables, a malicious schema may try to invoke them. Current SQLite provides multiple defenses, but the correct mental model is attack-surface reduction, not “set one PRAGMA and the file becomes safe.”

Threat model 1: untrusted SQL text

Lesson 1 prevented ordinary value injection by binding parameters. Some tools intentionally accept whole SQL programs—database consoles, report builders, migration runners, or educational sandboxes. In that case the input is code, not data. SQLite’s official security guidance recommends defensive mode, reduced run-time limits, optional authorizer callbacks, execution interruption, and memory limits according to the application’s needs.

Threat model 2: untrusted database files

An application may open a database created elsewhere while also registering a custom SQL function such as read_secret(), send_message(), or a virtual table that touches privileged resources. A malicious schema could attempt to reference such functions from views, triggers, CHECK constraints, defaults, generated columns, or indexes when SQLite evaluates schema expressions.

-- Imagine the host application registers a function privileged_lookup().-- A database from outside the trust domain might contain something like:CREATE VIEW attacker_view ASSELECT privileged_lookup() AS leaked_value;-- The risk appears when the privileged host opens and uses the schema,-- not because CREATE VIEW itself has network powers.

trusted_schema: do not automatically trust schema expressions

PRAGMA trusted_schema=OFF is the SQL-level control corresponding to SQLITE_DBCONFIG_TRUSTED_SCHEMA. Current SQLite defaults trusted schema to ON for legacy compatibility, but SQLite’s documentation advises applications to turn it off where possible. With trust disabled, functions used from schema contexts must carry the appropriate innocuous/direct-only properties, and virtual tables referenced by schema code face similar restrictions.

PRAGMA trusted_schema = OFF;PRAGMA foreign_keys = ON;-- Verify connection-scoped policy rather than assuming it:PRAGMA trusted_schema;PRAGMA foreign_keys;

This may break a schema that legitimately relies on application-defined functions in generated columns, indexes, triggers, or views. That is a compatibility signal: either redesign/tag the functions correctly in the C API, or explicitly document why the schema is trusted. Do not silently flip the setting back on globally.

Lab: observe trusted_schema blocking an application function

Python’s standard create_function() is enough to demonstrate the boundary because it does not tag the function SQLITE_INNOCUOUS. The same function works in direct SQL but is rejected when invoked through an untrusted schema object after trusted schema is disabled.

import sqlite3con = sqlite3.connect(":memory:")con.create_function("app_secret", 0, lambda: "demo-secret")con.execute("CREATE VIEW v_secret AS SELECT app_secret() AS value")print(con.execute("SELECT app_secret()").fetchone())   # direct useprint(con.execute("SELECT * FROM v_secret").fetchone()) # legacy trusted schemacon.execute("PRAGMA trusted_schema=OFF")try:    con.execute("SELECT * FROM v_secret").fetchone()except sqlite3.OperationalError as exc:    print("blocked:", exc)# Direct invocation is still a separate application policy decision.print(con.execute("SELECT app_secret()").fetchone())

SQLITE_DBCONFIG_DEFENSIVE

Defensive mode is a C-level connection configuration (also exposed by some language bindings). Current SQLite documents that it disables language features that ordinary SQL can use to deliberately damage the database file, including enabling writable_schema, setting journal_mode=OFF, directly assigning schema_version, writes to sqlite_dbpage, and direct writes to shadow tables.

import sqlite3con = sqlite3.connect("suspect.sqlite")if hasattr(con, "setconfig") and hasattr(sqlite3, "SQLITE_DBCONFIG_DEFENSIVE"):    con.setconfig(sqlite3.SQLITE_DBCONFIG_DEFENSIVE, True)con.execute("PRAGMA trusted_schema=OFF")# Confirm defensive mode if getconfig is available.if hasattr(con, "getconfig"):    print("defensive:", con.getconfig(sqlite3.SQLITE_DBCONFIG_DEFENSIVE))# Do not infer that all untrusted-file risks are solved by this flag.

Extension loading is native-code execution

A loadable SQLite extension is a shared library/DLL loaded into the application process. SQLite deliberately keeps run-time extension loading off by default in the application C API. If an application truly needs it, current SQLite recommends enabling the C API selectively with SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION rather than enabling the SQL load_extension() function as well.

Current optional capability reduction

SQLite 3.49.0 added SQLITE_DBCONFIG_ENABLE_ATTACH_CREATE, SQLITE_DBCONFIG_ENABLE_ATTACH_WRITE, and SQLITE_DBCONFIG_ENABLE_COMMENTS. An application that accepts constrained SQL may use the ATTACH controls to prevent SQL from creating attached database files or opening attached databases for writing. These are C/API capability controls, not portable SQL PRAGMAs, and older runtime libraries do not expose them.

// C-style pseudocode: requires a SQLite runtime that exposes these options.sqlite3_db_config(db, SQLITE_DBCONFIG_DEFENSIVE, 1, &oldValue);sqlite3_db_config(db, SQLITE_DBCONFIG_TRUSTED_SCHEMA, 0, &oldValue);sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 0, &oldValue);// SQLite 3.49.0+ capability reductions when appropriate:sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_ATTACH_CREATE, 0, &oldValue);sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_ATTACH_WRITE, 0, &oldValue);

The sqlite3 CLI safe mode

The current sqlite3 shell supports --safe, which disables shell features that can have dangerous side effects outside the main database, including many file/system operations. If a carefully audited script genuinely requires one normally blocked operation, --nonce plus the matching .nonce can temporarily allow the next statement/dot-command. SQLite’s own documentation describes this bypass as dangerous and recommends using it sparingly.

# Current sqlite3 CLI concept. Use a disposable working directory.sqlite3 --safe --readonly suspect.sqlite# Inside the shell, inspect data/schema with read-only SQL..tables.schemaPRAGMA integrity_check;# Do not bypass safe mode merely because a script fails.

Safe mode is a shell policy, not a guarantee that a malicious database has no parser/engine exploit. Keep SQLite patched and isolate truly hostile processing with OS/container sandboxing and resource limits appropriate to the risk.

Untrusted-file risk checklist

[ ] Open read-only unless writing is an explicit requirement.[ ] Use a current patched SQLite library and record sqlite_source_id().[ ] Set trusted_schema=OFF where compatible.[ ] Enable SQLITE_DBCONFIG_DEFENSIVE where binding exposes it.[ ] Keep loadable extensions disabled; never load arbitrary binaries.[ ] Inventory custom SQL functions/virtual tables and their side effects.[ ] Reduce ATTACH/write/create capabilities when current API + workload permit.[ ] Apply sqlite3_limit()/authorizer/time/memory constraints if accepting SQL code.[ ] Avoid privileged process identity and unnecessary filesystem/network access.[ ] Treat schema, triggers, virtual tables, and data as hostile input.[ ] Validate expected application_id/user_version/schema before normal workflows.[ ] Run integrity/business checks in a disposable/quarantined workflow.[ ] Do not overwrite the original evidence/source file during inspection.

Checkpoint

Bridge to deployment

Security controls operate inside a deployment environment. A database on a correct local filesystem inside one process has different failure and locking assumptions from a database bind-mounted into replicas or opened over a network share. Lesson 4 turns those infrastructure choices into explicit SQLite deployment patterns.

Authoritative references

• SQLite: Defense Against The Dark Arts
• SQLite database connection configuration options
• SQLite extension loading security
• SQLite command-line shell safe mode