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:
- Check out the brodyspark / sqlcipher-android-database project
- Build as described in this posting (no need to fix the Makefile in this version)
- Copy the libraries (except for commons-codec.jar)
Now as a test, revert the changes to SQLitePlugin.results2string() then compile & test but first uninstall the old version from the simulator or device.
