How to obtain database file and open it on a desktop?


#1

Codebook v3.1.3 on iOS 9.3.4 on an iPhone 5

Hello,

How do I access the database file? I’ve looked in iTunes under ‘device’ -> ‘apps’ but this app does not appear in the list that shows files available to share with the computer.

I’m preparing for the eventuality that I lose or break the iPhone. I’ll need to access the dB on a PC as I won’t be buying another Apple product and therefore will not be able to perform a restore. So, I’ll need to read the dB outside of the iOS app.

Thank You.


#2

Hello @teracow

If you are looking to have easy access to the Codebook database file outside of iTunes, the best option would be to setup synchronization with either Dropbox or Google Drive within the application. We’ve put together some instructions for doing so here. Thanks!


#3

Thank You Nick. :slight_smile:

I’ve now done that but am now stuck decrypting the dB. Using OpenSUSE 13.2:

$ sqlcipher strip.db 
sqlite> PRAGMA key = 'password'; 
sqlite> ATTACH DATABASE 'plaintext.db' AS plaintext KEY '';
Error: file is encrypted or is not a database
sqlite> .databases 
Error: file is encrypted or is not a database

After some searching online for this (and seeing lots of help from the friendly folks at Zetetic), I’m not sure what do do next. SQL is not my forté. :slight_smile:

Any thoughts?

Thank you.


#4

some more info:

$ sqlcipher strip.db 
sqlite> PRAGMA key = 'password'; 
sqlite> .schema
CREATE TABLE ditto_log (  rep_id TEXT NOT NULL,  csn INTEGER NOT NULL,  timestamp DATETIME NOT NULL,  type TEXT NOT NULL,  object_name TEXT NOT NULL,  object_guid TEXT NOT NULL,  operation TEXT NOT NULL,  diff TEXT);
CREATE TABLE ditto_reps (  rep_id TEXT PRIMARY KEY,  is_local INTEGER NOT NULL,  csn INTEGER NOT NULL);

… and so on.

So, it seems the dB is decrypted without issue.

I think problem might be with opening an empty database file to export into. Any thoughts? Do I need to create some sort of structure within it first?

Thank you.


#5

Hi @teracow

What version of SQLCipher are you using? You can find out by executing the following within the SQLCipher shell:

PRAGMA cipher_version;

You will need to provide the master password you use to access Codebook when you execute the PRAGMA key = '…'; command. Finally, if you are attempting to export the SQLCipher database to a plain text SQLite database, you will need to do the following:

$ sqlcipher strip.db
sqlite> PRAGMA key = 'YourPasswordGoesHere';
sqlite> ATTACH DATABASE 'plaintext.db' as plaintext KEY '';
sqlite> SELECT sqlcipher_export('plaintext');
sqlite> DETACH DATABASE plaintext;
sqlite> .q

Please note if you do that, all your sensitive information will be available in a decrypted form in the plaintext.db file. Would you give that a try and let me know your results? Thanks!


#6

lol… you just beat me to it… I was posting back here to say that it works now… I’ve been able to open the ‘plaintext.db’ in sqlitebrowser after exporting.

Don’t know why it worked the second time… I checked my command history in sqlcipher and I typed all commands the same both times. :blush:

Thank you.

Also, just for posterity:
SQLCipher version 3.8.10.2 2015-05-20


#7

Hello @teracow

Thank you for the follow up, I’m glad to hear you were able to export your data. Take care!


#8

Hi Nick,

It’s been a productive day. Looks like I’ve found a new project to work on. :wink:

Thank you.


#9

Hi @teracow

Nice work, thanks for sharing! It may be worth noting in your project that anyone who performs an export to utilize the tool is exposing their sensitive data.


#10

Thanks Nick, I’ll include a note about this if/when I add the ability to read directly from the strip.db file (which would require their password). At the moment, they have to export from the encrypted db to a plaintext db themselves - which I hope would only be done by someone who understands what that means.

Thank you. :slight_smile:


#12

I have the same question, i entered my PRAGMA key correctly and all that i got is

sqlite> PRAGMA cipher_version;
3.4.0
sqlite> PRAGMA key = '99002701';
sqlite> ATTACH DATABASE 'plaintext.db' as plaintext KEY '';
Error: file is encrypted or is not a database

im stuck decrypting the dB (FEDORA) and i want to open my db in desktop. (DB created in android, also i’ve opened the db in Visual studio but i want to decrypt it for use in dbeaver or navicat)


#13

Hi @chekostyle

What version of SQLCipher was used to create the database?


#14

I’m not sure exactly what version, but it’s older than the last update.


#15

Hi @chekostyle

Depending on what version you used to create the database you may need to adjust the key derivation length, and/or HMAC existence. Are there any runtime configuration changes that were made when creating the database?