AIR 1.5 encrypted SQLite database — how to use it, best practices, and new projects

Posted November 18, 2008 1:30 am
Filed under: AIR, Articles by Paul, Life at Adobe, Projects, Tutorials, Writing, local SQL database

Over a year ago I described a potential area of concern for using a SQLite database with an AIR application — because all apps use the same database engine, any AIR app can read any other app’s database (as long as it can find the database file).

As you may have heard among all the news that’s coming out from MAX right now, Adobe AIR 1.5 has just been released. Most of the features of AIR 1.5 are features that were introduced with Flash Player 10. However, one new feature in AIR 1.5 that helps address the easy-to-read database issue is AIR 1.5’s new support for AES encrypted databases.

I’ve had the interesting and at times complex job of writing the documentation for this new feature. The bulk of the new documentation is in a new section “Using encryption with SQL databases” in the SQL database chapter of the manual “Developing Adobe AIR Applications”.

It’s pretty straightforward to use an encrypted database. You have to create the database as an encrypted db. To create or open it, you call the SQLConnection class’s open() or openAsync() methods, just as you normally do, and there’s a new parameter where you provide a 16-byte ByteArray encryption key for the database. That’s all there is to it.

The SQLConnection class also has a new reencrypt() method, that you use to change the encryption key of an already encrypted database.

If you want to see some code examples, check out the documentation listed above, or see these quick start articles on the Adobe developer center:

There are a couple of parts of the new documentation that I think are particularly interesting (although I’m obviously biased since I wrote it all):

Now that AIR 1.5 is out the door, I’ve updated my AIR database admin tool to support encrypted databases (when you try to open an encrypted database it prompts you for an encryption key, which you enter as a Base-64 string). I already mentioned that I wrote the encryption key generator class that’s now in as3corelib. I’ve also got at least one more new encrypted database-related open-source project that I’m about to release…but I’ll wait until the MAX “firehose” dies down a bit before I write more about that one. =)

You can leave a comment, or trackback from your own site.

13 Comments so far

  1. November 24th, 2008 at 3:33 am
    davidderaedt is reported to have said:

    Hi Paul,

    Did you mean that your encryption class will be included in future releases of the core lib? Because, as far as I know, build .92.1 (latest build to date) does not include it.

  2. November 24th, 2008 at 10:00 am
    Paul Robertson is reported to have said:

    Hi David,
    Sorry about the confusion. You’re right that the current download .zip (.92.1) doesn’t include the encryption key generator class. The .zip hasn’t been updated yet with the newest changes. (All the project owners were at MAX last week when I posted the new content.)

    I’ll ping them to see if any of them can rebuild the .zip file. In the mean time, the class is available in the source, if you browse on the project web site or check it out via SVN.

    Paul

  3. December 3rd, 2008 at 2:18 pm
    JJ is reported to have said:

    Really good info, Paul. It gave me a good background for something that we need in an application we’re currently writing.

    I’m wondering if you can comment on a slightly different need that we have. We’re using the encrypted capabilities of AIR/SQLite to hold proprietary data from our server for offline use by our customers. That’s one of the huge advantages to AIR/encrypted SQLite - we can now give the user the option of running online and connected to our server, or offline (with our data on their desktop - allowing for synchonization issues, etc).

    Anyway, the encryption key that we need to use can’t be based on a user’s password because we only want them having access to the database through our AIR application - even if the key is further encrypted. What would stop someone from decompiling the SWF to uncover the mechanism or our own stored password? How can we protect our data that is local in the database?

    If I didn’t describe this well, please let me know and I’ll come up with another example of the problem.

  4. December 4th, 2008 at 6:21 am
    JJ is reported to have said:

    After doing a lot of research about this issue and sleeping on it, I’m coming to the conclusion that it is almost impossible to completely hide a password (even with the encrypted file store class). Decompiling seems to be always able to unravel the code and figure out the encoding. There is that very strong mode of file store encryption that is tied to the application that created it, but then you can’t update your AIR app because the decryption will fail.

    Am I missing something? Is the only way around all of this to encrypt the SWF with one of the commercial encrypters? Even those can be gotten around but I can make that pretty difficult to uncover.

    Are there any other ideas for really protecting the assets in a database?

  5. December 11th, 2008 at 7:06 am
    Ashish Mahajan is reported to have said:

    I have two issues I am still facing
    (A) What tool will I be able to manage structure and content of a encrypted database?
    (B) I am getting error from openSync method of SQLConnection that it only supports five arguments?

  6. January 15th, 2009 at 11:47 pm
    Abhishek Sawant is reported to have said:

    hello sir
    i want to use SQLite in the situation in which data wont be on any server it would be stored on cd and there wont be any server on client side as client would be accessing data directly thr cd
    so in this scenario it will be beneficial to use AIR sqlite or i use sqlite3 and how should i use thr javascript pls reply its urgent.

  7. January 16th, 2009 at 12:00 pm
    Paul Robertson is reported to have said:

    Hi Abhishek,
    Usually a server database includes two parts (both of which live on the server): the database engine, which processes all SQL statements etc., and the actual data storage on disk. SQLite also uses these two parts (database engine and data), but they are more explicitly separate. The database engine is designed as a library that can be included in various applications. The actual data is stored in files on the filesystem, wherever you choose to place those files. They are often on a local disk such as a hard drive. Theoretically they could be on a network drive that’s mounted like a local drive, although that’s not recommended for performance and stability reasons.

    In the case of AIR, the AIR runtime includes the SQLite engine. Within your AIR application you can choose a location for the database file, and the data is stored there. So in general if you want an app to access a local data store rather than a server data store, SQLite is a good choice.

    You could theoretically have a database file precreated, with the data prepopulated, and burn that database file onto a CD. You could then access the database file on the CD from your AIR application. However, the potential complications I see are:

    - An AIR application can’t be run from a CD — it must be installed on the user’s machine. So even though the data would be on the CD, the application would have to be installed. Because of that, it seems somewhat pointless to keep the data on the CD rather than the local machine, unless you have another reason for doing so. (The only thing I can think of is you could use it as a weak form of copy protection — i.e. the user would have to have the CD in order to run the app).

    - The AIR application would have to figure out the drive name of the CD drive, so that it could find the database file, since drive name mappings can vary between machines, and even on the same machine at different times

    - The database file would have to be opened in read-only mode. Usually the database engine writes a temporary file in the same directory as the database file, and obviously that wouldn’t work for a CD. I’m guessing that if you open the database in read-only mode, then the database wouldn’t attempt to create that temporary file, but I could be wrong in which case it probably wouldn’t work at all.

    As far as how to use the AIR SQLite functionality in a JavaScript-based AIR application, you’ll probably want to start here:
    Working asynchronously with a local SQL database
    Working with local SQL databases

  8. January 22nd, 2009 at 2:36 pm
    George is reported to have said:

    Hi Paul,

    I’m using your key generator class only modify salt, I tried to open the database with your Doppler, paste the hashed key, but nothing happened. Did I miss something?

    var hashedKey:String = SHA256.hashBytes(unhashedKey);
    trace(‘key:’+ hashedKey);
    var encryptionKey:ByteArray = generateEncryptionKey(hashedKey);

    Thanks for your great work,
    George

  9. March 27th, 2009 at 8:18 am
    Lisa B is reported to have said:

    I’m interested in distributing a prepopulated SQLite database with my application (and actually creating it on the fly via ColdFusion and the SQLite ODBC drivers). Do you have any recommendations for tools to create encrypted databases (outside of your AIR code) that an AIR application can read?
    Thanks for your help, tools and articles. They have definitely been helpful in the past (and I’m sure in the future).

    Lisa

  10. March 27th, 2009 at 8:37 am
    Lisa B is reported to have said:

    I should always read the most recent posts prior to commenting. I just found your work on Lita and this does exactly what I need. A good tool too. Thanks!

  11. March 1st, 2010 at 9:14 pm
    Jason is reported to have said:

    I have reviewed the documentation for the EncryptionKeyGenerator class on the Adobe site. Would you agree that there is no way to open an encrypted database on a computer other than the one on which the database was created? It seems that since the salt is stored in the ELS, the database is married to the machine. My application utilizes a sqlite database that I want encrypted but which can be moved freely to and from any machine and opened by any machine that has my application installed. I would expect a password to be provided every time the application opens the database.

    It seems that if one uses the EncryptionKeyGenerator to encrypt a database and then the hard drive crashes, that database is useless even if it is backed up because the ELS will be lost.

  12. July 23rd, 2010 at 6:52 am
    Amit is reported to have said:

    How can i open my encrypted database into Lita or any other tool , like sqlite expert professional
    i can’t able to open ,, i am using the EncryptionKeyGenerator Class for that Any helo would be fine foe me :((

  13. July 23rd, 2010 at 6:18 pm
    Paul is reported to have said:

    Hi Amit,
    If you’ve encrypted the database using an encryption key created with the EncryptionKeyGenerator class, you can only open it if you know the encryption key that was used to encrypt it. For Lita (and for Run!) you need to enter the Base64-encoded encryption key when you’re opening the database. (So you’ll need to not only know the encryption key, but you’ll have to Base64-encode it.) If you didn’t capture the encryption key when it was being created, you’ll have to capture it (when it’s re-created by your app using the EncryptionKeyGenerator class). To do that you’ll need to add some code in your app to take the generated encryption key, Base64-encode it (for example, using the Base64Encoder class in Flex), then print it using trace() or capture it in some way like that.

Add your comment





Comment notes

Please keep comments on topic. Comments that are inappropriate or offensive will be edited or removed.

Paragraphs and line breaks are automatically converted to HTML, and quotation marks are converted to “smart” quotes.

The following XHTML tags can be used: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> . All others will be removed.

Articles by Type

Articles by Topic

Random Reading

Currently...

Subscribe