Using encryption with SQL databases

Encryption is useful any time you want to restrict access to the information stored in a database. The database encryption functionality of Adobe AIR can be used for several purposes. The following are some examples of cases where you would want to use an encrypted database:

• A read-only cache of private application data downloaded from a server

• A local application store for private data that is synchronized with a server (data is sent to and loaded from the server)

• Encrypted files used as the file format for documents created and edited by the application. The files could be private to one user, or could be designed to be shared among all users of the application.

• Any other use of a local data store, such as the ones described in Uses for local SQL databases , where the data must be kept private from people who have access to the machine or the database files.

Understanding the reason why you want to use an encrypted database helps you decide how to architect your application. In particular, it can affect how your application creates, obtains, and stores the encryption key for the database. For more information about these considerations, see Considerations for using encryption with a database .

Other than an encrypted database, an alternative mechanism for keeping sensitive data private is the encrypted local store . With the encrypted local store, you store a single ByteArray value using a String key. Only the AIR application that stores the value can access it, and only on the computer on which it is stored. With the encrypted local store, it isn’t necessary to create your own encryption key. For these reasons, the encrypted local store is most suitable for easily storing a single value or set of values that can easily be encoded in a ByteArray. An encrypted database is most suitable for larger data sets where structured data storage and querying are desirable. For more information about using the encrypted local store, see Encrypted local storage .

To use an encrypted database, the database file must be encrypted when it is created. Once a database is created as unencrypted, it can’t be encrypted later. Likewise, an encrypted database can’t be unencrypted later. If needed you can change the encryption key of an encrypted database. For details, see Changing the encryption key of a database . If you have an existing database that’s not encrypted and you want to use database encryption, you can create a new encrypted database and copy the existing table structure and data to the new database.

Creating an encrypted database is nearly identical to creating an unencrypted database, as described in Creating a database . You first create a SQLConnection instance that represents the connection to the database. You create the database by calling the SQLConnection object’s open() method or openAsync() method, specifying for the database location a file that doesn’t exist yet. The only difference when creating an encrypted database is that you provide a value for the encryptionKey parameter (the open() method’s fifth parameter and the openAsync() method’s sixth parameter).

A valid encryptionKey parameter value is a ByteArray object containing exactly 16 bytes.

The following examples demonstrate creating an encrypted database. For simplicity, in these examples the encryption key is hard-coded in the application code. However, this technique is strongly discouraged because it is not secure.

var conn = new air.SQLConnection(); 
     
var encryptionKey = new air.ByteArray(); 
encryptionKey.writeUTFBytes("Some16ByteString"); // This technique is not secure! 
     
// Create an encrypted database in asynchronous mode 
conn.openAsync(dbFile, air.SQLMode.CREATE, null, false, 1024, encryptionKey); 
     
// Create an encrypted database in synchronous mode 
conn.open(dbFile, air.SQLMode.CREATE, false, 1024, encryptionKey);

For an example demonstrating a recommended way to generate an encryption key, see Example: Generating and using an encryption key .

Like creating an encrypted database, the procedure for opening a connection to an encrypted database is like connecting to an unencrypted database. That procedure is described in greater detail in Connecting to a database . You use the open() method to open a connection in synchronous execution mode , or the openAsync() method to open a connection in asynchronous execution mode . The only difference is that to open an encrypted database, you specify the correct value for the encryptionKey parameter (the open() method’s fifth parameter and the openAsync() method’s sixth parameter).

If the encryption key that’s provided is not correct, an error occurs. For the open() method, a SQLError exception is thrown. For the openAsync() method, the SQLConnection object dispatches a SQLErrorEvent , whose error property contains a SQLError object. In either case, the SQLError object generated by the exception has the errorID property value 3138. That error ID corresponds to the error message “File opened is not a database file.”

The following example demonstrates opening an encrypted database in asynchronous execution mode. For simplicity, in this example the encryption key is hard-coded in the application code. However, this technique is strongly discouraged because it is not secure.

// Include AIRAliases.js to use air.* shortcuts 
var conn = new air.SQLConnection(); 
conn.addEventListener(air.SQLEvent.OPEN, openHandler); 
conn.addEventListener(air.SQLErrorEvent.ERROR, errorHandler); 
var dbFile = air.File.applicationStorageDirectory.resolvePath("DBSample.db"); 
     
var encryptionKey = new air.ByteArray(); 
encryptionKey.writeUTFBytes("Some16ByteString"); // This technique is not secure! 
     
conn.openAsync(dbFile, air.SQLMode.UPDATE, null, false, 1024, encryptionKey); 
     
function openHandler(event) 
{ 
    air.trace("the database opened successfully"); 
} 
     
function errorHandler(event) 
{ 
    if (event.error.errorID == 3138) 
    { 
        air.trace("Incorrect encryption key"); 
    } 
    else 
    { 
        air.trace("Error message:", event.error.message); 
        air.trace("Details:", event.error.details); 
    } 
}

The following example demonstrates opening an encrypted database in synchronous execution mode. For simplicity, in this example the encryption key is hard-coded in the application code. However, this technique is strongly discouraged because it is not secure.

// Include AIRAliases.js to use air.* shortcuts 
var conn = new air.SQLConnection(); 
var dbFile = air.File.applicationStorageDirectory.resolvePath("DBSample.db"); 
     
var encryptionKey = new air.ByteArray(); 
encryptionKey.writeUTFBytes("Some16ByteString"); // This technique is not secure! 
     
try 
{ 
    conn.open(dbFile, air.SQLMode.UPDATE, false, 1024, encryptionKey); 
    air.trace("the database was created successfully"); 
} 
catch (error) 
{ 
    if (error.errorID == 3138) 
    { 
        air.trace("Incorrect encryption key"); 
    } 
    else 
    { 
        air.trace("Error message:", error.message); 
        air.trace("Details:", error.details); 
    } 
}

For an example demonstrating a recommended way to generate an encryption key, see Example: Generating and using an encryption key .

When a database is encrypted, you can change the encryption key for the database at a later time. To change a database’s encryption key, first open a connection to the database by creating a SQLConnection instance and calling its open() or openAsync() method. Once the database is connected, call the reencrypt() method, passing the new encryption key as an argument.

Like most database operations, the reencrypt() method’s behavior varies depending on whether the database connection uses synchronous or asynchronous execution mode. If you use the open() method to connect to the database, the reencrypt() operation runs synchronously. When the operation finishes, execution continues with the next line of code:

var newKey = new air.ByteArray(); 
// ... generate the new key and store it in newKey 
conn.reencrypt(newKey);

On the other hand, if the database connection is opened using the openAsync() method, the reencrypt() operation is asynchronous. Calling reencrypt() begins the reencryption process. When the operation completes, the SQLConnection object dispatches a reencrypt event. You use an event listener to determine when the reencryption finishes:

var newKey = new air.ByteArray(); 
// ... generate the new key and store it in newKey 
     
conn.addEventListener(air.SQLEvent.REENCRYPT, reencryptHandler); 
     
conn.reencrypt(newKey); 
     
function reencryptHandler(event) 
{ 
    // save the fact that the key changed 
}

The reencrypt() operation runs in its own transaction. If the operation is interrupted or fails (for example, if the application is closed before the operation finishes) the transaction is rolled back. In that case, the original encryption key is still the encryption key for the database.

The reencrypt() method can’t be used to remove encryption from a database. Passing a null value or encryption key that’s not a 16-byte ByteArray to the reencrypt() method results in an error.

The section Uses for an encrypted database presents several cases in which you would want to use an encrypted database. It’s obvious that the usage scenarios of different applications (including these and other scenarios) have different privacy requirements. The way you architect the use of encryption in your application plays an important part in controlling how private a database’s data is. For example, if you are using an encrypted database to keep personal data private, even from other users of the same machine, then each user’s database needs its own encryption key. For the greatest security, your application can generate the key from a user-entered password. Basing the encryption key on a password ensures that even if another person is able to impersonate the user’s account on the machine, the data still can’t be accessed. On the other end of the privacy spectrum, suppose you want a database file to be readable by any user of your application but not to other applications. In that case every installed copy of the application needs access to a shared encryption key.

You can design your application, and in particular the technique used to generate the encryption key, according to the level of privacy that you want for your application data. The following list provides design suggestions for various levels of data privacy:

• To make a database accessible to any user who has access to the application on any machine, use a single key that’s available to all instances of the application. For example, the first time an application runs it can download the shared encryption key from a server using a secure protocol such as SSL. It can then save the key in the encrypted local store for future use. As an alternative, encrypt the data per-user on the machine, and synchronize the data with a remote data store such as a server to make the data portable.

• To make a database accessible to a single user on any machine, generate the encryption key from a user secret (such as a password). In particular, do not use any value that’s tied to a particular computer (such as a value stored in the encrypted local store) to generate the key. As an alternative, encrypt the data per-user on the machine, and synchronize the data with a remote data store such as a server to make the data portable.

• To make a database accessible only to a single individual on a single machine, generate the key from a password and a generated salt. For an example of this technique, see Example: Generating and using an encryption key .

The following are additional security considerations that are important to keep in mind when designing an application to use an encrypted database:

• A system is only as secure as its weakest link. If you are using a user-entered password to generate an encryption key, consider imposing minimum length and complexity restrictions on passwords. A short password that uses only basic characters can be guessed quickly.

• The source code of an AIR application is stored on a user’s machine in plain text (for HTML content) or an easily decompilable binary format (for SWF content). Because the source code is accessible, two points to remember are:

  • Never hard-code an encryption key in your source code

  • Always assume that the technique used to generate an encryption key (such as random character generator or a particular hashing algorithm) can be easily worked out by an attacker

• AIR database encryption uses the Advanced Encryption Standard (AES) with Counter with CBC-MAC (CCM) mode. This encryption cipher requires a user-entered key to be combined with a salt value to be secure. For an example of this, see Example: Generating and using an encryption key .

• When you elect to encrypt a database, all disk files used by the database engine in conjunction with that database are encrypted. However, the database engine holds some data temporarily in an in-memory cache to improve read- and write-time performance in transactions. Any memory-resident data is unencrypted. If an attacker is able to access the memory used by an AIR application, for example by using a debugger, the data in a database that is currently open and unencrypted would be available.

This example application demonstrates one technique for generating an encryption key. This application is designed to provide the highest level of privacy and security for users’ data. One important aspect of securing private data is to require the user to enter a password each time the application connects to the database. Consequently, as shown in this example, an application that requires this level of privacy should never directly store the database encryption key.

The application consists of two parts: an ActionScript class that generates an encryption key (the EncryptionKeyGenerator class), and a basic user interface that demonstrates how to use that class. For the complete source code, see Complete example code for generating and using an encryption key .