Upgrading to SQLCipher 4

[diejmon]

@sjlombardo

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)

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?

[developernotes]

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.

[missdeer]

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

[hwbest]

Hi @missdeer

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

Upgrading to sqlcipher 4 and split databases

Hi sqlcipher team, Firstly, I want to thanks to sqlcipher team for providing the this great library. I’m an android developer, and I have a special requirement while upgrading to sqlciper 4. Please check below digram firstly: [wKgcQlxrxTyAF8_CAACJ-1TndPI735] As we can see, I want to upgrate exist single 3.x database(with default setting) to 4.x database(with default settings) and split into several databases. I’ve checked below thread before I raise this topic: upgrading-to-sqlcipher-4 I…

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

Thanks

[developernotes]

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:

Upgrading to SQLCipher 4

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.

[missdeer]

@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);

[krishnakukmartm]

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.

[sjlombardo]

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:

Upgrading to SQLCipher 4

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, na…

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

[mARICa]

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.

[developernotes]

Hi @mARICa,

@mARICa

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

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

@mARICa

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.

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.

[vit]

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.

[vit]

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.

[developernotes]

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?

[vit]

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.

[developernotes]

@vit

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.

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

[vit]

@developernotes

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

Do you mean Encryption settings? - If yes - Yes, I can choose SQLCipher3 or SQLCipher3 or custom settings. I opened with SQLCipher4 settings.

[developernotes]

Hello @vit

If you are able to access the migrated database using DB Browser for SQLite using the SQLCipher 4 settings, there is likely an issue within the application logic that is preventing access during subsequent attempts. You could always consider dropping the migrated database in the SQLCipher for Android test suite and writing a test to access it from the assets folder as an attempt to isolate your application logic.

[vit]

Hello @developernotes

We`ve found the reason of the issue. Migration, as I said, was successful. But, during opening the database I was trying to set PRAGMA cipher_page_size in SQLiteDatabaseHook.postKey.
Like in example - https://www.zetetic.net/sqlcipher/sqlcipher-api/#cipher_page_size And I got SQLiteException: file is not a database. And I was using sqlcipher:4.0.1 version.

I will try to investigate the reason.