Database File Encryption

What constitutes Database File Encryption

The SQLite architecture provides the functionality for work with encrypted databases. This means that encoding/decoding is applied to a database file, in the moment of execution of the file read/write operations. This is a low-level encryption "on the fly", it is implemented at the level of the SQLite client library and is completely transparent to the applications working with the database.

But, the fact is that in the client libraries available at the official SQLite website, the algorithms of database file encryption are not implemented. Therefore, usually, to work with encrypted databases one has to either use a custom-built client library with encryption support, or create an own library from the source code, available on the SQLite website.

LiteDAC functionality for Database File Encryption

LiteDAC provides built-in capabilities for Database File Encryption, which becomes available when working in Direct mode. Database File Encryption, built in LiteDAC, allows to:

encrypt a database;
create a new encrypted database;
connect and work with the encrypted database;
change the encryption key of the encrypted database;
decryp the encrypted database.

To encrypt/decrypt the database file, one of the following encryption algorithms can be used:

the Triple DES encryption algorithm;
the Blowfish encryption algorithm;
the AES encryption algorithm with a key size of 128 bits;
the AES encryption algorithm with a key size of 192 bits;
the AES encryption algorithm with a key size of 256 bits;
the Cast-128 encryption algorithm;
the RC4 encryption algorithm.

Important note: there are no strict standardized requirements for implementation of database file encryption in SQLite. Therefore, implementation of Database File Encryption in LiteDAC is incompatible with other implementations. When using LiteDAC, it is possible to work only with encrypted databases, created with the use of LiteDAC. In turn, no third-party application will be able to work with encrypted databases, created with the use of LiteDAC

The difference between Database File Encryption and Data Encryption.

The functionality of Data Encryption, which is realized with the help of the P:Devart.SQLiteDac.TLiteEncryptor component, allows to encrypt individual fields in database tables. In this case, the database itself is not encrypted. I.e. on the one hand, the information in this database (with the exception of the encrypted fields) is easily accessible for viewing by any SQLite DB-tools. On the other hand, such database is more simple in terms of modification of data structures.

Database File Encryption encrypts all the data file. Both structure and information on such database becomes unavailable for any third-party applications. An indisputable advantage is the increased level of secrecy of information. The disadvantage is that, for making any changes in the structure of the database, developers will have to use only LiteDAC.

Both Database File Encryption and Data Encryption methods are not mutually exclusive and can be used at the same time.

The usage of Database File Encryption in LiteDAC

To control database encryption in LiteDAC, the following properties and methods of the P:Devart.SQLiteDac.TLiteConnection component are used:

The TLiteConnection.Options.EncryptionAlgorithm property - specifies the encryption algorithm that will be used to connect to an encrypted database, or to create a new encrypted database.
The TLiteConnection.EncryptionKey property - specifies the encryption key that will be used to connect to an encrypted database, or to create a new encrypted database.
The TLiteConnection.EncryptDatabase method - is used to change the encryption key in an encrypted database, or to decrypt the database.

Encrypt a database

The following example shows how to encrypt an existing database:


LiteConnection.Database := 'C:\sqlite.db3';               // the name of the database to be encrypted
LiteConnection.Options.ForceCreateDatabase := False;      // to check that the database exists
LiteConnection.Options.Direct := True;                    // database file encryption is supported in the Direct mode only
LiteConnection.Options.EncryptionAlgorithm := leBlowfish; // the database will be encrypted with the Blowfish encryption algorithm
LiteConnection.EncryptionKey := '';                       // no encryption key specified, because the database is not encrypted yet
LiteConnection.Open;                                      // connect to the database
LiteConnection.EncryptDatabase ('11111');                 // encrypt the database using the "11111" encryption key

Creating of a new encrypted database

The following example shows creating a new encrypted database:


LiteConnection.Database := 'C:\sqlite_encoded.db3';       // the name of the database to be created
LiteConnection.Options.ForceCreateDatabase := True;       // this will allow to create the new database
LiteConnection.Options.Direct := True;                    // database file encryption is supported in the Direct mode only
LiteConnection.Options.EncryptionAlgorithm := leBlowfish; // the database will be encrypted with the Blowfish encryption algorithm
LiteConnection.EncryptionKey := '11111';                  // the encryption key for the database
LiteConnection.Open;                                      // create and connect to the database

Connecting to an encrypted database

To connect to an existing encrypted database, the following should be performed:


LiteConnection.Database := 'C:\sqlite_encoded.db3';       // the name of the database to connect to
LiteConnection.Options.ForceCreateDatabase := False;      // to check that the database exists
LiteConnection.Options.Direct := True;                    // database file encryption is supported in the Direct mode only
LiteConnection.Options.EncryptionAlgorithm := leBlowfish; // the encryption algorithm of the database
LiteConnection.EncryptionKey := '11111';                  // the encryption key for the database
LiteConnection.Open;                                      // connect to the database

Changing the encryption key for the database

To change the encryption key in the encrypted database, you must perform the following:


LiteConnection.Database := 'C:\sqlite_encoded.db3';       // the name of the database to connect to
LiteConnection.Options.ForceCreateDatabase := False;      // to check that the database exists
LiteConnection.Options.Direct := True;                    // database file encryption is supported in the Direct mode only
LiteConnection.Options.EncryptionAlgorithm := leBlowfish; // the encryption algorithm of the database
LiteConnection.EncryptionKey := '11111';                  // the encryption key for the database
LiteConnection.Open;                                      // connect to the database
LiteConnection.EncryptDatabase ('22222');                 // change the database encryption key to '22222'

After changing the encryption key, the database connection remains open and the further work with the database can continue. However, if disconnected from the database and for subsequent connection, the new value of the encryption key should be assigned to the LiteConnection.EncryptionKey property.

Decryption of the database

The encrypted database can be decrypted, after that it becomes available for viewing and editing in third-party applications. To decrypt the database you must first connect to it, as shown in the examples above, and then execute the LiteConnection.EncryptDatabase('') method, specifying an empty string as a new key.

PRAGMA Encryption

The PRAGMA ENCRYPTION statement specifies the encryption algorithm that will be used to encrypt a previously connected unencrypted database. The statement can be executed only after the database is connected. The statement must not be used on databases encrypted with a different encryption algorithm. The pragma values are the same as the EncryptionAlgorithm attribute values.

Example:
```
PRAGMA ENCRYPTION=TripleDES;
```
The statement can be executed from any database tool that uses Devart LiteDAC, or with the SQLExecuteDirect API function.
The PRAGMA REKEY statement – is used to encrypt unencrypted database, to change the encryption key of an encrypted database or to decrypt a database. The statement can be executed only after the database is connected.

Example of encryption or changing an encryption key:
```
PRAGMA REKEY='mynewkey';
```
Example of decryption:
```
PRAGMA REKEY='';
```
The statements can be executed from any database tool that uses Devart LiteDAC, or with the SQLExecuteDirect API function.

Request Support

DAC Forum

Provide Feedback