How to parse .db files for phone number recovery?
Posted: Thu May 22, 2025 9:11 am
Parsing .db files, particularly SQLite database files, for phone number recovery is a common task in digital forensics and data recovery. These files are widely used by mobile operating systems (Android, iOS for system logs and many apps), web browsers (Chrome, Firefox), and various desktop applications to store structured data.
The process of recovering phone numbers from .db files typically involves:
1. Accessing the .db File:
Mobile Devices (Android/iOS):
Rooted/Jailbroken Devices: This provides direct access to the app's sandboxed data directories where .db files are usually stored (e.g., /data/data/<package.name>/databases/ on Android, or within /var/mobile/Containers/Data/Application/<UUID>/ on iOS). You can use adb pull (Android) or SSH/file managers (iOS) to extract the files.
Non-Rooted/Non-Jailbroken Devices: Direct access is restricted.
Android: You might use adb backup (though this often creates encrypted backups that are hard to parse) or specialized forensic tools that leverage exploits or authorized backup methods.
iOS: You'll typically need to extract data from iTunes or iCloud backups. Tools designed to parse these backups can often reconstruct the app's file system structure and retrieve the .db files.
Physical Extraction: In severe cases (e.g., damaged device), forensic experts might perform a "chip-off" or "JTAG" extraction to get a raw dump of the memory, from which .db files can be carved.
Desktop Applications: Locating .db files for desktop apps (e.g., browser history, some messaging apps) is usually straightforward, residing in the application's data folders within the user's profile directory.
2. Identifying Relevant .db Files:
Once you have access to the device's or app's data, you need to find the specific SQLite .db files that might contain phone numbers. Common examples include:
Android System:
calllog.db: Call history.
mmssms.db: SMS/MMS messages.
contacts2.db: User contacts.
iOS System (within backups):
call_history.db: Call history.
chat.db: Messages (iMessage, SMS, MMS).
AddressBook.sqlitedb: Contacts.
Third-Party Apps (common for both OS):
WhatsApp: msgstore.db, wa.db (for contacts).
Telegram, Viber, Signal, etc., each have their own romania phone number list database files. You'll need to know the app's package name or bundle ID to locate its data directory.
3. Parsing and Querying the .db Files:
SQLite .db files are relational databases that can be opened and queried using standard SQL.
A. Tools for Direct Database Access and Viewing:
DB Browser for SQLite (Recommended, Free & Open Source): This is a user-friendly, cross-platform GUI tool that allows you to:
Open .db files.
Browse tables and their schemas.
View data directly in tables.
Execute SQL queries to select, filter, and export data.
How to use:
Open DB Browser for SQLite.
Go to File -> Open Database and select your .db file.
Navigate to the "Browse Data" tab.
Select a table from the "Table" dropdown (e.g., calls, messages, contacts, phone_lookup, wa_contacts, etc.).
Look for columns that might contain phone numbers (common names: number, phone_number, remote_jid, data1, normalized_number).
Switch to the "Execute SQL" tab to write custom queries for specific extraction. For example:
SQL
SELECT number, date, duration FROM calls ORDER BY date DESC;
SELECT display_name, data1 AS phone_number FROM data WHERE mimetype_id = (SELECT _id FROM mimetypes WHERE mimetype = 'vnd.android.cursor.item/phone_v2');
SELECT key_remote_jid, data FROM messages ORDER BY timestamp DESC;
SQLite Command-Line Interface (CLI): For experienced users, the native sqlite3 command-line tool can be used. It's powerful for scripting and batch processing but lacks a visual interface.
sqlite3 your_database.db
.tables (to list tables)
.schema <table_name> (to see table schema)
SELECT * FROM <table_name>;
B. Recovering Deleted Data:
Standard SQLite tools primarily show active (allocated) records. Phone numbers, especially from deleted calls or messages, might reside in "free space" or "unallocated space" within the .db file until they are overwritten or the database is VACUUMed.
Forensic SQLite Parsers: Specialized forensic tools are designed to carve out and recover deleted data from SQLite files. They analyze the raw binary structure of the file, including journal files (e.g., WAL - Write-Ahead Logging files) and unallocated pages.
FQLite (Forensic SQLite Toolkit): An open-source tool specifically designed for recovering deleted records in SQLite databases, including those in freelist pages and unallocated space.
SQLite Forensic Explorer (Sanderson Forensics): A commercial tool part of their Forensic Toolkit for SQLite, capable of recovering deleted databases, tables, and records.
Belkasoft X, Oxygen Forensic Detective, Cellebrite UFED, Magnet AXIOM: These are comprehensive commercial mobile forensic suites that include advanced SQLite parsing capabilities for both allocated and unallocated data, often with automated number extraction and categorization.
Python Scripts: Researchers often develop custom Python scripts (e.g., using the sqlite3 module and binary analysis) to parse SQLite file format specifics and recover deleted data.
C. Post-Extraction Steps:
Normalization: After extraction, phone numbers might be in various formats. It's crucial to normalize them to a consistent format (e.g., E.164 with country codes) for easier analysis, matching, and import into other systems.
Deduplication: Remove duplicate entries.
Export: Export the recovered numbers to a structured format like CSV, Excel, or vCard (.vcf) for further use or import into a new device.
Important Considerations:
Encryption: If the .db file itself is encrypted (e.g., by the app using SQLCipher or a custom encryption layer), you will need the decryption key to access the content. This is a significant hurdle for unauthorized recovery.
Data Integrity: Recovered deleted data might be fragmented or incomplete. Always verify the integrity of the recovered information.
Legal & Ethical Use: Always ensure you have the legal right and proper authorization to access and process the .db files and the data within them. Unauthorized access and data extraction can lead to severe legal penalties.
The process of recovering phone numbers from .db files typically involves:
1. Accessing the .db File:
Mobile Devices (Android/iOS):
Rooted/Jailbroken Devices: This provides direct access to the app's sandboxed data directories where .db files are usually stored (e.g., /data/data/<package.name>/databases/ on Android, or within /var/mobile/Containers/Data/Application/<UUID>/ on iOS). You can use adb pull (Android) or SSH/file managers (iOS) to extract the files.
Non-Rooted/Non-Jailbroken Devices: Direct access is restricted.
Android: You might use adb backup (though this often creates encrypted backups that are hard to parse) or specialized forensic tools that leverage exploits or authorized backup methods.
iOS: You'll typically need to extract data from iTunes or iCloud backups. Tools designed to parse these backups can often reconstruct the app's file system structure and retrieve the .db files.
Physical Extraction: In severe cases (e.g., damaged device), forensic experts might perform a "chip-off" or "JTAG" extraction to get a raw dump of the memory, from which .db files can be carved.
Desktop Applications: Locating .db files for desktop apps (e.g., browser history, some messaging apps) is usually straightforward, residing in the application's data folders within the user's profile directory.
2. Identifying Relevant .db Files:
Once you have access to the device's or app's data, you need to find the specific SQLite .db files that might contain phone numbers. Common examples include:
Android System:
calllog.db: Call history.
mmssms.db: SMS/MMS messages.
contacts2.db: User contacts.
iOS System (within backups):
call_history.db: Call history.
chat.db: Messages (iMessage, SMS, MMS).
AddressBook.sqlitedb: Contacts.
Third-Party Apps (common for both OS):
WhatsApp: msgstore.db, wa.db (for contacts).
Telegram, Viber, Signal, etc., each have their own romania phone number list database files. You'll need to know the app's package name or bundle ID to locate its data directory.
3. Parsing and Querying the .db Files:
SQLite .db files are relational databases that can be opened and queried using standard SQL.
A. Tools for Direct Database Access and Viewing:
DB Browser for SQLite (Recommended, Free & Open Source): This is a user-friendly, cross-platform GUI tool that allows you to:
Open .db files.
Browse tables and their schemas.
View data directly in tables.
Execute SQL queries to select, filter, and export data.
How to use:
Open DB Browser for SQLite.
Go to File -> Open Database and select your .db file.
Navigate to the "Browse Data" tab.
Select a table from the "Table" dropdown (e.g., calls, messages, contacts, phone_lookup, wa_contacts, etc.).
Look for columns that might contain phone numbers (common names: number, phone_number, remote_jid, data1, normalized_number).
Switch to the "Execute SQL" tab to write custom queries for specific extraction. For example:
SQL
SELECT number, date, duration FROM calls ORDER BY date DESC;
SELECT display_name, data1 AS phone_number FROM data WHERE mimetype_id = (SELECT _id FROM mimetypes WHERE mimetype = 'vnd.android.cursor.item/phone_v2');
SELECT key_remote_jid, data FROM messages ORDER BY timestamp DESC;
SQLite Command-Line Interface (CLI): For experienced users, the native sqlite3 command-line tool can be used. It's powerful for scripting and batch processing but lacks a visual interface.
sqlite3 your_database.db
.tables (to list tables)
.schema <table_name> (to see table schema)
SELECT * FROM <table_name>;
B. Recovering Deleted Data:
Standard SQLite tools primarily show active (allocated) records. Phone numbers, especially from deleted calls or messages, might reside in "free space" or "unallocated space" within the .db file until they are overwritten or the database is VACUUMed.
Forensic SQLite Parsers: Specialized forensic tools are designed to carve out and recover deleted data from SQLite files. They analyze the raw binary structure of the file, including journal files (e.g., WAL - Write-Ahead Logging files) and unallocated pages.
FQLite (Forensic SQLite Toolkit): An open-source tool specifically designed for recovering deleted records in SQLite databases, including those in freelist pages and unallocated space.
SQLite Forensic Explorer (Sanderson Forensics): A commercial tool part of their Forensic Toolkit for SQLite, capable of recovering deleted databases, tables, and records.
Belkasoft X, Oxygen Forensic Detective, Cellebrite UFED, Magnet AXIOM: These are comprehensive commercial mobile forensic suites that include advanced SQLite parsing capabilities for both allocated and unallocated data, often with automated number extraction and categorization.
Python Scripts: Researchers often develop custom Python scripts (e.g., using the sqlite3 module and binary analysis) to parse SQLite file format specifics and recover deleted data.
C. Post-Extraction Steps:
Normalization: After extraction, phone numbers might be in various formats. It's crucial to normalize them to a consistent format (e.g., E.164 with country codes) for easier analysis, matching, and import into other systems.
Deduplication: Remove duplicate entries.
Export: Export the recovered numbers to a structured format like CSV, Excel, or vCard (.vcf) for further use or import into a new device.
Important Considerations:
Encryption: If the .db file itself is encrypted (e.g., by the app using SQLCipher or a custom encryption layer), you will need the decryption key to access the content. This is a significant hurdle for unauthorized recovery.
Data Integrity: Recovered deleted data might be fragmented or incomplete. Always verify the integrity of the recovered information.
Legal & Ethical Use: Always ensure you have the legal right and proper authorization to access and process the .db files and the data within them. Unauthorized access and data extraction can lead to severe legal penalties.