Questions on use of transaparent encryption (PRAGMA hexkey=...)

[Mark]

Hi,

I've searched the group for all the keywords I can think of, and have
only come up with the references to Noah's Wiki with the 3 steps to
transparent encryption here...
http://groups.google.com/group/csharp-sqlite/browse_thread/thread/2704ff8e5d5738df#

I have finally managed to encrypt and access an encrypted database,
but only under the following circumstances. I wonder if the
observations I have indicate I was doing something incorrectly, or
whether they warrant further explanation on the Wiki.

Step 1 - Compile with SQLITE_HAS_CODEC

Not a problem, since the DLLs we use in our project have had that
option from Day 1.

Step 2 - Activate encryption with a pragma command similar to:
PRAGMA hexkey="0x0102030405060708090a0b0c0d0e0f10"

It may be my relative novice status with string handling in VB.Net
but, since the whole has to be passed as CommandText to the
SQLiteCommand used to 'issue' the PRAGMA, I have only ever got this to
work with single quotes around the hexkey value, as in:

Dim cmdEncrypt As New SqliteCommand("PRAGMA hexkey='" + myHexKey +
"'", mcnn)

I also had to issue a .Open on the connection before the PRAGMA,
despite Step 2 coming before Step 3!

I thought I had issues with terminating the hexkey PRAGMA with a semi-
colon as we have with the others we issue (cmdFKstate.CommandText =
"PRAGMA foreign_keys = ON;"). I used the code in
SQLiteClientTestDriver.cs (//Issue 76) as a guide to what was
expected, but it seems now that the semi-colon may either be present
or not.

The most drastic discovery seemd to be that I could not encrypt a
populated database - only a new one before I loaded the schema. Is
this correct, or should supplying the hexkey PRAGMA to an unencrypted
database encrypt it?

Step 3 - Open or attach the databse.
Place before Step 2!!

Keep up the great work!

[noah hart]

Mark,

Your confusion may be related to what is required by C#SQKite vs what is required when using the C#SQLite sample client.

The wiki is "technically" correct, when using the shell client.

For example:

sql:C#-SQLite3 encrypt.sqlite
SQLite version 3.7.10-C#
(source 2012-01-16 13:28:40)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA hexkey="0x0102030405060708090a0b0c0d0e0f10";
sqlite> create table t1 (c1);
sqlite> select * from sqlite_master;
table|t1|t1|2|CREATE TABLE t1 (c1)
sqlite> .quit

sql:C#-SQLite3 C#-SQLite3.exe encrypt.sqlite
SQLite version 3.7.10-C#
(source 2012-01-16 13:28:40)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA hexkey="0x0102030405060708090a0b0c0d0e0f10";
sqlite> select * from sqlite_master;
table|t1|t1|2|CREATE TABLE t1 (c1)
sqlite> PRAGMA hexrekey="0x0102030405060708090a0b0c0d0e0f11";
sqlite> select * from sqlite_master;
table|t1|t1|2|CREATE TABLE t1 (c1)
sqlite> .q

sql:C#-SQLite3 C#-SQLite3.exe encrypt.sqlite
SQLite version 3.7.10-C#
(source 2012-01-16 13:28:40)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA hexkey="0x0102030405060708090a0b0c0d0e0f10";
sqlite> select * from sqlite_master;
sqlite> .quit

sql:C#-SQLite3 C#-SQLite3.exe encrypt.sqlite
SQLite version 3.7.10-C#
(source 2012-01-16 13:28:40)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA hexkey="0x0102030405060708090a0b0c0d0e0f11";
sqlite> select * from sqlite_master;
table|t1|t1|2|CREATE TABLE t1 (c1)
sqlite> .quit

Your comments are correct in practice, when using the sample client.

I've updated the wiki for the shell client.  However, writing WIKIs is not my strong point, so if you want to write up a section regarding using the sample Client I'd be happy to post it as well.

Noah

[Mark]

Noah,

Thanks for the reply and clarification.

What about the final question?:

I could not encrypt a
populated database - only a new one before I loaded the schema. Is
this correct, or should supplying the hexkey PRAGMA to an unencrypted
database encrypt it?

It's impossible to tell whether encrypt.sqlite in your shell example
existed or not prior to your quoted usage, so the first thing you do
is encrypt it. You show that a previously encrypted database may be re-
encrypted with a different hexkey. But can an unencrypted and
populated database be encrypted? For the sake of completeness, can an
encrypted database be unencrypted, perhaps by a blank hexkey? Or, in
both cases, would it be necessary to transfer all from the existing
database to a new one in either case?

Cheers,

Mark

> here...http://groups.google.com/group/csharp-sqlite/browse_thread/thread/270...

[Noah Hart]

Hi Mark,

Correct, supplying hexkey PRAGMA to an unencrypted database should encrypt it.
And the file encrypt.sqlite did not exist before the example.

As for unencrypting with a blank hexkey -- haven't tried it :)
I don't remember that functionality. You can check the crypto_c.cs and let me know if I need to add that functionality.
<smile>

Noah

[Diego Torres]

I don't think that there are the needed hooks in the code to encrypt a full database.

On the other hand, it is very easy to open the original database and copy the contents to an encrypted one using the ATTACH command:

http://www.sqlite.org/lang_attach.html

--
-- Use of a keyboard or mouse may be linked to serious injuries or disorders.
diego dot torres at gmail dot com - Madrid / Spain