# Changing the encryption algorithm

**URL:** <https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922>\
**Category:** SQLCipher\
**Created:** [June 25, 2018, 7:26am UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922 "2018-06-25T07:26:48Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![korwinek](https://avatars.discourse-cdn.com/v4/letter/k/b782af/32.png) [@korwinek](https://discuss.zetetic.net/u/korwinek)\
**Post date:** [June 25, 2018, 7:26am UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/1 "2018-06-25T07:26:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sjlombardo](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/sjlombardo/32/3_2.png) [@sjlombardo](https://discuss.zetetic.net/u/sjlombardo)\
**Post date:** [June 25, 2018, 9:33pm UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/2 "2018-06-25T21:33:51Z")

</div>

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 cipher`is 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](https://discuss.zetetic.net/t/sqlcipher-performance-optimization/14)? 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.

---

<div class="post-metadata">

**Author:** ![korwinek](https://avatars.discourse-cdn.com/v4/letter/k/b782af/32.png) [@korwinek](https://discuss.zetetic.net/u/korwinek)\
**Post date:** [June 26, 2018, 7:21am UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/3 "2018-06-26T07:21:58Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![commonsguy](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/commonsguy/32/75_2.png) [@commonsguy](https://discuss.zetetic.net/u/commonsguy)\
**Post date:** [June 26, 2018, 10:41am UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/4 "2018-06-26T10:41:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![korwinek](https://avatars.discourse-cdn.com/v4/letter/k/b782af/32.png) [@korwinek](https://discuss.zetetic.net/u/korwinek)\
**Post date:** [June 26, 2018, 11:18am UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/5 "2018-06-26T11:18:14Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![sjlombardo](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/sjlombardo/32/3_2.png) [@sjlombardo](https://discuss.zetetic.net/u/sjlombardo)\
**Post date:** [June 26, 2018, 12:36pm UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/6 "2018-06-26T12:36:33Z")

</div>

@korwinek how many rows are there in the table where Column1 = 3?

---

<div class="post-metadata">

**Author:** ![korwinek](https://avatars.discourse-cdn.com/v4/letter/k/b782af/32.png) [@korwinek](https://discuss.zetetic.net/u/korwinek)\
**Post date:** [June 26, 2018, 12:39pm UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/7 "2018-06-26T12:39:02Z")

</div>

@sjlombardo  
~80000 rows

---

<div class="post-metadata">

**Author:** ![developernotes](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/developernotes/32/1309_2.png) [@developernotes](https://discuss.zetetic.net/u/developernotes)\
**Post date:** [June 26, 2018, 1:11pm UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/8 "2018-06-26T13:11:23Z")

</div>

Hi @korwinek

What do your indices look like for that table? Do you have an index that takes into account id, column1, and name?

---

<div class="post-metadata">

**Author:** ![sjlombardo](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/sjlombardo/32/3_2.png) [@sjlombardo](https://discuss.zetetic.net/u/sjlombardo)\
**Post date:** [June 26, 2018, 2:48pm UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/9 "2018-06-26T14:48:48Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![korwinek](https://avatars.discourse-cdn.com/v4/letter/k/b782af/32.png) [@korwinek](https://discuss.zetetic.net/u/korwinek)\
**Post date:** [June 27, 2018, 6:27am UTC](https://discuss.zetetic.net/t/changing-the-encryption-algorithm/2922/10 "2018-06-27T06:27:14Z")

</div>

@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.