How to build the Qt-SQL-driver-plugin ‘QSQLCIPHER’ for SQLite-DB with SQLCipher-extension using the Windows/MinGW-platform?

by

How to build the Qt-SQL-driver-plugin ‘QSQLCIPHER’ for SQLite-DB with SQLCipher-extension using the Windows/MinGW-platform:

  1. Qt 5.4.0 for Windows/MinGW

    • Download Qt
    • Install including the sources e.g to C:\Qt\Qt5.4.0

  2. OpenSSL for Windows

    • Download Win32 OpenSSL v1.0.2a
    • Download Visual C++ 2008 Redistributable
    • Install Visual C++ 2008 Redistributable by executing ‘vcredist_x86.exe’
    • Install OpenSSL v1.0.2a by executing ‘Win32OpenSSL-1_0_2.exe’
      • Target directory e.g. C:\OpenSSL-Win32
      • During installation choose the option to install the libraries to the Windows system directory (C:\Windows\SysWOW64)

  3. MinGW – Minimalist GNU for Windows

    • Download and install ‘mingw-get-setup.exe’
    • Start of MinGW Installer
      • Installation of MSYS Base System
        • Selection: All Packages -> MSYS -> MSYS Base System
        • Select msys-base (Class ‘bin’) for installation
        • Menu: installation -> apply changes
        • Installation of files by default to directory C:\MinGW
      • Installation of Tcl/Tk
        • Selection: All Packages -> MinGW -> MinGW Contributed
        • Select ‘mingw32-tcl’ and ‘mingw32-tk’ (Class ‘bin’) for installation
        • Menu: installation -> apply changes
        • Installation of files by default to directory C:\MinGW
    • Copy content of C:\MinGW to the Qt-MinGW-directory C:\Qt\Qt5.4.0\Tools\mingw491_32

    • Create file ‘fstab’ in C:\Qt\Qt5.4.0\Tools\mingw491_32\msys\1.0\etc

      • Insert content as follows:

        #Win32_Path                        Mount_Point
C:/Qt/Qt5.4.0/Tools/mingw491_32    /mingw
C:/Qt/Qt5.4.0/5.4                  /qt
C:/                                /c

  4. zlib-Library

    • Download zlib-dll-Binaries
    • Extract and copy file ‘zlib1.dll’ to the Qt-MinGW-directory C:\Qt\Qt5.4.0\Tools\mingw491_32\msys\1.0\bin

  5. SQLCipher

    • Download the SQLCipher-zip-file
    • Extract the zip-file e.g. to C:\temp\sqlcipher-master
    • Copy OpenSSL-Win32-libraries
      • Copy C:\OpenSSL-Win32\bin\libeay32.dll to C:\temp\sqlcipher-master
      • Copy C:\OpenSSL-Win32\lib\libeay32.lib to C:\temp\sqlcipher-master

    • Build SQLCipher.exe

      • Execute MSYS: C:\Qt\Qt5.4.0\Tools\mingw491_32\msys\1.0\msys.bat

        $ cd /c/temp/sqlcipher-master
$ ./configure --prefix=$(pwd)/dist --with-crypto-lib=none --disable-tcl CFLAGS="-DSQLITE_HAS_CODEC -DSQLCIPHER_CRYPTO_OPENSSL -I/c/openssl-win32/include /c/temp/sqlcipher-master/libeay32.dll -L/c/temp/sqlcipher-master/ -static-libgcc" LDFLAGS="-leay32"
$ make clean
$ make sqlite3.c
$ make
$ make dll
$ make install

    • Save the executable SQLite/SQLCipher-database e.g. to C:\sqlcipher

      • Copy C:\temp\sqlcipher-master\dist\bin\sqlcipher.exe to C:\sqlcipher.
        The file ‘sqlcipher.exe’ is the crypting equivalent to the non-crypting original command line interface ‘sqlite3.exe’.
      • Copy C:\temp\sqlcipher-master\sqlite3.dll to C:\sqlcipher.
        This file is the SQLite-library extended by the encryption.
    • The SQLite-database with SQLCipher-extension is now ready for work.

  6. Build Qt-QSQLCIPHER-driver-plugin

    • Create directory:
      C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\plugins\sqldrivers\sqlcipher

    • Create the following three files within the new directory:

      • File 1: smain.cpp:

        #include <qsqldriverplugin.h>
#include <qstringlist.h>
#include "../../../../src/sql/drivers/sqlite/qsql_sqlite_p.h" // There was a missing " at the end of this line
QT_BEGIN_NAMESPACE
class QSQLcipherDriverPlugin : public QSqlDriverPlugin
{
    Q_OBJECT
    Q_PLUGIN_METADATA(IID "org.qt-project.Qt.QSqlDriverFactoryInterface" FILE "sqlcipher.json")
public:
    QSQLcipherDriverPlugin();

    QSqlDriver* create(const QString &);
};
QSQLcipherDriverPlugin::QSQLcipherDriverPlugin()
    : QSqlDriverPlugin()
{
}
QSqlDriver* QSQLcipherDriverPlugin::create(const QString &name)
{
    if (name == QLatin1String("QSQLCIPHER")) {
        QSQLiteDriver* driver = new QSQLiteDriver();
        return driver;
    }
    return 0;
}
QT_END_NAMESPACE
#include "smain.moc"

      • File 2: sqlcipher.pro

        TARGET = qsqlcipher
SOURCES = smain.cpp
OTHER_FILES += sqlcipher.json
include(../../../sql/drivers/sqlcipher/qsql_sqlite.pri)
wince*: DEFINES += HAVE_LOCALTIME_S=0
PLUGIN_CLASS_NAME = QSQLcipherDriverPlugin
include(../qsqldriverbase.pri)

      • File 3: sqlcipher.json

        {
    "Keys": [ "QSQLCIPHER" ]
}
    • Copy directory C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\sql\drivers\sqlite
      to C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\sql\drivers\sqlcipher

    • Customize file C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\sql\drivers\sqlcipher\qsql_sqlite.pri
      The content of the file shall be like:

      HEADERS += $$PWD/qsql_sqlite_p.h
SOURCES += $$PWD/qsql_sqlite.cpp
!system-sqlite:!contains(LIBS, .*sqlite3.*) {
    include($$PWD/../../../3rdparty/sqlcipher.pri)     #<-- change path of sqlite.pri to sqlcipher.pri here !
} else {
    LIBS += $$QT_LFLAGS_SQLITE
    QMAKE_CXXFLAGS *= $$QT_CFLAGS_SQLITE
}
    • The remaining two files in this directory need not to be changed.

    • Create file ‘sqlcipher.pri’ in directory C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\3rdparty with following content:

      CONFIG(release, debug|release):DEFINES *= NDEBUG
DEFINES += SQLITE_OMIT_LOAD_EXTENSION SQLITE_OMIT_COMPLETE SQLITE_ENABLE_FTS3 SQLITE_ENABLE_FTS3_PARENTHESIS SQLITE_ENABLE_RTREE SQLITE_HAS_CODEC
!contains(CONFIG, largefile):DEFINES += SQLITE_DISABLE_LFS
contains(QT_CONFIG, posix_fallocate):DEFINES += HAVE_POSIX_FALLOCATE=1
winrt: DEFINES += SQLITE_OS_WINRT
winphone: DEFINES += SQLITE_WIN32_FILEMAPPING_API=1
qnx: DEFINES += _QNX_SOURCE
INCLUDEPATH +=  $$PWD/sqlcipher c:/openssl-win32/include
SOURCES +=      $$PWD/sqlcipher/sqlite3.c
LIBS += -L$$PWD/sqlcipher/lib -lsqlcipher -leay32 -lsqlite3
TR_EXCLUDE += $$PWD/*

    • Create and fill C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\3rdparty\sqlcipher

      • Create the two directories:

        C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\3rdparty\sqlcipher
C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\3rdparty\sqlcipher\lib

      • Copy the following files to C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\3rdparty\sqlcipher:

        C:\temp\sqlcipher-master\shell.c
C:\temp\sqlcipher-master\sqlite3.c
C:\temp\sqlcipher-master\sqlite3.h
C:\temp\sqlcipher-master\sqlite3ext.h

      • Copy the following files/directories to C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\3rdparty\sqlcipher\lib:

        C:\temp\sqlcipher-master\dist\lib
C:\temp\sqlcipher-master\sqlite3.dll
C:\OpenSSL-Win32\bin\libeay32.dll

      • The directory now consists of the following files and directories:

        C:\QT\QT5.4.0\5.4\SRC\QTBASE\SRC\3RDPARTY\SQLCIPHER
|   shell.c
|   sqlite3.c
|   sqlite3.h
|   sqlite3ext.h
|
\---lib
    |   libeay32.dll
    |   libsqlcipher.a
    |   libsqlcipher.la
    |   sqlite3.dll
    |
    \---pkgconfig
            sqlcipher.pc

    • Compile the QSQLCIPHER-driver-plugin for Qt:

      • Open Qt-command line C:\Windows\System32\cmd.exe /A /Q /K C:\Qt\Qt5.4.0\5.4\mingw491_32\bin\qtenv2.bat

      • Execute the following commands:

        cd C:\Qt\Qt5.4.0\5.4\Src\qtbase\src\pluins\sqldrivers\sqlcipher
qmake
mingw32-make

      • This builds the QSQLCIPHER-driver-plugin within the following directory:

        C:\QT\QT5.4.0\5.4\SRC\QTBASE\PLUGINS\SQLDRIVERS
    libqsqlcipher.a
    libqsqlcipherd.a
    qsqlcipher.dll
    qsqlcipherd.dll
      • Copy ‘qsqlcipher.dll’ and ‘qsqlcipherd.dll’ to the SQL-driver-plugin-directory C:\Qt\Qt5.4.0\5.4\mingw491_32\plugins\sqldrivers.

  7. Create a new encrypted SQLite/SQLCipher-database

    • Create new SQLite-Plaintext-database ‘plaintext.db’ with a test table and some test data

      • Change directory to C:\sqlcipher, which contains ‘sqlcipher.exe’ and ‘sqlite3.dll’ (see above).

        C:\sqlcipher>sqlcpher.exe plaintext.db
  SQLCipher version 3.8.6 2014-08-15 11:46:33
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> create table testtable (id integer, name text);
  sqlite> insert into testtable (id,name) values(1,'Bob');
  sqlite> insert into testtable (id,name) values(2,'Charlie');
  sqlite> insert into testtable (id,name) values(3,'Daphne');
  sqlite> select * from testtable;
  1|Bob
  2|Charlie
  3|Daphne
  sqlite> .exit
    • Open C:\sqlcipher\plaintext.db using a standard text-editor:
      Database scheme and test data can be read in plaintext.

    • Encrypting the plaintext-database
      This will create the database C:\sqlcipher\encrypted.db using the key ‘testkey’.

      C:\sqlcipher>sqlcipher.exe plaintext.db
    SQLCipher version 3.8.6 2014-08-15 11:46:33
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';
    sqlite> SELECT sqlcipher_export('encrypted');
    sqlite> DETACH DATABASE encrypted;
    sqlite> .exit
    • Open C:\sqlcipher\encrypted.db using a standard text-editor:
      Data are now encrypted.
    • For more useful information visit:
      https://www.zetetic.net/sqlcipher/sqlcipher-api/

  8. Usage of the SQLite-database with SQLCipher-extension and access via Qt

    • Create a new Qt-command-line-project e.g. ‘qsqlcipher’

    • Project file

      QT       += core sql
QT       -= gui
TARGET = qsqlcipher
CONFIG   += console
CONFIG   -= app_bundle
TEMPLATE = app
SOURCES += main.cpp

    • Test-program ‘main.cpp’

      #include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>
#include <QString>
int main(int argc, char *argv[]) {
    QCoreApplication a(argc, argv);
    qDebug() << QSqlDatabase::drivers();
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLCIPHER");
    db.setDatabaseName("C:/sqlcipher/encrypted.db");
    db.open();
    QSqlQuery q;
    q.exec("PRAGMA key = 'testkey';");
    q.exec("insert into testtable (id,name) values(4,'dummy')");
    q.exec("SELECT id,name anz FROM testtable");
    while (q.next()) {
        QString id = q.value(0).toString();
        QString name = q.value(1).toString();
        qDebug() << "id=" << id << ",  name=" << name;
    }
    db.close();
    return 0;
}

    • Compile and execute

      ("QSQLCIPHER", "QSQLITE", "QMYSQL", "QMYSQL3", "QODBC", "QODBC3", "QPSQL", "QPSQL7")
id= "1" ,  name= "Bob"
id= "2" ,  name= "Charlie"
id= "3" ,  name= "Daphne"
id= "4" ,  name= "dummy"

    • When delivering a Qt-program do not forget the Qt-libraries, the platforms-libraries, SQL-driver-plugin ‘qsqlcipher.dll’ and the OpenSSL-library ‘libeay32.dll’.
      Example for the test program above:

      C:\TEMP\QSQLCIPHER-TEST
|   icudt53.dll
|   icuin53.dll
|   icuuc53.dll
|   libeay32.dll
|   libgcc_s_dw2-1.dll
|   libstdc++-6.dll
|   libwinpthread-1.dll
|   qsqlcipher.exe
|   Qt5Core.dll
|   Qt5Sql.dll
|
+---platforms
|       qminimal.dll
|       qoffscreen.dll
|       qwindows.dll
|
\---sqldrivers
        qsqlcipher.dll

    • Caution: The test program contains the key:

      ...
q.exec("PRAGMA key = 'testkey';");
...

      This key string in the binary file of the test program can easiliy be read using a hex-editor, which is, to my opinion, a lack in security:

      ...
00002C90  70 68 65 72 2F 65 6E 63 72 79 70 74 65 64 2E 64  pher/encrypted.d
00002CA0  62 00 50 52 41 47 4D 41 20 6B 65 79 20 3D 20 27  b.PRAGMA key = '
00002CB0  74 65 73 74 6B 65 79 27 3B 00 00 00 69 6E 73 65  testkey';...inse
00002CC0  72 74 20 69 6E 74 6F 20 74 65 73 74 74 61 62 6C  rt into testtabl
...

      For approaches how to solve this problem, ask the search engine of your own choice. 😉
      E.g. search for: hide string in executable

More Related Contents:

  1. Access sqlite from a remote server
  2. Porting C++ Builder to Qt [closed]
  3. SQLite with encryption/password protection
  4. QLabel does not display in QWidget
  5. How to make item view render rich (html) text in Qt
  6. Qt Stylesheet for custom widget
  7. Android database encryption
  8. How to specify different Debug/Release output directories in QMake .pro file
  9. How to redirect qDebug, qWarning, qCritical etc output?
  10. How do I configure Qt for cross-compilation from Linux to Windows target?
  11. How can I hide/delete the “?” help button on the “title bar” of a Qt Dialog?
  12. How to use QMake’s subdirs template?
  13. qmake pre-build step before ANY compilation
  14. How to make a column in QTableWidget read only?
  15. Qt Layout on QMainWindow
  16. Why does calling quit() before exec() not quit the application?
  17. Where does Qt Creator save its settings?
  18. How to insert QChartView in form with Qt Designer?
  19. SQLite accent-insensitive search
  20. Why should nesting of QEventLoops be avoided?
  21. How to convert qmake to cmake?
  22. Console input with Qt Creator
  23. Error: C2228: left of ” must have class/struct/union
  24. Encrypt SQLite database in C#
  25. How to install a missing Qt module?
  26. Deploy Qt5 QML application
  27. Dynamic instantiation of QML objects?
  28. qfiledialog – Filtering Folders?
  29. How to drop a custom QStandardItem into a QListView
  30. Different delegates for QML ListView

Leave a Comment