Skip to content

Guide for 1.0 to 1.2

Jump to bottom Edit New page
Cheng Liang edited this page Mar 16, 2020 · 1 revision

QtCipherSqlitePlugin 1.x is released. It is based on wxSqlite3 4.0 and adds so many new features. This is a users guide for 1.x

Table of contents

Compile from source

To compile version 1.0, clone this project and checkout tag 1.0. There are three subdirectories as follows:

  • sqlitecipher: source of QtCipherSqlitePlugin.
  • test: unit test for QtCipherSqlitePlugin.
  • testapp: an execuable application which uses QtCipherSqlitePlugin.

Build instructions

git clone https://github.com/devbean/QtCipherSqlitePlugin.git
cd QtCipherSqlitePlugin
git checkout 1.0
cd sqlitecipher
mkdir -p build && cd build
qmake ../sqlitecipher.pro
make
make install  # most propably with sudo

Compile using Qt Creator

To compile the plugin, open the .pro file using Qt Creator, build it. It will be built in shadow target directory if there is no error. Then copy compiled dll (sqlitecipher(d).dll and sqlitecipher(d).lib for Qt4 and sqlitecipher(d).dll for Qt5) to Qt's plugin directory plugins/sqldrivers.

Usage

Check if the plugin is loaded

You could use this statement to check if the plugin is loaded successfully:

qDebug() << QSqlDatabase::drivers();

If you could find SQLITECIPHER in the output line, that means everything is OK.

Use QSqlDatabase as usual

If the plugin is OK, you could use QSqlDatabase to operate your SQLite database as usual.

QSqlDatabase dbconn = QSqlDatabase::addDatabase("SQLITECIPHER");
// Set database name, which is the database file name in SQLite
dbconn.setDatabaseName("test.db");
// Set password if you like.
// Leave it empty if you don't want to use password.
dbconn.setPassword("password");
if (!dbconn.open()) {
    qDebug() << "Can not open connection: " << dbconn.lastError().driverText();
    exit(CONNECTION_FAILED);
}

QSqlQuery query;
// do anything you want...
...
// Don't forget to close the connection.
dbconn.close();

Connection options

Connection options will be used for opening database files. You can set connection options using setConnectOptions() function as following:

QSqlDatabase dbconn = QSqlDatabase::addDatabase("SQLITECIPHER");
dbconn.setDatabaseName(DB_FILE_PATH);
dbconn.setPassword("test");
dbconn.setConnectOptions("QSQLITE_USE_CIPHER=sqlcipher; QSQLITE_CREATE_KEY");
if (!dbconn.open()) {
    qDebug() << "Can not open connection: " << dbconn.lastError().driverText();
    exit(CONNECTION_FAILED);
}
...

The original plugin provides following options:

  • QSQLITE_BUSY_TIMEOUT: Set SQLite busy timeout.
  • QSQLITE_OPEN_READONLY: Open SQLite using readonly mode.
  • QSQLITE_OPEN_URI: Enable URI filename interpretation.
  • QSQLITE_ENABLE_SHARED_CACHE: Enable "shared-cache" mode (disabled by default) intended for use in embedded servers.
  • QSQLITE_ENABLE_REGEXP: Enable regexp function using standard Qt regular expression engine so you can use regexp in SQL like select * from mapping where name regexp '(a|A)$'.

While QtCipherSqlitePlugin adds more connection options.

  • QSQLITE_CREATE_KEY: Creates a password.
  • QSQLITE_UPDATE_KEY: Updates the password.
  • QSQLITE_REMOVE_KEY: Removes the exsiting password.
  • QSQLITE_USE_CIPHER: Sets encryption cipher. There are more options for configurating each cipher, we will show them later.

Encrypts an existing database

If you want to encrypt an existing database which has no password, use connect option QSQLITE_CREATE_KEY as while as set a password for QSqlDatabase:

QSqlDatabase dbconn = QSqlDatabase::addDatabase("SQLITECIPHER");
dbconn.setDatabaseName("test.db");
dbconn.setPassword("test");
dbconn.setConnectOptions("QSQLITE_CREATE_KEY");
if (!dbconn.open()) {
   // ...
}

Updates the password

If you want to update the password of an encrypted database, use connect option QSQLITE_UPDATE_KEY as while as set the old password for QSqlDatabase and specify the new one using QSQLITE_UPDATE_KEY:

QSqlDatabase dbconn = QSqlDatabase::addDatabase("SQLITECIPHER");
dbconn.setDatabaseName("test.db");
dbconn.setPassword("test"); // the old password is 'test'
dbconn.setConnectOptions("QSQLITE_UPDATE_KEY=newtest"); // set new password 'newtest'

Removes the password

If you want to remove the password of an encrypted database, use connect option QSQLITE_REMOVE_KEY as while as set the password for QSqlDatabase:

QSqlDatabase dbconn = QSqlDatabase::addDatabase("SQLITECIPHER");
dbconn.setDatabaseName("test.db");
dbconn.setPassword("test"); // the exsiting password is 'test'
dbconn.setConnectOptions("QSQLITE_REMOVE_KEY");

Specifies encrypt cipher

From version 1.0, QtCipherSqlitePlugin supports specifying cipher algorithm at runtime. You could also specify the default cipher when compile the plugin.

Specifies default cipher at compile time

Default cipher is specified at compile time. In order to specify the default cipher, open 'sqlitecipher/sqlite3/sqlite3.pri', find CODEC_TYPE=CODEC_TYPE_CHACHA20 in line DEFINES += ..., change the value of CODEC_TYPE. The valid values are:

  • CODEC_TYPE_AES128
  • CODEC_TYPE_AES256
  • CODEC_TYPE_CHACHA20(Default)
  • CODEC_TYPE_SQLCIPHER

Specifies cipher at runtime

Connection option QSQLITE_USE_CIPHER could specify cipher at runtime. Use code as following:

QSqlDatabase dbconn = QSqlDatabase::addDatabase("SQLITECIPHER");
dbconn.setDatabaseName("test.db");
dbconn.setPassword("test");
dbconn.setConnectOptions("QSQLITE_USE_CIPHER=sqlcipher");

Valid values of QSQLITE_USE_CIPHER are:

  • aes128cbc
  • aes256cbc
  • chacha20
  • sqlcipher

Supported ciphers and their options

You could set detail parameters of each cipher using connection options.

The following ciphers are currently supported by wxSQLite3:

Definition of abbreviations:

  • AES = Advanced Encryption Standard (Rijndael algorithm)
  • CBC = Cipher Block Chaining mode
  • HMAC = Hash Message Authentication Code
  • ChaCha20 = symmetric stream cipher developed by Daniel J. Bernstein
  • Poly1305 = cryptographic message authentication code (MAC) developed by Daniel J. Bernstein
  • SHA1 = Secure Hash Algorithm 1

Since QtCipherSqlitePlugin is based on wxSQLite3, QtCipherSqlitePlugin also supports these ciphers.

Following is the detail information of these ciphers. Note you could set parameter values in connection options for the specified cipher.

wxSQLite3: AES 128 Bit CBC - No HMAC

This cipher was added to wxSQLite3 in 2007 as the first supported encryption scheme. It is a 128 bit AES encryption in CBC mode.

The encryption key is derived from the passphrase according to the algorithm described in the PDF specification (using the MD5 hash function and the RC4 algorithm).

The initial vector for the encryption of each database page is derived from the page number.

The cipher does not use a HMAC, and requires therefore no reserved bytes per database page.

The following table lists all parameters related to this cipher that can be set before activating database encryption.

Parameter Default Min Max Description Since
AES128CBC_LEGACY 0 0 1 Boolean flag whether the legacy mode should be used v1.0
AES128CBC_LEGACY_PAGE_SIZE 0 0 65536 Page size to use in legacy mode, 0 = default SQLite page size v1.2

Note: It is not recommended to use legacy mode for encrypting new databases. It is supported for compatibility reasons only, so that databases that were encrypted in legacy mode can be accessed.

wxSQLite3: AES 256 Bit CBC - No HMAC

This cipher was added to wxSQLite3 in 2010. It is a 256 bit AES encryption in CBC mode.

The encryption key is derived from the passphrase using an SHA256 hash function.

The initial vector for the encryption of each database page is derived from the page number.

The cipher does not use a Hash Message Authentication Code (HMAC), and requires therefore no reserved bytes per database page.

The following table lists all parameters related to this cipher that can be set before activating database encryption.

Parameter Default Min Max Description Since
AES256CBC_KDF_ITER 4001 1 Number of iterations for the key derivation function v1.0
AES256CBC_LEGACY 0 0 1 Boolean flag whether the legacy mode should be used v1.0
AES256CBC_LEGACY_PAGE_SIZE 0 0 65536 Page size to use in legacy mode, 0 = default SQLite page size v1.2

Note: It is not recommended to use legacy mode for encrypting new databases. It is supported for compatibility reasons only, so that databases that were encrypted in legacy mode can be accessed.

sqleet: ChaCha20 - Poly1305 HMAC

This cipher was introduced for SQLite database encryption by the project sqleet in 2017.

The Internet Engineering Task Force (IETF) officially standardized the cipher algorithm ChaCha20 and the message authentication code Poly1305 in RFC 7905 for Transport Layer Security (TLS).

The new default wxSQLite3 cipher is ChaCha20 - Poly1305.

The encryption key is derived from the passphrase using a random salt (stored in the first 16 bytes of the database file) and the standardized PBKDF2 algorithm with an SHA256 hash function.

One-time keys per database page are derived from the encryption key, the page number, and a 16 bytes nonce. Additionally, a 16 bytes Poly1305 authentication tag per database page is calculated. Therefore this cipher requires 32 reserved bytes per database page.

The following table lists all parameters related to this cipher that can be set before activating database encryption.

Parameter Default sqleet Min Max Description Since
CHACHA20_KDF_ITER 64007 12345 1 Number of iterations for the key derivation function v1.0
CHACHA20_LEGACY 0 1 0 1 Boolean flag whether the legacy mode should be used. v1.0
CHACHA20_LEGACY_PAGE_SIZE 4096 4096 0 65536 Page size to use in legacy mode, 0 = default SQLite page size v1.2

Note: It is not recommended to use legacy mode for encrypting new databases. It is supported for compatibility reasons only, so that databases that were encrypted in legacy mode can be accessed.

SQLCipher: AES 256 Bit CBC - SHA1 HMAC

SQLCipher was developed by Zetetic LLC and initially released in 2008. It is a 256 bit AES encryption in CBC mode.

The encryption key is derived from the passphrase using a random salt (stored in the first 16 bytes of the database file) and the standardized PBKDF2 algorithm with an SHA1 hash function.

A random 16 bytes initial vector (nonce) for the encryption of each database page is used for the AES algorithm. Additionally, a 20 bytes SHA1 authentication tag per database page is calculated. Therefore this cipher requires 48 reserved bytes per database page (since the number of reserved bytes is rounded to the next multiple of the AES block size of 16 bytes).

The following table lists all parameters related to this cipher that can be set before activating database encryption. The columns labelled v3, v2, and v1 show the parameter values used in legacy SQLCipher versions 3, 2, and 1 respectively. To access databases encrypted with the respective SQLCipher version the listed parameters have to be set explicitly.

Parameter Default v3 v2 v1 Min Max Description Since
SQLCIPHER_KDF_ITER 64000 64000 4000 4000 1 Number of iterations for key derivation v1.0
SQLCIPHER_FAST_KDF_ITER 2 2 2 2 1 Number of iterations for HMAC key derivation v1.0
SQLCIPHER_HMAC_USE 1 1 1 0 0 1 Flag whether a HMAC should be used v1.0
SQLCIPHER_HMAC_PGNO 1 1 1 n/a 0 2 Storage type for page number in HMAC:
0 = native, 1 = little endian, 2 = big endian		 v1.0
SQLCIPHER_HMAC_SALT_MASK 0x3a 0x3a 0x3a n/a 0 255 Mask byte for HMAC salt v1.0
SQLCIPHER_LEGACY 0 1 1 1 0 1 Boolean flag whether the legacy mode should be used v1.0
SQLCIPHER_LEGACY_PAGE_SIZE 1024 1024 1024 1024 0 65536 Page size to use in legacy mode, 0 = default SQLite page size v1.2

Note: It is not recommended to use legacy mode for encrypting new databases. It is supported for compatibility reasons only, so that databases that were encrypted in legacy mode can be accessed.

Legacy cipher modes

All supported encryption ciphers have a legacy mode. In this mode the bytes 16 through 23 of the database header are encrypted, too. This is in conflict with the description of the official SQLite Encryption Extension (SEE), stating that bytes 16 through 23 of the database file contain header information which is not encrypted. This is important, because these bytes are read and interpreted by the SQLite code before any encryption extension gets the chance to decrypt the database header.

If bytes 16 through 23 of the database header are encrypted, SQLite is usually not able to determine the page size of the database file correctly. Therefore either the encryption extension itself or the user has to explicitly set the correct page size. If this is not done, SQLite might fail to access the encrypted database.

Starting with the release of wxSQLite3 version 3.1.0 the code of the encryption extension for both wxSQLite3 ciphers (AES 128 Bit and AES 256 Bit) has been adjusted to fix potential problem resulting from encrypting bytes 16 through 23 of the database header, although chances were low that users of the prior versions of the encryption extension experienced problems, namely at most 1 out of 8192 cases.

The good news for users of prior versions of the wxSQLite3 ciphers is that the new version transparently converts existing encrypted databases to the new format. However, this is a one-way process, that is, once converted a database file can't be handled anymore by prior versions of the encryption extension. Instead, one will get the error message "not a database file or encrypted".

For the ciphers sqleet (ChaCha20) and SQLCipher the wxSQLite3 encryption extension offers variants that respect the SQLite requirements and do not encrypt the database header bytes 16 to 23. However, the resulting databases are not compatible with the original ciphers provided by sqleet resp SQLCipher (Zetetic LLC).

It is strongly recommended that the new encryption scheme is used, since it provides better compatibility with SQLite. However, if you need at all costs for some reason the old behaviour, you can activate it by defining the following preprocessor symbol:

#define WXSQLITE3_USE_OLD_ENCRYPTION_SCHEME

This sets the default value of the legacy parameter of the wxSQLite3 ciphers to true. However, the values of all parameters can be temporarily or permanently overwritten.

For the sqleet and SQLCipher ciphers the situation is a bit different. Their original implementations do not offer the option to leave bytes 16 through 23 of the database header unencrypted (although this is probably about to change at least for a future release of SQLCipher). Therefore the implementation of these ciphers in the wxSQLite3 encryption extension includes a mode where bytes 16 through 23 of the database header are not encrypted.

If it is required to access legacy sqleet or SQLCipher databases this is possible by setting the value of the legacy parameter to true. Additionally, it might be required to explicitly set the page size.

Add a custom footer
Add a custom sidebar
Clone this wiki locally