Monday, December 24, 2012

Using SQLCipher for Android with Cordova/PhoneGap

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

The project brodyspark / PhoneGap-SQLitePlugin-Android provides native sqlite database access with an API that is very close to the HTML5 SQL API and can also provide an interface to SQLCipher for Android. Here are the steps to use SQLCipher with a Cordova/PhoneGap project for Android.


Install sqlite plugin for Android


The first step is to install but not build the sqlite plugin for Android. A good starting point is the Cordova/PhoneGap Android example project, found in the lib/android/example subdirectory of Cordova/PhoneGap as downloaded from phonegap.com/download. These steps are also covered in README.md of brodyspark / PhoneGap-SQLitePlugin-Android. Here are the steps in brief to install the sqlite plugin from PhoneGap-SQLitePlugin-Android:

  • Install SQLitePlugin.js from the Android/assets/www subdirectory into assets/www; for example:
$ cp -v ../PhoneGap-SQLitePlugin-Android/Android/assets/www/SQLitePlugin.js assets/www
  • From the Android/src subdirectory copy the tree com/phonegap/plugin/sqlitePlugin with SQLitePlugin.java into src; for example:
$ cp -rv ../PhoneGap-SQLitePlugin-Android/Android/src/com src
  • Add the plugin element to res/xml/config.xml:
--- config.xml.old  2012-07-24 19:44:49.000000000 +0200
+++ res/xml/config.xml  2012-07-24 19:39:43.000000000 +0200
@@ -32,6 +32,7 @@
     <log level="DEBUG"/>
     <preference name="useBrowserHistory" value="false" />
 <plugins>
+    <plugin name="SQLitePlugin" value="com.phonegap.plugin.sqlitePlugin.SQLitePlugin"/>
     <plugin name="App" value="org.apache.cordova.App"/>
     <plugin name="Geolocation" value="org.apache.cordova.GeoBroker"/>
     <plugin name="Device" value="org.apache.cordova.Device"/>


Install prebuilt SQLCipher for Android


  • Download SQLCipher for Android from android-database-sqlcipher / downloads & unzip the package. Note that the package seems to contain an extra __MACOSX subdirectory which can be ignored.
  • Copy the contents of the lib subdirectory from the package into the libs subdirectory except for commons-codec.jar, for example:
$ cp -rv SQLCipher\ for\ Android\ 2.1.1/libs/* libs
$ rm libs/commons-codec.jar
  • Install the ICU file icudt46l.zip from the assets subdirectory in assets, for example:
$ cp SQLCipher\ for\ Android\ 2.1.1/assets/icudt46l.zip assets

Notes:

  • commons-codec.jar is already part of Cordova/PhoneGap
  • SQLCipher for Android attempts to use the system-provided ICU localisation file but it may not included with all target systems.
  • The x86 subdirectory is only necessary when using x86 as a target; for example, some developers prefer to use an x86 Android simulation platform.

Patches to sqlite plugin to use SQLCipher


In src/com/phonegap/plugin/sqlitePlugin/SQLitePlugin.java make the following changes:

  • In the imports:
@@ -24,7 +24,7 @@
 
 import android.database.Cursor;
 
-import android.database.sqlite.*;
+import net.sqlcipher.database.*;
 
 import android.util.Log;
 
  • In SQLitePlugin.execute():
@@ -63,7 +63,8 @@
     JSONObject o = args.getJSONObject(0);
     String dbname = o.getString("name");
 
-    this.openDatabase(dbname, null);
+    String key = o.getString("key");
+    this.openDatabase(dbname, key);
    }
    else if (action.equals("close")) {
     this.closeDatabase(args.getString(0));
  • In SQLitePlugin.openDatabase():
@@ -152,13 +153,15 @@
   */
  private void openDatabase(String dbname, String password)
  {
+  SQLiteDatabase.loadLibs(this.cordova.getActivity());
+
   if (this.getDatabase(dbname) != null) this.closeDatabase(dbname);
 
   File dbfile = this.cordova.getActivity().getDatabasePath(dbname + ".db");
 
   Log.v("info", "Open sqlite db: " + dbfile.getAbsolutePath());
 
-  SQLiteDatabase mydb = SQLiteDatabase.openOrCreateDatabase(dbfile, null);
+  SQLiteDatabase mydb = SQLiteDatabase.openOrCreateDatabase(dbfile, password, null);
 
   dbmap.put(dbname, mydb);
  }
  • In SQLitePlugin.results2string():
@@ -348,6 +351,7 @@
      for (int i = 0; i < colCount; ++i) {
       key = cur.getColumnName(i);
 
+      /**
       // for old Android SDK remove lines from HERE:
       if(android.os.Build.VERSION.SDK_INT >= 11)
       {
@@ -371,6 +375,7 @@
        }
       }
       else // to HERE.
+      **/
       {
        row.put(key, cur.getString(i));
       }

Build and test


Make sure the project is updated for a target platform:

$ android update project --path . --target <id>


Add a small test program to assets/www/index.html, for example:

--- assets/www/index.html.orig 2012-12-24 22:57:45.000000000 +0100
+++ assets/www/index.html 2012-12-24 23:05:40.000000000 +0100
@@ -34,9 +34,35 @@
             </div>
         </div>
         <script type="text/javascript" src="cordova-2.2.0.js"></script>
-        <script type="text/javascript" src="js/index.js"></script>
+        <script type="text/javascript" src="SQLitePlugin.js"></script>
         <script type="text/javascript">
-            app.initialize();
+        document.addEventListener("deviceready", onDeviceReady, false);
+
+        // Cordova is ready
+        function onDeviceReady() {
+          var db = window.sqlitePlugin.openDatabase({name: "DB", key: "secret1"});
+
+          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)');
+
+            tx.executeSql("INSERT INTO test_table (data, data_num) VALUES (?,?)", ["test", 100], function(tx, res) {
+              console.log("insertId: " + res.insertId + " -- probably 1");
+              alert("insertId: " + res.insertId + " -- probably 1");
+
+              db.transaction(function(tx) {
+                tx.executeSql("select count(id) as cnt from test_table;", [], function(tx, res) {
+                  console.log("res.rows.length: " + res.rows.length + " -- should be 1");
+                  console.log("res.rows.item(0).cnt: " + res.rows.item(0).cnt + " -- should be 1");
+                  alert("res.rows.item(0).cnt: " + res.rows.item(0).cnt + " -- should be 1");
+                });
+              });
+
+            }, function(e) {
+              console.log("ERROR: " + e.message);
+            });
+          });
+        }
         </script>
     </body>
 </html>



Try a debug install:

$ ant debug install


and the test should run.

To test that the encryption is working, try changing the password key in assets/www/index.html:

--- assets/www/index.html 2012-12-24 23:07:43.000000000 +0100
+++ assets/www/index.html.new 2012-12-24 23:13:38.000000000 +0100
@@ -40,7 +40,7 @@
 
         // Cordova is ready
         function onDeviceReady() {
-          var db = window.sqlitePlugin.openDatabase({name: "DB", key: "secret1"});
+          var db = window.sqlitePlugin.openDatabase({name: "DB", key: "secret2"});
 
           db.transaction(function(tx) {
             tx.executeSql('DROP TABLE IF EXISTS test_table');

and try installing and running again. The program should not work if a different encryption key is used to open the database.

Using cursor type enhancements


As described in this recent posting, some enhancements were made in brodyspark / sqlcipher-android-database to use the actual row/column data type information instead of treating all row/column data as strings. To use these changes:
Now as a test, revert the changes to SQLitePlugin.results2string() then compile & test but first uninstall the old version from the simulator or device.

3 comments:

  1. Nice post.Give it up. Thanks for share this article. For more visit:android development

    ReplyDelete
  2. Hi Chris
    Thanks for this article
    android 4.1 as sdk
    cordova 2.4.0
    and your last version of the sqliteplugin for android

    I followed this article I found an issue
    your plugin was working for me without the sqlcipher integration but now it is requesting close a cursor when an insert is do it.

    Did you see this error ?

    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): Releasing statement in a finalizer. Please ensure that you explicitly call close() on your cursor: INSERT INTO Test (id, name,
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): net.sqlcipher.database.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at net.sqlcipher.database.SQLiteCompiledSql.(SQLiteCompiledSql.java:62)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at net.sqlcipher.database.SQLiteProgram.(SQLiteProgram.java:103)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at net.sqlcipher.database.SQLiteStatement.(SQLiteStatement.java:39)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at net.sqlcipher.database.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1243)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at com.cordova.plugins.sqlitePlugin.SQLitePlugin.executeSqlBatch(SQLitePlugin.java:243)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at com.cordova.plugins.sqlitePlugin.SQLitePlugin.execute(SQLitePlugin.java:116)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at org.apache.cordova.api.CordovaPlugin.execute(CordovaPlugin.java:61)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at org.apache.cordova.api.PluginManager.exec(PluginManager.java:224)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at org.apache.cordova.ExposedJsApi.exec(ExposedJsApi.java:45)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at android.webkit.JWebCoreJavaBridge.nativeServiceFuncPtrQueue(Native Method)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at android.webkit.JWebCoreJavaBridge.nativeServiceFuncPtrQueue(Native Method)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at android.webkit.JWebCoreJavaBridge.handleMessage(JWebCoreJavaBridge.java:113)
    03-13 15:38:57.932: W/SQLiteCompiledSql(2879): at android.os.Handler.dispatchMessage(Handler.java:99)

    ReplyDelete
  3. Nice job on the SQLitePlugin. It works great. I have tried added SQLCipher to my app, but can't get it to work. I am getting the NPObject error when I try to open a database with a 'key' property. Do you have any patches for the JavaScript with SQLCipher. I believe it is missing the code to include in the 'key' parameter. I have managed to get the SQLitePlugin to work without using SQLCipher. I added all the patches to the Java code and managed to get it to work if I hard code the 'key' value in the Java:

    this.openDatabase(dbname, 'mykey');

    I also looked in the JavaScript and there is not reference to the 'key' property in the 'openargs' parameter. I downloaded the JS from https://github.com/brodyspark/PhoneGap-SQLitePlugin-Android.

    ReplyDelete