Editor’s note: This is a guest post from community member and contributor Mark Burggraf, on his latest addition to the module marketplace. Would you like to contribute a guest post? E-Mail us at community@appcelerator.com to let us know!

Our new custom module for Titanium, “SQL Encryption and Full-Text Indexing” allows you to create and maintain AES encrypted databases in your applications using SQLCipher technology.  To quote from the SQLCipher web site: “SQLCipher is an SQLite extension that provides transparent 256-bit AES encryption of database files. Pages are encrypted before being written to disk and are decrypted when read back. Due to the small footprint and great performance it’s ideal for protecting embedded application databases and is well suited for mobile development.”

Let’s build a mobile project in Titanium for Android that uses encryption and full-text indexing.

Step 1: Create your empty project.  We’ll assume here that you have Titanium Studio running, with Titanium Mobile SDK 1.8.1, and the Android SDK already installed and configured.  We called our project “SQLEncryption”.

Step 2: Copy the module zip file into the root of your project.  The module zip file should be named:  com.dmarie.sql-android-2.1.zip.

Add zip file to project

Step 3: Run your project in the Android emulator.  The first time you run your project, you’ll see the zip file disappear from the root of your project.  Titanium Studio will unzip the module and place it in /modules/android/com.dmarie.sql/2.1, as seen here:

Module is expanded into the project

Step 4: Add a module reference to your tiapp.xml line.  Open your tiapp.xml file in Titanium Studio, click the “tiapp.xml” tab to edit the xml version of the file, then scroll to the bottom of the file.
Remove the line:

<modules/>

And replace it with these lines:

<modules>
    <module version="2.1">com.dmarie.sql</module> 
</modules>

Step 5: Copy the CommonJS module “db.js” from the /modules/android/com.dmarie.sql/2.1/example folder to your /Resources folder.  This CommonJS module makes it much easier to use our encryption module, and it also provides compatibility with most existing SQLite code you may have already written.

Step 6: Edit your app.js file.
Remove all the contents and replace it with the following 6 lines — (we’re just going to make a simple scrolling screen here):

var win1 = Titanium.UI.createWindow({backgroundColor:'#fff'});
var scrollView = Titanium.UI.createScrollView({});
var label = Titanium.UI.createLabel({ color:"black"  });
scrollView.add(label);
win1.add(scrollView);
win1.open();

Now we can simply say:

label.text += "add text here\n";

when we want to write entries to our scrolling screen.

Step 7: Add a reference to our CommonJS module db.js:

var db = require("db");

Step 8: Open an encrypted database, create a table, and add data to it:

label.text += "databases will be stored here: " + db.dbPath + "\n";
label.text += "open encrypted.db, create a table, then add a record...\n";
var maindb = db.openDB("encrypted.db", "my secret password");
maindb.execute("create table if not exists people (name TEXT)");
maindb.execute("insert into people (name) values ('John Smith')");
maindb.execute("insert into people (name) values ('John Adams')");
maindb.execute("insert into people (name) values ('Peter Tork')");

Some things to note here –

• If the file “encrypted.db” does not exist, it will be created automatically.
• Databases will be created and opened in the folder specified by db.dbPath.  (You can set this to any folder you like inside db.js.)
• If you want to distribute a database with your application, you’ll need to manually copy it to the folder specified by db.dbPath before you can use it.  There is no equivalent to the Titanium.Database.install command, so installing your data file is up to you.
• The second parameter of db.openDB accepts one of three things:
• “” (an empty string) – this will create an unencrypted, normal SQLite database
• “DEFAULT” – if you send the literal word DEFAULT, the password used will be the value returned by Titanium.App.guid
• “any password you choose” – anything else you send will be considered a password (encryption key) for the database

• Use a complex key created by manipulating text from multiple sources, adding salt, doing bitwise manipulation, etc.
• Hide the key by using a sequence of bytes stored in a binary file that’s already included with your application.  You can read your binary file into a buffer and extract out a per-determined sequence of bytes that either comprises your key, or just a portion of your key.
• Don’t store the key with your application at all — instead open an ssl data connection to your secure server and get the key over the network at runtime.

There are many ways to secure your key (if that’s important to you) but that task is up to you.

Step 9: Open a cursor and extract some data:

label.text += "open a cursor and extract some data:\n";
var rows = maindb.execute("select name from people");
label.text += rows.length + " rows were found:\n";
while (rows.isValidRow())
{    
    label.text += "name: " + rows.fieldByName("name") + "\n";
    rows.next();
}

Step 10: Create a full-text index using FTS3 (see http://www.sqlite.org/fts3.html):

maindb.execute("create virtual table FT USING fts3(name TEXT)");
maindb.execute("insert into FT (rowid,name) select rowid,name from people");

Step 11: Query the full-text index and print some results:

label.text += "testing full-text index:\n";
var srch = maindb.execute("select name,rowid from FT where name match 'john'"); // uses FTS3 index for speed
while (srch.isValidRow())
{
    label.text += srch.fieldByName("name") + " found at rowid " + srch.fieldByName("rowid") + "\n";
    srch.next();       
}

Let’s look at the database now and see if it’s really encrypted.  To compare our encrypted database to a regular, un-encrypted version, let’s run this app twice.  We’ll create two databases, an un-encrypted version and an encrypted version.

First, let’s change the location of our databases to something easier to find.  Find the openDB line of code and change it to:

db.dbPath = "/sdcard/"; // this will put databases in the root of the sdcard
var maindb = db.openDB("unencrypted.db", "");

Now run the app. This should create an unencrypted database in the root of your sdcard.

Now change the openDB line to this and run the app again:

var maindb = db.openDB("encrypted.db", "foo");

Let’s see the results of our work.  Open a terminal session (assuming you’re running OSX here) and pull the databases off your sdcard image so we can look at them:

$ adb -e pull /sdcard/unencrypted.db .
$ adb -e pull /sdcard/encrypted.db .

You’ll need to have the Android adb tool on your path.  If it’s not on your path, you’ll have to specify a full path to adb.  At this point, you should have two files in your current folder named encrypted.db and unencrypted.db.

Try opening the unencrypted version with sqlite3:

$ sqlite3 unencrypted.db
SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
people
sqlite> .exit

So we can see the “people” table just fine — no encryption here!  Let’s do the same with the encrypted database:

$ sqlite3 encrypted.db
SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Error: file is encrypted or is not a database
sqlite> .exit

This database is encrypted alright.  Let’s do a hexdump and prove that it’s encrypted:

$ hexdump -C unencrypted.db

Looking at the output, it’s easy to see our data:

Hex dump of unencrypted database

Let’s do the same with the encrypted file:

$ hexdump -C encrypted.db

You’ll just see a long stream of encrypted bytes here:

Hex dump of encrypted database

• 

This entry was posted on Thursday, February 9th, 2012 at 9:32 am and is filed under , Android, Marketplace, Mobile, Tutorial. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.