Encryption SQLite

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

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

I will write my own encryption, but want to discuss some internals. It should be used on several mobile platforms – iOS, Android, WP7, desktop services more or less as test platforms .

Let’s first introduce the brief characteristics of the existing solution:

> SQLite standard (commercial) SEE extension – I don’t know how it works internally and how it does Cooperate with the mentioned mobile platform.
> System.data.sqlite (Windows only): Full DB, RC4 encryption in ECB mode. They will also encrypt the DB header, occasionally (0.01% chance). The database is damaged.*) Other advantages: They use SQLite to merge and distribute. SqlCipher (openssl, that is, several platforms): optional encryption scheme. They encrypt the entire database. CBC mode (I think) , A random IV vector. Therefore, they have to modify the page parameters (size reserved space to store the IV). They realized the problem related to unencrypted reading of the database header and tried to introduce a workaround, but the solution was not satisfactory . Another disadvantage: they use the SQLite3 source code tree. (On the other hand – other features – enable other features, that is, use special pragmas to fine-tune encryption parameters.)

According to my own analysis, I I think the following may be a good solution that will not encounter the above problems:

>Encrypt the entire DB except for the DB header.
> ECB mode: sounds risky, but in a brief look After the database is formatted, I can’t imagine how to use this attack to attack.
> AES128?
>Implemented on top of SQLite merge (similar to system.data.sqlite)

I want to discuss the possible problems of this encryption scheme.

*) Because of SQLite reading The DB header does not need to be decrypted. Due to RC4 (stream cipher), this problem will occur the first time it is used. AES will be more dangerous, because every “real-time” database will face this problem sooner or later.

EDITED – Case based on VFS encryption

The above method uses the codec-based method approved by sqlite.org. It is a set of 3 callbacks, the most important is this:

< /p>

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

This callback is used for encryption/decryption when SQLite decides to read from disk /Data written. Data is swapped page by page. (Pages are multiples of 512 By.)

The alternative option is to use VFS. VFS is a set of callbacks for low-level OS services. Several of them are related to File-related services, such as XOPEN / xSeek / xRead / xWrite / xClose. In particular, here is the method for data exchange

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

The data size in these calls ranges from 4 By (common case) to the DB page size. If you want to use block ciphers (what else is it useful?), then you need to organize the underlying block cache. I can’t imagine an implementation that is as safe and efficient as SQLite’s built-in transactions.

Second question: The implementation of VFS depends on the platform. Android / iOS / WP7 / desktop all use different sources, that is, VFS-based encryption must be implemented on a platform by platform.

The next question is A more subtle issue: the platform may use VFS calls to achieve file locking. These uses must not be encrypted. In addition, shared locks must not be buffered. In other words, VFS-level encryption may compromise the locking function.

EDITED – Plaintext attack based on VFS encryption

I later realized this: the DB header starts with the fixed string “SQLite format 3”, and the header contains many other solid Fixed byte value. This opens the door to the known plaintext attack (KPA).

This is mainly based on the VFS encryption problem, because it does not have the information that the DB header is encrypted.

< p>System.data.sqlite also has this problem, because it encrypts (RC4) is also the DB header.

SqlCipher overwrites the hdr string with salt to convert the password into a key. In addition, it uses it by default AES, so KPA attacks are not dangerous.

You don’t need to crack the db format or sqlite source code. SQLite exposes the virtual file system (vfs) API, which can Used to wrap the file system (or other vfs), the encryption layer can dynamically encrypt/decrypt pages. When I do this, the result is a very simple task, only a few hundred lines of code. This way the entire database will be encrypted, including log files , It is completely transparent to any client code. The typical page size is 1024, and almost any known block cipher can be used. From my documentation, it can be concluded that this is exactly what SQLCipher does.

About the’problem’ you see:

>You don’t need to re-implement the file system support, you can wrap the default VFS. So there are no locks or platform-dependent issues.> SQLite’s default operating system backend is also VFS, there is no overhead of using VFS except you add it.> You don’t need block caching. Of course, when it requires only 4 bytes you have to read the entire block, but don’t cache It, it will never be read again. SQLite has its own cache to prevent this (pager module).