Back in January I posted a blog entry that show how to create a SQLite manger API that allow you to work with an application that has many SQL commands easily. These commands may be initialized from different classes and used in cases where we may want to keep the database connection open and avoid duplicating code. The orginal blog post can be found here.

Adobe AIR 1.5 closed a security gap. The application is using the exact same database. Essentially any AIR application can read other applications database.

Adobe has updated the framework and now includes the ability to add additional properties to encrypt your SQLite database. Under the new security you can create an encrypted database and when attempting to open the database, your code must provide the database’s encryption key.

In the past few months I received many requests to add encryption to the API. I finally got a chance to update the code and have added the option to add a password to the database. The key is created using the Adobe’s EncryptionKeyGenerator class and is bond to Adobe’s license.

Here’s the method signature:

public function start(dbFullFileName:String, tableName:String, createTableStatement:String, password:String=null):void

In case you don’t need to include encryption to your database, just keep the password as “null”. Other than the start method and a method to generate a key I have updated. Additionally, I added custom events as well meta data for the event constants.

		public function start(dbFullFileName:String, tableName:String, createTableStatement:String, password:String=null):void
		{
			this.dbFullFileName = dbFullFileName;
			this.tableName = tableName;
			this.createDbStatement = createTableStatement;
			var encryptionKey:ByteArray = null;

			connection = new SQLConnection();
			sqlFile = File.applicationStorageDirectory.resolvePath(dbFullFileName);

			try
			{
				if (password != null)
				{
					encryptionKey = getEncryptionKey(password, sqlFile);
				}

				connection.open(sqlFile, SQLMode.CREATE, false, 1024, encryptionKey);

			    this.dispatchEvent(new DatabaseSuccessEvent(DatabaseSuccessEvent.DATABASE_CONNECTED_SUCCESSFULLY));
			}
			catch (error:SQLError)
			{
			    var errorMessage:String = "Error message:" + error.message;

			    if (error.details != "")
			    	errorMessage += ", Details:" + error.details;

			    fail(null, errorMessage);
			}
		}

The getEncryptionKey method takes the password and the File class validate the password that is 8-32 char with one letter lowercase letter as well as one upper case and one number. It return a ByteArray which being pass to the connection.open method.

		private function getEncryptionKey(password:String, sqlFile:File):ByteArray
		{
			var keyGen:EncryptionKeyGenerator = new EncryptionKeyGenerator();
			var encryptionKey:ByteArray;

			if (!keyGen.validateStrongPassword(password))
			{
				var errorMessage:String = "The password must be 8-32 char, " + "with one letter lowercase letter, " +
						"one upper case and one number";

				fail(null, errorMessage);

				return null;
			}

			encryptionKey = keyGen.getEncryptionKey(sqlFile, password);	

			return encryptionKey;
		}

To see an example of implementation, take a look at the code below:

<WindowedApplication xmlns="http://ns.adobe.com/mxml/2009" layout="absolute"
	initialize="initializeHandler(event)">

	<Script>
		<![CDATA[
			import com.elad.framework.sqlite.events.DatabaseSuccessEvent;
			import com.elad.framework.sqlite.events.StatementCompleteEvent;
			import com.elad.framework.sqlite.events.DatabaseFailEvent;
			import mx.collections.ArrayCollection;
			import mx.events.FlexEvent;
			import com.elad.framework.sqlite.SQLiteManager;

			private var database:SQLiteManager = SQLiteManager.getInstance();

			protected function initializeHandler(event:FlexEvent):void
			{
				database.addEventListener(DatabaseSuccessEvent.COMMAND_EXEC_SUCCESSFULLY, onSelectResult);
				database.addEventListener(DatabaseFailEvent.COMMAND_EXEC_FAILED, function(event:DatabaseFailEvent):void {
					trace("execution fail: "+event.errorMessage);
				});
				database.addEventListener(DatabaseFailEvent.DATABASE_FAIL, function(event:DatabaseFailEvent):void {
					trace("database fail: "+event.errorMessage);
				});
				database.addEventListener(DatabaseSuccessEvent.CREATING_DATABASE, function(event:DatabaseSuccessEvent):void {
					trace(event.message);
				});
				database.addEventListener(DatabaseSuccessEvent.DATABASE_CONNECTED_SUCCESSFULLY, onConnectedHandler);
				database.addEventListener(DatabaseSuccessEvent.DATABASE_READY, function():void { trace("database ready!"); } );

				// start database
				var password:String = "Pa55word";
				var createTableStatement:String = "CREATE TABLE Users(UserId VARCHAR(150) PRIMARY KEY, UserName VARCHAR(150))";
				database.start("Users.sql3", "Users", createTableStatement, password);
			}

			private function onConnectedHandler(event:DatabaseSuccessEvent):void
			{
				readEntries();
			}

			private function insertEntry():void
			{
				var sql:String =  "INSERT INTO Users VALUES('"+String(userId.text)+"','"+userName.text+"');";
				database.executeCustomCommand(sql);
			}

			private function readEntries():void
			{
				database.executeSelectAllCommand();
			}

			private function onSelectResult(event:StatementCompleteEvent):void
			{
				var result:Array = event.results.data;
				var rowsAffected:int = event.results.rowsAffected;  

				if (rowsAffected == 1)
					readEntries();

				if (result == null)
					return;

				var len:int = result.length;
				var dp:ArrayCollection = new ArrayCollection();

				for (var i:int; i<len; i++)
				{
					dp.addItem( {UserId: result[i].UserId, UserName: result[i].UserName} );
				}

				dataGrid.dataProvider = dp;
			}			

		]]>
	</Script>
	<Panel x="5" y="5" layout="absolute" height="356">

		<VBox horizontalScrollPolicy="off" verticalScrollPolicy="off">
			<!-- Form -->
			<Form width="414">
				<FormItem label="User ID:">
					<FxTextInput id="userId"/>
				</FormItem>
				<FormItem label="User Name:">
					<FxTextInput id="userName"/>
				</FormItem>
				<FormItem>
					<FxButton label="Insert Entry" click="insertEntry();"/>
				</FormItem>
			</Form>
		</VBox>

		<!-- Results -->
		<DataGrid x="19" y="123" id="dataGrid">
			<columns>
				<DataGridColumn headerText="User Id" dataField="UserId"/>
				<DataGridColumn headerText="User Name" dataField="UserName"/>
			</columns>
		</DataGrid>

	</Panel>

</WindowedApplication>

To download the complete code click here.