SQLite 3.25.3 tightens corruption handling and query rules

A database file can be structurally readable while an extension's internal records contain impossible lengths or inconsistent references. SQLite's core storage machinery cannot by itself establish that every full-text index node is meaningful. The extension that interprets those bytes needs its own checks, including checks for data that ordinary SQL operations would never generate.

SQLite 3.25.3, released on 5 November 2018, strengthens defenses against deliberately corrupted database files. It also restricts window functions in recursive queries and corrects behavior involving virtual tables. The common concern is the engine's treatment of inputs that cross between components with different assumptions.

I value SQLite's file format because an application can retain and exchange its data without a server. That usefulness makes defensive interpretation of the file especially important. A database is also an input to a native library, even when it arrives with a familiar extension and opens successfully.

Full-text storage has its own structure

FTS3 exposes a virtual table to SQL, but its implementation keeps supporting information in ordinary tables. The FTS3 source shipped with 3.25.3 calls these shadow tables. The code names content, segment and segment-directory tables among the objects associated with a full-text table.

Those tables contain information the extension interprets as index structures. A blob holding an index node is a valid SQLite value even if the bytes inside it do not describe a valid node. Core table storage and full-text index storage therefore have distinct notions of validity.

The corruption regression tests in this release demonstrate that distinction. A test creates a full-text table, inserts documents and inspects a root value in its segment directory. It then changes that value through SQL and issues a full-text search.

The expected outcome is a malformed-database error. Another sequence changes segment data and invokes a merge operation. That operation also needs to reject malformed bytes, because index maintenance reads the same internal structures that search relies on.

These tests deliberately bypass the virtual table's normal maintenance rules. They do not claim that an ordinary inserted document should produce the corrupted structure. Their purpose is to put the parser in front of bytes that violate its assumptions and establish that it reports an error.

Lengths need checking before they guide memory access

The versioned FTS3 implementation checks a node's position against its end after decoding initial fields. It also checks term prefix and suffix lengths against the bytes available in a node. These checks prevent an encoded length from becoming unquestioned authority over the next read or copy.

A compressed term can use a prefix from the preceding term and append a new suffix. That saves space, but the decoder must establish that the prefix refers to available data and the suffix fits in the remaining buffer. Both values are part of the stored input.

The source uses a corruption return code when these relationships fail. That converts an invalid internal representation into a database error that the caller can handle. It also keeps the distinction between an empty search result and an index that cannot be interpreted correctly.

This is why internal data deserves validation even when normal writes originate in the same project. A file can be damaged independently of the code that created it. SQL access to supporting tables can also produce states the extension's public operations would not create. Defensive readers need to account for those possibilities.

The release notes make a general hardening statement rather than describing a complete security incident. As of this release, that wording supports a claim about improved corruption defenses. It does not establish an exploit method or justify attributing every later-discovered problem to this particular patch.

Recursive queries now reject a window-function case

The patch explicitly disallows window functions in the recursive part of a common table expression. That is a rule about where a calculation is valid, not the removal of ordinary window queries. The engine needs to reject a construction whose evaluation does not fit its supported recursive processing.

A recursive query generates further rows from earlier rows. A window calculation relates rows within an input set. Combining those operations inside the recursive step raises a different problem from applying a window after the recursive result has been formed.

For an application author, the useful distinction is between generating a relation and analyzing the resulting relation. Moving analysis into a separate outer query expresses those stages independently when the desired semantics allow it. The release's restriction makes the unsupported placement explicit.

The patch also fixes typeof() and length() behavior on virtual tables. These scalar functions depend on what the virtual table supplies for a column. A virtual table is a SQL abstraction over another implementation, so apparently ordinary scalar expressions can exercise an interface between the engine and extension code.

Read the patch section separately from inherited changes

The 3.25.3 release page includes the earlier changes from 3.25.0, 3.25.1 and 3.25.2 before listing this patch's own changes. Window functions and column renaming appear near the top because they belong to the release family. They are not new additions in the November patch.

The same page records earlier corrections involving window functions in views and a compatibility pragma for table renaming. Those entries explain the maintenance context. The November-specific section adds the recursive-query restriction, virtual-table scalar-function fix and corruption defenses, along with planner repairs.

One planner correction concerns a row-value expression used with a primary key containing redundant columns. Another concerns IS NOT NULL in the ON clause of a LEFT JOIN when SQLite uses the STAT4 compile-time option. These are specific combinations of schema, query and build configuration.

I find that specificity useful when assessing an embedded database update. Applications share the engine but exercise different combinations of its facilities. Version 3.25.3 improves handling at several such boundaries, with the malformed-data tests showing a concrete expectation that invalid full-text index bytes produce an error through the SQL interface.