Error trying to insert into encrypted table

SQLCipher
1

Hi there, I’m in the midst of evaluating the commercial version of SQLCipher and am trying to use the table level encryption. I think I’ve followed the directions correctly. The table (and it’s shadow partner) create properly but whenever I go to try to insert a row into the table a SQLiteException gets thrown with the message ‘SQL Logic error’. I know the SQL being executed is correct so I feel like I’ve done something wrong in either setting up the encrypted table or how I’m addressing it. Any help would be appreciated.

Thanks!

On app launch:

  1. Set license
    PRAGMA cipher_license = ‘my_trial_key’

  2. creating the virtual table with:

CREATE VIRTUAL TABLE IF NOT EXISTS MyTable USING sqlcipher_vle(CREATE TABLE IF NOT EXISTS MyTable_shadow
(
Field1 TEXT NOT NULL,
Field2 TEXT NOT NULL,
Field3 TEXT NOT NULL,
Field4 TEXT NOT NULL,
Field5 NUMERIC,
Field6 INTEGER,
Field7 TEXT,
Field8 TEXT,
Field9 INTEGER,
Field10 INTEGER,
Field11 BLOB,
Field12 TEXT,
Field13 INTEGER NOT NULL,
Field14 INTEGER NOT NULL,
PRIMARY KEY(Field1,Field2,Field3,Field4), ‘1,2,3,4,5,6,11,12,13,14’);

  1. Set VLE level password before attempting any operations on above table

SELECT sqlcipher_vle_key(‘mypassword’);

Later in the code on some user event happening…

  1. Attempt to insert row

INSERT INTO [MyTable] ([Field1],[Field2],[Field3],[Field4]) VALUES(?,?,?,?)
(Prepared statement)

Error:

SQL Logic error at SQLite.SQLiteCommand.ExecuteNonQuery

I know the SQL I’m trying to execute works as I’ve been able to run it in SQLiteBrowser app.

Does sqlcipher_vle_key have to be run before every operation on the table? I’m only calling that once at app start.

2

Hi @breen

Thank you for your interest in SQLCipher. There are a couple items to address. First, SQL statement to create the virtual table is missing an extra paren. You should add another closing paren after your primary key listing. Something like this:

CREATE VIRTUAL TABLE IF NOT EXISTS MyTable USING sqlcipher_vle(CREATE TABLE IF NOT EXISTS MyTable_shadow
(
Field1 TEXT NOT NULL,
Field2 TEXT NOT NULL,
Field3 TEXT NOT NULL,
Field4 TEXT NOT NULL,
Field5 NUMERIC,
Field6 INTEGER,
Field7 TEXT,
Field8 TEXT,
Field9 INTEGER,
Field10 INTEGER,
Field11 BLOB,
Field12 TEXT,
Field13 INTEGER NOT NULL,
Field14 INTEGER NOT NULL,
PRIMARY KEY(Field1,Field2,Field3,Field4)), '1,2,3,4,5,6,11,12,13,14');

Secondly, I believe you are getting the SQL logic error because your table schema defines Field13 and Field14 as NOT NULL, however you are not providing them within your SQL insert command. If you include those two additional columns, it should work properly. Would you give that a try and let us know your results? Thanks!

3

Thanks for the reply! I’ll check those fixes out and see if it works.
Also does
SELECT sqlcipher_vle_key(‘mypassword’);
have to be called before any operation on an encrypted table?

Further testing with a sample app I made seems to point to yes.

4

Hello @breen

You will need to invoke sqlcipher_vle_key one time per creation of the connection to the database. You should preferably keep the connection alive for the duration of the application lifetime, if possible.

5

Does the once per creation also apply if you’re making a SQLiteAsyncConnection as well?

6

Hi @breen

Yes, still just a single application of sqlcipher_vle_key per connection, async or not. One item to note if you are using the async library/connection, you should likely await the invocation of sqlcipher_vle_key to prevent usage of the vle prior to providing the key.

7

Thanks,

If you do try to select rows from a table using VLE before executing sqlcipher_vle_key is the result that the query just returns no rows?

8

Also not sure it makes a diff, but I’m running the ‘Execute’ command within a ‘RunInTransactionAsync’.

The bad SQL in my first post turned out to just be a cut and paste error when I posted. Virtual table is being created properly when applicable and only when I try to query the table does it error.
’

9

Hi @breen,

You will receive an error which states the key is missing.

10

Yeah I seem to be having a problem with the Async connection. I have a single instance of a DB wrapper object injected with Autofac on app start. Within that class we setup both a sync SQLiteConnection as well as an async one.

In the constructor I call the following method: ‘ConfigureSqlCipher’ which looks like this:

private async void ConfigureSqlCipher()
{
await SetLicense(License);
await SetValueLevelEncryptionKey(“password”);
}

The other 2 methods look like this:

private async Task SetLicense(string licence)
{
SyncDb.ExecuteScalar($“PRAGMA cipher_license = ‘{license}’”);

await AsyncDb.ExecuteScalarAsync($“PRAGMA cipher_license = ‘{license}’”);
}

private async Task SetValueEncryptionKey(string password)
{
SyncDb.Query($“SELECT sqlcipher_vle_key(’{password}’);”);

await AsyncDb.QueryAsync($“SELECT sqlcipher_vle_key(’{password}’);”);

}

‘SyncDb’ and ‘AsyncDb’ are instances of SQliteConnection.

Later in the app when I go to use the instance of AsyncDB it will fail attempting to insert a row unless I repeat the ‘sqlcipher_vle_key’ call just before I go to insert a row.

I feel like I have something wrong with the Async calls as using the SyncDB somewhere else doesn’t require me to repeat the VLE key just before my other call.

11

Hi @breen

Are you using the Zetetic provided sqlite-net replacement package? Additionally, is there anything that could be closing down either connection as that would wipe the VLE key.

12

I’m using the sqlite-net from you guys + the xamarin IOS package. The IOS project references the IOS package while one of our common projects (which the IOS proj references) uses your sqlite-net replacement which I was just able to drop right in without any trouble.

I will check to see if anything is being closed between the cstr setup and when I go to use it but the odd thing is the SyncDB one works fine with this same setup as I tested it by replacing the async calls with sync ones in the area described above where I’m running into trouble. I also put some debugging in yesterday to ensure these async setup calls were in fact being awaited correctly and they seem to be.

13

I’ve made a very simple pared down Xamarin application where there are no explicit calls to closing the async SQLiteConnection and I’m awaiting everything on contruction (with debug printing) to make sure everything that should be set for SQLCipher is set be4 being used. I have a simple event handler on a button that will attempt to insert a row into the virtual table and without another call to ‘sqlcipher_vle_key’ just prior to the insert attempt, it will fail with a ‘SQL Logic’ exception. If I uncomment the ‘sqlcipher_vle_key’ before the insert it works perfectly.

I can send you the simple project if you want to take a look at it and see what the issue is.

14

Hello @breen - it would be great to take a look at your sample project. Can you send it to us over at support@zetetic.net ?