Cryptography in sqlite

2

I need to save some data in SQLite, but this data needs to be encrypted, and, when the user needs it, decrypted. What is the best way to do this?

    
asked by anonymous 08.03.2017 / 17:52

1 answer

5

You can use SQLCipher for Android

link

SQLCipher uses 256-bit AES encryption to encrypt the bank with 1kb pages. The library has the 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'll explain the steps in 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 in Gradle by adding:

compile 'net.zetetic:android-database-sqlcipher:[email protected]'

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

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

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

We define a class with the information of tables:

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 define a Helper class using net.sqlcipher.database instead of android.database.sqlite , as sqlcipher has its own implementation of SQLite, 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 Helper to access the database in much the same way we would do with the native Android SQLite driver:

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

We only have a few things to change:

  • Use the net.sqlcipher.database.SQLiteDatabase;
  • Add in the onCreate method of your MainActivity the SQLiteDatabase.loadLibs(this); line to load the dependencies of the sqlcipher before using it (an extra 7MB in the APK)
  • A% example example using SQLCipher looks more or less 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.:

    08.03.2017 / 17:56