SQLite Main Database File Header Parser
The Spyder Forensics SQLite Main Database file header parser will extract all the informational entries that can be found in the header for an SQLite Database.
The SQLite main database file header comprises the first 100 bytes of the file and contains all the information needed to decode the database. Whilst all the entries in the header may not be important from a forensic perspective, some are very useful.
Below are some useful entries:
Page Size: At the physical level an SQLite database is a series of fixed length pages. Once you know the page size you can 'page' through the file and start identifying the pages of interest.
Journal Mode: SQLite databases utilize journalling for atomic commit and rollback functionality (99.9% of the time). The header tells us which journalling method is used. If the journal files exist in the same directory as the main database file, they should be examined as well.
Freelist Information: There are 2 entries in the main database file header relating to freelist pages. We get information about the first freelist trunk page (table of contents for the freelist), and the number of freelist pages. With this information you have a starting point to start identifying the page numbers for all freelist pages in the database.
Vacuuming Information: SQLite does have vacuuming functionality which will truncate freelist pages at the end of a transaction. In the header we get information about whether auto vacuum is enabled (all freelist pages are truncated) and whether incremental vacuuming is enabled (only a set number of freelist pages are truncated). During an examination, if you don't find freelist pages then this could be the reason why. When vacuuming is enabled, pointer map pages will be present which need to be accounted for when mapping out the pages in the database.
Text Encoding: SQLite supports UTF-8, UTF-16 BE and UTF-16 LE text encoding. If you are parsing out a database at the physical level such as carving records from page unallocated space you will need to understand how text strings are encoded (Serial Type of N≥13 and Odd in the payload header of a record where N is the value derived from decoding the Record Keys).
The script utilizes the prettytable module to output a table in the console. This is included for training purposes.
The prettytable module can be installed using "pip install prettytable"
This script is very simple to use, just requires an input and an output.
-h, --help displays the help menu
-i file path to the SQLite Main Database File (required)
-o path to output the csv file including the name
Example Usage: SF_SQLite_Header_Parser.py -i C:\Evidence\mmssms.db -o C:\Reports\mmssms_sqliteheaderinfo.csv.
A table will be output in the console window displaying all the extracted information.
Example Console Output
If the -o switch is used, the information presented in the console will be exported to a csv file in the specified location.
Example CSV Output
This python script does include logging.
- When using the -o switch a log file will be created in the output location with the same name as the CSV file.
- If the -o switch is not used a log file will be created in the script location called 'Spyder_SQLiteHeaderParser.log'
The following information is logged:
- Script Execution Time
- Input file name
- Input file full path
- Other informational entries
- Error if the input file is not found
- Error if the input file is not an SQLite Database
Example Log
v1.0 2024-05-23
- Initial Release
v1.1 2024-06-24
- Shortened the width of the pretty table columns to display better on lower resolution screens
- Fixed an issue with CSV writer where there was no escape character set
v1.2 2024-08-19
- Fixed a syntax warning on the help page due to the \ in the folder paths being interpreted as escape characters
- Updated Examiner Tips