Upgrading to SQLCipher 4

The recent release of SQLCipher 4 introduces many new performance and security enhancements for applications that use SQLCipher for secure local data storage. However, the introduction of new default algorithms, increased KDF iterations and a larger page size means that SQLCipher 4 will not open older databases by default.

This document provides guidance on the upgrade options available to applications that have previously integrated SQLCipher versions 1 through 3.

Option 1: Database File Migration

SQLCipher provides a very convenient way to perform an ā€œin placeā€ migration of a SQLCipher database using PRAGMA cipher_migrate. This does all the work of updating the database file format with a single SQL statement. After migration the database will use all of the latest default settings so an application can immediately benefit from improved performance and security.

PRAGMA cipher_migrate be run a single time immediately after the key is provided (i.e. via sqlite3_key() or PRAGMA key in order to upgrade the database. This would normally occur on the first run after the application is upgraded to perform a one-time conversation.

After the migration is complete the application will no longer need to call the command again on subsequent opens.

PRAGMA key = '<key material>';
PRAGMA cipher_migrate;

The PRAGMA will return a single row with the value 0 after successful completion of the migration process. A non-zero column value will be returned in the event of a migration failure. On success the migrated database will remain open and use the same filename.

Important: The cipher_migrate PRAGMA is potentially expensive because it needs to attempt to open the database for each version to determine the appropriate settings. Therefore an application should NOT call the PRAGMA every time a database is opened. Instead, an application should use the recommended process in the cipher_migrate API documentation

Note: SQLCipher for Android Java users: when opening a database connection to run PRAGMA cipher_migrate, you must include the SQLITE_OPEN_CREATE flag as the migration process will temporarily attach a new database during the migration process.

Option 2: Backwards Compatibility

The second option is to use the new SQLCipher 4 library, but use all of the SQLCipher 3 (or earlier) settings. This requires an application to execute PRAGMA statements immediately after keying the database that will match the settings originally used to create the database.

Starting with SQLCipher 4.0.1, you can use the new cipher_compatibility feature. Passing values 1, 2, or 3 to the PRAGMA will cause SQLCipher to operate with default settings consistent with the respective major version number for the current connection. For example, the following will cause SQLCipher to treat the current database as a SQLCipher 3.x database:

PRAGMA cipher_compatibility = 3;

It is also possible to use the similar cipher_default compatibility PRAGMA to set the value for the lifetime of a process before key operations are invoked.

Applications are also free to explicitly manage the low-level page size, KDF, and algorithm settings. This option is more verbose, but works effectively the same way. The appropriate settings vary by the SQLCipher version used previously:

SQLCipher 3

PRAGMA cipher_page_size = 1024;
PRAGMA kdf_iter = 64000;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;

SQLCipher 2

PRAGMA cipher_page_size = 1024;
PRAGMA kdf_iter = 4000;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;

SQLCipher 1

PRAGMA cipher_page_size = 1024;
PRAGMA kdf_iter = 4000;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
PRAGMA cipher_use_hmac = OFF;

When using legacy settings, the library will be operating in a compatibility mode with a previous release. Thus, an application will not be taking advantage of all the security improvements available in the new version. Instead the application will function with the same level of security as the original SQLCipher version.

It is important to note that since no migration is occurring, compatibility statements must be executed every time a database is opened. It is, however, possible to set process-level defaults using the cipher_default_ versions of these PRAGMAs (e.g. PRAGMA cipher_default_kdf_algorithm). This will change the default settings for the lifetime of the process. Please refer to the SQLCipher API for a full list of available settings.

Option 3: Custom Export Migration

When an application uses a custom configuration or non-default settings it is possible to use the sqlcipher_export() convenience function for fine-grained control over the migration process. The general procedure for using sqlcipher_export() follows.

  1. Open a connection to the existing database and set the appropriate backward compatibility PRAGMAs as described in Option 2.
  2. Attach a new encrypted database, which will use the new settings by default.
  3. Set any custom PRAGMAs for the new attached database (optional).
  4. Call sqlcipher_export() to ā€œcopyā€ the data from the main database to the new attached database.
  5. After export, detach the new database, and close the main database connection.
  6. Re-open the new database, optionally deleting the original database and/or renaming the new database as appropriate.

The following example demonstrates the statements required to migrate a SQLCipher 3 database using sqlcipher_export(). At the end of the process the migrated database will be named sqlcipher-4.db.

PRAGMA key = '<key material>';
PRAGMA cipher_page_size = 1024;
PRAGMA kdf_iter = 64000;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
ATTACH DATABASE 'sqlcipher-4.db' AS sqlcipher4 KEY '<key material>';
SELECT sqlcipher_export('sqlcipher4');
DETACH DATABASE sqlcipher4;

Choosing the Right Approach
Applications are free to select an upgrade approach that most closely meets their requirements. However, the recommended approach is to use Option 1 to take advantage of new features while minimizing the complexity of the application code required for migration.

3 Likes

What ciphers are supported in SQLCipher 4? 3 at some point supported aes-256-cbc, but it seems to me that 4 uses aes-256-cfb? Or am I mistaken?

If so, how does one read an old database with aes-256-cbc, since the ā€œcipherā€ PRAGMA is no longer available?

@Dan

Thanks for using SQLCipher.

aes-256-cbc is still the cipher used by SQLCipher 4, which is referenced on the design page under Security Features. As you mentioned PRAGMA cipher is now fully deprecated and will be a no-op if you attempt to call it with a different cipher.

but it seems to me that 4 uses aes-256-cfb?

Where are you seeing that?

specifically in Android, at which point do we have to call

because by the moment I call getWritableDatabase(password) it crashes.

Hi @Gerardo_Robledo

The SQLiteOpenHelper allows you to provide a SQLiteDatabaseHook via this constructor which will allow you to run the PRAGMA cipher_migrate command.

Got it.
I used the postKey method to call PRAGMA cipher_migrate command.

Thanks

Hey @Gerardo_Robledo

could you share your solution? My current approach looks like this:

super(context, DATABASE_NAME, null, DATABASE_VERSION, new SQLiteDatabaseHook() {
        @Override
        public void postKey(SQLiteDatabase database) {
            database.rawQuery("PRAGMA key = `" + THE_SECRET_PASSWORD + "`; PRAGMA cipher_migrate;", null).close();
        }

        @Override
        public void preKey(SQLiteDatabase database) {

        }
    });

Nevertheless I get the same error as before:

net.sqlcipher.database.SQLiteException: file is not a database: , while compiling: select count(*) from sqlite_master;

Thanks for your help!

Hi @ifi_tubaf

You donā€™t need to execute PRAGMA key in the postKey event, the database will be keyed when you call getWritableDatabase(...). Here is an example of applying the PRAGMA cipher_migrate command and checking the result.

1 Like

Hi @ifi_tubaf
Iā€™m using Kotlin so it looks something like this:

     val hook = object: SQLiteDatabaseHook {
            override fun preKey(database: SQLiteDatabase?) {

            }

            override fun postKey(database: SQLiteDatabase?) {
                    database?.rawExecSQL("PRAGMA cipher_migrate")            
            }
        }

but I would recommend to check the example that @developernotes mentioned.

1 Like

Thanks a lot guys, got it working now! :slightly_smiling_face:

Whatā€™s the most elegant way to make sure in an Android app that ā€œPRAGMA cipher_migrateā€ is only executed when necessary?

I could save a flag to SharedPreferences to make sure that itā€™s executed exactly once, but that will do the migration even on a fresh install when itā€™s not necessary, and it will complicate things if we ever need to run the migration again, e.g. when we upgrade to SQLCipher 5. Neither of these are a huge deal but Iā€™d prefer to explicitly determine whether a migration is indeed necessaryā€¦

Alternatively, what are the drawbacks of executing the migration every single time the database is opened?

I tried this on android but its not working on app upgrade, on app upgrade itā€™s giving ā€œfile not databaseā€ error message, any idea why I am getting this?

init {
        val hook = object : SQLiteDatabaseHook {
            override fun preKey(database: SQLiteDatabase?) {

            }

            override fun postKey(database: SQLiteDatabase?) {
                database?.rawExecSQL("PRAGMA cipher_migrate")
            }
        }
        sqliteOpenHelper = object : SQLiteOpenHelper(context, name, null, version, hook) {
            override fun onCreate(db: SQLiteDatabase) {
                this@SqliteHelperWrapper.onCreate(getDbWrapper(db))
            }

            override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
                this@SqliteHelperWrapper.onUpgrade(getDbWrapper(db), oldVersion, newVersion)
            }
        }
    }

Hello @zpapp - one fairly standard way to do this follows:

  1. Attempt to open the database using standard settings (i.e. whatever the defaults are for the current version of SQLCipher for the app (in this case SQLCipher 4).

  2. If the database canā€™t be opened using the key and the default settings, try to open it and run PRAGMA cipher_migrate on it (e.g. with postKey in the case of android). This would then attempt to upgrade the database. If the migration succeeds, you can continue to use that connection for the remainder of the application lifecycle. If the key is incorrect here, then migration will not occur and the database will remain untouched.

  3. If step 1 and step 2 fail, then the key material is incorrect or the settings of the database were not consistent with defaults for previous SQLCipher verions (i.e. custom settings were used that require manual migration)

This approach has the benefit of performing optimimally in the standard case when the database has already been migrated. It has a slowdown in the event that the key material is incorrect because the key may be derived multiple times to attempt migration, but that usally acceptable in most cases.

In the event that incorrect keys are a common situation, and thus the performance hit for rechecking in step 2 is not acceptable, then the other approaches you mentioned are more suitable, i.e. statefully tracking the current version of the database in an application preference.

2 Likes

Thank you for this explanation!

I noticed that when I try to open an old database without cipher_migrate, even if I catch the resulting SQLiteException, the Android log shows this exception along with a stack trace. The app doesnā€™t crash when this happens, so itā€™s more of a cosmetic issue, but still I wanted to check with you if this is expected. Does the library do this intentionally? Could this be changed?

And on a slightly different note: does the time it takes to execute cipher_migrate depend on the size of the database? Is it safe to do this migration on the main thread, or am I risking an ANR?

Hello @anks

It is difficult to say why it is failing given your code example. A few things to consider:

  • Verify the password is correct
  • Can you open it via a SQLCipher command line shell or GUI management interface?
  • Check the return code from PRAGMA cipher_migrate, an example is here.
  • Does you application use any non-default configuration settings for SQLCipher?
  • What version of SQLCipher was used to create the database file you are attempting to open?

Hello @zpapp,

SQLCipher for Android will throw an exception when it is unable to access the database with the provided password material, this is by design. The migration processing time will differ depending on the size of the database itself, it would be a good idea to perform that operation on a non-UI thread to prevent any blocking that may occur.

Thanks @developernotes for your suggestion, We found an issue, It looks like somehow on app upgrade password gets changed and thats causing an issue.

Yes, but why is the stack trace of this exception appear in Logcat even if I catch the exception?

Hi @zpapp

SQLCipher for Android was recently changed to limit logging of that exception to debug builds only. This will be included in our next public release.

1 Like