Due to SQLCipher performance problems, I need to change the database encryption algorithm. I’ve tried different ways to do this and eventually my migration code looks like this.
SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbFile.getAbsolutePath(), (String)null, null);
int dbVersion = db.getVersion();
db.rawExecSQL(“PRAGMA cipher_default_page_size=” + CDatabaseHelper.CIPHER_PAGE_SIZE);
db.rawExecSQL(“PRAGMA cipher_default_kdf_iter=” + CDatabaseHelper.CIPHER_KDF_ITER);
db.rawExecSQL(“PRAGMA cipher_default_use_hmac=” + CDatabaseHelper.CIPHER_USE_HMAC);
db.execSQL(“ATTACH DATABASE ? AS encrypted KEY ?”, new Object[] { encDbFile.getAbsolutePath(), mPassword});
db.rawExecSQL(“PRAGMA encrypted.cipher=” + CDatabaseHelper.CIPHER_ENCRYPT_ALGORITHM);
cursor = db.rawQuery(“SELECT sqlcipher_export(‘encrypted’)”, null);
cursor.moveToFirst();
Creation of an empty encrypted database to which the data will be transferred. This works with no problem:
CDatabaseHelper(Context context)
{
// calls the super constructor, requesting the default cursor factory.
super(context, DATABASE_NAME, null, DATABASE_VERSION, new SQLiteDatabaseHook()
{
@Override
public void preKey(SQLiteDatabase database)
{
}
@Override
public void postKey(SQLiteDatabase database)
{
//database.rawExecSQL("PRAGMA cipher=" + CIPHER_ENCRYPT_ALGORITHM);
database.rawExecSQL("PRAGMA cipher_page_size=" + CIPHER_PAGE_SIZE);
database.rawExecSQL("PRAGMA kdf_iter=" + CIPHER_KDF_ITER);
database.rawExecSQL("PRAGMA cipher_use_hmac=" + CIPHER_USE_HMAC);
}
});
}
Unfortunately, I’m getting out of memory exception.
Can anyone advise me how to change the code so that the change of encryption will work.
Hello @korwinek - before we even address the conversion, we would strongly recommend against changing the database encryption algorithm. That feature does not work with all cryptographic providers, and PRAGMA cipheris deprecated; it will likely be removed from a future version of SQLCipher entirely. Furthermore, it is extremely unlikely that changing the encryption algorithm will have any meaningful impact on performance. Have you read the performance optimization guidelines for SQLCipher? If not, you should start there first, as those suggestions are likely to yield significant improvements in performance without changing the encryption algorithm in an unsupported way.
Hi @sjlombardo,
I know that PRAGMA cipher is deprecated and I’ve read your recommendations about performance. Despite this I need to improve query execution. Before SQLCipher one of queries was executed in 5 sec., after using SQLCipher time raised to 26 sec. and it is unacceptable to users.
The more I try to use PRAGMA the less I understand how it works. 
Following code works for change page size using PRAGMA cipher_default_page_size and I can observe lesser queries execution time.
Create new database:
CDatabaseHelper(Context context)
{
// calls the super constructor, requesting the default cursor factory.
super(context, DATABASE_NAME, null, DATABASE_VERSION, new SQLiteDatabaseHook()
{
@Override
public void preKey(SQLiteDatabase database)
{
database.rawExecSQL(“PRAGMA cipher_default_page_size=” + CIPHER_PAGE_SIZE);
}
@Override
public void postKey(SQLiteDatabase database)
{
}
});
}
Encrypt unencrypted database:
SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbFile.getAbsolutePath(), (String)null, null);
db.execSQL(“ATTACH DATABASE ? AS encrypted KEY ?”, new Object[] { encDbFile.getAbsolutePath(), mPassword});
cursor = db.rawQuery(“SELECT sqlcipher_export(‘encrypted’)”, null);
cursor.moveToFirst();
db.close();
Open encrypted database:
public static SQLiteDatabase openDatabase(File dbPath, String dbPassword) throws Exception
{
SQLiteDatabase db = SQLiteDatabase.openDatabase(dbPath.getAbsolutePath(), dbPassword, null, SQLiteDatabase.OPEN_READWRITE, new SQLiteDatabaseHook()
{
@Override
public void preKey(SQLiteDatabase database)
{
}
@Override
public void postKey(SQLiteDatabase database)
{
}
});
return db;
}
When I try to use PRAGMA cipher to migrate database from unencrypted to encrypted in the best case I do not get effect or out of memory exception. But when I create encrypted database and then fill it with data it works. I have no idea when and where to use PRAGMA cipher to make it work.
Is it even possible to change/set encryption algoritm during migration process?
I will echo the comment from @sjlombardo here. When it comes to SQLCipher performance, worry less about the Cipher and more about the SQL. A five-second query on a regular SQLite database, particularly on a mobile device, suggests that there is room for improvement in the SQL itself. Those improvements will carry over to using that same SQL in SQLCipher. Between using existing tools (e.g., EXPLAIN QUERY PLAN) and using SQLite support options (e.g., Stack Overflow), focus on getting your queries to execute faster from a SQL standpoint.
It’s also possible that a relational database is not the best store for your data, whether that database is encrypted or not.
@commonsguy
I was writing about simple query “SELECT ID FROM Table WHERE Column1 = 3 ORDER BY Name”. Table has 80 columns and about 80k records.
Executing “SELECT COUNT(ID) FROM Table WHERE Column1 = 3” takes almost 10 sec.
Columns are indexed.
@korwinek how many rows are there in the table where Column1 = 3?
Hi @korwinek
What do your indices look like for that table? Do you have an index that takes into account id, column1, and name?
@korwinek - so, the table contains 80000 rows, and almost all of them meet the criteria of Column1 = 3? In other words, effectively the entire table is returned in the result set?
If that is the case it is likely that the library won’t even be able to make efficient use of indexes anyway, since it would need to retrieve all pages. Given that the table has 80 columns, it is likely quite denormalized, and contains a significant amount of redundancy. Do you know how large each row is?
Your best option for non-invasively optimizing for queries that perform full table scans with large rows is to increase the page size. It sounds like you tried that above and noticed some performance improvement. What page size setting did you use? Try making it even bigger. You may gain some additional performance improvement by disabling HMAC (though there are security implications to doing so).
The other path to improvement is to restructure the database for better performance. A more normalized data structure will generally provide better performance overall, both with encrypted and non-encrypted databases.
@sjlombardo thanks for response,
Query read only one column with primary key and then during fill listview we read rest of needed data. I know it’s crazy to fill list with 80000 rows but it is client’s requirement.
I am aware that 80 columns that’s a lot but all columns are needed and many of them are keys to other tables. I changed page size and it speeded up query execution.
Change of encryption algorithm to ‘aes-128’ will make more improved. I know how to create new database with new algoritm and the fill it with data, it works. But I can’t make it to work when I try to do this during migrate databases.
Restructure database it’s not an option. No one would allow us to spend months on change db structure only on mobile and then data exchange with backend.
I only do what I can.