Ability to import iOS Sqlite DB

[Lodifice]

Hi, it happened to me that my iOS device crashed and of course I don't have a Threema backup.
However, I have already tried openMittsu three years ago and managed to import the old Threema ID.
So first of all this is a huge thank you for your work which now enables me to stay connected ;)

I also have a ThreemaData.sqlite from that time.
Since now openMittsu uses sqlite itself, would it be possible to import that database?
It appears to be unencrypted, but my first attempt failed (no error message, just not loaded).

Also, I must have somehow been able to import my Threema ID into openMittsu back then, but I can't remember how.
The version I used back then is openMittsu_0.9.2plus106-g74a18cb-linux-x64-le, starting it I could import my client file which contains a backup string and password... so it seems that back then only ID import worked?

Any help is appreciated, I'm also willing to share more information or spend some of my own time on that.

[blizzard4591]

Back then, the only way was to import the ID Backup (a string of the form AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA-AAAA) together with the password used for encrypting this backup.

What is your plan? Do you want to re-import your ID into the Threema app? If so, open openMittsu, and click "Database" -> "Create Backup...". This will allow you to create a new ID Backup with a password of your choice that you can use in the App. I think you have to click something because the app at first only asks for the Threema Safe thingy, but there is the option to use the old ID Backup somewhere.

If you want to import the sqlite file into openMittsu... That is not possible out of the box, but it could be done with a bit of work I guess. Could you dump the table structure of the file for me (I guess you do not want to send me the entire file ;) )? Tools like https://sqlitebrowser.org/ work great for this. I do not have access to an iOS device, so I need something to go on here.

Did you use a modern version of openMittsu for trying to load the sqlite? If so, could you use a debug build? If you can compile it yourself, supply "-DCMAKE_BUILD_TYPE=Debug -DOPENMITTSU_DEBUG=On" to CMake. This will greatly increase logging output and maybe give us an idea on what is currently not working.

[Lodifice]

My plan is indeed to import the sqlite file into openMittsu. Maybe data recovery works and I will also want to merge the messages received by openMittsu with the more recent messaged of the app... but that's not of concern for now 😄

I have dumped the schema: https://pastebin.com/UDPHvD8J
I've also tried to look at the sqlite database created by openMittsu, but this failed (sqlcipher said that it's not a database, maybe my attempt to decrypt it was wrong).

Unfortunately, the log is not very informative:

Logging location: ***REDACTED***
[2020-04-15 11:47:28.664] [main] [info] Qt library load paths are: /usr/lib/qt/plugins
[2020-04-15 11:47:28.664] [main] [info] Starting OpenMittsu Version: 0.9.15 (+4 commits) build from revis
[2020-04-15 11:47:28.691] [main] [debug] Creating QMainWindow instance.
PulseAudioService: pa_context_connect() failed
[2020-04-15 11:47:28.798] [main] [debug] Starting event loop.
[2020-04-15 11:47:29.033] [main] [info] No new version available. The latest version online is: 0.9.13+46 @ g392d80f (deb) in channel NIGHTLY.
[2020-04-15 11:47:31.698] [main] [warning] System Tray is not available.
[2020-04-15 11:47:31.698] [main] [info] System Tray supports messages.
[2020-04-15 11:47:51.523] [main] [info] Using the crypto-database interface (QSQLCIPHER).
[2020-04-15 11:47:51.528] [main] [info] SQLCipher Version reported as '4.3.0 community'

Only the last two lines appeared after trying to load the file.

[blizzard4591]

The schema looks similar, but it is not the same as the one used by openMittsu, a direct import is out of question.
What you could do (if you are up for a bit of coding) is adapting the format for the BackupReader (see backup/BackupReader.cpp) that I have written for importing the .zip backups of the Threema App - It expects csv, but I think you best bet is creating a script for converting the data from the Sqlite to these CSVs (which must be possible as this is what the App does ;) ).

If you need pointers, write here or send me a mail. Unfortunately I currently do not have time to work on this myself.
Should you do this in C++, we could integrate it "officially" in openMittsu :)

[Lodifice]

There is a script from "back then" which converts the iOS format into a CSV-like structure, basically one file per contact. However, the script doesn't work for me. I'm pretty sure I could fix it, though.

After briefly looking at the code it seems that it is possible to merge a backup into an existing database, can you say anything about that?

Also, it would be handy if you could tell me how to open the openMittsu.sqlite. I tried it with sqlcipher and adding key using pragma, but the response to commands such as .tables was "not a database".

[blizzard4591]

Merging backups: This should be possible with a little bit of new logic around how existing stuff is handled, but it should be very straight forward.

Opening openMittsu.sqlite: If you use the most recent version of https://sqlitebrowser.org/ (the one for SQLCipher, mind you, also - the latest alpha works well), it should "just work".
I just noticed that because of a change upstream, Debug logging did not work anymore, it was re-enabled in 70829e9. Please pull master and recompile your client - when opening the openmittsu database, you should now see log messages about the version of SQLCipher used for opening. If you see "Trying to open database file with backwards compatiblity flags for version 3!", then in the DB Browser, select the settings for v3, otherwise v4. This changes the key derivation, so this is critical to get right.

Also: Do you have any non-standard characters in your password? If so, look in the openMittsu log for a message like "The password contains non-printable or non-basic-ASCII characters. Converting to UTF-8 and stringifying resulting hexadecimal sequence.".
If you see this, you will need to hex-encode your password when viewed as a UTF-8 encoded byte sequence (or just put a LOGGER statement in src/database/SimpleDatabase.cpp line 241!).

[blizzard4591]

Just fyi: I am implementing support for the Threema Safe feature atm, did you have that set up? If so, everything (ID, contacts, groups) excluding messages can be recovered that way, which is much easier than getting the script to work I would guess.

[Lodifice]

Ok, sorry for the late reply – I've been very busy last week, but it's better now.

I managed to open the database using sqlitebrowser (using version 3 compatibility), but it didn't work using the sqlcipher command line tool (even if I enter the settings that sqlitebrowser uses via pragmas). Do you have any experience with that tool? It would be nice to access the database that way in scripts, but using the Qt application should also be fine.

I didn't have Threema Safe set up. However, I managed to get the export script to store at least messages in CSV (but not media). Their contents seem a bit weird, each line begins like an SQL statement INSERT INTO Export_<Some Threema-ID>. Could you provide some dummy files for further investigation?

[blizzard4591]

contacts.csv:

"identity","publickey","verification","acid","tacid","firstname","lastname","nick_name","color"
"ECHOECHO","4a6a1b34dcef15d43cb74de2fd36091be99fbbaf126d099d47d83d919712c72b","UNVERIFIED","","1435r3158-312D3745312D3745","","","ECHOECHO","-16121"

groups.csv:

"id","creator","groupname","created_at","members","deleted"
"a21fca2f9e4aab20","ECHOECHO","Channel Name","1529560488000","ECHOECHO;ECHOECHO","0"

group_message_a21fca2f9e4aab20-ECHOECHO.csv:

"apiid","uid","identity","isoutbox","isread","issaved","messagestae","posted_at","created_at","modified_at","type","body","isstatusmessage","isqueued","caption"
"","300021a7-d9d6-44e2-9279-a4af92c75a3b","","0","1","1","","1482325696767","1482325696790","","TEXT","Group created.","1","0",""

message_ECHOECHO.csv:

"apiid","uid","isoutbox","isread","issaved","messagestae","posted_at","created_at","modified_at","type","body","isstatusmessage","isqueued","caption"
"0a778ceab2edf984","27c53ba9-99b8-4e24-8786-7637eea34950","1","0","1","READ","1464883479530","1464883479530","1464883480000","TEXT","Test","0","1",""

Is this what you were looking for?

[Lodifice]

Yes, except that I only have message_THREEMAID.csv files and no group messages.
The header is

"apiid","uid","isoutbox","isread","issaved","messagestae","posted_at","created_at","modified_at","type","body","isstatusmessage","isqueued"

which is the same minus "caption" (do you have an idea what that is)?

The actual contents are SQL INSERT statements, but that could be fixed by extracting the VALUES tuple. So the real problem with the script is that it mixes group messages with regular messages by the same user (there are definitely group messages in these csv files).

My personal view is that rather than fixing the script, adding the possibility to read the sqlite file directly (which should not be a big deal since openMittsu is using sqlite already). What's your thought on this?