Skip to content

Guide for 1.3

Jump to bottom Edit New page
Cheng Liang edited this page Mar 16, 2020 · 2 revisions

This is a users guide for 1.3. If you are looking for earlier versions, please check other wiki pages.

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:

Notice AES 256 Bit CBC - SHA256/SHA512 HMAC (SQLCipher) are added from v1.3

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
  • SHA256 = Secure Hash Algorithm 2 (256 bit hash)
  • SHA512 = Secure Hash Algorithm 2 (512 bit hash)

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 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/SHA256/SHA512 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, an authentication tag per database page is calculated. SQLCipher version 1 used no tag; SQLCipher version 2 to 3 used a 20 bytes SHA1 tag; SQLCipher version 4 uses a 64 bytes SHA512 tag, allowing to optionally choose a 32 bytes SHA256 tag instead. Therefore this cipher requires 16, 48 or 80 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 v4, 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 v4 v3 v2 v1 Min Max Description Version
SQLCIPHER_KDF_ITER 256000 256000 64000 4000 4000 1 Number of iterations for key derivation v1.0
SQLCIPHER_FAST_KDF_ITER 2 2 2 2 2 1 Number of iterations for HMAC key derivation v1.0
SQLCIPHER_HMAC_USE 1 1 1 1 0 0 1 Flag whether a HMAC should be used v1.0
SQLCIPHER_HMAC_PGNO 1 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 0x3a n/a 0 255 Mask byte for HMAC salt v1.0
SQLCIPHER_LEGACY 0 4 3 2 1 0 4 SQLCipher version to be used in legacy mode v1.0, changed from v1.3
SQLCIPHER_LEGACY_PAGE_SIZE 4096 4096 1024 1024 1024 0 65536 Page size to use in legacy mode, 0 = default SQLite page size v1.2
SQLCIPHER_KDF_ALGORITHM 2 2 0 0 0 0 2 Hash algoritm for key derivation function 0 = SHA1, 1 = SHA256, 2 = SHA512 v1.3
SQLCIPHER_HMAC_ALGORITHM 2 2 0 0 0 0 2 Hash algoritm for HMAC calculation 0 = SHA1, 1 = SHA256, 2 = SHA512 v1.3
SQLCIPHER_PLAINTEXT_HEADER_SIZE 0 0 n/a n/a n/a 0 100 Size of plaintext database header must be multiple of 16, i.e. 32 v1.3

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. However, the default legacy mode for the various SQLCipher versions can be easily set using just the parameter SQLCIPHER_LEGACY set to the requested version number. That is, all other parameters have to be specified only, if their requested value deviates from the default value.

Note: Version 4 of SQLCipher introduces a new parameter SQLCIPHER_PLAINTEXT_HEADER_SIZE to overcome an issue with shared encrypted databases under iOS. If this parameter is set to a non-zero value (like 16 or 32), the corresponding number of bytes at the beginning of the database header are not encrypted allowing iOS to identify the file as a SQLite database file. The drawback of this approach is that the cipher salt used for the key derivation can't be stored in the database header any longer. Therefore it is necessary to retrieve the cipher salt on creating a new database, and to specify the salt on opening an existing database. The cipher salt can be retrieved with the function wxsqlite3_codec_data using parameter cipher_salt, and has to be supplied on opening a database via the database URI parameter cipher_salt.

Legacy cipher modes

SQLite reads bytes 16 through 23 from the database header before the database file is actually opened. The main purpose of this is to detect the page size and the number of reserved bytes per page. Legacy ciphers used to encrypt these header bytes as well, but this may prevent SQLite from successfully opening the database file.

The official SQLite Encryption Extension (SEE) leaves these header bytes unencrypted for this reason. Since version 3.1.0, wxSQLite3 also doesn't encrypt these header bytes unless the legacy parameter is explicitly set.

When using the ciphers sqleet (ChaCha20) or SQLCipher, this means that the databases written by wxSQLite3 won't be compatible with the original ciphers provided by sqleet and SQLCipher (Zetetic LLC) unless the legacy parameter is explicitly set. This is because the original implementations fully encrypt the database header by default. (Note that sqleet can also be compiled in non-legacy mode, and future releases of SQLCipher will probably provide this option as well.)

If a database is encrypted in legacy mode, then the legacy parameter must be set to true and the legacy_page_size parameter should be set to the correct page size. If this isn't done, wxSQLite3 might fail to access the database. Use wxsqlite3_config_cipher() to set these parameters.

When accessing a database encrypted with wxSQLite3 ciphers AES-128 or AES-256 in legacy format, then wxSQLite3 transparently converts the database into the new format unless the legacy parameter is explicitly set. Note that wxSQLite3 versions prior to 3.1.0 won't be able to access non-legacy database files and will report the error message "not a database file or encrypted" instead.

It's strongly recommended to use the new encryption scheme, since it provides better compatibility with SQLite. The unencrypted header bytes don't reveal any sensitive information. Note, however, that it will actually be possible to recognize encrypted SQLite database files as such. This isn't usually a problem since the purpose of a specific file can almost always be deduced from context anyway.

It's also possible to activate the legacy mode at compile time by defining the following preprocessor symbols:

Preprocessor symbol Description
WXSQLITE3_USE_OLD_ENCRYPTION_SCHEME Enable legacy mode for wxSQLite3 ciphers AES-128 and AES-256
WXSQLITE3_USE_SQLEET_LEGACY Enable legacy mode for sqleet (ChaCha20) cipher
WXSQLITE3_USE_SQLCIPHER_LEGACY Enable legacy mode for SQLCipher cipher

For the SQLCipher support the default version can be selected at compile time by defining the following preprocessor symbol:

| Preprocessor symbol | Description | | SQLCIPHER_VERSION_DEFAULT | Select default SQLCipher version 1, 2, 3, or 4 |

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