Encryption in the sqlite

Question

I am needing to store some data in Sqlite, but this data needs to be encrypted, and when the user needs it, decrypted. What’s the best way to do that?

Answer 1

You can use Sqlcipher for Android

https://www.zetetic.net/sqlcipher/sqlcipher-for-android/

Sqlcipher uses 256-bit AES encryption to encrypt the database with 1kb pages. The library has methods getReadableDatabase() and getWritableDatabase() with the requirement of a password, the password itself is not the key that encrypts and decrypts the bank, but that generates the key from this password. Therefore, such a password can be secured on a server to be provided to the app so that it can access the local database.

I will explain the steps of how to use the library in Android Studio in a new project.

You can add Sqlcipher to the application by adding it as a dependency on Gradle when adding:

compile 'net.zetetic:android-database-sqlcipher:3.5.6@aar'

in the archive build.gradle. Remembering that version 3.5.6 is the latest version released at the time of this writing, you can check the versions available on Repo Maven.

By doing the re-sync and run the app you can see that the APK has grown a bit in size.

After that you can already create a Sqlite database to use, if you do not know how you can learn this procedure directly from Google’s Android training on databases.

We define a class with table information:

package com.example.sqlcipher;

import android.provider.BaseColumns;

public final class FeedReaderContract {
    public FeedReaderContract() {}

    /* Inner class that defines the table contents */
    public static abstract class FeedEntry implements BaseColumns {
        public static final String TABLE_NAME = "news";
        public static final String COLUMN_NAME_ENTRY_ID = "news_id";
        public static final String COLUMN_NAME_TITLE = "title";
        public static final String COLUMN_NAME_SUBTITLE = "subtitle";
    }
}

And we set a Helper class using the net.sqlcipher.database instead of android.database.sqlite, because sqlcipher has its own Sqlite implementation, and without onDowngrade() because sqlcipher only supports Android 2.1 onwards.

package com.example.sqlcipher;

import android.content.Context;

import net.sqlcipher.database.SQLiteDatabase;
import net.sqlcipher.database.SQLiteOpenHelper;

public class FeedReaderDbHelper extends SQLiteOpenHelper {
    private static FeedReaderDbHelper instance;

    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "FeedReader.db";

    private static final String TEXT_TYPE = " TEXT";
    private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + FeedReaderContract.FeedEntry.TABLE_NAME + " (" +
        FeedReaderContract.FeedEntry._ID + " INTEGER PRIMARY KEY," +
        FeedReaderContract.FeedEntry.COLUMN_NAME_ENTRY_ID + TEXT_TYPE + "," +
        FeedReaderContract.FeedEntry.COLUMN_NAME_TITLE + TEXT_TYPE + "," +
        FeedReaderContract.FeedEntry.COLUMN_NAME_SUBTITLE + TEXT_TYPE +
        " )";

    private static final String SQL_DELETE_ENTRIES =
        "DROP TABLE IF EXISTS " + FeedReaderContract.FeedEntry.TABLE_NAME;

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

    static public synchronized FeedReaderDbHelper getInstance(Context context) {
        if (instance == null) {
            instance = new FeedReaderDbHelper(context);
        }
        return instance;
    }

    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate(db);
    }
}

From now on we would use the Helper to access the database almost the same way we would with the native Android Sqlite driver:

SQLiteDatabase db = FeedReaderDbHelper.getInstance(this).getWritableDatabase("somePass");

We only have a few things left to change:

1. Use the library net.sqlcipher.database.SQLiteDatabase;
2. Add in the method onCreate of his MainActivity the line SQLiteDatabase.loadLibs(this); to load the sqlcipher dependencies before using it (an extra 7MB in APK)

One MainActivity example using Sqlcipher looks something like this:

package com.example.sqlcipher;

import android.app.Activity;
import android.content.ContentValues;
import android.os.Bundle;
import android.util.Log;
import net.sqlcipher.Cursor;
import net.sqlcipher.database.SQLiteDatabase;

public class MainActivity extends Activity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        SQLiteDatabase.loadLibs(this); // para carregar as libs
        insertSthToDb(); // para testar a inserção no banco
    }

    private void insertSthToDb() {
        // getInstance com senha
        SQLiteDatabase db = FeedReaderDbHelper.getInstance(this).getWritableDatabase("somePass");

        // Valores a serem adicionados
        ContentValues values = new ContentValues();
        values.put(FeedReaderContract.FeedEntry.COLUMN_NAME_ENTRY_ID, 1);
        values.put(FeedReaderContract.FeedEntry.COLUMN_NAME_TITLE, "Easter Bunny has escaped!");
        values.put(FeedReaderContract.FeedEntry.COLUMN_NAME_SUBTITLE, "A thrilling story which proves how fragile our hearts are...");

        // Inserção no banco
        db.insert(FeedReaderContract.FeedEntry.TABLE_NAME, null, values);

        // Lê do banco e escreve o número de linhas registradas no banco
        Cursor cursor = db.rawQuery("SELECT * FROM '" + FeedReaderContract.FeedEntry.TABLE_NAME + "';", null);
        Log.d(MainActivity.class.getSimpleName(), "Rows count: " + cursor.getCount());
        cursor.close();
        db.close();

        // Essa linha carrega sem passar a senha correta e gera o erro: file is encrypted or is not a database: create locale table failed
        //db = FeedReaderDbHelper.getInstance(this).getWritableDatabase("");
    }
}

Ref.:

• http://lomza.totem-soft.com/tutorial-add-sqlcipher-to-your-android-app/
• https://github.com/sqlcipher/android-database-sqlcipher
• https://androidbycode.wordpress.com/2015/02/18/android-database-encryption-using-sqlcipher/