Magellan and SQLite's writable shadow tables

Allowing someone to issue SQL does not necessarily mean allowing that person to execute native code inside the database process. A query service can intentionally accept statements while relying on the database engine to interpret them safely. Magellan concerns a failure in that separation, where corrupted full-text index data could affect native memory operations.

In his 18 December 2018 explanation, Richard Hipp describes an FTS3 integer overflow that could lead to a buffer overrun after an attacker changed shadow tables and ran queries using them. The relevant configuration enables FTS3 or FTS4 and permits attacker-controlled SQL. He identifies SQLite 3.26.0 as fixing the bug.

That account makes the application's SQL permissions central to understanding exposure. Receiving a string as an ordinary bound data value is different from executing it as a statement. A service that deliberately accepts statements also needs a policy for which database objects those statements can modify.

I value SQLite's ability to put a database inside an application. That arrangement also puts the engine's native code inside the application's process. A memory-safety defect in an enabled extension therefore deserves attention even when the application has no separate database server to expose.

Shadow tables contain executable assumptions about data

FTS presents a virtual table to a caller while keeping index information in supporting tables. The FTS3 implementation in the 3.26.0 release contains the code that interprets those stored index structures. A stored blob can be valid as a SQLite value without being a valid full-text index node.

This creates a second interpretation step. The core engine retrieves bytes from a table. The extension decodes those bytes according to its own format and uses lengths and offsets to locate information. If those numbers are inconsistent, a decoder needs to reject the structure before using it to guide memory operations.

A shadow table is ordinary enough to be addressable through SQL, yet its contents embody assumptions maintained by the extension. Directly changing those contents bypasses the ordinary operations that keep the full-text index coherent. This is useful background for the attack precondition, because permission to write arbitrary tables can reach internal extension state.

The release's implementation includes corruption checks around node decoding and term lengths. Such checks express a requirement about relationships among bytes, rather than merely about their SQL types. A value being a blob does not establish that an encoded suffix fits within it or that a decoded reference points to available data.

Application authorization and defensive parsing address different stages. Restricting statements can stop an untrusted caller from creating malformed internal state through SQL. Validating an internal structure protects the interpreter when malformed state nonetheless reaches it. Neither task is adequately described by asking only whether the database file opens.

Defensive mode removes ways to corrupt the file through SQL

The SQLite 3.26.0 release notes, dated 1 December, introduce SQLITE_DBCONFIG_DEFENSIVE. They describe it as disabling the ability to create corrupt database files through ordinary SQL. The same release adds support for read-only shadow tables when that option is enabled.

The versioned C API header lists the operations disabled by the flag. They include enabling PRAGMA writable_schema, writes to the sqlite_dbpage virtual table and direct writes to shadow tables. These operations have a common property. They can bypass the ordinary relationships maintained by higher-level database operations.

The option applies to a database connection. That scope matters in an application using several connections for different purposes. Enabling a flag for a query-serving connection does not describe the configuration of every other connection the application may open.

Defensive mode also has a narrower purpose than making a connection read-only. Its documented restrictions concern ways to corrupt internal state. A permitted application write through an ordinary table interface is a different operation from a direct write into an extension's supporting table.

This allows a useful separation between application mutation and internal maintenance. The application can need to add or update records without needing permission to edit the representation of an index. A database engine can preserve that distinction even when the SQL language offers lower-level facilities for specialized tools.

For me, the useful design choice is to expose that restriction at the connection boundary. A service accepting statements can choose an operating mode consistent with its callers' role. The mode expresses an engine-level policy instead of depending entirely on the service to recognize every dangerous internal table name.

The repair and the restriction have different jobs

Hipp distinguishes the defect repair from the new defensive option. Upgrading to the repaired engine addresses the particular overflow. Enabling the option reduces the ways untrusted SQL can produce corrupt state. Those are complementary actions with separate reasons.

A restriction on shadow-table writes cannot establish that every decoder is correct. A file can reach an application through another route, or a different defect can create an inconsistent structure. The extension still needs to treat its input defensively. Conversely, a repaired parser does not make arbitrary internal modification a sensible capability to offer every SQL caller.

This distinction also prevents a misleading configuration recommendation. A feature introduced alongside a repair is not automatically the switch that activates the repair. The release information identifies a fixed engine and an additional operating restriction. Applications need to know which property they are relying on.

The difference is especially important for embedded libraries because build and connection choices are separate. The application's linked SQLite determines which implementation it executes. Compile-time options determine which extensions are present. Connection setup determines policies such as defensive mode.

A package version alone therefore gives an incomplete description of how an application uses SQLite. The relevant runtime may be embedded in the program, and the extension may be enabled by its build. The SQL entry point can then expose a subset of the operations that runtime supports.

Arbitrary SQL needs a defined authority

A service that accepts a complete statement has a different contract from a service that accepts a search term. The latter can bind the term as data. The former intentionally gives a caller some ability to choose operations and name database objects, so it needs to define the extent of that authority.

A full-text search interface does not inherently require its user to update a segment directory. A reporting interface does not inherently require writable schema metadata. These examples describe capabilities that an application can separate even when each is expressible through SQL.

The versioned header makes the separation concrete by naming prohibited lower-level operations. A caller can understand why direct writes to shadow tables fall outside a normal data-editing role. The distinction also helps maintainers avoid treating every table visible through introspection as an equally appropriate target for application writes.

This is a design question even for a program whose database never leaves one machine. A local process can execute statements received from another trust domain. The location of the file does not establish the origin or authority of the SQL that reaches it.

The disclosed failure makes that relationship worth documenting in an application. Which component supplies statements, which component binds values, and which connection runs them are facts needed to reason about permissions. They are more informative than a blanket label such as local database or private database.

A native library shares the application's privileges

SQLite operates within its host process, so native memory operations occur under that process's execution context. A database error and unintended native code execution have different consequences for the host. The former can be represented through a return code, while the latter crosses an intended interpreter boundary.

That is why existing permission to execute SQL does not make this defect irrelevant. An application may grant a caller access to a restricted data language while relying on the library to keep execution within that language. Internal corruption that reaches memory-unsafe behavior violates that expectation.

At the same time, exposure depends on the stated configuration and authority. The FTS implementation has to be present, and the described route involves malicious changes to its supporting tables. A claim about every application containing SQLite would omit the conditions that explain the failure.

I consider the practical lesson to be specific to those interfaces. The repaired 3.26.0 engine addresses the disclosed FTS defect, while its defensive flag prevents a connection's ordinary SQL from directly changing several forms of internal storage. An application accepting untrusted statements can use that restriction to preserve a narrower distinction between editing its records and editing the structures SQLite uses to interpret them.