Upgrading to SQLCipher 4

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.

1 Like

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

Hello @sjlombardo. Can you please clarify how to make migration when custom SQLCipher settings were used. Specifically, my application uses PRAGMA cipher_page_size=4096 and PRAGMA page_size=4096. Can I use PRAGMA cipher_migrate or have to fall down to sqlcipher_export?

Hi @diejmon

No, you cannot use PRAGMA cipher_migrate when non-default configurations were used. You can however use sqlcipher_export(...). For your situation, please review example #3 on the documentation here. In your case, the difference will be the cipher_page_size being different from the example.

1 Like

I found that PRAGMA cipher_migrate only upgrades the database file format, all data are lost, did I make something wrong?

Hi @missdeer

In my case, after exec “PRAGMA cipher_migrate”, database data are kept(i.e. no data lost).


Maybe you should share more details which can help sqlcipher team to support your issue.

Thanks

Hi @missdeer

While you cannot use PRAGMA cipher_migrate when the database was previously configured with non-default settings, no data loss should occur. Please refer to these comments here:

1 Like

@developernotes @hwbest
My fault.
I found that PRAGMA cipher_migrate returned 0, but in fact it didn’t migrate successfully, it only can be open and read/write with old SQLCipher 3 settings rather than new SQLCipher 4 settings.

My code:

do {
    rc = sqlite3_open_v2(origPath.c_str(), &m_db, SQLITE_OPEN_READWRITE, nullptr);
    if (rc != SQLITE_OK)
    {
        logErrorS("opening database failed: " << origPath << rc << sqlite3_errmsg(m_db));
        break;
    }
    rc = sqlite3_key_v2(m_db, nullptr, keyReader.c_str(), keyReader.getBufSize());
    if (rc != SQLITE_OK)
    {
        logErrorS("attach encrypt key failed: " << origPath << rc << sqlite3_errmsg(m_db));
        break;
    }
    auto resultPtr = execStatement(SQL_STATEMENT_CIPHER_MIGRATE, DBParamSetPtr(), true);
    if (!resultPtr || !resultPtr->size())
    {
        logErrorS("cipher_migrate doesn't returns result: " << origPath << sqlite3_errmsg(m_db));
        break;
    }
    DBRowPtr row = (*resultPtr)[0];
    DBColumnPtr column = (*row)["cipher_migrate"];
    if (!column)
    {
        logErrorS("cipher_migrate doesn't returns cipher_migrate field: " << origPath << sqlite3_errmsg(m_db));
        break;
    }
    int res = column->getInt32Value();
    if (res)
    {
        logErrorS("cipher_migrate failed: " << origPath << sqlite3_errmsg(m_db));
        break;
    }
    openFlag = true;
} while (0);

Hi @sjlombardo @developernotes ,
I am planing to take option 2 to be backward compatible. I would like to know is there any security vulnerabilities or performance implication with that approach.
I would like to take this option by checking version of DB by using 'PRAGMA user_version;" which returns 0 because there is no version set by creator of DB or select sqlite_version(); which returns 3 as major version. In any of these cases, will set PRAGMA cipher_compatibility = 3; and use it otherwise will use directly. Please let me know your views on this.

Hi @krishnakukmartm - There aren’t any specific vulnerabilities involved with using option 2 for backward compatibility. Note however that using backwards compatibility is less secure, due to the security enhancements in version 4 that would not be in place.

The approach you have described with user_version and sqlite_version won’t really work. Querying the user_version would require the database to be decrypted, and the compatibility flag would have to be set before decryption. With sqlite_version, it returns the version of the library being used, not the version of the database. One recommended approach for determining the database version is described earlier in this thread:

This example refers to cipher_migrate, but the same approach could conceivably be used for compatibility.