Upgrading to SQLCipher 4

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?

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

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.

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.

A post was split to a new topic: PRAGMA cipher_migrate problem