[HN Gopher] SQLite 3.37.2 fixes potentially database corruption bug
       ___________________________________________________________________
        
       SQLite 3.37.2 fixes potentially database corruption bug
        
       From Dr. Richard Hipp:  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][1] or set [PRAGMA
       journal_mode=MEMORY][2] at run-time.  2. Use the [SAVEPOINT][3] 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][4]. If that is not possible for you, you should at
       least consider applying the [one-character patch][p1] 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][5]. 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][6]) 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][7] 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.  [1]:
       https://www.sqlite.org/compile.html#temp_store [2]:
       https://www.sqlite.org/pragma.html#pragma_temp_store [3]:
       https://www.sqlite.org/lang_savepoint.html [4]:
       https://www.sqlite.org/releaselog/3_37_2.html [p1]:
       https://sqlite.org/src/fdiff?v1=a85f0dc5c02a4245&v2=ff4336a9...
       [5]: https://sqlite.org/forum/forumpost/e78ffd751185a67e [6]:
       https://sqlite.org/forum/forumpost/d7338bf4901f1151 [7]:
       https://www.sqlite.org/assert.html
        
       Author : marcobambini
       Score  : 22 points
       Date   : 2022-01-06 21:11 UTC (1 hours ago)
        
       | eternityforest wrote:
       | SQLite is the perfect example of a dev team I actually respect.
       | They have zero tolerance for data loss.
        
       ___________________________________________________________________
       (page generated 2022-01-06 23:01 UTC)