this value appeared to be regenerated after each checkpoint (which is assumed by the author to be incorrect behaviour) * In testing it was found that although the official (and at the time of writing, up to date) command line version of SQLite v3.7.11 behaved correctly, when using SQLite Expert v3.2. Salt-2 Random number, regenerated with every checkpointĬhecksum part 1 (for the first 24 bytes of the file)Ĭhecksum part 2 (for the first 24 bytes of the file) Salt-1 Random number, incremented with every checkpoint * The file header contains the following data: Offsetįile signature (0x377F0682 or 0x377F0683)įile format version (currently 0x002DE218 which interpreted as a big endian integer is 3007000)Īssociated database’s page size (32-bit big endian integer)Ĭheckpoint sequence number (32-bit big endian integer which is incremented with every checkpoint, starting at 0)
The “-wal” file is made up of a 32 byte file header followed by zero or more “WAL frames”. The “-wal” file is the actual Write Ahead Log which contains the new and updated database pages, its structure is actually fairly simplistic. if the database was called “sms.db” the two additional files would be “sms.db-wal” and “sms.db-shm”). However, probably the most obvious indication of a database in WAL mode is the presence of two files named as “-wal” and “-shm” in the same logical directory as the database (eg. Secondly you can issue the SQL command “PRAGMA journal_mode ” which will return the value “wal” if the database is in WAL mode (anything else indicates rollback journal). It is possible to detect whether a database is in WAL mode in a number of ways: firstly this information is found in the database file’s header examining the file in a hex editor, the bytes at file offset 18 and 19 will both be 0x01 if the database is using the legacy rollback journal or 0x02 if the database is in WAL mode.
A Checkpoint may take place automatically when the WAL file reaches a certain size (by default this is 1000 pages) or performed manually by issuing an SQL command (“PRAGMA wal_checkpoint ”) or programmatically if an application has access to the SQLite engine’s internal API.Ī checkpoint operation takes place and the new version of the page is written into the database file. This continues until a “Checkpoint” event takes place, finally copying the pages in the WAL file into the main database file. These altered or new pages will remain in the WAL file, the database engine reading data from the WAL in place of the historic version in the main database. WAL effectively turned the journal mechanism on its head: rather than backing up the original pages then making changes directly to the database file, the database file itself is untouched and the new or altered pages are written to a separate file (the Write Ahead Log). This means that if SQLite accesses a database and finds that a journal is still present something must have gone wrong and the engine will restore the database to its previous state using the copies of pages in the journal, avoiding corrupted data.įrom version 3.7.0 of the SQLite engine an alternative journal mechanism was introduced called “Write Ahead Log” (ubiquitously shortened to “WAL”). At the conclusion of a successful transaction the journal file would be removed conversely if the transaction was interrupted for any reason (crash, power cut, etc.) the journal remained. Whenever any data on a page of the database was to be altered, the entire page was backed up in a separate journal file. Historically SQLite used a mechanism called “Rollback Journals” for dealing with errors occurring during use of the database. The pages are numbered internally by SQLite starting from 1. Each page may have one of a number of roles, such as informing the structure of the database, and crucially holding the record data itself. The database image is broken up into “pages” of a fixed size (the size is specified in the file header). Briefly, records in the database are stored in file which in SQLite parlance is called the ‘Database Image’. This article sets out to examine the forensic implications, both pitfalls and opportunities, of a relatively new feature of the database engine: Write Ahead Log.īefore we begin, it is worth taking a moment to describe the SQLite file format.
#Db browser for sqlite 3.7.0 android
SQLite is a popular free file-based database format which is used extensively both on desktop and mobile operating systems (it is one of the standard storage formats available on both Android and iOS).
0 kommentar(er)
