How To Corrupt An SQLite Database File

How To Corrupt An SQLite Database File Table Of Contents Overview An SQLite database is highly resistant to corruption. If an application crash, or an operating-system crash, or even a power failure occurs in the middle of a transaction, the partially written transaction should be automatically rolled back the next time the database file is accessed. The recovery process is fully automatic and does not require any action on the part of the user or the application. Though SQLite is resistant to database corruption, it is not immune. This document describes the various ways that an SQLite database might go corrupt. 1. File overwrite by a rogue thread or process SQLite database files are ordinary disk files. That means that any process can open the file and overwrite it with garbage. There is nothing that the SQLite library can do to defend against this. 1.1. Continuing to use a file descriptor after it has been closed We have seen multiple cases where a file descriptor was open on a file, then that file descriptor was closed and reopened on an SQLite database. Later, some other thread continued to write into the old file descriptor, not realizing that the original file had been closed already. But because the file descriptor had been reopened by SQLite, the information that was intended to go into the original file ended up overwriting parts of the SQLite database, leading to corruption of the database. One example of this occurred circa 2013-08-30 on the canonical repository for the Fossil DVCS. In that event, file descriptor 2 (standard error) was being erroneously closed (by stunnel, we suspect) prior to sqlite3_open_v2() so that the file descriptor used for the repository database file was 2. Later, an application bug caused an assert() statement to emit an error message by invoking write(2,...). But since file descriptor 2 was now connected to a database file, the error message overwrote part of the database. To guard against this kind of problem, SQLite version 3.8.1 (2013-10-17) and later refuse to use low-numbered file descriptors for database files. (See SQLITE_MINIMUM_FILE_DESCRIPTOR for additional information.) Another example of corruption caused by using a closed file descriptor was reported by facebook engineers in a blog post on 2014-08-12.

Another example of this error was reported against Fossil on 2019-07-11. A file descriptor would be opened for debugging output, but then closed and reopened by SQLite. But the debugging logic continued to write into the original file descriptor. See the forum discussion for the bug report and a link to the fix. 1.2. Backup or restore while a transaction is active Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt. There are multiple safe approaches to making backup copies of SQLite databases - safe in the sense that they are generate a correct, uncorrupted backup. In no particular order: The sqlite3_rsync utility program (available beginning with SQLite 3.47.0 (2024-10-21) and later) will make a copy of a live SQLite over SSH using a bandwidth-efficient protocol. The VACUUM INTO filename command copies out the current state of an SQLite database into a separate file. The backup API is a C-language interface that can make a consistent copy of an SQLite database. Any of the above approaches will work even on a live database. It is also safe to make a copy of an SQLite database file as long as there are no transactions in progress while the copy is taking place. If the previous write transaction failed, then it is important that any rollback journal (the *-journal file) or write-ahead log (the *-wal file) be copied together with the database file itself. 1.3. Deleting a hot journal SQLite normally stores all content in a single disk file. However, while performing a transaction, information necessary to recover the database following a crash or power failure is stored in auxiliary journal files. Such journal files are described as "hot". The journal files have the same name as the original database file with the addition of -journal or -wal suffix. SQLite must see the journal files in order to recover from a crash or power failure. If the hot journal files are moved, deleted, or renamed after a crash or power failure, then automatic recovery will not work and the database may go corrupt. Another manifestation of this problem is database corruption caused by inconsistent use of 8+3 filenames.

1.4. Mispairing database files and hot journals The previous example is a specific case of a more general problem: The state of an SQLite database is controlled by both the database file and the journal file. In a quiescent state, the journal file does not exist and only the database file matters. But if the journal file does exist, it must be kept together with the database to avoid corruption. The following actions are all likely to lead to corruption: Swapping journal files between two different databases. Overwriting a journal file with a different journal file. Moving a journal file from one database to another. Copying a database file without also copying its journal. Overwriting a database file with another without also deleting any hot journal associated with the original database. 2. File locking problems SQLite uses file locks on the database file, and on the write-ahead log or WAL file, to coordinate access between concurrent processes. Without coordination, two threads or processes might try to make incompatible changes to a database file at the same time, resulting in database corruption. 2.1. Filesystems with broken or missing lock implementations SQLite depends on the underlying filesystem to do locking as the documentation says it will. But some filesystems contain bugs in their locking logic such that the locks do not always behave as advertised. This is especially true of network filesystems and NFS in particular. If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database corruption might result. 2.2. Posix advisory locks canceled by a separate thread doing close() The default locking mechanism used by SQLite on unix platforms is POSIX advisory locking. Unfortunately, POSIX advisory locking has design quirks that make it prone to misuse and failure. In particular, any thread in the same process with a file descriptor that is holding a POSIX advisory lock can override that lock using a different file descriptor. One particularly pernicious problem is that the close() system call will cancel all POSIX advisory locks on the same file for all threads and all file descriptors in the process. So, for example, suppose a multi-thread process has two or more threads with separate SQLite database connections to the same database file. Then a third thread comes along and wants to read something out of that same database file on its own, without using the SQLite library.

Maybe the third thread wants to make a backup copy of the database. Or maybe the third thread is just trying to identify the file type and hences tries to read the first 16 bytes to determine if it really is an SQLite database. Regardless of the reason, the third thread does an open(), a read() and then a close(). One would think this would be harmless. But the close() system call caused the locks held on the database by all the other threads to be dropped. Those other threads have no way of knowing that their locks have just been trashed (POSIX does not provide any mechanism to determine this) and so they keep on running under the assumption that their locks are still valid. This can lead to two or more threads or processes trying to write to the database at the same time, resulting in database corruption. Note that it is perfectly safe for two or more threads to access the same SQLite database file using the SQLite library. The unix drivers for SQLite know about the POSIX advisory locking quirks and work around them. This problem only arises when a thread tries to bypass the SQLite library and read the database file directly. Beginning with SQLite version 3.51.0 (2025-11-04), SQLite implements additional defenses to try to avoid problems caused by locks that are broken by close(). These new defenses help when the database is in WAL mode and is being accessed from multiple processes. But they are not a cure-all. To avoid corruptions, developers should be careful to never use close() on an SQLite database file while one or more database connections are open, even in other threads. 2.3. Multiple copies of SQLite linked into the same application As pointed out in the previous section, SQLite takes steps to work around the quirks of POSIX advisory locking. Part of that work-around involves keeping a global list (mutex protected) of open SQLite database files. But, if multiple copies of SQLite are linked into the same application, then there will be multiple instances of this global list. Database connections opened using one copy of the SQLite library will be unaware of database connections opened using the other copy, and will be unable to work around the POSIX advisory locking quirks. A close() operation on one connection might unknowingly clear the locks on a different database connection, leading to database corruption.

The scenario above sounds far-fetched. But the SQLite developers are aware of at least one commercial product that was released with exactly this bug. The vendor came to the SQLite developers seeking help in tracking down some infrequent database corruption issues they were seeing on Linux and Mac. The problem was eventually traced to the fact that the application was linking against two separate copies of SQLite. The solution was to change the application build procedures to link against just one copy of SQLite instead of two. 2.4. Two processes using different locking protocols The default locking mechanism used by SQLite on unix platforms is POSIX advisory locking, but there are other options. By selecting an alternative sqlite3_vfs using the sqlite3_open_v2() interface, an application can make use of other locking protocols that might be more appropriate to certain filesystems. For example, dot-file locking might be selected for use in an application that has to run on an NFS filesystem that does not support POSIX advisory locking. It is important that all connections to the same database file use the same locking protocol. If one application is using POSIX advisory locks and another application is using dot-file locking, then the two applications will not see each other's locks and will not be able to coordinate database access, possibly leading to database corruption. 2.5. Unlinking or renaming a database file while in use If two processes have open connections to the same database file and one process closes its connection, unlinks the file, then creates a new database file in its place with the same name and reopens the new file, then the two processes will be talking to different database files with the same name. (Note that this is only possible on Posix and Posix-like systems that permit a file to be unlinked while it is still open for reading and writing. Windows does not allow this to occur.) Since rollback journals and WAL files are based on the name of the database file, the two different database files will share the same rollback journal or WAL file. A rollback or recovery for one of the databases might use content from the other database, resulting in corruption. A similar problem occurs if a database file is renamed while it is opened and a new file is created with the old name. In other words, unlinking or renaming an open database file results in behavior that is undefined and probably undesirable.