Managing SQLCipher

Just as we need to think about managing our ordinary SQLite databases, we need to think about doing the same for SQLCipher for Android databases. In this chapter, we will briefly explore some of those common concerns.

Backup and Restore

Earlier in the book, we looked at how to back up and restore a SQLite database. The same mechanisms would be used for import/export operations, to make a copy of the database available for users to take to another machine, for example.

With an encrypted database, things get more complicated. Roughly speaking, there are two scenarios to consider:

1. You want to back up the database and keep it encrypted. This would be good for cases where the passphrase is user-supplied, or for cases where you are going to back up the passphrase as well (by some secure means). Similarly, you might be trying to restore the database that is already encrypted.
2. You want to export the database in a decrypted form, one that can be used by ordinary SQLite clients, not necessarily ones that support the SQLCipher format. Similarly, you might be trying to import a decrypted database and store that data in your encrypted database.

The first scenario works pretty much the same as with regular SQLite databases. If you are copying using filesystem APIs, the data in the database remains in its current form. And, with SQLCipher for Android, the “current form” is encrypted. So, if you copy the database file for an encrypted database, you wind up with a copy of that encrypted database.

The import/export to unencrypted (“plaintext”) databases gets more involved, as we will see in the ImportExportCrypt module of the book’s primary sample project. That sample builds upon the ImportExport sample from the chapter on backing up a database. However, this time, the database is encrypted, using a hard-coded passphrase for simplicity. And, our UI now has five buttons, including options for both plain and encrypted import and export:

Exporting a Plaintext Database

Our app has a SQLCipher for Android database, encrypted by our hard-coded passphrase. We want to export a plaintext copy of that database: same schema, same data, just without the encryption.

SQLCipher for Android has a recipe for doing this. Since it involves a fair bit of manual database manipulation, rather than using Room or the SupportSQLiteDatabase API, we will go “old school” and work with the SQLCipher for Android version of SQLiteDatabase directly. In the end, we wind up with a decryptTo() function on a SQLCipherUtils object that takes:

• A path to our encrypted database file
• A path to the destination plaintext database file
• The passphrase for our encrypted database
• A Context, because this is Android, and you cannot get out of bed in the morning without a Context

After calling this blocking function, our plaintext database should reside at the requested path.

Examining the Utility Function

The function looks a bit nasty:

  fun decryptTo(
    ctxt: Context,
    originalFile: File,
    targetFile: File,
    passphrase: ByteArray?
  ) {
    SQLiteDatabase.loadLibs(ctxt)

    if (originalFile.exists()) {
      val originalDb = SQLiteDatabase.openDatabase(
        originalFile.absolutePath,
        passphrase,
        null,
        SQLiteDatabase.OPEN_READWRITE,
        null,
        null
      )

      SQLiteDatabase.openOrCreateDatabase(
        targetFile.absolutePath,
        "",
        null
      ).close() // create an empty database

      //language=text
      val st =
        originalDb.compileStatement("ATTACH DATABASE ? AS plaintext KEY ''")

      st.bindString(1, targetFile.absolutePath)
      st.execute()
      originalDb.rawExecSQL("SELECT sqlcipher_export('plaintext')")
      originalDb.rawExecSQL("DETACH DATABASE plaintext")

      val version = originalDb.version

      st.close()
      originalDb.close()

      val db = SQLiteDatabase.openOrCreateDatabase(
        targetFile.absolutePath,
        "",
        null
      )

      db.version = version
      db.close()
    } else {
      throw FileNotFoundException(originalFile.absolutePath + " not found")
    }
  }

If you wish to take it on faith that the function works, feel free to skip ahead to the next section.

But, to explain what this function does, let’s take it step by step.

    SQLiteDatabase.loadLibs(ctxt)

SQLCipher for Android contains a complete copy of SQLite with the SQLCipher for Android extensions compiled in. This is native code, compiled via the NDK. When we use the SQLCipher for Android dependency, we get that compiled code in the form of .so files in the AAR. This statement loads the SQLCipher for Android library.

Then, after checking to see if the encrypted database file exists, we open it using the SQLCipher for Android edition of SQLiteDatabase:

      val originalDb = SQLiteDatabase.openDatabase(
        originalFile.absolutePath,
        passphrase,
        null,
        SQLiteDatabase.OPEN_READWRITE,
        null,
        null
      )

The primary difference from what you would do with the framework SQLiteDatabase is that you supply the passphrase to use to open the database.

We then need to set up the target plaintext database. Once again we use the SQLCipher for Android edition of SQLiteDatabase, but this time we use "" as the passphrase, which tells SQLCipher to leave this database decrypted:

      SQLiteDatabase.openOrCreateDatabase(
        targetFile.absolutePath,
        "",
        null
      ).close() // create an empty database

You will notice that we then immediately close this database. We need the database to exist, but we do not need (or want) it to be open — we are going to use it in a slightly different fashion… via ATTACH DATABASE:

      //language=text
      val st =
        originalDb.compileStatement("ATTACH DATABASE ? AS plaintext KEY ''")

      st.bindString(1, targetFile.absolutePath)
      st.execute()

ATTACH DATABASE is part of SQLite’s SQL syntax. It allows you to open two databases at once. The database that you opened using SQLiteDatabase is identified normally; the database that you attach is identified by the name you give it via the AS keyword. So, in this case, we are attaching the empty plaintext database as plaintext. SQLCipher extends ATTACH DATABASE to take a KEY keyword that provides the passphase to use — in this case, we are passing the empty string, to indicate that this database is not encrypted. The ? in the ATTACH DATABASE statement is the fully-qualified path to the database file, which we supply as a query parameter using bindString(). The net effect of executing this SQL statement is that we now have both databases open at once.

SQLCipher comes with a sqlcipher_export() function that we can invoke that copies the contents of the database from the original to the attached one:

      originalDb.rawExecSQL("SELECT sqlcipher_export('plaintext')")
      originalDb.rawExecSQL("DETACH DATABASE plaintext")

The effect of these two statements is to copy almost all of the encrypted data from the original database to the plaintext database, then to detach that plaintext database (thereby closing it).

One piece of data that sqlcipher_export() misses is the schema version. So, our final step is to get the schema version from the encrypted database and apply it to the plaintext database:

      val version = originalDb.version

      st.close()
      originalDb.close()

      val db = SQLiteDatabase.openOrCreateDatabase(
        targetFile.absolutePath,
        "",
        null
      )

      db.version = version
      db.close()

The result is that all of our data resides in our new plaintext database, and both database files are closed when we are done.

This code is far from perfect. It does not have much in the way of recovery from problems, for example. For the purposes of this book example, it works and is reasonable. If you wish to do this sort of work in a production app, though, you should look to improve upon this function.

Using the Utility Function

One thing that SQLCipherUtils.decryptTo() requires is that the original and target both be files. That is because SQLite and SQLCipher for Android both need files. However, RandomDatabase.copyTo() function uses an OutputStream as a target, as the user is choosing the destination of the export via ActivityResultContracts.CreateDocument, so we do not have a file.

So, RandomDatabase.decryptTo() has SQLCipherUtils.decryptTo() decrypt the database to a temporary file, which we then copy to the desired OutputStream:

    fun decryptTo(context: Context, stream: OutputStream) {
      val temp = File(context.cacheDir, "export.db")

      temp.delete()
      
      SQLCipherUtils.decryptTo(
        context,
        context.getDatabasePath(DB_NAME),
        temp,
        PASSPHRASE.toByteArray()
      )

      temp.inputStream().copyTo(stream)
      temp.delete()
    }

That way, we can use RandomDatabase.decryptTo() the same way that we used RandomDatabase.copyTo(). The activity, viewmodel, and repository all follow the same pattern as in the original ImportExport app, just routing the export-plain case through new functions that, in the end, trigger a call to RandomDatabase.decryptTo().

The result, if you run the app and populate the database, then opt to export the plaintext database, is that your exported copy can be opened in a SQLite client without any passphrase.

Importing a Plaintext Database

The other direction — importing a plaintext database — works much the same way. However, this time, we also add a new wrinkle: detecting whether the to-be-imported database really is a plaintext database or not.

Detecting Plaintext

Sometimes, you will be in a situation where you do not know if the database is already encrypted or not. The way to determine if the database is encrypted is to try opening it with the empty string as a passphrase. As we saw earlier, that is how you tell SQLCipher for Android to open an unencrypted database. If we can successfully open the database with an empty passphrase, we know that the database is not encrypted. If we get some sort of problem, we know that either:

• The database is encrypted, or
• The database is unencrypted but the file has been corrupted somehow

For security reasons, SQLCipher for Android does not distinguish between those two cases. That is so attackers cannot learn from a failed open attempt (perhaps with a candidate passphrase, like 12345) whether the database is encrypted or not.

The SQLCipherUtils object has a getDatabaseState() function that applies this technique, returning a State object for three possibilities:

• The database is unencrypted (UNENCRYPTED)
• The database is encrypted (ENCRYPTED)
• The database is missing (DOES_NOT_EXIST)

Just remember that ENCRYPTED is short for ENCRYPTED_OR_POSSIBLY_CORRUPT.

To detect those cases, getDatabaseState() sees if your requested file exists and, if it does, tries opening it with the empty passphrase:

  /**
   * The detected state of the database, based on whether we can open it
   * without a passphrase.
   */
  enum class State {
    DOES_NOT_EXIST, UNENCRYPTED, ENCRYPTED
  }

  fun getDatabaseState(ctxt: Context, dbPath: File): State {
    SQLiteDatabase.loadLibs(ctxt)

    if (dbPath.exists()) {
      var db: SQLiteDatabase? = null

      return try {
        db = SQLiteDatabase.openDatabase(
          dbPath.absolutePath,
          "",
          null,
          SQLiteDatabase.OPEN_READONLY
        )
        db.version
        State.UNENCRYPTED
      } catch (e: Exception) {
        State.ENCRYPTED
      } finally {
        db?.close()
      }
    }

    return State.DOES_NOT_EXIST
  }

Examining the Utility Function

Given a plaintext database, SQLCipherUtils.encryptTo() will encrypt it to a designated database file:

  fun encryptTo(
    ctxt: Context,
    originalFile: File,
    targetFile: File,
    passphrase: ByteArray?
  ) {
    SQLiteDatabase.loadLibs(ctxt)

    if (originalFile.exists()) {
      val originalDb = SQLiteDatabase.openDatabase(
        originalFile.absolutePath,
        "",
        null,
        SQLiteDatabase.OPEN_READWRITE
      )
      val version = originalDb.version

      originalDb.close()

      val db = SQLiteDatabase.openOrCreateDatabase(
        targetFile.absolutePath,
        passphrase,
        null
      )

      //language=text
      val st = db.compileStatement("ATTACH DATABASE ? AS plaintext KEY ''")

      st.bindString(1, originalFile.absolutePath)
      st.execute()
      db.rawExecSQL("SELECT sqlcipher_export('main', 'plaintext')")
      db.rawExecSQL("DETACH DATABASE plaintext")
      db.version = version
      st.close()
      db.close()
    } else {
      throw FileNotFoundException(originalFile.absolutePath + " not found")
    }
  }

As with decryptTo(), encryptTo() is a bit complex, but it has the same basic structure.

We start by loading SQLCipher for Android’s native libraries using SQLiteDatabase.loadLibs(ctxt). Then, assuming that the plaintext database exists, we open it using the empty string as a passphrase (indicating it is plaintext), get the database version, and close the database back up:

      val originalDb = SQLiteDatabase.openDatabase(
        originalFile.absolutePath,
        "",
        null,
        SQLiteDatabase.OPEN_READWRITE
      )
      val version = originalDb.version

      originalDb.close()

We then open or create the encrypted database, using the supplied passphrase:

      val db = SQLiteDatabase.openOrCreateDatabase(
        targetFile.absolutePath,
        passphrase,
        null
      )

Next, we attach the plaintext database using ATTACH DATABASE:

      //language=text
      val st = db.compileStatement("ATTACH DATABASE ? AS plaintext KEY ''")

      st.bindString(1, originalFile.absolutePath)
      st.execute()

We then use a two-parameter form of sqlcipher_export(), saying that we want to “export” the plaintext database into the encrypted one:

      db.rawExecSQL("SELECT sqlcipher_export('main', 'plaintext')")

Finally, we detach the plaintext database, set the version in the encrypted database, and close everything up:

      db.rawExecSQL("DETACH DATABASE plaintext")
      db.version = version
      st.close()
      db.close()

Using the Utility Function

Just as RandomDatabase has its decryptTo() that wraps the SQLCipherUtils equivalent, RandomDatabase also has encryptFrom() that wraps SQLCipherUtils.encryptFrom():

    fun encryptFrom(context: Context, stream: InputStream) {
      val temp = File(context.cacheDir, "import.db")

      temp.delete()

      stream.copyTo(temp.outputStream())

      try {
        when (SQLCipherUtils.getDatabaseState(context, temp)) {
          SQLCipherUtils.State.UNENCRYPTED -> SQLCipherUtils.encryptTo(
            context,
            temp,
            context.getDatabasePath(DB_NAME),
            PASSPHRASE.toByteArray()
          )
          SQLCipherUtils.State.DOES_NOT_EXIST ->
            throw IllegalStateException("Could not find $temp???")
          SQLCipherUtils.State.ENCRYPTED ->
            throw IllegalStateException("Original database appears encrypted!")
        }
      } finally {
        temp.delete()
      }
    }

Both getDatabaseState() and encryptTo() on SQLCipherUtils work with a file, and we are starting with an InputStream. So, we start by copying the contents of that stream to a temporary database file.

We then call getDatabaseState(). In the expected outcome, we get UNENCRYPTED for the state and can then call encryptTo() to encrypt the database and put it in our desired location. If we find any other state, we throw an exception. All of that is wrapped in try / finally, so we can delete the temporary unencrypted database copy.

About That language Comment

In both encryptTo() and decryptFrom() on SQLCipherUtils, we have an odd comment:

      //language=text

This appears before each of our compileStatement() calls:

      //language=text
      val st =
        originalDb.compileStatement("ATTACH DATABASE ? AS plaintext KEY ''")

This stems from a feature of Android Studio called “language injection”. Basically, there are ways for the IDE to interpret a string as being associated with a programming language and to provide syntax validation of the string following the rules of that language.

In this case, by default, Studio thinks that the parameter to compileStatement() is a SQL string, and so it wants to validate that SQL.

This sounds wonderful!

However, Studio’s SQL syntax rules are based on SQLite, not SQLCipher for Android. As a result, Studio does not like our KEY clause:

The language=text comment is a hack to block that validation warning.