Migrating from Existing Room database to Sqlcipher

Question

My app is currently using room database. I'm tying to migrate to use Sqlcipher data base. I have fallbackToDestructiveMigration() enabled but still throwing the following error

java.lang.RuntimeException: Exception while computing database live data.
    at androidx.room.RoomTrackingLiveData$1.run(RoomTrackingLiveData.java:92)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636)
    at java.lang.Thread.run(Thread.java:764)
 Caused by: net.sqlcipher.database.SQLiteException: file is not a database: , while compiling: select count(*) from sqlite_master;
    at net.sqlcipher.database.SQLiteCompiledSql.native_compile(Native Method)
    at net.sqlcipher.database.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
    at net.sqlcipher.database.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
    at net.sqlcipher.database.SQLiteProgram.<init>(SQLiteProgram.java:91)
    at net.sqlcipher.database.SQLiteQuery.<init>(SQLiteQuery.java:48)
    at net.sqlcipher.database.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:60)
    at net.sqlcipher.database.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:2016)
    at net.sqlcipher.database.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1902)
    at net.sqlcipher.database.SQLiteDatabase.keyDatabase(SQLiteDatabase.java:2673)
    at net.sqlcipher.database.SQLiteDatabase.openDatabaseInternal(SQLiteDatabase.java:2603)
    at net.sqlcipher.database.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1247)
    at net.sqlcipher.database.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:1322)
    at net.sqlcipher.database.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:166)
    at net.sqlcipher.database.SupportHelper.getWritableDatabase(SupportHelper.java:83)
    at androidx.room.RoomDatabase.inTransaction(RoomDatabase.java:476)
    at androidx.room.RoomDatabase.assertNotSuspendingTransaction(RoomDatabase.java:281)
    at androidx.room.RoomDatabase.query(RoomDatabase.java:324)
    at androidx.room.util.DBUtil.query(DBUtil.java:83)
    at com.screenlocker.secure.room.MyDao_Impl$29.call(MyDao_Impl.java:1249)
    at com.screenlocker.secure.room.MyDao_Impl$29.call(MyDao_Impl.java:1246)
    at androidx.room.RoomTrackingLiveData$1.run(RoomTrackingLiveData.java:90)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162) 
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636) 
    at java.lang.Thread.run(Thread.java:764) 

Is there any way to destroy all my database and move to Sqlcipher? i have also tried database.delete("table_name",null,null) command to manually deleting tables n migration but it is still not working. My code fro database creation is following.

DatabaseSecretProvider provider = new DatabaseSecretProvider(context);
        byte[] passphrase = provider.getOrCreateDatabaseSecret().asBytes();
        SupportFactory factory = new SupportFactory(passphrase);
        instance = Room.databaseBuilder(context, MyAppDatabase.class, AppConstants.DATABASE_NAME)
                .openHelperFactory(factory)
                .fallbackToDestructiveMigration()
                .build();

I'm using following version of Sqlcipher

implementation 'net.zetetic:android-database-sqlcipher:4.3.0'
    implementation "androidx.sqlite:sqlite:2.1.0"

Borglum

Answer 1

You can encrypt an unencrypted database with the sqlcipher_export convenience method from sqlcipher. So you don't have to do use fallbackToDestructiveMigration or spend time writing your custom migration tool.

From the developer's website:

To use sqlcipher_export() to encrypt an existing database, first open up the standard SQLite database, but don’t provide a key. Next, ATTACH a new encrypted database, and then call the sqlcipher_export() function in a SELECT statement, passing in the name of the attached database you want to write the main database schema and data to.

$ ./sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'newkey';
sqlite> SELECT sqlcipher_export('encrypted');
sqlite> DETACH DATABASE encrypted;

Finally, securely delete the existing plaintext database, and then open up the new encrypted database as usual using sqlite3_key or PRAGMA key.

Source: https://discuss.zetetic.net/t/how-to-encrypt-a-plaintext-sqlite-database-to-use-sqlcipher-and-avoid-file-is-encrypted-or-is-not-a-database-errors/868

@commonsguy also has an example of how to do this in Android:

  /**
   * Replaces this database with a version encrypted with the supplied
   * passphrase, deleting the original. Do not call this while the database
   * is open, which includes during any Room migrations.
   *
   * @param ctxt a Context
   * @param originalFile a File pointing to the database
   * @param passphrase the passphrase from the user
   * @throws IOException
   */
  public static void encrypt(Context ctxt, File originalFile, byte[] passphrase)
    throws IOException {
    SQLiteDatabase.loadLibs(ctxt);

    if (originalFile.exists()) {
      File newFile=File.createTempFile("sqlcipherutils", "tmp",
          ctxt.getCacheDir());
      SQLiteDatabase db=
        SQLiteDatabase.openDatabase(originalFile.getAbsolutePath(),
          "", null, SQLiteDatabase.OPEN_READWRITE);
      int version=db.getVersion();

      db.close();

      db=SQLiteDatabase.openDatabase(newFile.getAbsolutePath(), passphrase,
        null, SQLiteDatabase.OPEN_READWRITE, null, null);

      final SQLiteStatement st=db.compileStatement("ATTACH DATABASE ? AS plaintext KEY ''");

      st.bindString(1, originalFile.getAbsolutePath());
      st.execute();

      db.rawExecSQL("SELECT sqlcipher_export('main', 'plaintext')");
      db.rawExecSQL("DETACH DATABASE plaintext");
      db.setVersion(version);
      st.close();
      db.close();

      originalFile.delete();
      newFile.renameTo(originalFile);
    }
    else {
      throw new FileNotFoundException(originalFile.getAbsolutePath()+ " not found");
    }
  }

Source: https://github.com/commonsguy/cwac-saferoom/blob/v1.2.1/saferoom/src/main/java/com/commonsware/cwac/saferoom/SQLCipherUtils.java#L175-L224

You can pass context.getDatabasePath(DATABASE_NAME) as originalFile parameter.

Also, see this comment from commonsguy which explains how you can use this in combination with the getDatabaseState function to migrate your data from an existing plain-text database to a sqlcipher encrypted database.

Zeculon

Answer 2

It can be fixed as using SQLCipherUtils.encrypt method from thelib based on SQLCipherUtils Database state as mentioned below:

    @Synchronized
    fun getInstance(context: Context,key :String):  AppDB? {
       val  state=  SQLCipherUtils.getDatabaseState(context,
            DB_NAME)
       

        if (state == SQLCipherUtils.State.UNENCRYPTED) {
            
            SQLCipherUtils.encrypt(
                context,
                DB_NAME,
                Base64.decode(key, Base64.DEFAULT)
            )
        }

        if (INSTANCE == null) {
           
                val factory =
                    SafeHelperFactory(Base64.decode(key, Base64.DEFAULT))
                INSTANCE = Room.databaseBuilder(
                    context.applicationContext,
                    AppDB::class.java, DB_NAME
                )
                   
                    .openHelperFactory(factory)
                    .build()
                appContext = context.applicationContext
        
        }
        return INSTANCE
    }

make sure to call SQLCipherUtils encrypt before DB opened

Nehru

Important thing to mention is that the SQLCipherUtils come from this place: github.com/commonsguy/cwac-saferoom/blob/… And not from the SQLCipher library. – Priesthood

Answer 3

There is one ugly catch with Mad's answer. It crashes on some devices (Xiaomi for me) with "file is not a database". Apparently files created using File.createTempFile are not considered database on such devices. Don't ask me why, I simply don't know. So I've had to use standard new File call

So my implementation looks like this:

private fun encrypt(context: Context, originalFile: File, passphrase: ByteArray) {
    SQLiteDatabase.loadLibs(context)
    if (originalFile.exists()) {
        val newFile = File(context.cacheDir, "sqlcipherutils.db")
        if (!newFile.createNewFile()) {
            throw FileNotFoundException(newFile.absolutePath.toString() + " not created")
        }

        // get database version from existing database
        val databaseVersion = SQLiteDatabase.openDatabase(originalFile.absolutePath, "", null, SQLiteDatabase.OPEN_READWRITE).use { database ->
            database.version
        }

        SQLiteDatabase.openDatabase(
            newFile.absolutePath, passphrase, null, 
            SQLiteDatabase.OPEN_READWRITE, null, null
        ).use { temporaryDatabase ->
            temporaryDatabase.rawExecSQL("ATTACH DATABASE '${originalFile.absolutePath}' AS sqlcipher4 KEY ''")
            temporaryDatabase.rawExecSQL("SELECT sqlcipher_export('main', 'sqlcipher4')")
            temporaryDatabase.rawExecSQL("DETACH DATABASE sqlcipher4")
            temporaryDatabase.version = databaseVersion
        }
        originalFile.delete()
        newFile.renameTo(originalFile)
    } else {
        throw FileNotFoundException(originalFile.absolutePath.toString() + " not found")
    }
}

Underlinen

Answer 4

This worked for me but I feel like its not the best answer:

    val factory: SupportFactory = SupportFactory(masterKeyAlias.toByteArray())

    private fun buildDatabase(context: Context) =
        Room.databaseBuilder(
            context.applicationContext,
            AppDatabase::class.java,
            "MyDatabaseNew.db"  // <<--- change the name of this database file
        ).openHelperFactory(factory)
            .build()

This is a brand new database and the data needs to be populated brand new. Maybe there is a way to migrate it in a migration.

Pengelly

Yes, I did the same thing and got it working. Its a workaround. – Borglum

@MuhammadNadeem fromt he stuff ive read on the internet it sounds like the password is wrong but I tried every single one I had =/ – Pengelly

I was getting a file is not a db error below. This fixed that. It would also only happen on certain devices from a google play store install. Side loading, and dev builds never had this error. net.sqlcipher.database.SQLiteException: file is not a database: , while compiling: select count(*) from sqlite_master; – Infinite