Integrating SQLCipher with Cordova/PhoneGap sqlite plugin for iOS

NOTICE (June 2015): These instructions are completely out-of-date, the following Cordova plugin supports sqlcipher out-of-the-box: https://github.com/litehelpers/Cordova-sqlcipher-adapter

These directions are based on the sqlcipher iOS tutorial, with a few adaptations to integrate with a Cordova/PhoneGap project.

Start with a Cordova/PhoneGap iOS project (documented here for Cordova/PhoneGap 2.2.0).

Download & extract the OpenSSL source from www.openssl.org/source in a location that will be referenced later (I used OpenSSL 1.0.1c).

Inside the PhoneGap iOS project folder, clone the github sqlcipher & openssl-xcode projects using commands like the following:

$ git clone git@github.com:sqlcipher/sqlcipher.git
$ git clone git@github.com:sqlcipher/openssl-xcode.git

Download the OpenSSL source from www.openssl.org/source (I used OpenSSL 1.0.1c) & put the source tar.gz file in the openssl-xcode sub-folder.


Add the subproject references for sqlcipher & openssl-xcode. This can be achieved by:

• selecting the top-level target at the top of the tree control;
• press alt-command-a;
• for openssl-xcode: select the openssl-xcode folder and then select openssl-xcode.xcodeproj &
• repeat these steps for sqlcipher (select the sqlcipher folder & select sqlcipher.xcodeproj).

Configure build dependencies:

• select the top-level target again;
• click the Build Phases tab;
• expand the Target Dependencies;
• add crypto (click "+", select crypto, and press Add);
• add sqlcipher

and add the libraries to link:

• expand the Link Binary With Libraries;
• click "+" to add
• add both libcrypto.a & libsqlcipher.a

IMPORTANT: please make sure that no sqlite3 library is being added here.

CHECK POINT: at this point, it should be possible to build the project with the openssl & sqlcipher dependencies.

NOTE: a couple steps from the sqlcipher iOS tutorial were omitted since they should not be necessary. If the project does not build, here are some things to check & try:

• Please double-check that all dependencies, including Target Dependencies & Link Binary With Libraries have been fulfilled.
• If the sqlcipher/libcrypto does not build, please read through README.md under openssl-xcode. You may have to configure the location of the OpenSSL sources if nothing else works.
• The SQLITE_HAS_CODEC C flag, which is necessary to build sqlcipher with its cipher capabilities, should be defined within the sqlcipher subproject. It should not be necessary to define this flag within the top-level application project, however it is noted here just in case.

It should now be possible to add SQLitePlugin.[hm] to the project Plugins folder & build again. The following patch to SQLitePlugin.m should enable the plugin to use database encryption:


diff --git a/iOS/Plugins/SQLitePlugin.m b/iOS/Plugins/SQLitePlugin.m
index 871bd89..3d62208 100644
--- a/iOS/Plugins/SQLitePlugin.m
+++ b/iOS/Plugins/SQLitePlugin.m
@@ -79,6 +79,9 @@
         return;
     }

+    const char *key = [[options objectForKey:@"key"] UTF8String];
+    sqlite3_key(db, key, strlen(key));
+
     dbPointer = [NSValue valueWithPointer:db];
     [openDBs setObject:dbPointer forKey: dbname];
     [self respond:callback withString: @"{ message: 'Database opened' }" withType:@"success"];

Add SQLitePlugin to Cordova.plist resources, add SQLitePlugin.js to the www folder, and include SQLitePlugin.js in index.html. Try a small test program, like the one from the brodyspark/ PhoneGap-SQLitePlugin-iOS homepage, but open the database with a statement like this:


var db = window.sqlitePlugin.openDatabase({name: "DB",
    key: "secret1"});


If you try the program again but with a different key, you should see a db error in the console log.

In the future, I would like to provide a script or boilerplate to make it easy to create Cordova/PhoneGap projects with encrypted storage working from the beginning.

Improvements to PhoneGap-SQLitePlugin for Android & iOS: PRAGMAs & multiple databases

After moving and splitting the iOS & Android versions of the SQLitePlugin for Cordova/PhoneGap a couple improvements have been posted:

• Explicit support for PRAGMA statements (UPDATED: iOS & Android)
• Support for multiple sqlite database files fixed for Android

PRAGMA statements

In sqlite PRAGMA statements can be used to specify some configuration or actions that are not considered normal SQL statements. Some examples include:

• SQLite foreign key support
• PRAGMA table_info
• For the SQLCipher flavor: PRAGMA key to set the encryption key

While it is possible to run a PRAGMA statement in the executeSql() function, a number of the PRAGMA statements are treated as no-ops within a database transaction. Since the Web SQL API and hence the sqlitePlugin API for Android only allows executeSql() to be run on a transaction, a better solution is required to support PRAGMA statements.

In the Android version the new executePragmaStatement() has been added to the sqlitePlugin database object. Here is an sample:

        document.addEventListener("deviceready", onDeviceReady, false);

        function onDeviceReady() {

          var db = window.sqlitePlugin.openDatabase("Database", "1.0", "PhoneGap Demo", 200000);

          db.transaction(function(tx) {

            tx.executeSql('DROP TABLE IF EXISTS test_table');

            tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)', [], function() {

              console.log("test_table created");

            });

            db.executePragmaStatement(

              "pragma table_info (test_table);", function(res) {

                alert("PRAGMA res: " + JSON.stringify(res));

              });

          });

        }

Note that there is no argument substitution, just use your trusty JavaScript or CoffeeScript string manipulation tools.

Support for multiple sqlite database files

This is really just a bug fix for the Android version, considering that the API is designed to support multiple database files and this is already working for the iOS version. Here is the test that was used to verify the fix:

        document.addEventListener("deviceready", onDeviceReady, false);

        function onDeviceReady() {

          var db = window.sqlitePlugin.openDatabase("Database", "1.0", "PhoneGap Demo", 200000);

          var db2 = window.sqlitePlugin.openDatabase("DB2", "1.0", "PhoneGap Demo", 200000);

          db.transaction(function(tx) {

            tx.executeSql('DROP TABLE IF EXISTS test_table');

            tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)', [], function() {

              console.log("test_table created");

            });

            db.executePragmaStatement("pragma table_info (test_table);", function(res) {

              alert("PRAGMA res: " + JSON.stringify(res));

            });

          });

          db2.transaction(function(tx) {

            tx.executeSql('DROP TABLE IF EXISTS tt2');

            tx.executeSql('CREATE TABLE IF NOT EXISTS tt2 (id2 integer primary key, data2 text, data_num2 integer)', [], function() {

              console.log("tt2 created");

            });

            db.executePragmaStatement("pragma table_info (test_table);", function(res) {

              alert("PRAGMA (db) res: " + JSON.stringify(res));

            });

            db2.executePragmaStatement("pragma table_info (tt2);", function(res) {

              alert("PRAGMA (tt2) res: " + JSON.stringify(res));

            });

          });

        }

How the PRAGMA feature can be used with SQLCipher will be described in a future posting.