Skip to main content

Databases

Using sqlite3​

sqlite3 is a command-line tool for interacting with SQLite databases. SQLite is a lightweight, serverless database engine commonly used for applications, including mobile apps and embedded systems. It stores data in a single file, making it easy to manage and transfer.

Installation​

info

Pre-installed with Kali Linux. You can also download the GUI version at the SQLite Download Page

Linux (Debian/Ubuntu)​

sudo apt install sqlite3

Basic Commands​

Opening a Database​

To open an SQLite database file, use the following command:

sqlite3 database.db

This command opens the specified database.db file. If the file does not exist, a new database will be created.

Viewing Tables​

To list all tables in the database, use:

.tables

This command displays the names of all tables in the currently opened database.

Querying Data​

You can execute SQL queries to retrieve data. For example, to select all records from a table:

SELECT * FROM table_name;

Inserting Data​

To insert new records into a table:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Updating Data​

To update existing records:

UPDATE table_name SET column1 = value1 WHERE condition;

Deleting Data​

To delete records from a table:

DELETE FROM table_name WHERE condition;

Importing and Exporting Data​

Importing CSV Files​

You can import data from a CSV file using:

.mode csv
.import file.csv table_name

This sets the mode to CSV and imports data from file.csv into table_name.

Exporting Data​

To export data to a CSV file:

.headers on
.mode csv
.output output.csv
SELECT * FROM table_name;

This sets the output mode to CSV and saves the results to output.csv.

SQLite in Forensics​

SQLite databases are often encountered in digital forensics investigations, especially on mobile devices and applications. Their lightweight nature makes them a popular choice for storing user data, settings, and application logs.

Common Uses in Forensics​

  1. Data Recovery: Forensic analysts can extract and analyze data from SQLite databases to recover deleted records, which can provide critical evidence in investigations.

  2. User Activity Analysis: SQLite databases often contain logs and user activity records. By examining these logs, investigators can reconstruct user behavior and timeline events.

  3. Application Data: Many applications store user preferences, chat histories, and other important data in SQLite databases. Forensics tools can access these databases to retrieve relevant information.

  4. Integrity Verification: By comparing data from SQLite databases with known sources, forensic analysts can verify the integrity of the data, helping to identify tampering or unauthorized changes.

Conclusion​

sqlite3 is a great tool for managing SQLite databases, and its role in digital forensics is significant. Using sqlite3 enables forensic analysts to extract valuable insights from database files, aiding in investigations and data recovery efforts.