Link to Repository: https://github.com/wahabjawed/SQLite-Encryption
What is it about!
SQLite is an embedded SQL database engine that reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform and a popular database for mobile [iOS and Android] and IoT devices.
In the current era where data security and privacy are crucial, there is a high need for an SQLite DB encryption extension. SQLCipher is an SQLite extension that provides transparent 256-bit AES encryption of database files. To date, it has been open-sourced, sponsored, and maintained by Zetetic LLC. In the mobile space, SQLCipher has enjoyed widespread use in Apple’s iOS, as well as Nokia / QT for quite some time.
Why we need it!
For my mobile app, which is a reference/library type app, the size of the database is over 200mb and the app performs intensive database operations [DML]. The app downloads the content from the server on user demand and dumps into the SQLite database using bulk insertion. The speed of data insertion is crucial here because the sooner it finishes, the lesser will be the waiting time of the user.
I readily integrated SQLCipher which was a seamless process but soon I found that it has increased the database size by 70% and intensely decreased the performance of SQL operations. The average bulk insertion which used to take few seconds jumped to a couple of minutes using SQLCipher.
SQLCipher provides high security but at the cost of performance and storage.
These similar issues have concerned many others in the community as discussed below:
- https://discuss.zetetic.net/t/sqlcipher-4-2-on-android-performance-issues-even-without-encryption/4042
- https://github.com/sqlcipher/android-database-sqlcipher/issues/411
- https://github.com/amitshekhariitbhu/Android-Debug-Database/issues/142
SQLCipher encryption is a secure encryption extension but not ideal for the use-case where database size and performance are crucial. So now the problem was to find an encryption extension that doesn't impact the size and performance of the database at the cost of weak security. I found that there is no such solution available so I decided to develop it myself!
How I did it!
I started looking at some basic ciphers that fulfill our acceptance criteria and found classical ciphers to began with. Classical ciphers are very simple to implement and can provide relatively less secure encryption while keeping the performance intact. To begin with, I selected the following three ciphers:
- Shift Cipher
- Substitution Cipher
- Double Transpose Cipher [securest among others]
There are other classical ciphers i.e. Vigenère and Gronsfeld Cipher that are more complex and secure. I decided to implement a double transpose cipher for my use-case. Another requirement for my use-case was to encrypt only specified tables and columns. I created a JSON based configuration file to capture this information.
I created a utility in java that would take the SQLite database file and encrypt it as per my JSON based configuration file. In the App.java class of the utility, you have to define the path of the SQLite DB and configuration file.
The main method of the App.java builds DBEncryptor.java Object using the App Constants defined above and a type of ICipher object. In the example below, I have used ShiftCipher.java. It also takes the configuration file parsed into a Map<String, List<String>> for its processing.
The structure of the JSON based configuration file is rather simple. It contains an array of objects and each object defines the encryption configuration of a table. Only the table defined in the configuration file will be encrypted.
The DBEncrypter.java encrypts the database based on the configuration file by generating a set of select and update statements. Each Cipher class has a decode method that can perform decryption. You can convert the decryption code into a different programming language as per your platform.
Remarks
Although classical ciphers are less secure than modern cryptography, the tradeoff between security and performance is best suited for a database and storage intensive application.
Please contribute to the Github Repository or comment below if you know a better encryption scheme for my use-case.
