Loadable Extensions, Built-In Extensions, and Supply-Chain Safety

Learning outcomes

SQLite can be extended with functions, collations, virtual-table modules, and even VFS implementations. That power means extension loading belongs in the software supply-chain and security model. The final lesson separates capabilities compiled into your SQLite library from native code loaded at runtime, then builds a repeatable capability checklist before Chapter 15 moves into application drivers.

“Extension” can mean several deployment shapes

Runtime extension loading is code execution

A SQLite loadable extension is a native shared library: .dll on Windows, usually .so on Linux/Unix, and .dylib on macOS. Its initialization routine can register SQL functions, collations, virtual tables, or VFSes. If the binary is malicious or compromised, SQL sandboxing cannot make its native machine code harmless.

Why application APIs disable loading by default

SQLite's core documentation says extension loading is turned off by default for security. An application must explicitly enable it before using the C loading interface or SQL load_extension() function. This default reduces the chance that untrusted SQL can cause the process to execute arbitrary native libraries.

/* Preferred application decision point: enable extension loading   only when the deployment policy explicitly requires it. */sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 1, NULL);/* Then load only an approved extension path/entry point. */sqlite3_load_extension(db, approved_path, NULL, &errmsg);/* Disable again when the loading window is over. */sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 0, NULL);

Higher-level drivers expose different wrappers—or none at all. Chapter 15 will treat driver behavior as its own contract rather than assuming the C API surface is available unchanged.

The sqlite3 CLI intentionally behaves differently

The official command-line shell enables extension loading as part of its setup, so its .load dot-command can load a trusted extension without an extra enable step. That convenience is for an interactive tool; do not infer that an application connection should enable loading globally.

-- Dot-command, not SQL:.load ./approved_extension-- Optional non-default entry point:.load ./approved_extension custom_entry_point

The course intentionally does not provide an arbitrary extension download URL. The safe lesson is the loading mechanism and trust process, not “run this unknown binary.”

Capability detection comes before loading

Many useful features may already be available in the exact SQLite library. Loading duplicate or unnecessary native code expands deployment risk without benefit.

SELECT sqlite_version() AS sqlite_version;SELECT nameFROM pragma_module_listWHERE name IN (  'fts5','fts5vocab','rtree','rtree_i32','dbstat',  'json_each','json_tree')ORDER BY name;SELECT compile_optionsFROM pragma_compile_optionsWHERE compile_options LIKE '%FTS%'   OR compile_options LIKE '%RTREE%'   OR compile_options LIKE '%DBSTAT%'   OR compile_options LIKE '%SESSION%'   OR compile_options LIKE '%OMIT_LOAD_EXTENSION%'ORDER BY compile_options;

Survey official/bundled capabilities without promising universality

Optional local lab: prove loading is disabled before trusting anything

Python's standard sqlite3 wrapper may expose enable_load_extension() when its underlying library supports loadable extensions. This lab does not load a binary. It only records runtime capabilities and demonstrates the application-side control surface.

import sqlite3con = sqlite3.connect(":memory:")print("SQLite:", sqlite3.sqlite_version)print("modules:", [r[0] for r in con.execute("PRAGMA module_list")])print("has enable_load_extension:", hasattr(con, "enable_load_extension"))# Do not call load_extension() with an unreviewed path.con.close()

On the generation environment, Python is linked to SQLite 3.46.1 and exposes enable_load_extension(); its module list already includes FTS5, RTree, dbstat, JSON traversal modules, and others, so no external binary is needed for this chapter's labs.

Portability cost: the database file can outlive the extension environment

An ordinary SQLite database file is famously portable, but a schema can contain virtual tables whose modules are unavailable on another host. Opening the file may still be possible, yet statements touching those objects can fail because the module is missing. A deployment package therefore needs both the database schema and the executable capabilities it depends on.

Do not let SQL choose arbitrary filesystem paths

If application users can submit SQL, leaving the SQL load_extension() function enabled can turn SQL input into native-library loading. SQLite's explicit enablement model exists for a reason. Keep extension policy in trusted application/deployment code, not in an arbitrary user query channel.

Failure cases and diagnosis

Production capability checklist

Chapter recap: SQLite's extension model has three responsibilities

Semantic responsibility: know what the module actually indexes or exposes. Operational responsibility: maintain structures such as external-content FTS indexes and benchmark their costs. Security responsibility: treat native extensions as executable dependencies with a supply chain. With those boundaries established, Chapter 15 can safely move from shell labs into application drivers, connections, prepared statements, and binding.

Authoritative references

• SQLite Run-Time Loadable Extensions
• SQLite Virtual Table Mechanism and Security Considerations
• SQLite FTS5 Extension
• SQLite R*Tree Module
• SQLite Compile-Time Options