Getting back on track with projects I have already started, I want to finish up this series of posts of using SQLite databases with Adobe AIR. This last section will be on using Encrypted SQLite Databases.
Why use Encrypted Databases?There will likely come a time in your career when you need to deal with sensitive data. When dealing with AIR applications, it may come sooner than you think. With AIR applications, if you need to persist data for use offline, one of the better options to do so is inside of a SQLite database. However, if you do this without any encryption, then the file is (obviously) stored in a clear-text way, meaning that it can be read by anyone who had access to the machine, including other applications like Trojan horses or other AIR applications written by malicious users.
Since we want to protect our users' data from prying eyes, we will want to, in some cases, encrypt the database so that it cannot be read by other applications or users who have compromised the file. Adobe AIR makes this quite easy.
Encryption ConsiderationsThere are several things to consider when encrypting the local SQLite database for an AIR application.
- Do I want one DB for each user or do the users need to share the same DB?
- If I create a DB based-on a user password, and the user forgets the password, is that catastrophic?
- If I need to store the encryption key for offline use, where should I store it?
One DB for Each User or One shared DB?One of the options we have for the SQLite database is to make a different database for each user of the application. This is accomplished through a couple of mechanisms. First, the database file itself can be stored in the user's application storage directory, which is in the user's profile. This is nice because typically the user has permissions to her own profile and it is then separated from other user profiles.
But this may not be enough. If we've decided that we also need to encrypt the data then we still need to encrypt the database. Since we are creating a DB for each user, we can use an encryption key based on a user-selected password. We'll go into more detail on how to do that in a future post. This means that not only is the file separated from other users on the machine, but it is also protected from other applications or other users who do not have the password.
The other option we have for storing data in an encrypted DB is to have a shared DB. Of course, with a shared DB, we would need a shared key. This is less secure than the previous option, but it may still be secure enough for your needs, and if this is what you need the application to do, then it's what you need the application to do.
To accomplish this, first you would need a central location to store the DB file that all users have read/write access to, perhaps in shared files or if this is for a limited install and you have admin access you can create your own place for it. Second, you will need a shared key. You can accomplish this by either having a shared password (not recommended), or by having the AIR application request a key from a secure server the first time it runs. The AIR application can then store the password in its encrypted local store for later use.
Of course, I strongly recommend you consider all of the security ramifications for either of these options. This is a very high level description of these topics. I will go into some more detail on this in a future post.
What if my user forgets her password?Simply put, the user is hosed. But of course, this is nothing unique to AIR. Whenever you are dealing with encrypted databases or files, if the key is lost, you are buggered. Now, there are a few ways to protect yourself from this happening.
- Don't forget the password (Yeah, right!)
- When the user enters their password the first time, require that they be connected to the internet or local network of your organization. Send the password they enter (over SSL) and store it securely in a central repository. Then, when the user forgets the password, you can either send them the password (not the best choice), or have them choose a new password and then programatically re-encrypt the database using the new password and the stored password. I will have a future post on how to do this.
- Using a similar method from above, instead of storing the password in a central location, store a Base64 encoded version of the binary encryption key. This is what I do and it serves me in 2 ways. One, it allows me to look up the key when I need to and open the DB file in a DB manager called Run! and second, it is what I need to re-encrypt the DB, so it makes that a little easier.
Of course, with ANY of the methods that store your users' credentials, you need to let your users know that you are doing that. That can affect their choice of password strategy and it is your responsibility to let the user know what information of theirs you are storing.
ConclusionI already answered the last question of "If I need to store the encryption key for offline use, where should I store it?" And that would be in the encrypted local store (ELS).
In my next few posts I will outline ways of obtaining a strong encryption key from a user password, and how to use that key to encrypt and decrypt a database. We'll also look at how to re-encrypt a database from one password to another.