Build ok but does not encrypt

[Jim505]

I am not sure if anyone monitoring mobileorchard.com section of the SQLCipher. I left a comment there few days back about SQLCipher. I dont get compile errors and the build is ok, but the engine treats standard database as if it was already encrypted so when using the key after open it does not allow interaction with the database as if the supplied key was wrong.

Is there a way i can encrypt the database outside iPhone simulator then supply db file already encrypted to xcode resources, this will confirm if the above is true as if i supply the correct key and it worked then something not letting the key encrypt standard database on first run.

[sjlombardo]

The first issue you're running into is that PRAGMA key can't be used to encrypt an existing database. PRAGMA key, and it's sqlite3_key function counterpart, are used to prepare the key information in the database handle. If you want to encrypt a database with a key you'll need to run it as the first operation on a new database.

When you run PRAGMA key on an existing plaintext SQLite database, SQLCipher will still treat the database as if it were encrypted. This will include reading salt from the first 16 bytes of the database file, deriving the provided key and attempting to decrypt pages as they are read from storage. Of course, since the data is plain text the decryption options will fail.

The second issue is an oversight in the documentation on my part for which I apologize. Rekeying a standard database is not supported at this time. The upstream SQLite source introduced a regression several versions ago that prevented the reserved page size from being modified during a VACUUM operation. SQLCipher uses CBC mode encryption by default and requires a reserved data segment at the end of each page to store the initialization vector. Because we can't modify the reserve size of an database we can't encrypt a pre-existing plaintext database using the rekey operation. Unfortunately I didn't update the README to reflect that rekey on an existing database is unsupported. I've made the change now.

As for your options moving forward, my recommendation would be to start off with an encrypted database from the onset if at all possible. When you first open the database run PRAGMA key and any further writes will be fully encrypted as you desire. As the comments in this thread have show there is a really small overhead.

If you can't start off using encryption from the start you could take a standard database, ATTACH an encrypted DB, and then copy your tables and data between the two. This is effectively what a rekey operation on an unencrypted database would do anyway.

Finally, we have a potential workaround under development that would allow rekeying of a standard database. However, we're still working on ironing out some kinks and optimizing it. I can't commit to an exact timeframe but we will be adding support in the future

[sjlombardo]

Hi Jim,

Here is some information on how to work with SQLCipher from the command line to create encrypted databases outside of xcod using a SQLCipher enabled sqlite3 command line tool. Go into the SQLCipher source directory and run this:

./configure CFLAGS="-DSQLITE_HAS_CODEC -lcrypto"

make

Then, in the same directory run

./sqlite3 /path/to/your/database

When it loads then run some commands:

sqlite> pragma key = '43ec78dec5e8805d143bb1a1452d274ff49cb';
sqlite> select * from sqlite_master;

Once you set pragma key on a new database all further writes will be encrypted. This should let you create an encrypted database outside of xcode and then load it into your project if you desire.

[Jim505]

Hi Stephen,

Thank you for taking the time to write a detailed explanation and your guide to move forward seems the logical next step. I am going to try to implement that today.

Last night i had defined CODEC_DEBUG for sqlchiper xcode project and was able to see all the codec_traces.

As mentioned offline in email, it seems rekey works if you key = '' (empty key with no space between '', then rekey command after the key, this does make the db get encrypted ok. However, when using same key used in rekey again to open it it does not decrypt it, i was still digging through sqlite3.c code and understanding every codec_trace when i got your reply. I am sure you guys will know more what happened there and what key the rekey used really and be able to fix it way faster and probably better than me trying to understand every line in sqlite3.c to solve my problem.

I am very new to MAC and XCode in general (actually this month) and come from a two decade long career in C++ for Win, i did notice somewhere in the docs that you already provided compiled DLL library but couldnt find it on the site or project folder. It will be fairly easier for us to make a Windows GUI App (compared with a MAC App) which does this initial creation of DB, copy data over. For now, we will build a small iphone simulator app that create a new db and open an existing db and copy over. But if someone would provide a standard MAC app that does this along with this project that will be very helpful for this SQLCipher starters.

[Jim505]

My issue is now clear and resolved with your suggestion. We made an app that takes the standard DB and then copy it over to a new created and encrypted DB and now SQLCipher works great.

Thanx again Stephan, keep up this great work.

[sjlombardo]

Jim,

I'm really happy that everything is working out for you now. We'll let you know as soon as rekey on an unencrypted database is working. Thanks again for your patience!

Cheers,
Stephen