Comparing SQLite schemas across multiple databases
17 December 2019
Many forensic investigators will have built their own scripts and tools for extracting data from SQLite databases, but these need to be kept up to date with any changes to the SQLite database schema.
The same applies to developers of forensic software, for example at Foxton Forensics we need to keep our browser history tools up to date with any changes in the SQLite databases within Firefox or Chrome profiles. With multiple databases to compare this is something that requires automation.
We have therefore updated SQLite Examiner
to include a Schema Export feature which exports the schema of a SQLite database to a JSON file containing table names, column names and column data types.
Here is an example of how it can be used to compare Chrome profiles for any SQLite database changes:
- Load the Chrome profile into SQLite Examiner using the ‘File > Load SQLite > Search Folder (Recursive)’ menu option
- Export the schema for all SQLite databases to a JSON file using the ‘File > Export Schema’ menu option
- Use a diff tool to compare the generated JSON file against a previous version in order to view any changes to existing databases or discover new databases
In the example above we can see that a new column called date_last_used has been added to the Chrome Login Data database which stores the timestamp of when a saved login was last used.
SQLite Examiner is free to download
from our website (with version 1.1 now featuring Schema Export) enabling examiners and developers alike to more easily track the changes to important artefacts and verify their scripts and tools remain accurate.