I’ve you tried to decode a Room of Japanese hiragana?

Protect your Room database with SQLCipher on Android

Welcome to this serie of articles about Android Security and how you can improve tremendously the protection of your users’ privacy, by implementing 3 things that provide a great effort/safety ratio.

Today we are going to look at protecting the Room Database. In deep Room database is handle by SQLite and the file is saved without encryption, which can expose your users’ data, for instance on a rooted device.

Prerequisites

minimum SDK : 16 (Android 4.1 JellyBean)

Add Room and SQLCipher into your gradle build file.

implementation "androidx.room:room-runtime:$room_version"
kapt "androidx.room:room-compiler:$room_version" 

// optional - Kotlin Extensions and Coroutines support for Room
implementation "androidx.room:room-ktx:$room_version"

implementation 'net.zetetic:android-database-sqlcipher:$sqlcipher_version@aar'

How SQLCipher works

SQLCipher add an encryption layer to SQLite. The encryption is pretty strong, using AES-256, a standard in the industry.

The developer will have to provide a Master Key that will be used to encrypt the data. It is the developer responsibility to protect and secure the Master Key. (could use for instance userID + salt + hash)

SQLCipher encrypt/decrypt page by page, which improve the performances, also each page is encrypted differently.

This is only an overview of the key feature. If you want an exhaustive understanding, please check SQLCipher Design

Compatibility with Room

Because SQLCipher is an extension of SQLite, it is compatible with all SQLite functions, it doesn’t disturb Room functionings.

You can continue using your DAO as usual, nothing special is required.

Implementation

As you can see in the following piece of code, only a few lines of code are required, which make it a quick-win to implement.

val builder = Room.databaseBuilder(
    context.applicationContext,
    Database::class.java, "encrypted.db"
)
val factory = SupportFactory(SQLiteDatabase.getBytes("PassPhrase".toCharArray()))
builder.openHelperFactory(factory)
builder.build()

Yes, you read it right, only the 2 lines in bold are required to make your DB encrypted.

But let’s get back to them for a moment. We first need to create the (SQLCipher) SupportFactory , and we will provide the passphrase we want to use as a MasterKey for the encryption.

Then we tell our Room database builder, to use this factory, thanks to openHelperFactory() .

From now on, the Room Database is encrypted.

⚠️ Make sure your passphrase is NOT hardcoded and is secured, like using the Android KeyStore

A Closer Look At The Encryption

Thanks to Android Studio 4.1 (RC2 as I write this article), we can inspect SQLite Database, and this is going to be helpful to verify the encryption.

Both encrypted DB can’t be open by Android Studio, show a red cross

As you can see Android Studio is actually not able to open the encrypted database. Which is a good sign 😀. However, that also mean debugging can be more complicated. The trick is to comment out the following line, so you database doesn’t get encrypted.

⚠️ Notice that you will have to change the DB Version or clear your local data, else you will get SQLException

// builder.openHelperFactory(factory)

We can download the DB locally, thanks to the Device Explorer

save .db file on local computer

So we can verify if the DB are encrypted, thanks to a tool like Hexdump

hexdump -C not-encrypted.db

Not-encrypted database show data clearly

hexdump -C encrypted.db

The encrypted file is only showing random, not readable data.

Performances

This is probably the most interesting part. Our users are expecting lightning fast apps, this is also why, us developers like native development. So let’s be clear, they will be drawbacks when applying a security layer of encryption.

Before we continue, SQLCipher is offering full encryption DB + Memory protection, but it is possible to disable the memory protection, which can improve performances. The memory protection consists in locking memory segments and wiping the memory after use.

PRAGMA cipher_memory_security = OFF

Using indices

Room is based on SQLite and SQLite is using tables, columns, primary key, foreign keys and other indices. It is something some Android Developer forget. Room is a powerful abstraction layer, but to take advantage of SQLite and have very good performances, it is important to think about the underlying layer: SQLite.

I won’t give you a Database class here, but I encourage you to read about it, because this will improve your performance a lot.

So identifying the columns that requires index, and defining them in your Entity will help.

@Entity(tableName = "person", indices = [Index(value = ["id"], unique = true)])

A Benchmark App

As you might have guessed, I like to understand how the tools I’m using are performing. So I thought, let’s put this into practice, and I build SQLCipher Performance, an App that allows you to test the performance on your device.

SQLCipher Performance is available on Play Store

Inserts

So the first test is about inserting 10,000 lines into the database, and the results show that it is about 25% slower (this can depends on the device as well).

Selects

For selects I have choosen to do two tests. One is focusing on querying indexed column, and the other one, non-indexed column.

Select on Indexed column is showing around perfomance are 5% slower than non-encrypted DB. In my humble opinion, it’s pretty good.

Selects on a non-indexed column is however much more challenging, first of all this means SQLite will have to look at each and every record in the table. And for SQLCipher, it also means decrypting. Performances get very bad up to 10 times slower.

Before you stop here and give up on implementing SQLCipher, keep in mind my advice earlier. If you setup the right indices on your database, you will probably get very reasonable performances.

╔═════════════════╦════════════╦════════════╦═════════════╗
║ 10,000          ║ No         ║ Encryption ║ Encryption  ║
║ transactions    ║ Encryption ║            ║  + Memory   ║  
╠═════════════════╬════════════╬════════════╬═════════════╣
║ Insert          ║      443ms ║      453ms ║       563ms ║
║ Select Index    ║    3,711ms ║    3,836ms ║     3,882ms ║
║ Select No Index ║   75,462ms ║  450,329ms ║ 1,027,048ms ║
╚═════════════════╩════════════╩════════════╩═════════════╝

Source code will be available on github when we reached 100 claps, so you know what to do 😄.

UPDATE: the source code can be found here

Migration

In order to migrate a non-encrypted/plain SQLite Database to SQLCipher, the team provide a function sqlcipher_export that can be used in a SQL Query ran in the Room migration script as a RawQuery.

database = SQLiteDatabase.openOrCreateDatabase("clearDatabase.db","", null);
database.rawExecSQL(
   "ATTACH DATABASE '${encryptedDbPath}' AS encrypted KEY '${passphrase}'");
database.rawExecSQL("select sqlcipher_export('encrypted')");
database.rawExecSQL("DETACH DATABASE encrypted");
database.close();

Conclusion

This article conclude my series of articles about Android Security. We have seen EncryptedSharedPreferences, SSLPinning and SQLCipher. I believe implementing these 3 things is feasible for most developers as it requires reasonable amount of work, for great security benefits.

2023 update of this article is available here

Regarding SQLCipher, I highly recommend it, don’t be afraid about the performances. I have a fair experience with it and I never really had any noticeable slowness. Of course it will depends of your dataset and your queries, but indices can help a lot.

Thanks a lot for reading this article, I hope you enjoyed this series.

Get SQLCipher Performance on PlayStore
Inspect the source code on GitHub (coming soon)

Please have look at Android Development News, to stay informed about the latest trends in Android Development.

Thanks 🙏

I would like to thank Stephen from SQLCipher who helped me to clarify some concepts and make this article as accurate as possible.