using SQLCipher with android shipped sqlite database file

Question

I have a database file reset in the assets file.

How can I use SQLCipher to encrypt the database in android?

Insidious

Since the shipped database will already be encrypted, you will not be able to re-encrypt it with the user's chosen password. This means the encryption is pointless, so just use regular SQLite, perhaps with SQLiteAssetHelper to handle the package-the-database-in-assets part. – Semitrailer

what do you mean that " the shipped database will already be encrypted" i haven't encrypt it ? – Insidious

Ah! never mind, my apologies. – Semitrailer

Well, you said you had a database in the assets folder and you wanted to encrypt it... therefor the user won't be able to re-encrypt it. However, you could send it out un-encrypted and have a function in the app to let the user encrypt it on first run I suppose. Assuming that scenario I posted an answer that might help. – Atonal

Answer 1

This is going to be a bit complicated. Since the database file format is different between SQLite and SQLCipher for Android, and since you want to ship an unencrypted database, you will have to do a few things.

First, I'd get SQLiteAssetHelper going, to deliver the unencrypted database to your environment.

Then, use standard SQLCipher for Android to create an empty-but-encrypted database.

Next, you will need to implement the code to copy the data out of the packaged-but-unencrypted database and insert it into the empty-but-encrypted database.

Once that is all done, you can close and delete the packaged-but-unencrypted database and just use the encrypted one.

This might make a useful extension to SQLiteAssetHelper, someday...

Semitrailer

couldn't i encrypt it before i ship the database with cipher ? – Insidious

@user4o01: No, because the database should be encrypted with the user's chosen password. – Semitrailer

so if any one get my apk file will get the database and can read it content ? – Insidious

@user4o01: Of course. They can always do that. If you do not want people to read your data, do not give them your data. Encrypting your database with your own encryption key will not help, because people will just get the key out of your app and use it. If you put the data on the user's device, it is now the user's data, not yours. Use SQLCipher for Android to help the user defend their data against others; you cannot use SQLCipher for Android to defend the user's data against the user. – Semitrailer

@Semitrailer just need a suggestion on this. I have successfully implemented sqlite-asset-helper and it works fine when I add a zipped db file unencrypted to assets folder but it doesn't when I add a zipped encrypted file to assets folder. – Archean

@Semitrailer Then, use standard SQLCipher for Android to create an empty-but-encrypted database. Next, you will need to implement the code to copy the data out of the packaged-but-unencrypted database and insert it into the empty-but-encrypted database. This would be again time consuming process as I am copying in the assets folder and then to sdcard just because I don't wanna spend time in insertion of long data. – Archean

@Semitrailer https://mcmap.net/q/53212/-ship-an-application-with-a-database/1503130 this is what actually brought me to solve my problem using SqliteAssetHelper. Now if I have to re-insert the data to another db file then there is no point in using this approach. – Archean

@pKs: "I don't wanna spend time in insertion of long data" -- you do not have a choice. You have to get a passphrase from the user to create the encrypted database. You do not have to manually do all the SQL -- there is a sqlcipher_export() function you can use that gets you most of the way there. I have that wrapped up in an encrypt() utility method here: github.com/commonsguy/cwac-loaderex#usage-sqlcipherutils – Semitrailer

@Semitrailer : Thanks for sharing the sqlicipherutils class , this did it for me. And when the file from assets folder is copied to sdcard then using the mentioned function SQLCipherUtils.encrypt(mContext,Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+DBName, "password"); I encrypt the file and then read contents from the encrypted db. This all works good on 2.3 and 4.1 , but creates issue with ICS as all the tables are not copied from the database from assets even if we keep encryption aside. – Archean

@Semitrailer One more thing I just noticed the app gets installed on device and the db is copied in case the app is directly installed to the device from pc. But when the app is copied to device and then installed manually then it crashes. Is this any issue of sqlitehelper , because right now I am not encrypting the database even. – Archean

@pKs: I would presume that your issue is one with SQLCipher. Create a sample project demonstrating the issue, and point to it from a post on the SQLCipher Google Group: groups.google.com/group/sqlcipher – Semitrailer

@Semitrailer I have removed the all statements where SqliteCipher is used actually I am not using it right now. What I have the issue with is SqliteAssetHelper here. As when I install the app and the db is copied from assets folder to sdcard then all of the tables inside the Dash.db file are not copied, but only a few. I can't get the issue, Are there any prerequisites while creating a db file from sqlite Browser. – Archean

@pKs: "What I have the issue with is SqliteAssetHelper here." -- then create a sample project demonstrating the issue, and post an issue on the SQLiteAssetHelper GitHub repo. – Semitrailer

Answer 2

They cover how to use SQLCipher in detail on their website here

Basically you download their binaries, set them up in your project and then use their SQLiteDatabase class instead of the standard android SQLiteDatabase class.:

 import info.guardianproject.database.sqlcipher.SQLiteDatabase;

Atonal

However, the database file format is different, so you cannot use this with an unencrypted database file you pre-package with your app or download from somewhere. – Semitrailer

Arrgh. Didn't read far enough into their docs to realize that, and made the mistake of assuming since it was a version for android it would automagically be compatible (yeah, I know, bad mistake). Thanks for pointing it out. – Atonal

Answer 3

The following code excerpt can be used to create multiple tables in an SQLite Cipher database:

Use these imports:

import java.sql.SQLException;
import net.sqlcipher.database.SQLiteDatabase;
import net.sqlcipher.database.SQLiteOpenHelper;
import android.content.ContentValues;  
import android.content.Context;  
import android.database.Cursor;  
import android.util.Log;

/** Helper to the database, manages versions and creation */

    public class DBAdapter extends SQLiteOpenHelper {
        private static final String DATABASE_NAME = "Test";
        private static final int DATABASE_VERSION = 1;

        // Table name
        public static final String TABLE_1 = "Table1";
        public static final String TABLE_2 = "Table2";

        // Column names for Table1 table
        static final String KEY_PASSWORD = "password";
        static final String KEY_USER = "user";

        // Column names for Table2 table
        static final String KEY_EVENT = "event";
        static final String KEY_USERNAME = "username";


        public DBAdapter(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            String sql1 = "create table " + Table_1 + " (" + KEY_USER + " text primary key, " + KEY_PASSWORD + " text not null);";
            String sql2 = "create table " + Table_2 + " (" + KEY_EVENT + " text primary key, " + KEY_USERNAME + " text not null FOREIGN KEY(" + KEY_USERNAME + ") REFERENCES " + TABLE_1 + "(" + KEY_USER + "));";
            db.execSQL(sql1);
            db.execSQL(sql2);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            if (oldVersion >= newVersion){
                return;
            }

            String sql = null;
            if (oldVersion == 1) 
                sql = "alter table " + TABLE_1 + " add note text;";
            if (oldVersion == 2)
                sql = "";

            Log.d("EventsData", "onUpgrade  : " + sql);
            if (sql != null)
                db.execSQL(sql);
        } 

        public Cursor getAllUsers(String username, SQLiteDatabase db){
            return db.query(TABLE_1, ...);
        }

        public Cursor getAllEvents(String event, SQLiteDatabase db){
            return db.query(TABLE_2, ...);
        }
   }

Now you can do all the CRUD methods for both tables. Just make sure that each method has an SQLiteDatabase as an argument as shown in the getAllUsers() method.

Dubitation