There is a bug in versions 3.35.0 (2021-03-12) through 3.37.1 (2021-12-30) which could potentially cause database corruption. Upgrading to version 3.37.2 (2022-01-06) or later is recommended.

About the bug

In order to encounter this problem you must:

1. Change SQLite's default behavior so that it stores secondary journal files in memory, rather than as temporary files on disk. Secondary journals are stored on disk by default. To move to in-memory storage you need to compile with -DSQLITE_TEMP_STORE=2 or 3 or set PRAGMA journal_mode=MEMORY at run-time.

2. Use the SAVEPOINT SQL statement to create nested transactions and sometimes roll back those transactions using ROLLBACK TO.

3. You also need a measure of bad luck, as the problem is difficult to hit.

Recommended fix.

The recommended fix for this problem is to upgrade to version 3.37.2. If that is not possible for you, you should at least consider applying the one-character patch to fix the problem.

How this bug was created and how it was discovered

The bug arose from an enhancement that we installed in response to forum post e78ffd751185a67e. Repeated use of SAVEPOINT and ROLLBACK TO with an in-memory journal was causing excess memory usage. The fix was to "truncate" the in-memory journals following a ROLLBACK TO, in order to contain the memory growth.

The bug was first discovered (and reported on this forum) 10 months later by researchers at the Wingtecher Lab of Tsinghua University. Using the AFL++ fuzzer with a custom mutator, they managed to cause an assert() statement to fail. It turns out that assert() statement existed to verify that the statement journals were operating correctly, and so the failure of that assert() statement indicated a possible corruption bug. Further analysis showed that corruption was possible, though it is difficult to hit.

This bug is a sufficiently obscure corner case that it might have gone unnoticed for many years, had it not been for SQLite's heavy use of assert() statement to verify internal consistency, and for Wingtecher Lab's ground-breaking analysis tools that found a way to get one of those assert() statements to fail.