SQL with Friends ?

I'm a regular player of the WordsWithFriends game from Zynga. With some of my regular opponents, we have some side chat. That might be something as simple as letting them know you won't be playing for a few days, or a joke arising from an odd sequence of words.

Recently I'd been sent an URL as a chat message, with a picture from a holiday. It was quite a long URL, with a dubious few characters that may have been the number zero or the letter "O" etc. The chat doesn't allow copying, so rather than trying the variations manually, I took the geek road.

Starting with a Cheeky Monkey post, I learnt that the chat messages were probably in an SQLLite file for the application in a relatively inaccessible 'data/data' location on my Android phone or tablet. 

Stackoverflow told me that I can pull the information from there using the Android debugger's backup command (adb). You may need to install a bunch of stuff, such as an up-to-date Java JDK, to get that running. I'd done that before so it was pretty painless. You also need to enable USB debugging on your device.

Those backups are almost, but not quite, a TAR file. I grabbed a Java tool to convert my backup file into a regular TAR, and then unzipped them with 7-Zip.

It wasn't too hard to find the relevant db file that contained the chat messages. I've got a newer version of the game than the one Cheeky Monkey used, so I had to dig a bit more. My package was called "com.zynga.wwf2.free" rather than the older "com.zynga.words" (but I still had the data from the older version on my phone).

Once I found the right package, the db file was in the db directory as "wf_database.sqlite". sqlite3 was conveniently in the same toolkit as the android debugger.

Back to Stackoverflow for some quick sqlite info and I had a set of CREATE TABLE and INSERT statements.

I could have simply grepped for the URL, but being a database person I couldn't resist a final stage.

A few find/replaces were need to switch the DDL to Oracle syntax (different data type names and Oracle is constrained by the 30 character column names). I then imported the users (players), games, moves and chat messages into my XE database and came up with a query to extract the chat messages and the player who posted it.

I think the chat for a deleted game would be a lot harder to recover. While you don't need to root your device, you will need to enable debugging and authorise the backup and so you need regular access to the device. If you can't get past the lock screen, this won't help.

I mentioned that I still had the data files for the older version of the game. I mistakenly opened these first, and was surprised to find that the user data included email addresses for many entries. None were for my regular opponents, but some were for people I recall playing once or twice. I don't recall many of the users, who may have been people I played as a random pick, or may have been on a 'leaderboard'. The data for the newer version of the game only had the email address for my user. 

My player name (sydoracle) is pretty easy to track back to the "real" me, and I use a unique email address when I sign up to most services. But others might have been more concerned to find the email addresses were being shared, even in a concealed manner.