Android - SQLCipher web server database not being read/decrypted properly

Question

I have an application that can successfully download an unencrypted database from a web server and use that database for my purposes. However, I know that this isn't safe and 3rd party applications can see this database if the device is rooted. So I decided to encrypt it using SQLCipher.

My application can create and read its own SQLCipher database with no problems. So what I did next was to run the same application over at the emulator, pull the database using adb, and using this guide, I created a dump file and then zipped it. I also followed the sample code there that enables the application to download the zip file from the web server and use it.

However, the problem arises after the database has been downloaded and extracted by the application. It seems to me that the application was not able to download it properly OR the database was not decrypted.

However, upon reviewing how the database was converted from a .db file to a .dmp to a .zip, I'm also beginning to think that when the shell command $ sqlite3 sampleDB.dp .dump > DBDump.dmp command was not being executed properly since it is a sqlite3 command and the database was already encrypted (SQLite Database Browser 2.0 for Mac OSX says that the db is either encrypted or not a database file). Therefore, since it's not a sqlite3 database, the dump file was not created properly and thus, the application can't unzip and execute the dump file properly.

Another solution I tried was to just simply upload the .db file and download that. However, I've had no success.

Does anyone have an idea on how to download and decrypt a SQLCipher database file?

So far, these are my codes (Note that all imports are net.sqlcipher and not android.database.sqlite):

The AsyncTask that is called on the onCreate Method, this is the one that initiates the download:

private class Connection extends AsyncTask<String, Void, String> {

    @Override
    protected void onPreExecute() {
        super.onPreExecute();
        Log.d("Hi", "Download Commencing");
    }

    @Override
    protected String doInBackground(String... params) {
        myDroidSQLDatabase = new MyDroidSQLDatabase(LogInPage.this);
        myDroidSQLDatabase.open();
        Log.d("Hi", "Downloading");
        return "Executed!";
    }

    @Override
    protected void onPostExecute(String result) {
        super.onPostExecute(result);
        Log.d("Hi", "Done Downloading.");
    }
}

MyDroidSQLDatabase class:

public class MyDroidSQLDatabase {
  private SQLiteOpenHelper sqlLiteOpenHelper;
  private SQLiteDatabase sqlLiteDatabase;

  public MyDroidSQLDatabase(Context context) {
            sqlLiteOpenHelper = new MyDroidSQLiteOpenHelper(context);       
  }

  public void open() {
    sqlLiteDatabase = sqlLiteOpenHelper.getWritableDatabase(DBAdapter.key);
  }

  public void close() {
    sqlLiteDatabase.close();
  }

  public SQLiteDatabase getSqlLiteDatabase() {
    return sqlLiteDatabase;
  }
}

Here is my MyDroidSQLiteOpenHelper class

public class MyDroidSQLiteOpenHelper extends SQLiteOpenHelper {
  private Context context;
  private static final String __DB_NAME = "system.db";
  private static final int __DB_VERSION = 1;

  public MyDroidSQLiteOpenHelper(Context context) {
    super(context, __DB_NAME, null, __DB_VERSION);
    this.context=context;
  }

  @Override
  public void onCreate(SQLiteDatabase sqlLiteDb) {
    try {
      SQLiteDBDeploy.deploy(sqlLiteDb,"http://192.168.1.4/dbtest/system.db");
    } catch (IOException e) {
        //Log.e(MyDBAppActivity.TAG,e.getMessage(),e);
        throw new Error(e.getMessage());
    }
  }

  @Override
  public void onUpgrade(SQLiteDatabase sqlLiteDb, int oldVersion, int newVersion) {

  }
}

And here is SQLiteDBDeploy class:

public class SQLiteDBDeploy {
  private static final String TAG = "SQLiteDBDeploy";
  private static List<String> ignoreSQLs;

  static {
    ignoreSQLs = new LinkedList<String>();
    ignoreSQLs.add("--");
    ignoreSQLs.add("begin transaction;");
    ignoreSQLs.add("commit;");
    ignoreSQLs.add("create table android_metadata (locale text);");
    ignoreSQLs.add("create table \"android_metadata\" (locale text);");
    ignoreSQLs.add("insert into android_metadata values('en_us');");
    ignoreSQLs.add("insert into \"android_metadata\" values('en_us');");
  }  

  /**
   * Deploys given zip file in SQLiteDatabase
   * 
   * @param sqlLiteDb
   *            the database
   * @param context
   *            to use to open or create the database
   * @param dbName
   *            dump zip file
   * @throws IOException
   */
  public static void deploy(SQLiteDatabase sqlLiteDb, Context context, String dbName) throws IOException {
    Log.i(TAG, "reading zip file: " + dbName);
    InputStream dbStream = context.getAssets().open(dbName);

    deploy(sqlLiteDb, dbStream);

    dbStream.close();
  }

  /**
   * Deploys given zip file url in SQLiteDatabase
   * 
   * @param sqlLiteDb
   *            the database
   * @param dbUrl
   *            dump zip file url
   * @throws IOException
   */
  public static void deploy(SQLiteDatabase sqlLiteDb, String dbUrl) throws IOException {
    Log.i(TAG, "reading url: " + dbUrl);

    HttpURLConnection c = (HttpURLConnection) new URL(dbUrl).openConnection();
    c.setRequestMethod("GET");
    c.setDoOutput(true);
    c.connect();
    InputStream dbStream = c.getInputStream();

    deploy(sqlLiteDb, dbStream);

    dbStream.close();
    c.disconnect();
  } 

  /**
   * Deploys given dump  file stream in SQLiteDatabase
   * 
   * @param sqlLiteDb the database
   * @param dbStream stream to read dump data 
   * @throws IOException
   */
  private static void deploy(SQLiteDatabase sqlLiteDb, InputStream dbStream) throws IOException {
    ZipInputStream zis = new ZipInputStream(new BufferedInputStream(dbStream));
    ZipEntry entry = null;


    while ((entry = zis.getNextEntry()) != null) {
        Log.i(TAG, "deploying zip entry: " + entry);
        InputStreamReader dbReader = new InputStreamReader(zis);
        deploy(sqlLiteDb, dbReader);
    }
  }

  /**
   * Deploys given stream in SQLiteDatabase
   * 
   * @param sqlLiteDb
   *            the database
   * @param dbReader
   *            stream to read dump SQL statements
   * @throws IOException
   * @throws SQLException
   */
  private static void deploy(SQLiteDatabase sqlLiteDb, InputStreamReader dbReader) throws IOException {
    String sqlLine = null;
    StringBuffer sqlBuffer = new StringBuffer();
    BufferedReader bufferedReader = new BufferedReader(dbReader);

    sqlLiteDb.beginTransaction();

    try {
        while ((sqlLine = bufferedReader.readLine()) != null) {
            String sql = sqlLine.trim();
            if (!isIgnoreSQL(sql)) {
                if (sql.endsWith(";")) {
                    sqlBuffer.append(sql);
                    String execSQL = sqlBuffer.toString();
                    Log.d(TAG, "running sql=>" + execSQL);
                    sqlLiteDb.execSQL(execSQL);
                    sqlBuffer.delete(0, sqlBuffer.length());
                } else {
                    if (sqlBuffer.length() > 0) {
                        sqlBuffer.append(' ');
                    }
                    sqlBuffer.append(sql);
                }
            }
        }
        sqlLiteDb.setTransactionSuccessful();
    } finally {
        sqlLiteDb.endTransaction();
    }
  }

  /**
   * Returns true if the given SQL statement is to be ignored
   * @param sql SQL statement
   * @return
   */
    private static boolean isIgnoreSQL(String sql) {
    if (sql.length() == 0) {
        return true;
    }

    String lowerSQL = sql.toLowerCase();
    for (String ignoreSQL : ignoreSQLs) {
        if (lowerSQL.startsWith(ignoreSQL)) {
            return true;
        }
    }
    return false;
  }
}

Right now, what I'm thinking of is to create a function that would decrypt the database first before executing it. However, I do not know where to place/call and how to write the decrypt function.

Any ideas anyone?

Also, downloading the community edition of sqlcipher to run shell commands is not a viable solution since it requires a fee.

Answer 1

Deployment of a database over a network is an option if that is a requirement. I would recommend verifying the state of your encrypted database with the SQLCipher command shell, instructions for building on Linux or OS X can be found here this way you can rule out invalid downloads during your process.