Approach

AnyDAC for Delphi SQLite driver is using a selection of NIST approved AES based primitives to encrypt the database. The key is derived from the password using a function taken from the NIST Special Publication 800-90 that preserves the strength of the password within the bounds of the 128, 192 or 256 bit key size selected.

Each page of the database is encrypted using a conventional generic composition of AES-CTR and AES-CBC-MAC. The nonces required by AES-CTR and AES-CBC-MAC are generated using a NIST approved ANSI X9.31 based PRNG that uses AES as block cipher primitive. 

The encrypted database format is not compatible with other similar SQLite encryption extensions. That means, you cannot use an encrypted database, encrypted with non-AnyDAC libraries. If you need that, then you have to decrypt a database with original tool and encrypt it with AnyDAC.

The encryption is provided through the officially supported SQLite approach - custom codec code and compilation with SQLITE_HAS_CODEC defined. All encryption routines are implemented on Delphi and embedded into sqlite3 code. As result, the encryption is correctly handled for:

• ATTACH
• VACUUM
• database recovery
• database backup
• etc 

The SQLite documentation for the encryption functionality applies to the AnyDAC SQLite driver as well.

Connection Definition Parameters

The encryption is controlled through the connection definition parameters NewPassword and Password. They may have a form:

[aes-128 | aes-192 | aes-256 | aes-ctr-128 | aes-ctr-192 | aes-ctr-256 | 
aes-ecb-128 | aes-ecb-192 | aes-ecb-256:] password

There "aes-XXX-NNN:" is an optional prefix, controlling the cipher algorythm to use. If it is not specified, then will be used:

• algorythm, specified by Encrypt connection definition parameter;
• aes-256 if nothing is specified.

AnyDAC supports four encryption usage cases:

1. To encrypt unencrypted database, specify:
   NewPassword=xxxx
   And xxxx will be the encrypted database password.
2. To open encrypted database, specify:
   Password=xxxx
3. To change encrypted database password, specify:
   Password=xxxx
   NewPassword=yyyy
4. To unencrypt encrypted database, specify:
   Password=xxxx
   NewPassword=

Please look at documentation for the full list of SQLite connection definition parameters.

Encryption modes  

AES-NNN 

The aes-NNN algorithms are generic compositions of AES-CTR and AES-CBC-MAC. This composition guarantees both Confidentiality and Integrity, meaning that only entities with access to the correct
password will be able to read and modify the pages of the encrypted database. These algorithms will add a linear overhead of 32 bytes per page to your encrypted database.

This algorithm will help you detect most malicious attempts to inject data into the database, but it will not prevent such attempts and it will not help you undo such modifications. It is essentially a complement to frequent backups, but it is much better than most other database encryption schemes at alerting you when you have been subject to an attack and it is time to restore your database from the backup.

Please note that the aes-NNN algorithm, by itself, neither will detect deletion of entire pages at the end of the database (but it will detect deletions in the middle of the database), nor will it detect attacks that consist in reverting the database to an older version encrypted using the same password.

AES-CTR-NNN 

The aes-ctr-NNN algorithms are AES-CTR only. It will not detect modifications to the database, but it will provide Confidentiality against passive attacks. That is, as long as the attacker does not have access to your password, and does not attempt to modify the database to see how your application reacts to the modifications, your data will remain as secret as your application allows.

Needless to say, the algorithms will only protect your database file against attackers that are not able to tap into your AnyDAC application using e.g. a debugger and extract the password that way. In a similar way, if you store your password in a configuration file or as a constant in the software itself, it will be trivial for any not even moderately skilled attacker to find it and compromise your security.

AEC-ECB-NNN

The aes-ecb-NNN algorythms are AES-ECB only. It will not detect modifications to the database, and it will not provide strong Confidentiality against passive attacks, in contrast to AES-NNN and AES-CTR-NNN.

What is right for you ?

The AES-NNN provides the top strong Confidentiality and Integrity. But at a price of some performance reduction, noticeable across other encryption modes.

The AES-CTR-NNN provides the top strong Confidentiality, but not an Integrity. For that you will give a better performance.

The AES-ECB-NNN provides comparably weak Confidentiality, and no Integrity. But it has the best performance across other encryption modes.

For details see benchmark results at bottom. Honestly, if your application is not a real-time performance sensitive application, then just use AES-256.

SQL Extensions 

The ATTACH command got an extension. The full syntax of the ATTACH now is:

ATTACH [DATABASE] 'filename' [AS name] [KEY 'password']

When KEY is omitted, then the password value will be inherited from the main database. To specify an empty password to attach an unencrypted database, use something like that:

ATTACH 'D:\tmp\test.db' AS tst KEY ''

The sqlite_attach SQL function is changed accordingly.

Performance

The following table shows a comparative results for all encryption modes. There "Insert 100K records" is a test inserting 100,000 of records using Array DML into a DB table, with corresponding encryption mode. The "Fetch 100K records" is a test fetching 100,00 of records from a DB table, with corresponding encryption mode.

As you may conclude, the performance difference between unencrypted database and most strong encryption (AES-256) is 12% at writing and 30% at reading. That is pretty good, is not ? :)