When you modify some data in the database: you need to encrypt the whole file or just what was modified.

[Zapotecatl]

Hi, I did an application using SQLite3 Multiple Ciphers. This application essentially works in this way:

First, with my Linux script I execute this:

1. If Is there a database in a specific directory?
2. Yes: the application assumes that the database is encrypted and it uses PRAGMA key and execute X query in database.
3. No: create a new database and use PRAGMA rekey to encrypt the database.

After, I run my C program and I am using sqlite3_key to decrypt my encrypt database.

However, I receive a question about my application that I am not understanding very well:

"Have you made any benchmark of the encrypted database on the target? I could not understand if when adding a new row to the table, if it needs to encrypt the complete file or if it would just encrypt the new row until VACCUM is called."

In the discussion (#72) I found in the answer this:

"Actually, the SQLite3 Multiple Ciphers project manages to get away without copying the database file by using a modified vacuum procedure to encrypt/recrypt/decrypt the database file in place."

Base on this response, then:

• I am understand is not necessary create any benchmark to encrypt database, right?
• And second, I am understanding if adding a new row, then encrypt the new row until VACCUM is called, right?

[utelle]

However, I receive a question about my application that I am not understanding very well:

"Have you made any benchmark of the encrypted database on the target? I could not understand if when adding a new row to the table, if it needs to encrypt the complete file or if it would just encrypt the new row until VACCUM is called."

SQLite reads and writes database pages of a fixed size to and from a database file. Encryption/Decryption happens pagewise. The default size of a database page is 4k bytes, but that can be changed (maximum 64k). When a new data row is added and committed to a database, SQLite will write the data to disk, and this may affect 1 or more database pages, depending on the total size of data of the new row. Only new or modified database pages will be encrypted.

The VACUUM command is a different story: essentially all pages of the whole database file will be read and written to remove empty unused pages. Thus, VACUUM is an expensive operation.

In the discussion (#72) I found in the answer this:

"Actually, the SQLite3 Multiple Ciphers project manages to get away without copying the database file by using a modified vacuum procedure to encrypt/recrypt/decrypt the database file in place."

This applies to the rekey operation. The SQLCipher product supports PRAGMA rekey only for already encrypted databases. To encrypt a plain unencrypted database one has to invoke a special function that creates an encrypted copy of the source database.

In contrast, SQLite3 Multiple Ciphers supports PRAGMA rekey for all use cases, using a modified VACUUM function, if necessary.

However, just as the VACUUM command rekeying a database file has to process the whole database, and is therefore an expensive operation.

Base on this response, then:

* I am understand is not necessary create any benchmark to encrypt database, right?

Of course, encryption incurs some overhead. How much overhead, depends on the selected cipher and its implementation. Compared to the actual reading or writing to or from disk the encryption/decryption should usually be almost neglectable. However, opening a database can be expensive due to the key derivation process. That is, frequently opening and closing should be avoided for encrypted databases.

* And second, I am understanding if adding a new row, then encrypt the new row until VACCUM is called, right?

New data are encrypted and written, as soon as a COMMIT is executed either explicitly or implicitly. This has nothing to do with VACUUM.

[Zapotecatl]

Thank you @utelle , it was very clear!