How does SQLCipher compare to other SQLite encryption extensions?

SQLCipher is one of the more popular SQLite encryption extensions available at this time. Despite this, we are sometimes asked by prospective users how SQLCipher compares to other available SQLite encryption tools from a performance and security perspective.

We don’t have first hand experience with other SQLite encryption tools because SQLCipher was deliberately implemented as a clean room design. However, we are aware that there are some important factors that differ between SQLCipher and other similar products. It is very important to account and control for these factors because SQLCipher may provide enhanced security with the default settings, but these same features may impact performance testing:

Key derivation - SQLCipher uses an expensive key derivation process (PBKDF2) to derive the actual encryption key from the provided key material and a random per-database salt. This security feature hardens SQLCipher databases against brute force, dictionary, and pre-computation attacks. However, this key deviation process is deliberately very slow. It occurs on the first access of the database after the key is set. When comparing SQLCipher against a similar implementation that does not perform key derivation, the competitor may appear to perform better than SQLCipher. In order to rule this out you can set a test key using the RAW key semantics , e.g. PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'"; so that SQLCipher skips key derivation. Make sure to disable key derivation before comparing against implementations that do not offer this security feature.

Page Data Validation (MAC) - SQLCipher implements an additional security feature where each page data is protected by a HMAC “signature”. This helps protect the database against tampering because if any page data is modified externally or resequenced SQLCipher will detect the invalid change when the page is read back. This validation involves additional processing overhead whenever pages are read or written. For testing purposes you can disable HMAC for a SQLCipher database using PRAGMA cipher_use_hmac = OFF; after setting the key, but before using the database. Disable HMAC for an accurate comparison against other extensions that don’t provide page data validation / tamper evidence.

Page Size - SQLCipher uses a default page size of 1024 bytes for encrypted databases. Depending on the structure of a database this can have a significant affect on performance, particularly if a competing product is using a different page size. SQLCipher does allow you the flexibility of changing the page size used via PRAGMA cipher_page_size. Typically a smaller page size will perform well with operations that affect a small amount of data, because less information must be encrypted / decrypted to satisfy a statement. If statements are operating on large amounts of data then a larger page size may be more efficient (due to a lower number of overall operations required). Determining the best page size is therefore an application-specific performance tuning exercise, but you should make sure the same page size is used by all implementations when testing. Set SQLCipher’s page size to 4096 to ensure an accurate comparison against current SQLite implementations.

SQLCipher users have reported that SQLCipher outperforms some other SQLite encryption extensions when controlling for these variables.

1 Like

I am wondering the encryption process? Does it encrypt or decrypt the database file page by page in a whole ?

@yep_zhang - SQLCipher encrypts and decrypts the database on a per page basis, but only as needed. If only a few pages are needed to execute a statement, only those pages are in scope. You can see more details on the design page.

So when we type "pragam key = 'aaa‘ " in the command line, it doesn’t encrypt or decrypt the whole database file actually?

Hi @yep_zhang,

Not initially, that only provides the password to be used within PBKDF2 to derive your encryption key (unless you provide a raw key format). Encryption/decryption is deferred until the execution of a SQL command.

I truly appreciate your timely help. :100: