UniDAC 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.

UniDAC functionality for Database File Encryption

UniDAC provides built-in capabilities for Database File Encryption, which becomes available when working in Direct mode. Database File Encryption, built in UniDAC, 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 UniDAC is incompatible with other implementations. When using UniDAC, it is possible to work only with encrypted databases, created with the use of UniDAC. In turn, no third-party application will be able to work with encrypted databases, created with the use of UniDAC

The difference between Database File Encryption and Data Encryption.

The functionality of Data Encryption, which is realized with the help of the TUniEncryptor 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 UniDAC.

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 UniDAC

To control database encryption in UniDAC, the following properties and methods of the TUniConnection component are used:

• The TUniConnection.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 TUniConnection.EncryptionKey property - specifies the encryption key that will be used to connect to an encrypted database, or to create a new encrypted database.
• The TUniConnection.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:

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

Creating of a new encrypted database

The following example shows creating a new encrypted database:

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

Connecting to an encrypted database

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

UniConnection.Database := 'C:\sqlite_encoded.db3';                           // the name of the database to connect to
UniConnection.SpecificOptions.Values['ForceCreateDatabase'] := 'False';      // to check that the database exists
UniConnection.SpecificOptions.Values['Direct'] := 'True';                    // database file encryption is supported in the Direct mode only
UniConnection.SpecificOptions.Values['EncryptionAlgorithm'] := 'leBlowfish'; // the encryption algorithm of the database
UniConnection.SpecificOptions.Values['EncryptionKey'] := '11111';            // the encryption key for the database
UniConnection.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:

UniConnection.Database := 'C:\sqlite_encoded.db3';                           // the name of the database to connect to
UniConnection.SpecificOptions.Values['ForceCreateDatabase'] := 'False';      // to check that the database exists
UniConnection.SpecificOptions.Values['Direct'] := 'True';                    // database file encryption is supported in the Direct mode only
UniConnection.SpecificOptions.Values['EncryptionAlgorithm'] := 'leBlowfish'; // the encryption algorithm of the database
UniConnection.SpecificOptions.Values['EncryptionKey'] := '11111';            // the encryption key for the database
UniConnection.Open;                                                          // connect to the database
TLiteUtils.EncryptDatabase(UniConnection, '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 UniConnection.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 UniConnection.EncryptDatabase('') method, specifying an empty string as a new key.