Upgrading to SQLCipher 4

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.

A post was split to a new topic: SQLCipher 4 and REAL data type retrieval

Hello,

I do migration in android app from version 3.5.9 to 4.1.3 by doing option 1. I have two question.

  1. What other values except 0 PRAGMA cipher_migrate may return? And what do they mean?

  2. You said in Note: you must include the SQLITE_OPEN_CREATE flag. How to do it? I’m using net.sqlcipher.database.SQLiteOpenHelper.getWritableDatabase(password) to open db and I don’t see ways to add this flag.

1 Like

Hi @mARICa,

There could be different error codes, however aside from success, you might receive SQLITE_ERROR which is represented as 1.

Calling getWritableDatabase from SQLiteOpenHelper will provide the correct database flags when the database file is opened on your behalf, however, you would not be able to migrate a database when using getReadableDatabase.

1 Like

Hello all.

I use sqlcipher for Android app. The database is customized with: PRAGMA cipher_memory_security, PRAGMA journal_mode, PRAGMA synchronous.

And I have upgraded the database like was mentioned in Option 1: Database File Migration: run cipher_migrate.
I`m trying to open the database in try/catch block with SQLiteDatabase.openOrCreateDatabase(flag CREATE_IF_NECESSARY set by default). Then in the catch block, I run “PRAGMA cipher_migrate” in postKey of SQLiteDatabaseHook. Everything goes ok. An app works correctly. Looks like the database is migrated. All data is saved. No exception occurred.

Cursor cursor = database.rawQuery(“PRAGMA cipher_migrate”, null);
cursor.getString(0) returns “0”. It`s mean db migrated successfully.

But, after the app was stopped (the process is killed) and it runs from the scratch - I get the next exception: SQLiteException: file is not a database.

What am I doing wrong?

Thanks.

Found the next issue: when open database with SQLiteDatabase.openDatabase with the OPEN_READWRITE (0) flag - the database is opened, data is saved, database.rawQuery(“PRAGMA cipher_migrate”, null) returns “1” - application works and issue with SQLiteException: file is not a database - not reproduced.

Hi @vit

Would you try pulling the migrated database off the emulator and attempt to open it with the SQLCipher command line shell and let us know your results?

Hello,

I would not able to install SQLCipher Line Shell, instead, I opened migrated database with DB Browser for SQLite. It`s opened successfully. All data is saved.

As I mentioned - the migration is successful - database.rawQuery(“PRAGMA cipher_migrate”, null) returns 0. But after app process is stopped - the database cannot be opened. I get the next exception: SQLiteException: file is not a database.

Is DB Browser for SQLite is able to open the database using SQLCipher 3 or SQLCipher 4 settings at the prompt?