Most people probably enjoy New Year’s eve beneath a lovely firework with some sparkling wine. Notwithstanding here is my New Year’s eve story.

My story

After I upgraded from Android M dev to Android M stable, I experienced a lot of unknown issues with the app Threema. Eventually I managed to resolve them, however I had to encrypt their Android database and as this is quite a complicated task without given instructions, I wanted to share my insights.

You can find all detailed instructions and some helper scripts on github. These instructions should be straightfoward, so let me present some interesting results that I obtained.

Contacts

By looking at the decrypted database, one can verify what information Threema actually saves. As you can see below the public key of every contact is stored and Threema internally only saves information that you expected it to save e.g. first and last name or the AndroidContactId when given.

sqlite> .schema contacts
CREATE TABLE `contacts` (`identity` VARCHAR , `publicKey` BLOB , `firstName` VARCHAR , `lastName` VARCHAR , `publicNickName` VARCHAR , `verificationLevel` INTEGER , `state` VARCHAR DEFAULT 'ACTIVE' NOT NULL , `androidContactId` VARCHAR , `threemaAndroidContactId` VARCHAR , `isSynchronized` SMALLINT DEFAULT 0 , `featureLevel` INTEGER DEFAULT 0 NOT NULL , `color` INTEGER , `avatarExpires` BIGINT , PRIMARY KEY (`identity`) );

Messages

Messages just contain their connected apiMessageId, identity, body and their interaction status (sent, delievered, read) - nothing more is tracked. However I know that since March 2014 until 2015 I sent 16304 personal messages and received 13852 messages.

Groups

Group messages are according to Threema’s whitepaper implemented with End-to-End encryption.

In Threema, groups are managed without any involvement of the servers. That is, the servers do not know which groups exist and which users are members of which groups

If this being true, why is there an apiGroupId saved in the local database?

CREATE TABLE `m_group` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `apiGroupId` VARCHAR , `name` VARCHAR , `creatorIdentity` VARCHAR , `createdAt` VARCHAR , `synchronizedAt` BIGINT , `deleted` SMALLINT );

Update The developer of Threema wrote a clarification (Thanks!)

The apiGroupId is a randomly generated unique id provided by the creator of the group and not the server (the name of the column might be a little misleading). A group is uniquely identified by the combination of apiGroupId and creatorIdentity.

Ballots (aka surveys)

Ballots seem also to be implemented in End-to-End - so remember that all your friends can see your vote. I personally had a couple of unclosed ballots (the owner already closed them on his phone), just set them to closed and your annoyance has an end.

Media files

All locally stored media files are encrypted with one’s static key phase. With this code you can decrypt them.

Acknowledgment

A big thanks goes to the Threema developer for building this convenient end-to-end encrypted cross-platform messenger and letting me publish the decompiled instructions.

Next

Don’t forget to read Threema’s excellent whitepaper about their used cryptography.

What do you plan to with your gained access to your Threema database? Converting your message history to csv for archiving purposes? Extracting the identities of your Threema friends? Let me know in the comments or on github!