Type WhatsApp Database

Details

Defined Constant	Type_WhatsApp_Database
File Type Value	874
Parent Type	SQLite
Carve	Yes
Format Details	Yes
MIME Type	application/x-sqlite3
Filename Extension	db

WhatsApp Database

Description

WhatsApp Application Database

Details

This file is exploited by the Expand ETL (source code).

Filenames

Android

• msgstore.db

Queries

Apple

Get Owner

This query figures out who the owner of the device is.

SELECT DISTINCT([ZTOJID]), COUNT([ZTOJID]) AS [Occurrences] FROM [ZWAMESSAGE] WHERE [ZMESSAGETYPE] = 10 ORDER BY [Occurrences] DESC LIMIT 1

Android

Get Owner

This query figures out who the owner of the device is. This was determined by searching the msgstore.db database dump for the phone number of the known owner. Four records were found and they had the column values listed in the query below.

SELECT DISTINCT([remote_resource]), COUNT([remote_resource]) AS [Occurrences]
FROM [messages]
WHERE [media_size] = 4
AND [data] IS NULL
AND [key_from_me] = 1
AND [status] = 6
ORDER BY [Occurrences] DESC LIMIT 1

Get Messages

SELECT [messages].[key_remote_jid],
[messages].[remote_resource],
[messages].[timestamp],
[messages].[data],
[messages].[key_from_me],
[messages].[status], 
[messages].[media_url], 
[messages].[media_wa_type], 
[messages].[media_size], 
[messages].[media_name], 
[messages].[media_caption], 
[messages].[media_duration],
[messages].[latitude], 
[messages].[longitude], 
[message_thumbnails].[thumbnail]
FROM [messages] 
LEFT JOIN [message_thumbnails] ON [messages].[key_id] = [message_thumbnails].[key_id] 
LEFT JOIN [messages_quotes] ON [messages].[quoted_row_id] > 0 AND [messages].[quoted_row_id] = [messages_quotes].[_id] 
LEFT JOIN [messages_links] ON [messages].[_id] = [messages_links].[message_row_id] 
WHERE [messages].[status] >= 0
ORDER BY [messages].[key_remote_jid] ASC, [messages].[timestamp] ASC