14.2 Mobile Artifact Analysis: Call Logs, SMS/Chat SQLite Databases, Location Data & Plists

14.2 Mobile Artifact Analysis: Call Logs, SMS/Chat SQLite Databases, Location Data & Plists

Quick Answer: Both Android and iOS rely fundamentally on SQLite databases for operational data storage. SQLite operates using Write-Ahead Logging (WAL), meaning an examiner must acquire the main database (.db/.sqlite), the write-ahead log (.db-wal), and the shared memory index (.db-shm) simultaneously to prevent evidence omission. Deleted records remain recoverable from SQLite freelist pages and unallocated cell slack until purged by a VACUUM command. Key Android artifacts reside in /data/data/ (e.g., contacts2.db, mmssms.db), while iOS artifacts reside in /private/var/mobile/ (e.g., sms.db, CallHistory.storedata, binary .plist files). Handset geolocation caches (iOS routined and consolidated.db) correlate with carrier Call Detail Records (CDRs) through antenna azimuth sectors (typically 120° beam coverage) and Timing Advance (TA) distance calculations.

---

SQLite Database Architecture & Record Recovery

Unlike enterprise relational databases running dedicated background service engines, SQLite is a serverless, self-contained, file-based database engine embedded directly into mobile operating systems and applications.

+-------------------------------------------------------------------------+
|                    SQLITE STORAGE & WAL ARCHITECTURE                    |
+-------------------------------------------------------------------------+
|                                                                         |
|  +------------------+         +------------------+                      |
|  |   contacts.db    |         | contacts.db-wal  |                      |
|  | (B-Tree Pages:   |         | (Sequential New  |                      |
|  |  4096-byte blocks|         |  Transactions:   |                      |
|  |  Allocated Cells |         |  Uncommitted     |                      |
|  |  & Freelist Pgs) |         |  Writes & Edits) |                      |
|  +------------------+         +------------------+                      |
|           ^                            ^                                |
|           | Checkpoint Operation       | Shared Memory Index            |
|           | (Commits WAL to Main DB)   |                                |
|           +----------------------------+                                |
|                          |                                              |
|                 +------------------+                                    |
|                 | contacts.db-shm  |                                    |
|                 | (WAL Index Table)|                                    |
|                 +------------------+                                    |
+-------------------------------------------------------------------------+

1. Page Allocation & Write-Ahead Logging (WAL)

• Page Structures: SQLite databases are structured into fixed-size blocks called pages (ranging from 512 to 65,536 bytes; 4,096 bytes is the modern mobile default). Pages are organized into B-trees for tables and B*-trees for indexes.
• The WAL Mechanism: In legacy rollback journal mode, changes were written to a .journal file before updating the database. In modern mobile systems, SQLite utilizes Write-Ahead Logging (WAL):
  • Transactions (INSERT, UPDATE, DELETE) are appended sequentially to the *-wal file instead of altering the primary .db file immediately.
  • A memory-mapped index file (*-shm) tracks transaction offsets within the WAL.
  • The Checkpoint Operation: Periodically, the SQLite engine executes a checkpoint (PASSIVE, FULL, RESTART, or TRUNCATE), flushing WAL records back into the main database file's B-tree pages.
• Critical Forensic Rule: If a forensic examiner copies or extracts sms.db without sms.db-wal and sms.db-shm, recent messages, deleted status flags, and updated contact links are completely lost from the analysis.

2. Deleted Record Recovery Mechanics

When a user deletes a message, call record, or contact, SQLite does not wipe the underlying data bytes:

1. Cell Unallocated Space: The record's cell header is marked as unallocated, and its offset is added to the page's internal freeblock linked list.
2. Database Freelist: If an entire 4,096-byte page is emptied of active records, the page is removed from the active B-tree and placed onto the database freelist (freelist trunk pages and freelist leaf pages). The freelist page count is tracked in bytes 36–39 of the SQLite database header.
3. VACUUM Operation: Deleted data remains in the database file indefinitely until the application explicitly executes VACUUM; or auto-vacuuming is triggered during low disk storage, which rebuilds the database file from scratch, discarding all freelist pages.
4. Recovery Workflows: Examiners carve raw deleted records from SQLite freelist pages using forensic suites (Cellebrite Physical Analyzer, Magnet AXIOM, Oxygen) or command-line utilities such as undark:

   # Dump deleted and unallocated records from an SQLite database
   undark -d mmssms.db --freelist > carved_freelist_messages.txt
   

---

Android Forensic Artifacts

+-------------------------------------------------------------------------+
|                    ANDROID CRITICAL FORENSIC PATHS                      |
+-------------------------------------------------------------------------+
| Artifact Category  | File System Path                                   |
|--------------------|----------------------------------------------------|
| Call History       | /data/data/com.android.providers.contacts/         |
| & Address Book     | databases/contacts2.db                             |
|--------------------|----------------------------------------------------|
| SMS & MMS Messages | /data/data/com.android.providers.telephony/        |
|                    | databases/mmssms.db                                |
|--------------------|----------------------------------------------------|
| Chrome Browser     | /data/data/com.android.chrome/app_chrome/          |
| History & Cookies  | Default/History, Cookies                           |
|--------------------|----------------------------------------------------|
| Wi-Fi Networks     | /data/misc/wifi/wpa_supplicant.conf                |
| & Passwords        | (or apexdata/com.android.wifi/WifiConfigStore.xml)  |
|--------------------|----------------------------------------------------|
| App Sandboxes      | /data/data/<package_name>/ (or /data/user/0/)      |
+-------------------------------------------------------------------------+

1. Contacts and Call Logs (contacts2.db)

Located within the contacts provider package: /data/data/com.android.providers.contacts/databases/contacts2.db.

• calls Table: Stores full incoming, outgoing, and missed call metadata:
  • number: Dialed or receiving telephone number string.
  • date: Timestamp of the call formatted as a 13-digit Unix Epoch timestamp in milliseconds (milliseconds since January 1, 1970 UTC).
  • duration: Call length in integer seconds.
  • type: Call disposition code:
    • 1: Incoming (Answered)
    • 2: Outgoing
    • 3: Missed
    • 4: Voicemail
    • 5: Rejected
    • 6: Blocked
• raw_contacts & data Tables: Contain contact display names, associated email addresses, synced Google accounts, and social profile links.

2. SMS and MMS Messages (mmssms.db)

Located at: /data/data/com.android.providers.telephony/databases/mmssms.db.

• sms Table: Contains native short messaging text entries:
  • _id: Unique message index.
  • address: The telephone number of the sender or recipient.
  • date: Unix Epoch timestamp in milliseconds.
  • read: Read status (0 = unread, 1 = read).
  • type: Message directionality (1 = Received, 2 = Sent, 3 = Draft).
  • body: Plaintext contents of the SMS message.
• pdu, part, and addr Tables: Handle Multimedia Messaging Service (MMS) artifacts, linking binary images, audio attachments, and multi-party recipient structures.

3. Application Sandboxing Architecture

Every installed Android package receives an isolated sandbox at /data/data/<package_name>/ (or symlinked to /data/user/0/<package_name>/). Standard subdirectories include:

• databases/: Holds application-specific SQLite databases (e.g., msgstore.db in WhatsApp, wa.db).
• shared_prefs/: Extensible Markup Language (.xml) files recording application preferences, user account credentials, auth tokens, and last-sync timestamps.
• files/: Local assets, cached document downloads, and media files.
• cache/: Temporary transient objects and network thumbnail caches.

4. Wi-Fi Profiles & Network Discovery

• Legacy Android (Android 9 and earlier): /data/misc/wifi/wpa_supplicant.conf
  • Contains cleartext or pre-shared key (PSK) Wi-Fi access credentials, Service Set Identifiers (SSIDs), and network security configurations (WPA2/WPA3):

    network={
        ssid="Target_Corporate_WiFi"
        psk="CompanySecretKey2026!"
        key_mgmt=WPA-PSK
        priority=1
    }
    

• Modern Android (Android 10+): Stored as XML configuration containers under /data/misc/apexdata/com.android.wifi/WifiConfigStore.xml.

---

iOS Forensic Artifacts

+-------------------------------------------------------------------------+
|                      IOS CRITICAL FORENSIC PATHS                        |
+-------------------------------------------------------------------------+
| Artifact Category  | File System Path                                   |
|--------------------|----------------------------------------------------|
| SMS / iMessage     | /private/var/mobile/Library/SMS/sms.db             |
|--------------------|----------------------------------------------------|
| Call History       | /private/var/mobile/Library/CallHistoryDB/         |
|                    | CallHistory.storedata                              |
|--------------------|----------------------------------------------------|
| Safari History     | /private/var/mobile/Library/Safari/History.db      |
|--------------------|----------------------------------------------------|
| Location Caches    | /private/var/mobile/Library/Caches/                |
|                    | com.apple.routined/Local.sqlite                    |
|--------------------|----------------------------------------------------|
| Photos Metadata    | /private/var/mobile/Media/PhotoData/Photos.sqlite  |
|--------------------|----------------------------------------------------|
| Property Lists     | *.plist (System & Application Preferences)         |
+-------------------------------------------------------------------------+

1. Property List Files (.plist)

Property lists store user preferences, device configurations, and application states on iOS and macOS systems.

• Formats: Exist in two distinct formats: human-readable XML format and high-density Binary format.
• Binary Plist Signature: Binary plists begin with the 8-byte magic header bplist00 (hex: 62 70 6C 69 73 74 30 30).
• Forensic Conversion: Examiners inspect and convert binary plists into readable XML using command-line utilities:

  # Convert binary plist to human-readable XML format
  plutil -convert xml1 com.apple.preferences.plist -o preferences_readable.xml
  

2. SMS and iMessage Database (sms.db)

Located at /private/var/mobile/Library/SMS/sms.db.

• message Table: Contains message records across SMS, MMS, and Apple iMessage:
  • text: Plaintext content of the message.
  • handle_id: Foreign key referencing the handle table to determine the sender or recipient phone number / Apple ID email.
  • is_from_me: Directionality indicator (0 = Received message, 1 = Sent message).
  • date: Timestamp recording the transmission time.
• Apple Timestamp Mechanics (Mac Absolute Time / Cocoa Core Data Time):
  • iOS records timestamps in seconds (or nanoseconds) relative to January 1, 2001 00:00:00 UTC (epoch offset = 978,307,200 seconds after Unix epoch).
  • Nanosecond timestamps are stored as 18-digit integers (e.g., 717082800000000000). To convert to Unix time, divide by $10^9$ and add $978,307,200$.
• chat_message_join Table: Maps individual messages to specific multi-party group chats or one-on-one threads.

3. Call History (CallHistory.storedata)

Located at /private/var/mobile/Library/CallHistoryDB/CallHistory.storedata (a CoreData SQLite database):

• ZCALLRECORD Table: Core table storing telephony metadata:
  • ZADDRESS: Binary blob or string representation of the phone number.
  • ZDATE: Mac Absolute Time timestamp.
  • ZDURATION: Float or integer representing call duration in seconds.
  • ZORIGINATED: Boolean flag (0 = Incoming call, 1 = Outgoing call).
  • ZANSWERED: Boolean flag (0 = Unanswered / Missed, 1 = Answered).

4. iOS Geolocation Artifacts

• Significant Locations (routined Daemon): iOS tracks frequently visited locations (home, workplace, frequent coffee shops) to optimize predictive maps and battery routines.
  • Stored at /private/var/mobile/Library/Caches/com.apple.routined/Local.sqlite.
  • Contains ZRTLEARNEDLOCATIONOFINTERESTMO and ZRTLEARNEDVISITMO tables recording GPS latitude, longitude, horizontal accuracy radius (meters), arrival timestamp, and departure timestamp.
• Historical Cell & Wi-Fi Geolocation (consolidated.db / cellular.db): Caches neighboring cell tower IDs and Wi-Fi BSSID geographical coordinates gathered by Apple location services to expedite GPS lock.

5. Photos Metadata & Photos.sqlite

Located at /private/var/mobile/Media/PhotoData/Photos.sqlite.

• EXIF Metadata: Media files captured by the onboard camera (JPEG, HEIC, MOV) store Exchangeable Image File Format (EXIF) tags directly within file headers: GPS Latitude, Longitude, Altitude, Timestamp, Lens Model, and Sub-second shutter times.
• ZGENERICASSET Table: Records photo capture state, albums, facial recognition tags, hidden status, and deletion tracking. The column ZTRASHEDSTATE flags whether a photo is active (0) or resides in the user's "Recently Deleted" album (1) awaiting permanent 30-day deletion.

---

Cellular Telecommunications & Call Detail Records (CDRs)

Handset location artifacts are frequently cross-examined against external carrier records provided via subpoena or search warrant.

+-------------------------------------------------------------------------+
|                    CELL SITE SECTORIZATION & AZIMUTH                    |
+-------------------------------------------------------------------------+
|                                                                         |
|                            Sector 1 (Alpha)                             |
|                             Azimuth = 0° / 360°                         |
|                                   /\                                    |
|                                  /  \                                   |
|                                 /    \                                  |
|                                /      \                                 |
|                               /   __   \                                |
|                              /   /  \   \                               |
|                             /    \__/    \                              |
|                            +-------|------+                             |
|                                    |                                    |
|       Sector 3 (Gamma)             |            Sector 2 (Beta)         |
|       Azimuth = 240°               |            Azimuth = 120°          |
|             \                      |                   /                |
|              \                     |                  /                 |
|               \                    |                 /                  |
|                \                   |                /                   |
|                 v                  v               v                    |
|         Beamwidth: ~65°     Cellular Tower  Beamwidth: ~65°             |
+-------------------------------------------------------------------------+

1. Call Detail Records (CDR) Architecture

Telecommunication service providers (AT&T, Verizon, T-Mobile) log network transactions generated at Mobile Switching Centers (MSC) and base stations:

• Identifiers:
  • MSISDN (Mobile Station International Subscriber Directory Number): The standard phone number.
  • IMSI (International Mobile Subscriber Identity): Unique 15-digit code identifying the SIM card on the cellular network (MCC + MNC + MSIN).
  • IMEI (International Mobile Equipment Identity): 15-digit hardware serial number identifying the physical handset.
• Event Types: Voice calls, SMS delivery (note: CDRs log message metadata, not message body text), data sessions, and location area updates.
• Cell Global Identity (CGI): Every cellular tower antenna is uniquely identified by four parameters: MCC-MNC-LAC-CI (Mobile Country Code, Mobile Network Code, Location Area Code, and Cell ID).

2. Cell Site Analysis & Geospatial Triangulation

• Antenna Sectors & Azimuth: Standard cellular macro towers feature three directional antenna faces spaced at 120-degree intervals, known as sectors (Alpha, Beta, Gamma):
  • Sector 1: Typically points North (Azimuth = $0^\circ$).
  • Sector 2: Typically points Southeast (Azimuth = $120^\circ$).
  • Sector 3: Typically points Southwest (Azimuth = $240^\circ$).
  • Beamwidth: Antennas concentrate radio energy across an approximate 60° to 65° horizontal beamwidth.
• Timing Advance (TA): In LTE and 5G networks, the base station measures the round-trip signal propagation delay between the handset and the tower. The Timing Advance metric is converted into an accurate distance ring (radius from the tower) where the handset operated during the call.
• Forensic Corroboration: Examiners overlay the tower coordinates, antenna azimuth angle, and Timing Advance distance arc over local mapping data, cross-referencing this footprint with the handset's internal SQLite location caches (Local.sqlite or Google Maps history) to conclusively place a suspect at a physical crime scene.

---

Practical Forensic Case: Resolving an Armed Robbery via Multi-Artifact Correlation

The Incident

An armed robbery occurred at a jewelry boutique at 14:22 UTC. Surveillance cameras recorded a masked individual fleeing in a vehicle. Two hours later, a suspect was apprehended during a traffic stop; an iPhone 13 Pro was seized in AFU state.

Investigative Correlation Steps

1. Extraction & Database Acquisition: The forensic lab executed an Advanced Logical / Full File System extraction. Examiners verified that sms.db, sms.db-wal, and sms.db-shm were all present.
2. SMS Deletion Analysis: Querying the active message table in sms.db yielded no relevant communications. However, running an SQLite freelist carving tool against unallocated blocks in sms.db and the active .wal file recovered three deleted incoming messages received between 14:15 and 14:18 UTC reading: "Meet me at the back alley behind the jeweler now."
3. Timestamp Alignment: The message timestamps were stored in Mac Absolute Time (717084900). Converting to Unix Epoch ($717084900 + 978307200 = 1695392100$) aligned the messages to 14:15 UTC on the robbery date.
4. Geolocation Triangulation: Analysis of /private/var/mobile/Library/Caches/com.apple.routined/Local.sqlite revealed a visit entry in ZRTLEARNEDVISITMO placing the iPhone within a 15-meter horizontal accuracy radius of the jewelry store between 14:10 and 14:35 UTC.
5. Carrier CDR Validation: Carrier CDR records confirmed the suspect's IMEI and IMSI connected to Cell Tower #4021, Sector 2 (Azimuth 120°), with a Timing Advance indicating a distance of 450 meters—directly intersecting the jewelry store.
6. Result: The convergence of carved deleted SQLite messages, handset GPS artifacts, and carrier CDR cell sector logs defeated the suspect's alibi, resulting in a guilty plea.