SMS and MMS on Android: mmssms.db

What it is

mmssms.db is the SQLite database behind Android's telephony content provider (com.android.providers.telephony). Whatever app the user picks as the default SMS app (Google Messages, Samsung Messages or another), classic SMS and MMS are stored here, and every app with SMS permission reads them through content://sms and content://mms. It is one of the first databases to open in almost any case involving a phone number.

Two things it does not hold: RCS chat messages, which Google Messages and OEM messaging apps keep in their own databases, and messages of third-party apps such as WhatsApp.

Where it lives

Paths are the same from Android 8 to 16 on AOSP-based builds. OEMs add columns (Samsung adds several vendor fields) but keep the core tables. On a multi-SIM phone, one database covers all SIMs, distinguished by sub_id.

What it proves

• That an SMS or MMS with a given body, address and direction exists in the device's message store, and in which folder (inbox, sent, draft, outbox, failed, queued).
• When the device received it (date) and, for received SMS, when the sender's side stamped it (date_sent).
• Which subscription (SIM) handled it (sub_id) and which app inserted the row (creator, the package name).
• Read and seen state, and whether the thread was locked or archived.

It does not prove that the user read or wrote a message: read flips when the thread is opened by any app, and a message in sent could have been sent by malware with SMS permission (check creator).

Key fields

Timestamps

SMS times are Unix epoch milliseconds; MMS times in pdu are Unix epoch seconds. Mixing them up puts MMS in January 1970 or SMS in the year 55000. All are UTC.

SELECT _id, thread_id, address,
       datetime(date/1000, 'unixepoch')                AS device_time_utc,
       datetime(NULLIF(date_sent,0)/1000, 'unixepoch') AS sender_time_utc,
       CASE type WHEN 1 THEN 'inbox' WHEN 2 THEN 'sent' WHEN 3 THEN 'draft'
                 WHEN 4 THEN 'outbox' WHEN 5 THEN 'failed' WHEN 6 THEN 'queued' END AS box,
       read, sub_id, creator, body
FROM sms ORDER BY date;

SELECT p._id, datetime(p.date, 'unixepoch') AS utc, p.msg_box,
       (SELECT group_concat(address) FROM addr WHERE msg_id = p._id AND type = 137) AS sender,
       pt.ct, pt._data, pt.text
FROM pdu p LEFT JOIN part pt ON pt.mid = p._id
ORDER BY p.date;

A large gap between date and date_sent on a received SMS usually means the phone was off or out of coverage when the message was sent.

Retention

Messages stay until deleted by the user, by the messaging app (for example an "auto-delete old messages" setting) or by a factory reset. Deleting a thread removes rows from sms, pdu, part and the search index, and the app_parts files. The database is SQLite, so recently deleted rows often survive in WAL frames, rollback journals, freelist pages or unallocated space inside the file until they are overwritten or the database is vacuumed.

Collection

• Full file system extraction: the only way to get the database with its sidecars and MMS part files. Because the provider lives in device-encrypted storage, a privileged extraction can reach it even in BFU, unlike most app data (see file-based encryption).
• Agent-based logical extraction: commercial and open-source agents read content://sms and content://mms with SMS permission. You get the provider's current view, with no deleted records.
• User export: SMS Backup & Restore produces sms-<date>.xml and calls-<date>.xml files that include MMS and call history.
• Not available: ADB backup on current versions, bugreport.

Parsing

• ALEAPP, Andriller and Autopsy parse mmssms.db into SMS and MMS reports; commercial suites add carving of deleted records.
• Messaging Forensics opens mmssms.db (the sms table, WAL applied) and SMS Backup & Restore XML in the browser and puts them on one timeline with other chat sources.
• For MMS, query pdu, addr and part yourself (above) and hash the app_parts files you export.
• To look for deleted rows, compare a copy opened with the WAL against the main file alone, and inspect freelist pages with a SQLite carving tool.

Investigator tips

• On a phone using Google Messages with RCS enabled, mmssms.db can look empty for contacts who also use RCS: open bugle_db before concluding there were no messages.
• creator is the quickest way to spot SMS written by something other than the default SMS app, for example a banking trojan or a backup-restore tool. Cross-check that package in installed packages.
• Match address against contacts through phone_lookup or normalised numbers, and against the call log to build a per-person communication timeline.
• Short codes and alphanumeric senders (bank names, delivery services) are common smishing lures: pivot from the URL in the body to Chrome history.
• A restored backup inserts old messages with new _id values in a burst: many rows with ascending _id but old date values point to a restore, not to live traffic.