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

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

4

Hello @commonsguy - I wanted to mention that we’ve incorporated this feature that we discussed into the sqlcipher_export() function in the SQLCipher 4.0.0 release. The function now takes a second optional parameter for the source database, which is “main” by default.

This lets you do exactly what we proposed: opening up a connection to a new encrypted database, then attaching a plaintext database for migration, and finally copying into the main database, i.e.

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

This has the side benefit of leaving the open database immediately available for other operations (i.e. it doesn’t require closing down a plaintext database connection and re-opening the encrypted database). Let us know what you think.

1 Like
5

This works swimmingly — many thanks!