header plaintext checksum exposed and WAL plaintext written to disk

[MathieuBordere]

I was looking into implementing my own SQLite encryption VFS, however, I identified the following problems that your implementation also suffers from and does not tackle.

1. In WAL mode the frame header contains a rolling checksum over plaintext page data. This is an information leak and might totally break your protocol. The frame header is not encrypted.

   SQLite3MultipleCiphers/src/sqlite3mc_vfs.c

   Line 1031 in 06838d2

   static int mcWriteWal(sqlite3_file* pFile, const void* buffer, int count, sqlite3_int64 offset)

2. Same problem in default, rollback-journal, mode, every page of the rollback journal contains a checksum over the plaintext data of the journal page, you just write this to disk unencrypted.

   SQLite3MultipleCiphers/src/sqlite3mc_vfs.c

   Line 935 in 06838d2

   static int mcWriteMainJournal(sqlite3_file* pFile, const void* buffer, int count, sqlite3_int64 offset)

3. And lastly, in WAL mode, SQLite will possibly write a page in 2 pieces to disk, your logic will write these 2 pieces of a page unencrypted to disk see
   https://github.com/sqlite/sqlite/blob/c81ab76cd9f2ec77cb9e1219b504d741845a0703/src/wal.c#L3891

I've basically concluded it's near impossible to write a safe encryption VFS. There is not enough context in the VFS to make sound decisions about what data to encrypt or not.

[utelle]

I was looking into implementing my own SQLite encryption VFS, however, I identified the following problems that your implementation also suffers from and does not tackle.

Actually, you are plain wrong regarding WAL journal mode, although I have to admit that one can easily be misled by the code in sqlite3mc_vfs.c.

1. In WAL mode the frame header contains a rolling checksum over plaintext page data. This is an information leak and might totally break your protocol. The frame header is not encrypted.

   SQLite3MultipleCiphers/src/sqlite3mc_vfs.c

   Line 1031 in 06838d2

   static int mcWriteWal(sqlite3_file* pFile, const void* buffer, int count, sqlite3_int64 offset)

In principle, you are right, BUT you are right only until SQLite3MC version 1.3.2 (released in May 2021). Due to issues #39 and #40 I changed the WAL implementation - mainly for compatibility reasons. However, I introduced a legacy mode (via configuration parameter mc_legacy_wal) to allow users to recover WAL databases created with prior versions. The use of this legacy mode in normal applications is strongly discouraged, of course.

2. Same problem in default, rollback-journal, mode, every page of the rollback journal contains a checksum over the plaintext data of the journal page, you just write this to disk unencrypted.

   SQLite3MultipleCiphers/src/sqlite3mc_vfs.c

   Line 935 in 06838d2

   static int mcWriteMainJournal(sqlite3_file* pFile, const void* buffer, int count, sqlite3_int64 offset)

Here you are right. However, IMHO the "problem" is neglectable, because the checksum is calculated based on a very small number of bytes (for example about 20 bytes of a 4096 byte database page). I doubt that someone is able to reconstruct the content of the database page based on this small amount of data.

In principle, the problem could be solved in the same way as I did for WAL journal mode: patching the SQLite code to encrypt the page data before the checksum is calculated.

3. And lastly, in WAL mode, SQLite will possibly write a page in 2 pieces to disk, your logic will write these 2 pieces of a page unencrypted to disk see https://github.com/sqlite/sqlite/blob/c81ab76cd9f2ec77cb9e1219b504d741845a0703/src/wal.c#L3891

You were right until SQLite3MC version 1.3.1. Since then the problem does no longer exist (see above).

I've basically concluded it's near impossible to write a safe encryption VFS. There is not enough context in the VFS to make sound decisions about what data to encrypt or not.

Yes, it is nearly impossible to implement a safe encryption without patching the original SQLite code. One approach I heard of is to ignore the structure of the database and journal files and applying a stream cipher to file blocks of a fixed size. However, this approach has many drawbacks in respect to security.

SQLite3 Multiple Ciphers applies a certain number of patches to the original SQLite code. Using a VFS implementation limits the number of required patches to a rather small amount. The SQLCipher project chose a different approach: applying a significantly larger number of patches to restore the SQLITE_HAS_CODEC encryption that was included in SQLite until May 2020.

[MathieuBordere]

Thanks for the clarification. I think you'd be surprised how much information a seasoned hacker could extract from that information leak. I wouldn't not worry about it.

[utelle]

I think you'd be surprised how much information a seasoned hacker could extract from that information leak. I wouldn't not worry about it.

A rollback journal is a temporary file. That is, a hacker must have access to the computer while a transaction is ongoing. If that is the case the hacker could more easily intercept the opening of the database file where the user enters the passphrase giving full access to the database. No need to struggle with any journal checksums.

There is no 100% security. SQLite encryption is meant primarily to protect SQLite database files at rest. Security can be increased by switching to WAL journal mode.

[utelle]

Closing, because IMHO even the current handling of the rollback journal file doesn't impose a serious threat.