No such collation sequence: PHONEBOOK during PRAGMA cipher_migrate

#1

Android app running an older version of SQLCipher. Call PRAGMA cipher_migrate inside of SQLiteDatabaseHook to upgrade sqlcipher from v2 to v3.3.1.

Migration failed. The error message shows:
sqlite returned: error code = 1, msg = no such collation sequence: PHONEBOOK
I/Database(27940): sqlite returned: error code = 1, msg = statement aborts at 2: [SELECT sqlcipher_export(‘migrate’);] no such collation sequence: PHONEBOOK

So it looks like the migration doesn’t recognize a collation named “PHONEBOOK”. But on a fresh install of the app with the collation explicitly specified for one of a table column, there is no error, which is weird.

#2

Hello @mrpc_cambodia

Would you be able to provide a reproducible test case in the SQLCipher for Android test suite? We would be glad to take a look further into this. Thanks!

#3

So it happens during the upgrade of SQLCipher from a version less than 3 to v3.3.1. Start with an Android app and create a table:

CREATE TABLE table1 (_id INTEGER PRIMARY KEY AUTOINCREMENT, contact_no TEXT COLLATE PHONEBOOK)

The important thing here is to have PHONEBOOK collation assigned to one of the column. Now update SQLCipher binary, both jar and *.so to v3.3.1. Update constructor of a subclass of SQLiteOpenHelper to include SQLiteDatabaseHook to handle the upgrade.

new SQLiteDatabaseHook() {
            @Override
            public void preKey(SQLiteDatabase sqLiteDatabase) {

            }

            @Override
            public void postKey(SQLiteDatabase sqLiteDatabase) {
                    sqLiteDatabase.rawQuery("PRAGMA cipher_migrate;",
                            new String[]{});
             }
}

Rebuild and install on top of the existing app. Launch the app and logcat will show the message below:

sqlite returned: error code = 1, msg = no such collation sequence: PHONEBOOK
I/Database(27940): sqlite returned: error code = 1, msg = statement aborts at 2: [SELECT sqlcipher_export(‘migrate’);] no such collation sequence: PHONEBOOK

In spite of the error message, any API call to access the database works fine. After a device reboot or force close of the app through app manager, the app will crash as soon as there is a call to access the database.