AIR SQLite encryption and security strategies

When updating my SQLite admin tool (Lita) to AIR 1.5, I ran into some issues regarding the implementation of sqlite database encryption. After talking with Paul Robertson, I managed to get a better idea of what security strategies should apply to the different application scenarios. These are of course pretty important issues, so I thought I should share my thoughts.

But first of all, if you're about to implement sqlite database encryption in your next AIR app, I would strongly encourage you to read the related AIR docs articles, especially this page : Considerations for using encryption with a database.


The need for tools to generate encryption keys

As you probably know, the AIR 1.5 SQLConnection open() method now allows us to specify an encryption key in the form of a ByteArray object.

To help us with the process of generating this key, Adobe's own Paul Robertson has provided a very useful EncryptionKeyGenerator class.

This class enforces the most secure scenario I can think of : a password protected database which resulting key is generated using a salt that is tied to the application, through the EncryptedLocalStore. Again, refer to the docs for more information on this class.

What this (roughly) means is that, even if you have guessed the password, you cannot open the db from another application (or even from another user session). This, among other things, prevents brute force attacks.

It is very important to note that, since Paul's class :
-is very good
-is included in the AIR docs
-will soon be included in the corelib

...it will soon become the de facto standard for encrypting sqlite databases using AIR.

Issues and possible workarounds

However, sometimes, this very secure use case is not necessary what you want. There are times when you still want your encrypted database to be accessible from other users and applications, granted they have a valid password (or equivalent).

This is exactly the case I ran into with Lita since, as a db admin tool, it has to open db files created by other applications.

So I had to re-write Lita encryption related logic to make it possible to open db which keys were created with this class. As Paul suggested me, the simplest way to do so is probably to use the Base64 hashed key String in order to regenerate the key (see below).

But Lita also has to create encrypted databases (and reencrypt them). Of course, it cannot do so using the EncryptionKeyGenerator class since it would then tie the db key salt to Lita's EncryptedLocalStore.

To create encrypted databases without tying them to Lita, I have written a very simplified version EncryptionKeyGenerator class, called SimpleEncryptionKeyGenerator, which implements a password only (ie no salt) based encryption.

(You can download the class at the bottom of this page)

package com.dehats.air.sqlite
{
	import flash.filesystem.File;
	import flash.utils.ByteArray;
 
	import mx.utils.SHA256;
 
 
	/**
	 * 
	 * @author davidderaedt
	 * 
	 * This class is a simplified version of Paul Roberston's
	 * EncryptionKeyGenerator.
	 * 
	 * It is designed to let users generate encryption keys without
	 * using EncryptedLocalStore based salt values.
	 * 
	 * As a result, it can be used by third party applications to 
	 * access encrypted SQLite DBs created by other applications 
	 * using the same shared password.
	 * 
	 * It is also much less secure, as it's pretty easy to break
	 * using brute force. You may consider adding some limited
 
	 * login attempts logic.
	 * 
	 */			
	public class SimpleEncryptionKeyGenerator
	{
		// ------- Constants -------
		public static const PASSWORD_ERROR_ID:uint = 3138;
 
		private static const STRONG_PASSWORD_PATTERN:RegExp = /(?=^.{8,32}$)((?=.*\d)|(?=.*\W+))(?![.\n])(?=.*[A-Z])(?=.*[a-z]).*$/;		
 
		// ------- Constructor -------
		public function SimpleEncryptionKeyGenerator()
		{
		}
 
 
		// ------- Public methods -------
		public function validateStrongPassword(password:String):Boolean
		{
			if (password == null || password.length <= 0)
			{
				return false;
			}
 
			return STRONG_PASSWORD_PATTERN.test(password);
		}
 
 
		public function getEncryptionKey(password:String):ByteArray
		{
 
			if (!validateStrongPassword(password))
			{
				throw new ArgumentError("The password must be a strong password. It must be 8-32 characters long. It must contain at least one uppercase letter, at least one lowercase letter, and at least one number or symbol.");
			}
 
			var concatenatedPassword:String = concatenatePassword(password);
 
			var bytes:ByteArray = new ByteArray();
			bytes.writeUTF(concatenatedPassword);
 
			var hashedKey:String = SHA256.computeDigest(bytes);
 
			trace(hashedKey);
 
			var encryptionKey:ByteArray = generateEncryptionKey(hashedKey);
 
			return encryptionKey;
		}
 
 
		// ------- Creating encryption key -------
 
		private function concatenatePassword(pwd:String):String
		{
			var len:int = pwd.length;
			var targetLength:int = 32;
 
			if (len == targetLength)
			{
				return pwd;
			}
 
			var repetitions:int = Math.floor(targetLength / len);
			var excess:int = targetLength % len;
 
			var result:String = "";
 
			for (var i:uint = 0; i < repetitions; i++)
			{
				result += pwd;
			}
 
			result += pwd.substr(0, excess);
 
			return result;
		}
 
 
 
		private function generateEncryptionKey(hash:String):ByteArray
		{
			var result:ByteArray = new ByteArray();
 
			// select a range of 128 bits (32 hex characters) from the hash
			// In this case, we'll use the bits starting from position 17
			for (var i:uint = 0; i < 32; i += 2)
			{
				var position:uint = i + 17;
				var hex:String = hash.substr(position, 2);
				var byte:int = parseInt(hex, 16);
				result.writeByte(byte);
			}
 
			return result;
		}
	}
}

Please note that, while functionnal, I consider this a first draft. I know that it could be much more secure, but encryption is definitely not my area of exertise. Advices are welcome ! ;)

So, Lita now lets you open db encrypted either by itself or other applications using the EncryptionKeyGenerator, my own SimpleEncryptionKeyGenerator, or any equivalent mechanism. To do so, you just have to provide the hashed key resulting from the SHA256.computeDigest() method call. This means that you should be the developer of the original app that created the encrypted database, and should therefore be able to trace this string:

var hashedKey:String = SHA256.computeDigest(unhashedKey);			
trace(hashedKey);

Of course, the generateEncryptionKey() method is the same as in Paul's class, to ensure that Lita can open both types of key with a single mechanism. You too should use the same method if you want your db to be opened by Lita.

Summary

If you want to create a db with the best security possible, use the EncryptionKeyGenerator. This will prevent your db from being opened from other apps than the one which has created it, even with the correct password.

If you just need password based encryption, and want people to be able to open it from other apps (or sessions) if they have the correct password, you can use my SimpleEncryptionKeyGenerator.

In any case, if you need your db to be opened by Lita, you have to get the hash through the hashedKey:String variable. I imagine other 3rd party apps will use similar behavior, eventually.

Now, these two classes can be considered as two opposites in the range of db security. You may very well want to write your own class to adapt it to your needs.

If you want to write your own key generation mechanism and still want to be possible to open it with Lita, the only requirement is that the final key has to be generated from the hash using the following method.

	private function generateEncryptionKey(hash:String):ByteArray
	{
 
		var result:ByteArray = new ByteArray();
 
		// select a range of 128 bits (32 hex characters) from the hash
		// In this case, we'll use the bits starting from position 17
		for (var i:uint = 0; i < 32; i += 2)
		{
			var position:uint = i + 17;
			var hex:String = hash.substr(position, 2);
			var byte:int = parseInt(hex, 16);
			result.writeByte(byte);
		}
 
		return result;
	}

I hope this can be useful to others, even non-Lita users.

As always, please feel free to correct me and give me your thoughts on this (pretty complex) subject.

AttachmentSize
SimpleEncryptionKeyGenerator.as3.05 KB

Arun Ganesh (not verified) on May 17th 2010

Hi,

I found one issue by using this algorithm. This algorithm always return same hash key 'eb142b0cae0baa72a767ebc0823d1be94e14c5bfc52d8e417fc4302fceb6240c', whatever password i gave. Please have a look on this and give me feasible solution.

 

Thanks and Regards,

Arun Ganesh. P

david_deraedt on June 14th 2010

Hi,

Absolutely. This issue was adressed in the next release. I should probably update this post too, thanks for the heads up ! ;)

Amit (not verified) on July 23rd 2010

Hello

You can tell me when you will update this issue in next l release or

give me some idea ,, what is the change in this algorithm :(

 

Amit (not verified) on July 23rd 2010

You can tell me where you will update this issue ,, or tell me what is the change in this algorithm

Amit (not verified) on July 23rd 2010

I am using EncryptionKeyGenerator class to generate the encrypted db ,, but when i opne this into Lita then using this class generated db is not opened ,, :((

david_deraedt on July 26th 2010

Are you sure you are using the version of the class which is embedded with the software (copied inside the preferences folder) ?