Whatsapp export met SQL en PHP

Inleiding

Vooraanstaande berichtendiensten zoals Signal en Whatsapp maken gebruik van punt-tot-punt versleuteling (end-to-end encryption, E2E). Hiermee wordt alle data tussen de gesprekspartners versleuteld en kan niemand anders meekijken - ook de aanbieder zelf niet. De back-ups van deze apps zijn eveneens versleuteld, om ook offline de veiligheid van de berichten en data te waarborgen.

Af en toe heb ik de behoefte om mijn chatgeschiedenis door te bladeren en zo bepaalde gesprekken nog eens terug te lezen. Maar met die versleutelde back-ups kon ik zo 1-2-3 niets beginnen. Ik zocht en vond de informatie om via een omweg de onversleutelde databases hiervoor te gebruiken. Maar écht comfortabel is het openen van een SQLite database en het uitvoeren van talloze SQL queries daarvoor niet. Dit moest toch elegant geautomatiseerd kunnen worden? Ja dus! Het uiteindelijke resultaat is op Github te bewonderen.

Update

Januari 2023 - In een recente Whatsapp update is de databasestructuur veranderd. Het script dat ik geschreven had, werkte niet meer; ik moest opnieuw de tabellen doorzoeken naar de gegevens voor mijn export. Uiteindelijk heb ik de aanpassingen online verwerkt in versie 0.2.

; WA Chat Export v0.1
; Creates a readable export of WhatsApp's chat history
;
; Export started: 2022-02-03 17:19:08 +01:00
; Group chat: no
;
; Participants in this chat:
;  +31612345678: Alice
;  +31687654321: Bob

[2016-07-12 08:16:32 +02:00] Alice: Hi Bob, how are you?
[2016-07-12 10:09:36 +02:00]   Bob: Hi.
[2016-07-26 09:37:42 +02:00]   Bob: I'm fine, how's your cat doing?
[2016-07-26 09:38:27 +02:00] Alice: (media file) Photo of my cat
[2016-07-26 09:39:25 +02:00]   Bob: Waw!
...

Prototype

Gelukkig bood PHP, de scripttaal waar ik het beste in thuis ben, van huis uit een mogelijkheid om met SQLite-databases te werken. Met behulp van een 'normale' SQLite browser kon ik de tabellen en kolommen die ik nodig zou hebben, makkelijk identificeren.

In het bestand wa.db staan de namen van alle bekende contactpersonen; zeg maar, het adresboek van de applicatie. Alle andere data, daaronder gegevens van de groepsapps en de afzonderlijke chatberichten, staan in msgstore.db. De tabellen en kolommen die voor mijn script van belang zijn, zijn:

SELECT
    `jid`,          -- ID of the contact
    `display_name`, -- name as set in the user's addressbook
    `wa_name`       -- name as set by the contact him/herself
FROM
    `wa_contacts`;  -- in wa.db

SELECT
    `J`.`raw_string`, -- ID of the group chat
    `C`.`subject`     -- name of the group chat
FROM
    `chat` AS `C`     -- in msgstore.db
    JOIN `jid` AS `J` ON `C`.`jid_row_id` = `J`.`_id`;

SELECT
    `key_remote_jid`,  -- ID of the remote contact, empty if sent by 'me'
    `key_from_me`,     -- boolean, 1 if sent by 'me', 0 otherwise 
    `data`,            -- message body
    `remote_resource`, -- in a group chat, the ID of sender, empty otherwise
    `media_caption`,   -- caption of a media file, if any
    `forwarded`,       -- boolean, 1 if forwarded to 'me', 0 otherwise
    `timestamp`        -- Unix timestamp * 1000, when sent/received
FROM
    `messages`         -- in msgstore.db
ORDER BY
    `key_remote_jid` ASC,
    `timestamp` ASC;

Elk jid in de verschillende tabellen is een unieke tekenreeks in de vorm van een e-mailadres. Bij contactpersonen bestaat het uit het internationale telefoonnummer, een @ en het domein s.whatsapp.net. Bijvoorbeeld: 31612345678@s.whatsapp.net.

Bij een groepchat bestaat het uit het internationale telefoonnummer van de gebruiker die de groep heeft aangemaakt, een minteken, de Unix-timestamp van het moment dat de groep is aangemaakt, een @ en het domein g.us. Bijvoorbeeld: 31687654321-1576684071@g.us.

CLI handling

Met behulp van PHP's getopt() functie kun je argumenten op de commandoprompt verwerken, ongeacht hun volgorde. Ook kun je zowel korte argumenten (van één minteken met één letter) als lange (twee mintekens en meerdere letters) ondersteunen. In dit geval koos ik voor beide:

WA Chat Export v0.1
Chat-export tool for unencrypted WhatsApp databases

Usage: wa-chat-export.php OPTIONS

[ -h | --help ]              show this help message and exit
[ -a | --addressdb= ] FILE   path to WhatsApp addressbook file
[ -m | --messagedb= ] FILE   path to WhatsApp database file
[ -n | --number= ] NUMBER    phonenumber of the exporting user, e.g. +31612345678 
[ -o | --outdir= ] DIRECTORY output folder, defaults to parent of database file

De code die de argumenten verwerkt, ziet er (ingekort) als volgt uit:

$validSQLite3mimes = ['application/x-sqlite3', 'application/vnd.sqlite3'];
$wacDBfile = null;
$msgDBfile = null;
$mePhone = null;
$outDir = null;

$opts = getopt(
    'ha:m:n:o:',
    ['help', 'addressdb:', 'messagedb:', 'number:', 'outdir:']
);
if (array_intersect_key(['h' => 0, 'help' => 0], $opts)) {
    print $outHeader; // print the program name and version
    print $outUsage;  // print the program usage info
    exit(0);
}
if (array_intersect_key(['a' => 0, 'addressdb' => 0], $opts)) {
    $wacDBfile = array_key_exists('a', $opts) ? $opts['a'] : $opts['addressdb'];

    if (!file_exists($wacDBfile) || !is_readable($wacDBfile)) {
        print $outHeader; // print program name and version to STDOUT
        fwrite(
            STDERR,
            "The addressbook file does not exist, or could not be read.".
            PHP_EOL."Exiting.".PHP_EOL
        );
        exit(1);
    }
    if (!in_array(mime_content_type($wacDBfile), $validSQLite3mimes)) {
        print $outHeader; // print program name and version to STDOUT
        fwrite(
            STDERR,
            "The addressbook file is not an SQLite3 database.".
            PHP_EOL."Exiting.".PHP_EOL
        );
        exit(2);
    }
}

// ...

if (array_intersect_key(['n' => 0, 'number' => 0], $opts)) {
    $mePhone = array_key_exists('n', $opts) ? $opts['n'] : $opts['number'];

    if (preg_match('!^\+?[0-9]+$!', $mePhone) != 1) {
        print $outHeader; // print program name and version to STDOUT
        fwrite(
            STDERR,
            "The phonenumber was not in the correct format, e.g. '+31612345678'.".
            PHP_EOL."Exiting.".PHP_EOL
        );
        exit(5);
    }
}
if (empty($wacDBfile) || empty($msgDBfile) || empty($mePhone)) {
    print $outHeader; // print program name and version to STDOUT
    print $outUsage;  // print program usage info to STDOUT

    fwrite(
        STDERR,
        "Not all required arguments were provided.".
        PHP_EOL."Exiting.".PHP_EOL
    );
    exit(6);
}
if (array_intersect_key(['o' => 0, 'outdir' => 0], $opts)) {
    $outDir = array_key_exists('o', $opts) ? $opts['o'] : $opts['outdir'];
} else {
    $outDir = dirname($msgDBfile);
}
if (!is_dir($outDir) || !is_writable($outDir)) {
    print $outHeader; // print program name and version to STDOUT
    fwrite(
        STDERR,
        "The output directory does not exist, or isn't writable.".
        PHP_EOL."Exiting.".PHP_EOL
    );
    exit(7);
}

Klassen

Het export script maakt gebruik van een aantal simpele klassen: Contact met ID en naam, Message met de gegevens van de berichten zelf. Hieronder zijn ze verkort weergegeven.

Contact

class Contact
{
    private $_jID;

    public function getJID() {
        return $this->_jID;
    }
    public function setJID(string $_jID) {
        $this->_jID = $_jID;
        return $this;
    }

    private $_displayName;

    public function getDisplayName() {
        return $this->_displayName;
    }
    public function setDisplayName(string $_displayName) {
        $this->_displayName = $_displayName;
        return $this;
    }
}

Message

class Message
{
    private $_fromMe = false;
    private $_forwarded = false;
    private $_inGroup = false;

    private $_remote;     // Contact | NULL
    private $_receivedAt; // DateTime
    private $_text;       // string
    private $_group;      // Contact | NULL

    // ...
}

ChatExporter

De kern van het script huist in de klasse ChatExporter.

class ChatExporter
{
    private $_addressBook = []; // Contact[]
    private $_me;               // Contact
    private $_messages = [];    // Message[]
    private $_outputDirecory;   // string

    // ...
}

De argumenten die via de commandoprompt aan het script worden meegegeven komen via de constructor in de ChatExporter terecht:

public function __construct(
    string $abDbFilename, string $msgDbFilename, string $mePhoneNumber,
    string $outputDirectory
) {
    $meJID = trim($mePhoneNumber, "+")."@s.whatsapp.net";
    $this->_outputDirecory = $outputDirectory;

    // ...
}

Daarna wordt het adresboek geopend en de ID's en namen als contactpersonen opgeslagen.

$wa = new \SQLite3($abDbFilename, SQLITE3_OPEN_READONLY);
$res = $wa->query(
    "SELECT `jid`, `display_name`, `wa_name` FROM `wa_contacts`"
);
// Add contacts to local address book
while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
    if (!empty($row['display_name']) || !empty($row['wa_name'])) {
        $n = $row['wa_name'] ? '('.$row['wa_name'].')' : null;

        $c = (new Contact())
            ->setJID($row['jid'])
            ->setDisplayName($row['display_name'] ?? $n);
        $this->addContactToAddressBook($c);

        if ($row['jid'] == $meJID) {
            $this->setMe($c);
        }
    }
}
$wa->close();

De gegevens van de groepschats worden uit de tabel chat ingelezen en eveneens aan het adresboek toegevoegd.

$db = new \SQLite3($msgDbFilename, SQLITE3_OPEN_READONLY);
$res = $db->query(
    "SELECT `J`.`raw_string`, `C`.`subject`
    FROM `chat` AS `C` JOIN `jid` AS `J` ON `C`.`jid_row_id` = `J`.`_id`"
);
// Add group chats as separate contacts to the address book
while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
    if (!empty($row['raw_string']) && !empty($row['subject'])) {
        $this->addContactToAddressBook(
            (new Contact())
                ->setJID($row['raw_string'])
                ->setDisplayName($row['subject'])
        );
    }
}
// ...
$db->close();

Tot slot worden alle berichten, per contactpersoon van het adresboek, toegevoegd aan de collectie.

// Loop through address book...
foreach ($this->_addressBook as $jID => $contact) {
    $stmt = $db->prepare(
        "SELECT
            `key_remote_jid`, `key_from_me`, `data`, `remote_resource`,
            `media_caption`, `forwarded`, `timestamp`
        FROM
            `messages`
        WHERE
            `key_remote_jid` == :jid
            AND `status` != 6
        ORDER BY
            `key_remote_jid` ASC,
            `timestamp` ASC"
    );
    $stmt->bindValue(':jid', $jID, SQLITE3_TEXT);

    if ($res = $stmt->execute()) {

        // ... and fetch this contact's messages
        while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
            $msg = (new Message())
                ->setFromMe($row['key_from_me'] == 1)
                ->setForwarded($row['forwarded'] == 1)
                ->setInGroup(
                    (strpos($row['key_remote_jid'], '-') !== false)
                );
            $remoteJid = $row['key_remote_jid'];
            $groupJid = null;

            if ($msg->isInGroup()) {
                $remoteJid = $row['remote_resource'];
                $groupJid = $row['key_remote_jid'];
            }
            $remoteContact = null;
            if (!$msg->isFromMe()) {
                $remoteContact = $this->_findContact($remoteJid)
                    ?? (new Contact())
                    ->setJID($remoteJid)
                    ->setDisplayName(self::_jidToNumber($remoteJid));
            }
            $groupContact = $this->_findContact($groupJid);
            $dt = (new \DateTime())
                ->setTimestamp(intdiv($row['timestamp'], 1000));
            $fw = ($msg->isForwarded() ? '(forwarded) ' : '');
            $mediaCaption = ' '.($row['media_caption'] ?? '');
            $text = $fw.($row['data'] ?? '(media file)'.$mediaCaption);
            $msg
                ->setRemote($remoteContact)
                ->setGroup($groupContact)
                ->setReceivedAt($dt)
                ->setText($text);
            // Add the message to this contact's chats
            $this->addMessage($contact, $msg);
        }
    }
}

Export

Het daadwerkelijke exporteren begint met het doorlopen van het actuele adresboek. Voor alle contactpersonen (dus ook groepchats, maar zonder de eigen gebruiker) wordt een nieuw export-bestand gemaakt.

public function exportAllChats() : int
{
    $x = 0;
    foreach ($this->_addressBook as $jID => $contact) {
        if ($contact !== $this->_me) {
            $o = $this->exportSingleChat($jID);
            if (!empty($o)) {
                $ok = file_put_contents(
                    $this->_outputDirecory.DIRECTORY_SEPARATOR.$jID.'.txt',
                    $o
                );
                if ($ok !== false) {
                    $x++;
                }
            }
        }
    }
    return $x;
}

exportSingleChat() is de functie die voor één contactpersoon (lees: één specifiek jID) de export uitvoert. Bovenaan het exportbestand staan alle deelnemers aan het gesprek (participants). Die lijst begint met de eigen gebruiker ($this->_me). In groepschats worden alle andere deelnemers vanuit de berichten toegevoegd. In privéchats wordt met de functie _findContact() de betreffende gesprekspartner opgezocht. Daarna wordt de uiteindelijke lijst in tekst opgebouwd binnenin een array_walk().

public function exportSingleChat(string $jID) : string
{
    if (!array_key_exists($jID, $this->_messages)) {
        return '';
    }
    $o = '';
    $msgs = $this->_messages[$jID];
    $top1msg = reset($msgs);

    $participants = [];
    $participants[$this->_me->getJID()] = $this->_me;

    if ($top1msg->isInGroup()) {
        foreach ($msgs as $m) {
            if ($m->getRemote()) {
                $participants[$m->getRemote()->getJID()] = $m->getRemote();    
            }
        }
    } else {
        $participants[$jID] = $this->_findContact($jID);
    }
    uasort(
        $participants, function (Contact $c1, Contact $c2) {
            return strcmp($c1->getDisplayName(), $c2->getDisplayName());
        }
    );
    $participantsString = '';
    $maxDisplayNameLength = 0;

    array_walk(
        $participants,
        function (Contact $c, string $jid) use (
            &$participantsString, &$maxDisplayNameLength
        ) {
            $participantsString .= sprintf(
                ';  %s: %s%s',
                self::_jidToNumber($jid),
                $c->getDisplayName(),
                PHP_EOL
            );
            $senderStringLength = mb_strlen($c->getDisplayName());
            if ($maxDisplayNameLength < $senderStringLength) {
                $maxDisplayNameLength = $senderStringLength;
            }
        }
    );

    // ...
}

Finale met hobbels

Elk exportbestand begint met een header. Hierin wordt met sprintf() de starttijd van de export, de eventuele groepchat-naam en de deelnemers met hun telefoonnummers getoond. Voor het formatteren van datum en tijd maak ik gebruik van PHP's IntlDateFormatter.

$exportFileHeader = <<<b8a38c2a67185f5d7f25
; WA Chat Export v0.1
; Creates a readable export of WhatsApp's chat history
;
; Export started: %s
; Group chat: %s
;
; Participants in this chat:
%s

b8a38c2a67185f5d7f25;

$locale = 'nl_NL.utf8';
$tz = new \DateTimeZone('Europe/Amsterdam');
$dtNow = new \DateTime('now', $tz);
$dtfmt = new \IntlDateFormatter(
    $locale, \IntlDateFormatter::NONE, \IntlDateFormatter::NONE,
    $tz, \IntlDateFormatter::GREGORIAN, "yyyy-MM-dd HH:mm:ss xxx"
);
$o .= sprintf(
    $exportFileHeader,
    $dtfmt->format(new \DateTime('now', $tz)),
    ($top1msg->isInGroup() ? $top1msg->getGroup()->getDisplayName() : 'no'),
    $participantsString
);

Onder de header volgen de berichten, voorafgegaan door een timestamp. Om het visueel aantrekkelijk maar vooral makkelijk leesbaar te maken, heb ik er voor gekozen om de namen/telefoonnumers rechts uit te lijnen met daarachter de berichten links uitgelijnd. Berichten met meerdere regels worden eveneens zover ingesprongen, dat het gesprek optimaal leesbaar wordt.

foreach ($msgs as $m) {
    $indent = 29 + $maxDisplayNameLength + 2;
    $indentSpaces = str_repeat(' ', $indent);
    $o .= sprintf(
        '[%s] %'.$maxDisplayNameLength.'s: %s%s',
        $dtfmt->format($m->getReceivedAt()),
        $m->isFromMe()
            ? $this->_me->getDisplayName()
            : $m->getRemote()->getDisplayName(),
        str_replace("\n", "\n".$indentSpaces, $m->getText()),
        PHP_EOL
    );
}

Had ik eindelijk de export af en bladerde ik steekproefsgewijs door de tientallen bestanden, kwam ik een tekortkoming van PHP's sprintf() op het spoor. Zo gauw een contactpersoon bijvoorbeeld een é of ë in zijn naam had, klopte het aantal spaties voor het uitlijnen niet meer.

Dit bleek te worden veroorzaakt door PHP's kijk op strings. Elk karakter wordt daar met één byte gecodeerd. Moderne encodings zoals UTF-8 hebben echter vaak 2 of meer bytes per karakter. Voor strlen() en aanverwante functies zijn daarvoor de mb_...() functies uitgevonden. Maar voor sprintf() is er geen vergelijkbare mb_sprintf() beschikbaar. Gelukkig zijn er enthousiaste ontwikkelaars die dat gat vakkundig opvullen. Ik mocht er zelf uiteindelijk nog één kleine fix aan toevoegen; zie de volledige broncode op Github.

Conclusie

Het schrijven van dit script en de omliggende code was een leuke uitdaging. Ik heb weer eens gestoeid met een nieuwe database en net zolang aan de export geschaafd totdat ik helemaal tevreden was. Ook het documenteren voor Github en het schrijven van dit artikel waren uiterst leerzaam.

Wel wist ik, nog vóór het publiceren van dit script, dat een waarschuwing op zijn plaats zou zijn. Berichtdiensten zoals Signal en (in dit geval) Whatsapp maken gebruik van end-to-end versleuteling en gaan tot het uiterste om de communicatie te beveiligen en ook de offline back-ups te versleutelen.

Handmatig goochelen met onversleutelde databases en het exporteren van chats als platte tekst staan haaks op deze inspanningen. Zie dit script dan ook alsjeblieft als een mooi vrijetijdsproject van de programmeur en wellicht een interessante stukje lectuur, maar niet méér dan dat. Dankjewel!