Cryptography in sqlite

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:3.5.6@aar'

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:

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.:

• link
• link
• link