Encrypting SQLite

Question

I am going to write my own encryption, but would like to discuss some internals. Should be employed on several mobile platforms - iOS, Android, WP7 with desktop serving more or less as a test platform.

Let's start first with brief characteristics of existing solutions:

• SQLite standard (commercial) SEE extension - I have no idea how it works internally and how it co-operates with mentioned mobile platforms.

• System.data.sqlite (Windows only): RC4 encyption of the complete DB, ECB mode. They encrypt also DB header, which occasionally (0.01% chance) leads to DB corruption.*) Additional advantage: They use SQLite amalgamation distribution.

• SqlCipher (openssl, i.e. several platforms): Selectable encryption scheme. They encrypt whole DB. CBC mode (I think), random IV vector. Because of this, they must modify page parameters (size + reserved space to store IV). They realized the problems related to unencrypted reading of the DB header and tried to introduce workarounds, yet the solution is unsatisfactory. Additional disadvantage: They use SQLite3 source tree. (Which - on the other hand - enables additional features, i.e. fine tuning of the encryption parameters using special pragmas.)

Based on my own analysis I think the following could be a good solution that would not suffer above mentioned problems:

• Encrypting whole DB except the DB header.
• ECB mode: Sounds risky, but after briefly looking at the DB format I cannot imagine how this could be exploited for an attack.
• AES128?
• Implementation on top of the SQLite amalgamation (similarly as system.data.sqlite)

I'd like to discuss possible problems of this encryption scheme.

*) Due to SQLite reading DB header without decryption. Due to RC4 (a stream cipher) this problem will manifest at the very first use only. AES would be a lot more dangerous as every "live" DB would sooner or later face this problem.

---

EDITED - case of VFS-based encryption

Above mentioned methods use codec-based methodology endorsed by sqlite.org. It is a set of 3 callbacks, the most important being this one:

void *(*xCodec)(void *iCtx, void *data, Pgno pgno, int mode)

This callback is used at SQLite discretion for encrypting/decrypting data read from/written to the disk. The data is exchanged page by page. (Page is a multiple of 512 By.)

Alternative option is to use VFS. VFS is a set of callbacks used for low-level OS-services. Among them there are several file-related services, e.g. xOpen/xSeek/xRead/xWrite/xClose. In particular, here are the methods used for data exchange

int (*xRead)(sqlite3_file*, void*, int iAmt, sqlite3_int64 iOfst);
int (*xWrite)(sqlite3_file*, const void*, int iAmt, sqlite3_int64 iOfst);

Data size in these calls ranges from 4 By (frequent case) to the DB page size. If you want to use a block cipher (what else to use?), then you need to organize underlying block cache. I cannot imagine an implementation that would be as safe and as efficient as SQLite built-in transactions.

Second problem: VFS implementation is platform-dependent. Android/iOS/WP7/desktop all use different sources, i.e. VFS-based encryption would have to be implemented platform-by-platform.

Next problem is a more subtle: Platform may use VFS calls to realize file locks. These uses must not be encrypted. More over, shared locks must not be buffered. In other words, encryption at the VFS level might compromise locking functionality.

---

EDITED - plaintext attack on VFS-based encryption

I realized this later: DB header starts with fixed string "SQLite format 3" and the header contains a lot of other fixed byte values. This opens the door for known plaintext attacks (KPA).

This is mainly the problem of VFS-based encryption as it does not have the info that the DB header is being encrypted.

System.data.sqlite has also this problem as it encrypts (RC4) also the DB header.

SqlCipher overwrites hdr string with salt used to convert password to the key. Moreover, it uses by default AES, hence KPA attack presents no danger.

Answer 1

You don't need to hack db format or sqlite source code. SQLite exposes virtual file-system (vfs) API, which can be used to wrap file system (or another vfs) with encryption layer which encrypts/decrypts pages on the fly. When I did that it turned out to be very simple task, just hundred lines of code or so. This way whole DB will be encrypted, including journal file, and it is completely transparent to any client code. With typical page size of 1024, almost any known block cipher can be used. From what I can conclude from their docs, this is exactly what SQLCipher does.

Regarding the 'problems' you see:

• You don't need to reimplement file system support, you can wrap around the default VFS. So no problems with locks or platform-dependence.
• SQLite's default OS backend is also VFS, there is no overhead for using VFS except that you add.
• You don't need block cache. Of course you will have to read whole block when it asks for just 4 bytes, but don't cache it, it will never be read again. SQLite has its own cache to prevent that (Pager module).

Answer 2

Didn't get much response, so here is my decision:

• Own encryption (AES128), CBC mode

• Codec interface (same as used by SqlCipher or system.data.sqlite)

• DB header unencrypted

• Page headers unencrypted as well and used for IV generation

• Using amalgamation SQLite distribution

AFAIK this solution should be better than either SqlCipher or system.data.sqlite.