Forensic Analysis of Damaged SQLite Databases

by Oleg Skulkin & Igor Mikhaylov

SQLite databases are very common sources of forensic artifacts nowadays. A lot of mobile applications store data in such databases: you can also find them on desktop computers and laptops as well as, for example, forensicating web-browsers, messengers and some other digital evidence sources.

There are a lot of forensic tools on the market that support analysis of SQLite databases, for example, Magnet AXIOM, Belkasoft Evidence Center and BlackBag BlackLight to name a few. The tools can automatically parse some of these databases and even carve data out of free lists and unallocated space. Also they provide SQLite viewers forensicators can use to analyze this type of databases manually.

But what about damaged or corrupted databases? Let’s find out!

One of our DFIR-mates sent us a SQLite database he couldn’t open with any tool he had. He even wrote an email to one of the vendors, but the answer was – the database contained no tables.


Get The Latest DFIR News

Join the Forensic Focus newsletter for the best DFIR articles in your inbox every month.


Unsubscribe any time. We respect your privacy - read our privacy policy.

Ok, let’s start our journey. The database has the following name: “contacts2.db”. If you are forensicating mobile devices often enough, you notice that it’s a typical Android database, the one containing information about a user’s contacts.

As we have quite a big forensic toolkit, we decided to start from trying to open the database with the most popular tools, including Belkasoft Evidence Center, BlackBag BlackLight, Cellebrite UFED Physical Analyser, DB Browser for SQLite, Magnet AXIOM and Oxygen Forensic Detective. Unfortunately, none of the tools could open the database. Look at the following screenshots:

The database opened with Belkasoft Evidence Center
The database opened with BlackBag BlackLight
The database opened with Cellebrite UFED Physical Analyzer
The database opened with DB Browser for SQLite
The database opened with Magnet AXIOM
The database opened with Oxygen Forensic Detective

As you can see, none of the big guys could solve the problem. So what should a forensic analyst do in such situation? Go back to the roots!

First of all, go to to the official SQLite website and download the command-line tools for managing database files.

Now extract the contents of the archive and put the database into the same folder (optional).

Start Windows Command Prompt and change the directory to the one to which you extracted the SQLite command-line tools.

Run the following command sequence:

sqlite3.exe database_name.db
.mode insert
.output database_dump.sql
.dump
.exit

Now you have an SQL file with dumped database tables. If you are lucky, you can just delete the transaction statements, like BEGIN TRANSACTION and ROLLBACK, and import the file to a new SQLite database, for example, using DB Browser for SQLite. But in our case the database was heavily damaged, so we had to examine the file manually and save tables of interest in separate SQL files.

For example, we found the ‘accounts’ table, it looked like this:

CREATE TABLE accounts (_id INTEGER PRIMARY KEY AUTOINCREMENT,account_name TEXT, account_type TEXT, data_set TEXT);
INSERT INTO accounts VALUES(1,’vnd.sec.contact.phone’,’vnd.sec.contact.phone’,NULL);
INSERT INTO accounts VALUES(2,’primary.sim.account_name’,’vnd.sec.contact.sim’,NULL);
INSERT INTO accounts VALUES(4,’vnd.sec.contact.agg.account_name’,’vnd.sec.contact.agg.account_type’,NULL);
INSERT INTO accounts VALUES(506,’test@gmail.com’,’com.google’,NULL);
INSERT INTO accounts VALUES(538,’WhatsApp’,’com.whatsapp’,NULL);
INSERT INTO accounts VALUES(655,’test’,’com.yandex.passport’,NULL);
INSERT INTO accounts VALUES(656,’+7 903 000-00-00′,’com.yandex.passport’,NULL);
INSERT INTO accounts VALUES(657,’+7 925 000-00-00′,’com.yandex.passport’,NULL);
INSERT INTO accounts VALUES(658,’+7 978 000-00-00′,’com.yandex.passport’,NULL);
INSERT INTO accounts VALUES(672,’test’,’com.vkontakte.account’,NULL);
INSERT INTO accounts VALUES(677,’+7 968 000-00-00′,’com.yandex.passport’,NULL);
INSERT INTO accounts VALUES(687,’Youla account’,’com.allgotitm.youla’,NULL);
INSERT INTO accounts VALUES(792,’+7 968 000-00-00′,’com.viber.voip’,NULL);

We saved it into a separate SQL file and used DB Browser for SQLite to create a database.

Here is how to do it:

  • Open DB Browser for SQLite.
  • Go to File – Import – Database from SQL file…
  • Choose the SQL file you saved the table of interest.
  • Choose the name of the database to be created.
  • Now you can browse the data and use simple SQL queries to export it.
Recovered table

Of course, you can do the same with all the other tables of interest. And yes, our database actually contains tables. This is one more proof that digital forensic analysts mustn’t rely solely on forensic tools, even if they have most of the top commercial products. Don’t forget to check potential digital evidence sources manually!

Happy forensicating!

About The Authors

Oleg Skulkin, GCFA, MCFE, ACE, is a DFIR enthusional (enthusiast + professional), Windows Forensics Cookbook and Practical Mobile Forensics co-author.

Igor Mikhaylov, MCFE, EnCE, ACE, OSFCE, is a digital forensic examiner with more than 20 years of experience and Mobile Forensics Cookbook author.

1 thought on “Forensic Analysis of Damaged SQLite Databases”

Leave a Comment

Latest Videos

Digital Forensics News Round-Up, February 21 2024 #digitalforensics #dfir

Forensic Focus 21st February 2024 6:19 pm

Alan Platt, Professional Services Consultant at MSAB, discusses his experience as a former UK police officer working in digital forensics. He talks about the different levels of digital forensics capabilities within police forces and how MSAB products like XAMN and XEC Director are used by frontline officers versus lab analysts. 

The discussion covers how MSAB partners with law enforcement to develop custom workflows for mobile device acquisitions that facilitate ISO compliance. Alan explains MSAB's managed service offering, where approved MSAB staff can remotely access a customer's XEC Director server to assist with software updates and troubleshooting. He emphasizes the strict data segregation policies enforced by customers to prevent MSAB from accessing any sensitive case data.

Looking ahead, Alan mentions MSAB's new CEO and hints at some exciting developments coming down the pipeline. He spotlights recent enhancements to XEC Director's speed and database functionality for managing large estates of networked Kiosks. Alan also plugs the new XEC Director training he created to help users fully leverage the platform's capabilities.

00:00 – Introduction to Alan Platt
07:00 – Training
12:00 – Workflows
17:20 – Ensuring a secure environment
19:45 – Customer training
20:35 – Helping customers comply with ISO accreditation
25:00 – Validation and verification
27:30 – ISO standards
30:00 – MSAB’s pipeline plans
32:40 – XEC Director 
43:45 – Privacy of user data

Alan Platt, Professional Services Consultant at MSAB, discusses his experience as a former UK police officer working in digital forensics. He talks about the different levels of digital forensics capabilities within police forces and how MSAB products like XAMN and XEC Director are used by frontline officers versus lab analysts.

The discussion covers how MSAB partners with law enforcement to develop custom workflows for mobile device acquisitions that facilitate ISO compliance. Alan explains MSAB's managed service offering, where approved MSAB staff can remotely access a customer's XEC Director server to assist with software updates and troubleshooting. He emphasizes the strict data segregation policies enforced by customers to prevent MSAB from accessing any sensitive case data.

Looking ahead, Alan mentions MSAB's new CEO and hints at some exciting developments coming down the pipeline. He spotlights recent enhancements to XEC Director's speed and database functionality for managing large estates of networked Kiosks. Alan also plugs the new XEC Director training he created to help users fully leverage the platform's capabilities.

00:00 – Introduction to Alan Platt
07:00 – Training
12:00 – Workflows
17:20 – Ensuring a secure environment
19:45 – Customer training
20:35 – Helping customers comply with ISO accreditation
25:00 – Validation and verification
27:30 – ISO standards
30:00 – MSAB’s pipeline plans
32:40 – XEC Director
43:45 – Privacy of user data

YouTube Video UCQajlJPesqmyWJDN52AZI4Q_ifoHVkjJtRc

How MSAB Is Managing The Digital Forensics Challenges Of Frontline Policing

Forensic Focus 21st February 2024 3:07 pm

Podcast Ep. 80 Recap: Empowering Law Enforcement With Nick Harvey From Cellebrite

Forensic Focus 20th February 2024 11:49 am

This error message is only visible to WordPress admins

Important: No API Key Entered.

Many features are not available without adding an API Key. Please go to the YouTube Feed settings page to add an API key after following these instructions.

Latest Articles