Mobile App Artifact Analysis

# Android WhatsApp Locations
/data/data/com.whatsapp/databases/
    msgstore.db         # Main message database
    wa.db              # Contacts database
    axolotl.db         # Encryption keys
    chatsettings.db    # Chat-specific settings

/data/media/0/WhatsApp/
    Media/             # Sent/received media files
    Databases/         # Encrypted backup (msgstore.db.crypt14)
    Backups/           # Local backups

# iOS WhatsApp Locations
/private/var/mobile/Containers/Shared/AppGroup/[UUID]/
    ChatStorage.sqlite  # Main message database
    ContactsV2.sqlite   # Contacts
    Media/              # Media files

-- Extract all messages with contact info
SELECT
    m.key_remote_jid AS contact,
    m.key_from_me AS sent_by_me,
    m.data AS message_text,
    datetime(m.timestamp/1000, 'unixepoch', 'localtime') AS message_time,
    m.media_wa_type AS media_type,
    m.status
FROM messages m
ORDER BY m.timestamp DESC;

-- Find messages between specific dates
SELECT * FROM messages
WHERE timestamp BETWEEN
    strftime('%s', '2024-01-01') * 1000
    AND strftime('%s', '2024-01-31') * 1000;

-- Extract deleted messages (where data IS NULL but row exists)
SELECT * FROM messages
WHERE data IS NULL
    AND media_wa_type = 0
    AND key_remote_jid IS NOT NULL;

-- Extract browsing history with visit count
SELECT
    u.url,
    u.title,
    datetime(v.visit_time/1000000-11644473600, 'unixepoch') AS visit_time,
    u.visit_count,
    v.transition
FROM urls u
JOIN visits v ON u.id = v.url
ORDER BY v.visit_time DESC;

-- Extract search terms
SELECT
    term,
    url_id
FROM keyword_search_terms
ORDER BY url_id DESC;

-- Extract downloads
SELECT
    target_path,
    tab_url,
    datetime(start_time/1000000-11644473600, 'unixepoch') AS download_time,
    total_bytes
FROM downloads;

# Significant Locations (requires encryption key)
/private/var/mobile/Library/Caches/com.apple.routined/
    Cache.sqlite        # Significant locations learned by device

# Location Services Cache
/private/var/root/Library/Caches/locationd/
    consolidated.db     # Historical location cache (legacy)
    cache_encryptedB.db # Cell tower and WiFi locations

# App-specific location data
/private/var/mobile/Containers/Data/Application/[UUID]/
    # Individual apps store their own location history

# Facebook/Messenger
/data/data/com.facebook.orca/databases/
    threads_db2         # Messenger conversations
    prefs_db            # User preferences

# Instagram
/data/data/com.instagram.android/databases/
    direct.db           # Direct messages
/data/data/com.instagram.android/shared_prefs/
    # Account tokens and settings

# Telegram
/data/data/org.telegram.messenger/files/
    cache4.db           # Messages and chats
    # Note: Secret chats use device-specific encryption

# Twitter/X
/data/data/com.twitter.android/databases/
    [user_id]-66.db     # Timeline and DMs

# Check for WAL (Write-Ahead Log) files
msgstore.db-wal      # Contains uncommitted transactions
msgstore.db-shm      # Shared memory file

# WAL files may contain deleted records that haven't been
# checkpointed (written back) to the main database

# Freelist pages contain previously deleted data
# Use specialized tools to extract:
# - sqlparse (forensic SQLite parser)
# - undark (SQLite deleted record recovery)
# - Commercial tools: Oxygen, Cellebrite, Magnet AXIOM

# Manual approach: Examine database pages for fragments
SELECT * FROM sqlite_master
WHERE type = 'table';
# Then use hex editor to examine raw page content

# Example: Establishing presence at crime scene

Time: 2024-01-15 14:30 IST
Source: Photo EXIF
GPS: 19.0760, 72.8777 (Mumbai)
Evidence: Photo taken at location

Time: 2024-01-15 14:32 IST
Source: WhatsApp
Message: "I'm here" sent to contact
Evidence: Self-admission of presence

Time: 2024-01-15 14:35 IST
Source: Cell Tower (CDR)
LAC/Cell: 12345/6789
Evidence: Connected to tower covering area

Time: 2024-01-15 14:40 IST
Source: Google Maps
Search: "nearest ATM"
Evidence: Activity at location

Corroboration: Multiple independent sources
confirm presence at location during timeframe