Unable to open manually-encrypted sqlite database with QtCipherSqlitePlugin and viceversa

[SupIQ]

I have manually created a SQLite database (named "localDB.db") and protected it with a password, by using DB browser. If I am not mistaken, DB browser uses SQLCipher (with a default page size of 1024), for encrypting a database. However, if I try to open the database with this plugin (see following code), I always get the error that "the password is invalid or cipher does not match", although I clearly use the correct password and have set the cipher as "sqlcipher".

#include <QCoreApplication>
#include <QDebug>
#include <QDir>
#include <QSqlError>
#include <QtSql/QSqlDatabase>

#ifdef Q_OS_IOS

#include <QtPlugin>

Q_IMPORT_PLUGIN(SqliteCipherDriverPlugin)

#endif

#define CONNECTION_FAILED -1

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

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

    QString dir = QCoreApplication::applicationDirPath();
    QString db_file_path = dir + "/Data/localDB.db";
    qDebug() << "DB File Path is:" << db_file_path;

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

    if (!db.isOpen())
    {
        qDebug() << "Can not open connection: " << db.lastError().driverText();
        exit(CONNECTION_FAILED);
    }

    return 0;
}

The same happens for the inverse process: if I encrypt a database by using this plugin, then I am unable to manually open the database with DB Browser or other similar programs, because it does not accept the password.

How can I solve this problem? I need to access an encrypted sqlite database through both my software and a third-party application like DB browser.

Thanks in advance

[Swerved]

SQLCipher != QCipherSqlite

You are getting confused as these are different encryption tools for SQLite Databases.

DB Browser will not work with this plugin, but it will for SQLCipher, which is different, developed by zetetic I believe and costs money for a compiled PC version of the driver (the Android version is available on their Community Edition).

But fear not, you CAN use this plugin with a database browser AND keep it encrypted. You have, the following options: 1) Create your own DB Browser App with Qt. There is a example project to get you started in QtCreator. 2) Make an app to decrypt the Db before you edit it in DB Browser. 3) Don’t encrypt with a password. Instead, use QtAESEncryption, or QuaZip, or both, to encrypt the file you will need to decrypt the file and unzip before loading it into your App.

I use both 1 and 3, QuaZip enables me to store multiple files in an archive. Rather than just pass wording the zip file (can do this too if you want) I put it into a QtAESEncrypted QByteArray and write this to a QFile output of my choosing.

I also have written a function to decrypt my dbs if I need to use a more powerful DB Editing program than the one I wrote myself (ie. SQLite Browser).

SQLite Browser is Open Source and you can add a QtCipherSqlite plugin for that, but you’d need to write it. Something I’d like to look into at some point time depending.

[devbean]

The newest version (v1.1) of this plugin QtCipherSqlitePlugin does support SQLCipher. But I believe you need try the following connection options:

dbconn.setConnectOptions("QSQLITE_USE_CIPHER=sqlcipher; SQLCIPHER_LEGACY=1");

That's because current version of SQLCipher only supports legacy mode so you need set SQLCIPHER_LEGACY to 1.

I have opened the database encrypted by SQLiteStudio SQLCipher. Please let me know if this helps you.

[SupIQ]

By using your suggestion, I am now able to open a database encrypted with DB browser, so thanks for that. However, the inverse process does not work: if I encrypt a database with the following command

db.setConnectOptions("QSQLITE_USE_CIPHER=sqlcipher; SQLCIPHER_LEGACY=1; QSQLITE_CREATE_KEY");

DB browser opens the database without asking for password first. It seems that the above command does not encrypt the database.

Which settings do I need to use for encrypting a database with your plugin, so that when the user tries to open it with DB browser or similar programs, he must insert the correct password first?

[devbean]

I have tested as you said. First created key using plugin then close Qt application. When I opened the database generated by the plugin with SQLiteDatabaseBrowser, DBBrowser required password. So could you please submit your code for testing? Thank you.

[SupIQ]

This is the code I have written for encrypting an already existing unencrypted database named localDB.db with your plugin:

#include <QCoreApplication>
#include <QDebug>
#include <QDir>
#include <QSqlError>
#include <QtSql/QSqlDatabase>

#ifdef Q_OS_IOS

#include <QtPlugin>

Q_IMPORT_PLUGIN(SqliteCipherDriverPlugin)

#endif

#define CONNECTION_FAILED -1

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

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

//    QString dir = QCoreApplication::applicationDirPath();
//    QString db_file_path = dir + "/Data/localDB.db";
//    qDebug() << "DB file path is:" << db_file_path;

    QSqlDatabase db = QSqlDatabase::addDatabase("SQLITECIPHER");
    db.setDatabaseName("F:/Project_alpha/Data/localDB.db"); //localDB.db is already existing before running the application
    db.setPassword("pass");
    db.setConnectOptions("QSQLITE_USE_CIPHER=sqlcipher; SQLCIPHER_LEGACY=1; QSQLITE_CREATE_KEY");

    db.open();

    if (!db.isOpen())
    {
        qDebug() << "Connection failed: " << db.lastError().driverText();
        exit(CONNECTION_FAILED);
    }

    return 0;
}

After running the application, localDB.db is opened by DB Browser without asking for any password! What you say works only if the database does not already exist, but if I want to encrypt a plaintext database, created before running the application, it does not work.

[devbean]

Thank you for your reply. I could comfirm this problem. Here is some solution.

First, you have some incorrect code:

db.open();

if (!db.isOpen())
{
    qDebug() << "Connection failed: " << db.lastError().driverText();
    exit(CONNECTION_FAILED);
}

db.open() will return false but db.isOpen() will return true. I'll explain why this happen.

In QtCipherSqlitePlugin, open() function's implementation as following (some pseudo-code):

bool SQLiteCipherDriver::open()
{
...
if (sqlite3_open_v2(db, &d->access, openMode, nullptr) == SQLITE_OK) {
    setOpen(true);
    wxsqlite3_config(d->access, "cipher", CODEC_TYPE_SQLCIPHER);
    const int result = sqlite3_rekey(d->access, "pass", 4);
    if (SQLITE_OK != result) {
        return false;
    }
} else {
  setOpen(false);
}
...
}

As you can see, first the plugin need open SQLite then rekey (create password). In Qt SQL plugin, QSqlDriver::open() is used for opening a database. If we open database successfully, we should set isOpen() to true but because rekey is only an action after open and it fails so open() returns false. (But I'm not sure if this is correct for open(). Maybe this should be recode. Not sure for now. Please leave your suggestion if you wish. Thank you) So you need use open() instead of isOpen():

if (!db.open()) {
    qDebug() << "Can not open connection: " << db.lastError().driverText();
    exit(CONNECTION_FAILED);
}

Second, why rekey failed? I have tested sqlite3_rekey() result code is SQLITE_CORRUPT. After communicating with wxSQLite3 (which QtCipherSqlitePlugin based on) author, the reason seems to be that the default page size (of the plain database) is 4096 bytes, while the SQLCipher requested page size is 1024. All non-legacy test cases using the default page size worked flawlessly.

A workaround is to change the page size of the plain database first. To do this you can issue the following SQL commands:

PRAGMA page_size=1024;
VACUUM;

Thereafter rekeying in legacy SQLCipher mode works.

You could find out our communication at here.

[houlang]

Here are some instructions on cryptographic algorithms that may be helpful Encryption Algorithm Description