How to Corrupt an SQLite Database File
7 hours ago
- #SQLite
- #database corruption
- #data integrity
- SQLite is highly resistant to corruption and automatically rolls back partially written transactions after crashes, but it is not immune.
- Corruption can occur due to misdirected writes from closed file descriptors, such as when a file descriptor is reused for a database file and old writes overwrite it.
- Background automatic backups during transactions can create corrupt copies if not done safely; safe methods include sqlite3_rsync, VACUUM INTO, and the backup API.
- Moving, deleting, or renaming hot journal files (e.g., -journal or -wal) after a crash prevents automatic recovery and may cause corruption.
- File locking issues, including bugs in filesystem locking (especially NFS) and quirks of POSIX advisory locking, can lead to concurrent writes and corruption.
- Multiple links (hard or symbolic) to a database file can cause undefined behavior and corruption due to mismatched journal files.
- Using an inherited database connection after a fork() can result in locking problems and corruption; connections must be opened in the child process.
- Sync operation failures (e.g., lying storage devices or disabling PRAGMA synchronous) can lead to out-of-order writes and corruption, though WAL mode mitigates this.
- Hardware failures (e.g., bit flips, fraudulent USB sticks, or flash memory wear-leveling issues) can corrupt database files.
- Application bugs (e.g., stray pointers or buffer overruns) and SQLite configuration changes (e.g., disabling protections) can cause corruption.
- Historical SQLite bugs (e.g., WAL-reset race conditions, expression index issues) have been fixed, but obscure bugs may still exist.