Issue Migrating From android-database-sqlcipher:3.5.9 to sqlcipher-android:4.5.5

[Anshuman_Biswal]

Hello
I am migrating from net.zetetic:android-database-sqlcipher:3.5.9 to net.zetetic:sqlcipher-android:4.5.5

My implementation looks like below.

private DatabaseHelper(Context context) {
super(context, DBNAME, null, VERSION);
this.context = context;
}

@Override
public void onCreate(SQLiteDatabase db) {
	db.execSQL(CREATE_PERFORMANCE);
	db.execSQL(CREATE_TICKET);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	Log.w(Constants.tag, "Upgrading database from version " + oldVersion + " to " + newVersion
			+ ", which will destroy all old data");
	db.execSQL("DROP TABLE IF EXISTS ticket"); //
	db.execSQL("DROP TABLE IF EXISTS performance"); //
	onCreate(db);
}

public DatabaseHelper open() throws SQLException {
	System.loadLibrary("sqlcipher");
	database = this.getWritableDatabase();
	return this;
}

public SQLiteDatabase getDatabase() {
	if(database == null){
		System.loadLibrary("sqlcipher");
		database = getWritableDatabase();
	}
	return database;
}

I made changes after referring SQLCipher for Android Migration - How to Convert Applications from android-database-sqlcipher to sqlcipher-android | Zetetic

but when getWritableDatabase() gets called , crashing with below message
Caused by: android.database.sqlite.SQLiteException: file is not a database (code 26): , while compiling: PRAGMA journal_mode

please help on this, I am stuck since few days.

[mmoore]

Hi @Anshuman_Biswal

Thank you for your interest in SQLCipher and for posting to the discussion forum.

SQLCipher 3.x has different encryption settings than SQLCipher 4.x, so you’ll need to migrate your database(s) to use these new encryption settings (or open them in compatibility mode) as outlined in this post: Upgrading to SQLCipher 4

SQLCipher should properly migrate all your tables and database data when performing the migration, so you shouldn’t need to drop your tables and re-create them onUpgrade like you’re doing unless that’s an application requirement.

[mmoore]

@Anshuman_Biswal

The API documentation for cipher_migrate is here: SQLCipher API - Full Database Encryption PRAGMAs, Functions, and Settings | Zetetic

You’ll want to follow the guidance mentioned in the API documentation for how to determine whether to perform the migration as it is expensive.

There’s an example of calling cipher_migrate in the postKey hook here: sqlcipher-android-tests/app/src/main/java/net/zetetic/tests/CipherMigrateTest.java at master · sqlcipher/sqlcipher-android-tests · GitHub

From your code it looks like you’re hard coding a key value. We strongly recommend against doing that. There’s some information about Key Material and Selection, along with some technical guidance in these posts:

SQLCipher Database Key Material and Selection

By design, SQLCipher doesn’t perform permanent storage of the database password and key material. This provides maximum flexibility, allowing an application to provide key material from any combination of sources (e.g. user provided passphrase, key material on device, external hardware tokens, etc.). While this cleanly separates the management of the key material from the implementation of the database encryption, allowing SQLCipher to address the widest possible set of requirements and use cas…

Technical Guidance: Using Random Values as SQLCipher Keys

[sebalz]

Hi, I’m trying to migrate from sqlcipher 3.5.9 to (4.5.4 or to 4.6.0).
The starting db contained a custom page size “4096”

public CipherDBOpenHelper(@Nullable Context context, String password) {
super(context, DATABASE_NAME, null, DATABASE_VERSION, new SQLiteDatabaseHook() {
@Override
public void preKey(SQLiteDatabase database) {
database.execSQL("PRAGMA key = "+ password);
database.execSQL(“PRAGMA cipher_page_size = 4096”);
}

        @Override
        public void postKey(SQLiteDatabase database) {

        }
    });
}

From the documentation I understood that in this case i need to use the export function:

public static void migrateDb(String password) {
    SQLiteDatabaseHook hook = new SQLiteDatabaseHook() {
        @Override
        public void preKey(SQLiteDatabase database) {
        }
        @Override
        public void postKey(SQLiteDatabase database) {
            //database.execSQL("PRAGMA cipher_compatibility = 3;");
            database.execSQL("PRAGMA cipher_page_size = 4096;");
            database.execSQL("PRAGMA cipher_hmac_algorithm = HMAC_SHA1;");
            database.execSQL("PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;");
            try {
                database.rawExecSQL(String.format("ATTACH DATABASE '%s' AS history_log_new KEY '%s'",
                        dbNewFile.getAbsoluteFile(),password));
            }catch (Exception e){
                Log.e(DBTEST_LOG,"errore "+e);
            }

            database.rawExecSQL(String.format("SELECT sqlcipher_export('%s')","history_log_new"));
            database.rawExecSQL(String.format("DETACH DATABASE '%s'","history_log_new"));
        }
    };
    //create new db
    SQLiteDatabase database = SQLiteDatabase.openOrCreateDatabase(dbNewFile.getPath(),password, null);
    database.close();
    //start export
    SQLiteDatabase databaseOld = SQLiteDatabase.openOrCreateDatabase(dbOldFile.getPath(),password,null,hook);
    databaseOld.close();

    //open new db
    database = SQLiteDatabase.openOrCreateDatabase(dbNewFile.getPath(), password, null);

I get an exception : File is not a database.

I did a series of tests and looked at all the examples you posted. I tried to do the migration starting from a db with default settings and using “cipher migration” and it works correctly. In my opinion the problem arises from the page size.

What am I doing wrong :-)?
Thank you,

[developernotes]

Hi @sebalz,

You don’t need to create the new database first, the sqlcipher_export(...) function will do that during the export. You need to open the old database using the hook, but also execute the following:

PRAGMA cipher_compatiblity = 3;
PRAGMA cipher_page_size = 4096;
ATTACH DATABASE 'history_log_new.db' as sqlcipher4 KEY 'YourPasswordHere';
SELECT sqlcipher_export('sqlcipher4');
DETACH DATABASE sqlcipher4;

This will allow history_log_new.db to be created with default SQLCipher 4 configuration settings.

[sebalz]

Thank you. You solved my problem. I had read in a comment not to use “compatibility” when the original db uses custom settings.
In any case there was a piece missing: “there was no need to create a new database”.
Greetings

[developernotes]

Hi @sebalz,

Excellent, glad to hear that addressed the issue.

[Anshuman_Biswal]

Hello
Now I am trying to update
implementation ‘net.zetetic:android-database-sqlcipher:3.5.9’ to implementation ‘net.zetetic:android-database-sqlcipher:4.5.4’

My implementation is mentioned below.
public DatabaseHelper open() throws SQLException {
SQLiteDatabase.loadLibs(this.context);
database = this.getWritableDatabase(Constants.api_secret);
return this;
}

public SQLiteDatabase getDatabase() {
    if (database == null) {
        SQLiteDatabase.loadLibs(this.context);
        database = getWritableDatabase(Constants.api_secret);
    }
    return database;
}

private DatabaseHelper(Context context) {
super(context, DBNAME, null, VERSION, new SQLiteDatabaseHook() {

        @Override
        public void preKey(SQLiteDatabase sqLiteDatabase) {

        }

        @Override
        public void postKey(SQLiteDatabase sqLiteDatabase) {
            try (Cursor cursor = sqLiteDatabase.rawQuery("PRAGMA cipher_migrate", null)) {
                // Check if migration was successful
                if (cursor != null && cursor.moveToFirst()) {
                    int result = cursor.getInt(0);
                    cursor.close();
                    if (result == 0) {
                        Log.d("DataBaseHelper", "migration is successful");
                    } else {
                        // Handle migration failure
                        throw new SQLiteException("Cipher migration failed");
                    }
                } else {
                    // Handle no result or cursor error
                    throw new SQLiteException("No result from PRAGMA cipher_migrate");
                }
            } catch (Exception e) {
                throw new SQLiteException("Error executing PRAGMA cipher_migrate", e);
            }
        }
    });
    this.context = context;
}

working fine but some times getting ANR.
Can you please let me know based on below link
postKey() implementation is correct or not( not sure why this change is causing ANR as log is printed - Migration is successful )?. Only for changing the version( 3.5.9 to 4.5.4) do I need to increase the db version?. Can you please help on this.

Upgrading to SQLCipher 4

The recent release of SQLCipher 4 introduces many new performance and security enhancements for applications that use SQLCipher for secure local data storage. However, the introduction of new default algorithms, increased KDF iterations and a larger page size means that SQLCipher 4 will not open older databases by default. This document provides guidance on the upgrade options available to applications that have previously integrated SQLCipher versions 1 through 3. Option 1: Database File Migr…

[Anshuman_Biswal]

Hello
Can you please help on this.

[developernotes]

Hi @Anshuman_Biswal,

Unfortunately, there is not enough information to determine why you are occasionally receiving an ANR. Because the cipher_migrate process will have to rewrite each page within the database, the size of each database may impact the overall time to complete the process. You should make sure you are not performing this operation on the main UI thread as it may block.

[Anshuman_Biswal]

Can you please let me know
postKey() implementation is correct or not?
private DatabaseHelper(Context context) {
super(context, DBNAME, null, VERSION, new SQLiteDatabaseHook() {
@Override
public void preKey(SQLiteDatabase sqLiteDatabase) {

    }

    @Override
    public void postKey(SQLiteDatabase sqLiteDatabase) {
        try (Cursor cursor = sqLiteDatabase.rawQuery("PRAGMA cipher_migrate", null)) {
            // Check if migration was successful
            if (cursor != null && cursor.moveToFirst()) {
                int result = cursor.getInt(0);
                cursor.close();
                if (result == 0) {
                    Log.d("DataBaseHelper", "migration is successful");
                } else {
                    // Handle migration failure
                    throw new SQLiteException("Cipher migration failed");
                }
            } else {
                // Handle no result or cursor error
                throw new SQLiteException("No result from PRAGMA cipher_migrate");
            }
        } catch (Exception e) {
            throw new SQLiteException("Error executing PRAGMA cipher_migrate", e);
        }
    }
});
this.context = context;

}

[developernotes]

Hi @Anshuman_Biswal,

Yes, the usage of PRAGMA cipher_migrate within your postKey event looks good. Checking for a value of 0 to determine success is correct.

[Anshuman_Biswal]

unfortunately I am getting below crash.
@Override
public void postKey(SQLiteDatabase sqLiteDatabase) {
Executors.newSingleThreadExecutor().execute(new Runnable() {
@Override
public void run() {
try (Cursor cursor = sqLiteDatabase.rawQuery(“PRAGMA cipher_migrate”, null)) {
// Check if migration was successful
if (cursor != null && cursor.moveToFirst()) {
int result = cursor.getInt(0);
cursor.close();
if (result == 0) {
Log.d(“DataBaseHelper”, “migration is successful”);
} else {
// Handle migration failure
throw new SQLiteException(“Cipher migration failed”);
}
} else {
// Handle no result or cursor error
throw new SQLiteException(“No result from PRAGMA cipher_migrate”);
}
} catch (Exception e) {
throw new SQLiteException(“Error executing PRAGMA cipher_migrate”, e);
}
}
});
}
});

=================

FATAL EXCEPTION: AsyncTask #2
Process:
java.lang.RuntimeException: An error occurred while executing doInBackground()
at android.os.AsyncTask$4.done(AsyncTask.java:415)
at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:381)
at java.util.concurrent.FutureTask.setException(FutureTask.java:250)
at java.util.concurrent.FutureTask.run(FutureTask.java:269)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:305)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:644)
at java.lang.Thread.run(Thread.java:1012)
Caused by: android.database.sqlite.SQLiteException: unable to close due to unfinalized statements or unfinished backups: sqlite3_close() failed
at net.sqlcipher.database.SQLiteDatabase.dbclose(Native Method)
at net.sqlcipher.database.SQLiteDatabase.openDatabaseInternal(SQLiteDatabase.java:2635)
at net.sqlcipher.database.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1250)
at net.sqlcipher.database.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:1325)
at net.sqlcipher.database.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:167)
at net.sqlcipher.database.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:136)
at net.sqlcipher.database.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:132)
at android.os.AsyncTask$3.call(AsyncTask.java:394)
at java.util.concurrent.FutureTask.run(FutureTask.java:264)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:305)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:644)
at java.lang.Thread.run(Thread.java:1012)
2024-06-25 20:39:06.126 4166-4585 AndroidRuntime E FATAL EXCEPTION: pool-11-thread-1
Process:
android.database.sqlite.SQLiteException: Error executing PRAGMA cipher_migrate
at
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:644)
at java.lang.Thread.run(Thread.java:1012)
Caused by: android.database.sqlite.SQLiteException: Cipher migration failed

                                                                                                	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) 
                                                                                                	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:644) 
                                                                                                	at java.lang.Thread.run(Thread.java:1012)

[developernotes]

It look like you’re using try-with-resources, but also explicitly closing the cursor. If you remove the explicit call to close on the cursor does it go away? Is your AsyncTask doing anything else with the connection?

[Anshuman_Biswal]

If I remove cursor.close() , still it crashes

there are 2 FATAL EXCEPTIONS
One during migration and another during clearTable()

2024-06-26 08:06:54.353 13645-13769 AndroidRuntime com.ticke.android.app E FATAL EXCEPTION: AsyncTask #1
Process: com.ticke.android.app, PID: 13645
java.lang.RuntimeException: An error occurred while executing doInBackground()
at android.os.AsyncTask$4.done(AsyncTask.java:415)
at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:381)
at java.util.concurrent.FutureTask.setException(FutureTask.java:250)
at java.util.concurrent.FutureTask.run(FutureTask.java:269)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:305)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:644)
at java.lang.Thread.run(Thread.java:1012)
Caused by: android.database.sqlite.SQLiteException: unable to close due to unfinalized statements or unfinished backups: sqlite3_close() failed
at net.sqlcipher.database.SQLiteDatabase.dbclose(Native Method)
at net.sqlcipher.database.SQLiteDatabase.openDatabaseInternal(SQLiteDatabase.java:2635)
at net.sqlcipher.database.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1250)
at net.sqlcipher.database.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:1325)
at net.sqlcipher.database.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:167)
at net.sqlcipher.database.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:136)
at net.sqlcipher.database.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:132)
at com.ticke.android.app.common.services.DatabaseHelper.getDatabase(DatabaseHelper.java:124)
at com.ticke.android.app.common.services.DatabaseHelper.clearTables(DatabaseHelper.java:130)
atcom.ticke.android.app.common.services.DatabaseHelper$StoreUserTicketListTask.doInBackground(DatabaseHelper.java:146)
at com.ticke.android.app.common.services.DatabaseHelper$StoreUserTicketListTask.doInBackground(DatabaseHelper.java:140)
at android.os.AsyncTask$3.call(AsyncTask.java:394)
at java.util.concurrent.FutureTask.run(FutureTask.java:264)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:305)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:644)
at java.lang.Thread.run(Thread.java:1012)

===============================================================
2024-06-26 08:06:54.356 13645-13770 AndroidRuntime com.ticke.android.app E FATAL EXCEPTION: pool-11-thread-1
Process: com.ticke.android.app, PID: 13645
android.database.sqlite.SQLiteException: Error executing PRAGMA cipher_migrate
at com.ticke.android.common.services.DatabaseHelper$1$1.run(DatabaseHelper.java:91)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:644)
at java.lang.Thread.run(Thread.java:1012)
Caused by: android.database.sqlite.SQLiteException: Cipher migration failed
at com.ticke.android.common.services.DatabaseHelper$1$1.run(DatabaseHelper.java:84)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:644)
at java.lang.Thread.run(Thread.java:1012)

Inside asyncTask first we are calling
clearTables()
public void clearTables() {
getDatabase().execSQL(“delete from ticket”);
getDatabase().execSQL(“delete from performance”);
}

public SQLiteDatabase getDatabase() {
if (database == null) {
SQLiteDatabase.loadLibs(this.context);
database = getWritableDatabase(Constants.api_secret); // this is the last call from app’s class DatabaseHelper before crash.
}
return database;
}

postKey() code remains same , but removed cursor.close()

@Override
public void postKey(SQLiteDatabase sqLiteDatabase) {
Executors.newSingleThreadExecutor().execute(new Runnable() {
@Override
public void run() {
try (Cursor cursor = sqLiteDatabase.rawQuery(“PRAGMA cipher_migrate”, null)) {
// Check if migration was successful
if (cursor != null && cursor.moveToFirst()) {
int result = cursor.getInt(0);
if (result == 0) {
Log.d(“DataBaseHelper”, “migration is successful”);
} else {
// Handle migration failure
throw new SQLiteException(“Cipher migration failed”);
}
} else {
// Handle no result or cursor error
throw new SQLiteException(“No result from PRAGMA cipher_migrate”);
}
} catch (Exception e) {
throw new SQLiteException(“Error executing PRAGMA cipher_migrate”, e);
}
}
});
}
});

[Anshuman_Biswal]

Any suggestion please

[developernotes]

Hi @Anshuman_Biswal,

What happens when you pull an existing SQLCipher 3 database off the device onto your local machine and attempt to run the PRAGMA cipher_migrate command on your host computer when using SQLCipher 4?

[Anshuman_Biswal]

I am really sorry.
I am not getting exactly what we need to do here.

[developernotes]

Hello @Anshuman_Biswal,

I am asking you to extract the SQLCipher 3 database from an Android device onto a host computer, then run the SQLCipher command line shell program (using version 4.*), opening the database and running PRAGMA cipher_migrate. What is your result from doing this?