Feature Request: rawExecSql(char[]) or rawExecSQL(String, Object[])


#1

AFAIK, the recommended approach to migrate a non-encrypted database to an encrypted one still involves:

ATTACH DATABASE ... AS encrypted KEY '...'
SELECT sqlcipher_export('encrypted')
DETACH DATABASE encrypted

where the first ... is the filesystem path to the encrypted database and the second ... is the passphrase.

In SQLCipher-Android, that winds up with code like:

      db.rawExecSQL("ATTACH DATABASE '"+newFile.getAbsolutePath()+
        "' AS encrypted KEY '"+String.valueOf(passphrase)+"'");
      db.rawExecSQL("SELECT sqlcipher_export('encrypted')");
      db.rawExecSQL("DETACH DATABASE encrypted");

where newFile is a File pointing to the encrypted database and passphrase is a char[].

The problem is that now our passphrase is embedded in a String, and strings are immutable in Java. There is no rawExecSQL(char[]) that we could use to clear out the passphrase after executing the SQL statement. And execSQL() does not appear to work for this, as I tried:

      db.execSQL("ATTACH DATABASE '"+newFile.getAbsolutePath()+
        "' AS encrypted KEY ?", new Object[] { passphrase });
      db.rawExecSQL("SELECT sqlcipher_export('encrypted')");
      db.rawExecSQL("DETACH DATABASE encrypted");

and the resulting database could not be decrypted using that passphrase.

Ideally, one of those would work, so we can clear out any char[] that contains the passphrase.

Thanks for considering this!


#2

Hi @commonsguy - That is a great suggestion. One thought that might work would be to adjust the sqlcipher_export function so that you could specify the target and optionally add a source database to copy from as a second parameter, i.e. sqlcipher_export('target') and sqlcipher_export('target', 'source').

This would allow a developer open up a new empty encrypted database using char[] key material, attach the original plaintext database with an explicit empty key, and finally run sqlcipher_export into the main database from the attached database, e.g.

      db.rawExecSQL("ATTACH DATABASE '"+oldNonEncryptedFile.getAbsolutePath() + "' AS plaintext KEY ''");
      db.rawExecSQL("SELECT plaintext.sqlcipher_export('main', 'plaintext')");
      db.rawExecSQL("DETACH DATABASE plaintext");

What would you think of something like that?

Edit for clarity: SQLCipher doesn’t support this today, this is an idea of how it could be modified to support this scenario in the future.


#3

This seems cool. I hadn’t considered going in that direction.

I assume that main in your code sample represents the opened encrypted database. If so, how do we set up that name? Or is main a magic name for the originally-opened/non-ATTACHed database?

If you didn’t want to mess with sqlcipher_export(), you could perhaps go with sqlcipher_export_to() or something for this two-parameter approach.