C# to IOS encrypted database Error on sqlite3_prepare_v2

[E K]

Greetings all,

I'm having a bit of a problem and I hope that someone might be able to assist. I find this issue rather strange because it is not as though there is a lot of code involved between the encrypted and unencrypted DB handling yet the unecrypted DB works just fine and the encrypted DB does not.

Here's the situation...

We have a C# back end synchronizing a database with an IOS (iPad and iPhone). The database is streamed to the device in large packets though the details aren't important. It's been working for a few years now. The IOS side of things is the sqlcipher community edition and the C# back end is the professional edition.

Recently, someone requested that the DB be encrypted on the server side presumably to save cycles on the device. The Docs seem pretty straight forward so I downloaded the System.Data.Sqlite.dll ADO.Net provider from here and wired it up into the server side slapped in a test Key and two lines of code later had an encrypted database.

I do note that my registered copy of Sqlite Expert cannot open this database. Though I don't know why (different encryption algo maybe?).

Because of this I wrote a quick UI tool to allow me to look at the encrypted database. This opens it up just fine, displays table structures and data.

Once the DB gets transferred to the device, however, the device's SqlCipher isn't able to successfully query the database, always returning error 26 - file is encrypted or is not a database.

I have spent the last day or so poking around the site and the net trying to figure out why and I'm coming up dry.

On the server I am currently using the following line of code to encrypt the database:

tempConnection.ExecuteNonQuery(string.Format("PRAGMA key=\"x'{0}'\"", "2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99"));

ExecuteNonQuery is an extension method on IDbConnection that wraps running a query against an already open connection. I did note that in order to run a query against a connection the connection has to be open. This is fine for creating a new DB but for opening an existing encrypted DB in C#-land you cannot call connection.Open() without a password. I've also used the example code's casting of 

((SQLiteConnection)tempConnection).SetPassword(<<the key>>);

but that didn't work either. I've tried it with and without the escaped quotes... with and without the x... with a simple password of 'test123' and none of it has worked.

The key is copy/pasted right out of the docs here. 

On the device side I use the following block of code to try to access the DB.

int sqlResult = SQLITE_OK;

sqlite3* database = NULL;

const char* foo = "PRAGMA key=\"x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'\"";

sqlResult = sqlite3_open([databasePath UTF8String], &database); 

// This returns SQLITE_OK and the pointer is valid.

const char* statement = "<<insert valid query string here>>";

sqlite3_stmt *compiledStatement;

sqlResult = sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL);

Unfortunately this call always returns error 26 - SQLITE_NOTADB - file is encrypted or is not a database.

 

I've gone so far as to pull the database off of the simulator and bring it within firing range of my sqlite DB tool and open it with the same key.. the DB opens and I can see all the table structures and data therein. It is only on the device that this is failing.

There's not a whole lot of code to go wrong here. Am I doing something wrong server side? Or device side? or both? I do know that the files for the device side are probably out of date whereas the C# side of things is the freshest & latest. Could there be some sort of versioning problem?

Any help you can render would be appreciated.

Thanks,

EK

[Stephen Lombardo]

Hi EK,

It sounds like you've been running the iOS version for some time, so it's very likely that you're app is using SQLCipher 1.1.x on iOS, while your C# app is using the newer SQLCipher 2 ADO.NET provider. By default these have slightly different and incompatible database formats, by virtue of the latter's MAC extensions.

The easiest way to fix this is to have the C# application generate a 1.1.x compatible database. To do this, you'll need to make 2 small changes:

1. Add "Synchronous=Full;" to your connections string, i.e. :"Data Source=sqlcipher.db;Pooling=false;Synchronous=Full;"
2. Call "PRAGMA cipher_use_hmac = OFF;" after opening the database but before creating any schema / data.

This will cause the ADO.NET provider to create a SQLCipher 1.1.x compatible database with HMAC disabled, so it will be interoperable with the older version of SQLCipher you're using on iOS.

There is a complete code example in this post as well, for reference:

https://groups.google.com/forum/#!msg/sqlcipher/0mlJdejzgJ8/syIViTJ1quYJ

An alternative, but more involve approach, would be to upgrade the iOS application to SQLCipher 2, but that's more involved because you might need to migrate existing databases, etc.

Please let us know if that works for you. Thanks!

Cheers,

Stephen