Forensic SQLite Data Recovery Tool

Author: Dirk Pawlaszczyk

Introduction

FQLite is a tool to find and restore deleted records in SQlite databases. It therefore examines the database for entries marked as deleted. Those entries can be recovered and displayed. It is written with the Java programming language. The program can operate in two different modes. It can be started from the command line (CLI mode). A simple graphical user interface is also supported (GUI mode). The program is able to search a SQLite database file for regular as well as deleted records.

Technical Background

SQLite is a software library that implements a simple SQL database engine. It is trendy and used in different software products. Mozilla Firefox and Google Chrome, for example, use SQLite version 3 databases for user data such as history, cookies, and downloads. Sometimes single records are deleted from the database, or even a complete table has been dropped. But this does not mean that this data is physically removed. In most cases, such artifacts can still be found, even if the beginning of the data records has been partially overwritten. Beyond this, a database file might contain one or more pages that are not in active use. Such unused pages can arise when information is deleted from the database. They are stored on the free list and were reused, whenever new pages are required. Such artifacts are often of high forensic value. FQLite can make this data visible again by extracting the desired information directly from the binary data. The tool works without an interface on the raw data image.

Figure: The FQLite user interface

Features

FQLite allows you to:

browse and recover the content of freelist pages
recover records in all database pages including unallocated space
support of UTF-8,UTF-16BE,UTF-16LE encoded databases
support for multi-byte columns as well as overflow pages
recover dropped tables
create CSV-format data export

Some features:

100% written with Java standard class library
lightweight graphical user interface
open-source
free of charge
runs out of the box

Benchmark

Below are some test results of programs achieved with the forensic corpus [2], a benchmark specifically designed for SQLite databases. The table shows how well the individual tools performed in restoring the 278 deleted records of the corpus.

Undark	SQLite Deleted Record Parser	Stellar Phoenix Repair for SQLite	SysTools SQLite Database Recovery	Sanderson Forensic Browser for SQLite
95/278 34,8%	139/278 50%	0/278 0%	0/278 0%	33/278 11,9%
SQLabs SQLite Doctor	Sqlite Forensic Explorer	Autopsy SQLite Deleted Records Plugin	bring2lite	FQLite
0/278 0%	73/278 26,3	0/278 0%	147/278 52,9%	278/278 100%

Table: Recovery rates of FQLite compared to
9 other tools tested with the Forensic Corpus
(vgl. D. Pawlaszczyk and C. Hummert 2021 [3], S. Nemetz and S. Schmitt et al 2018 [2])

Download latest version

Jan 17th, 2022

Download fqlite_v1.5.8.jar

sha1 sum 55dd66948598d6e04f32574e7da665fc96be02d4

What's new:

added support for ROWIDs even for removed records
replaced hex-edtior with a faster version
sorting of date-columns now works as expected
wrong sorting of integer-columns is fixed
fixed issue with last page in free page list

Jan 4th, 2022 (New Year Edition)

Download fqlite_v1.5.7.jar

sha1 sum 60e458358515553b1232f790ba1503f22652a510

What's new:

completely reworked handling of overflow pages (iterative instead of recursive processing)
addressing some stack-overflow problems
smaller bug fixes

Dec 27th, 2021 (Christmas Edition)

Download fqlite_v1.5.6.jar

sha1 sum 1c69c30f6b41edcec40356122ec7abcb6d91a9cd

What's new:

smaller bug fixes (github issues #2 and #3)
improved detection of deleted data sets

Jul 15th, 2021

Download fqlite_v1.5.5.jar

sha1 sum 936847b365f36339ba2388d86d50653b13eb8347

What's new:

smaller bug fixes
DB-Schema is now taken from WAL-archive if the database file has been wiped

Jul 6th, 2021

Download fqlite_v1.5.4.jar

sha1 sum 1634a85a706de0078da21882801f90533e5ca36e

What's new:

open new database with drag&drop
each table has a line number column
primary key columns are labeled with a key icon
tables in tree view are listed alphabetically
details on db-schema a now available(in web browser)

Apr 9th, 2021

Download fqlite_v1.5.3.jar

sha1 sum 44b96b529ff545aa64a747ea733e9dbbe97a7b27

What's new:

smaller bug-fixes (new JAR-Starter, records with payload length (pll) < 10 bytes are detected now)

Apr 1st, 2021

Download fqlite_v1.5.2.jar

sha1 sum f7a4bbfc4ffa24e51de1e722cc69b1cf60e1960e

What's new:

smaller bug-fixes (header fields tab, log-level from ERROR to INFO)

Feb 13th, 2021

Download fqlite_v1.5.1.jar

sha1 sum 87eabd2d9be50974b74dcc4fc956ac94eb22b635

What's new:

all file types (DB-file,WAL-file and Journal-file) support for complete header attributes
colored checkpoints in WAL file
salt and frame information in WAL tables (for time line creation)
tab pane with file attributes information and md5 and sha256 hashsum
popup and copy/paste functionality on nearly all text fields, tables and views

Feb 12th, 2021

Download fqlite_v1.4.jar

sha1 sum 9027d151b37561c031b26406d297144ff4b4c181

What's new:

smaller bug-fixes (in Schema Parser, improved support for IOS databases)

Feb 02th, 2021

Download fqlite_v1.3.jar

sha1 sum 3a4e81db478709afa0639c0d72df085dfd3c244d

What's new:

improved export functionality (for single tables as well as the overall database)
performance improvements
improved visualization (new icons), alphabetical sorting of tables in tree view
automatic detection of *.png, *.jpg, *.gif file format header in BLOB columns (preview on click)
support for UNIX-timestamp to readable date format

Dec 03th, 2020

Download fqlite_v1.2.jar

sha1 sum 5e59693c384569a8e64d96f17caaa7e048a3e5b5

What's new:

support for write ahead log files
support for rollback journal files
add TrueType-font support for emojis
hex-viewer does now support search for text/hex
improved detection functionalities
selection of multiple rows and collumns in the tableview

Nov 12th, 2020

Download fqlite_v1.1.jar

md5 sume835ac1eace90d7969ff44e3dd17b5e0

What's new:

improved detection rate
support for virtual table modules (rtree, fts3/4)
support for index tables
overview to important database header fields and sqlite_master table for each database
completely revised user interface
hex-viewer design has been reworked
each table now has a status column (with tool tips)

Oct 5th, 2020

Download fqlite_v1.0.jar

md5 sum 76c8f66329f2e0e1f33050aadf113f13

Note: To run any version of the tool you need at least a Java Runtime Environment (JRE) 1.8 or higher.

The latest version can be obtained via the following link:

https://www.java.com/de/download/manual.jsp

Example usage

To run the FQLite in GUI mode the executable jar can normally be started with a double-click on the jar-archive file. If this does not work, since javaw is not linked correctly to .jar files, you can use the command line as well:

$ java -jar fqlite<version>.jar

To run the FQLite from the command line you can use the following command:

$ java -jar fqlite<version>.jar nogui <database.db>

Here is a more complex example with parameters:

$ java -jar fqlite<version>.jar nogui --threads:4 --loglevel:ERROR <database.db>

nogui -> start program in command line mode

--threads:4 -> use 4 threads to analyze the data records

--loglevel:ERROR -> print only ERROR messages to standard output

Video tutorial and other resources

You can watch here a small introduction video, which explains the essential functions of FQLite right here (in German) click here.

References

[1] D. Pawlaszczyk: SQLite. In: Hummert, C., Pawlaszczyk, D. (eds) Mobile Forensics – The File Format Handbook. Springer, Cham. 2022. https://doi.org/10.1007/978-3-030-98467-0_5
https://link.springer.com/content/pdf/10.1007/978-3-030-98467-0.pdf

[2] S. Nemetz, S. Schmitt, F. Freiling: A standardized corpus for SQLite database forensics. In: Digital Investigation, vol. 24, Supplement, 2018, pages 121-130, (2018).
https://doi.org/10.1016/j.diin.2018.01.015.

[3] D. Pawlaszczyk, C. Hummert: (2021). Making the Invisible Visible – Techniques for Recovering Deleted SQLite Data Records. International Journal of Cyber Forensics and Advanced Threat Investigations, 0, 1-1. Retrieved from https://conceptechint.net/index.php/CFATI/article/view/17

Contact

Address:

Dirk Pawlaszczyk

Technikumplatz17

09846 Mittweida

Germany

Email:

pawlaszc@hs-mittweida.de

https://www.cb.hs-mittweida.de/professoren/informatik/prof-pawlaszczyk.html

https://www.researchgate.net/profile/Dirk_Pawlaszczyk

Hint: This web page was tested and can be viewed with the Netscape Navigator 9 and Lynx web browser. No tracking. No JavaScript.

Impressum