Logo ot the FQLite Project

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.


user interface
Figure: The FQLite user interface

Features



FQLite allows you to:

Some features:


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:

Jan 4th,  2022 (New Year Edition)

Download fqlite_v1.5.7.jar

sha1 sum 60e458358515553b1232f790ba1503f22652a510


What's new:

Dec 27th,  2021 (Christmas Edition)

Download fqlite_v1.5.6.jar

sha1 sum 1c69c30f6b41edcec40356122ec7abcb6d91a9cd


What's new:

Jul 15th, 2021

Download fqlite_v1.5.5.jar

sha1 sum 936847b365f36339ba2388d86d50653b13eb8347


What's new:

Jul 6th,  2021

Download fqlite_v1.5.4.jar

sha1 sum 1634a85a706de0078da21882801f90533e5ca36e


What's new:

Apr 9th, 2021

Download fqlite_v1.5.3.jar

sha1 sum 44b96b529ff545aa64a747ea733e9dbbe97a7b27


What's new:


Apr 1st,  2021

Download fqlite_v1.5.2.jar

sha1 sum f7a4bbfc4ffa24e51de1e722cc69b1cf60e1960e


What's new:

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 sum e835ac1eace90d7969ff44e3dd17b5e0

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.

© 2019-2022 Dirk Pawlaszczyk 
Impressum