Adventures in WhatsApp DB – extracting messages from backups, with code examples
medium.com
medium.com
I eventually managed to pull the sqlite databases while each was unlocked, merge them, and then put it back and convince WA to read it rather than overwrite from the network.
Several hours of my time, and almost certainly not worth it. Reminded me how bad and annoying platform lock-in is though.
Do you delete all your emails and wipe your contacts when you replace a device too?
2) Yes, I delete emails I don't need anymore. Almost all WhatsApp content is not important.
3) I upload pictures/videos to my Nextcloud NAS after which I delete them. Goes automatically.
It is a sacrifice of user-unfriendly security. They could've easily made a backup function which does not (solely) involve Google Drive. They opted not to.
However there's an upside to this as well: if you delete that key, you're done for. If your discussion partner(s) do the same, the data between you and your discussion partners is also done for.
I agree with you, but I don't find it difficult to work around it, and the previous solutions (plaintext, unencrypted logs) were terrible.
On the other hand I find it interesting that Apple decided it was important to create some shim on top of sqlite with their own conventions, but said shim is not so convoluted it can't be reverse-engineered. Can't really decide if it's good or bad.
Apple adds additional metadata on top of the entities themselves in order to provide smart versioning and migration utilities. The entire object model is persisted inside the database, so that lightweight and heavyweight migration patterns can be deduced the next time the database is read. This metadata can also assist with opening WhatsApp's database and parsing it using Core Data.
Some network protocols use timestamps for security. For example, OAuth 1.0 HMAC used timestamps as part of its mechanism for preventing replay attacks.
I have no particular insight into what WhatsApp is doing, though.
"Reading around it seems that Apple, in their infinite unique wisdom have decided to use dates starting from 1.1.2001 on iPhone so let’s see what happens if we add an offset ...."
This did put a smile on my face. The author forgets/doesn't know WhatsApp is Facebook's app, has nothing to do with Apple and the fact that he used this from an iPhone is irrelevant. On Android local /data/data WhatsApp's database is the same.
"Let’s see what we’ve got. The below is my analysis based on the data I’ve found in the tables in my own DB and my inferences about it: 1. Z_PK — seems like a serial number..."
Yup, it is a serial number. One those who dream SQL, like me, for past 20+ years, will also call it Primary Key. This article is getting better with every re-read :)
(the Google Drive backup of it is not encrypted, but ... it's also not accessible to you! Facebook and Google are dealing your data behind your back, but don't believe you have any rights to access this data except through the tiny whatsapp app window)
You can access the Google Drive backup with this third-party tool, which admittedly is less than ideal: https://github.com/YuriCosta/WhatsApp-GD-Extractor-Multithre...
re: original question: AFAIK since Android 8 there is encryption of everything exported.
I try to hold on to all digital communications with people I know (as opposed to service providers/professional interactions/strangers on the internet), and preserve it on my own hardware in portable formats. The idea is to have the digital equivelant of the box in the attic full of letters. Maybe that record will be interesting to me in a few decades, or to my descendants - or maybe not, which is fine too. But either way it would be a damn shame if we all collectively lost the "boxes of letters in the attic" to digitization, and these proprietary platforms aren't helping.