Database::isEncrypted is not correct with sqlcipher / sqlite-m-c unencrypted first bytes

[jagerman]

There's a bit of a story behind this one.

On Apple iOS, there is some sort of OS-level baked in auto-detection of SQLite databases to add a special case to locks that are allowed to be held when iOS suspends a process to the background: if any file locks are held (such as the ones sqlite holds) on a file when the suspend happens, iOS kills the process instead of suspending it. But there is a loophole for sqlite: if iOS thinks the file is an SQLite database, it doesn't kill it, and instead allows the suspend with the lock held. It detects this by looking at the first 24 bytes of the file, looking for the magic "SQLite format 3\0" 16-byte header and then, apparently, the current expected constants in the next 8 bytes. And in typical Apple fashion, this isn't documented anywhere, can't be opted into, and is forced on you whether you want it or not.

As a consequence, SQLCipher and SQLite Multiple Ciphers have a "don't-encrypt-the-first-n-bytes" option that is effectively required by any code that wants to target iOS. (For SQLCipher this actually needs to be the first 32 bytes because its AES-CBC encryption uses 16-byte blocks; SQLite Multiple Ciphers non-sqlcipher mode allow applying this to just the first 24 bytes). This mode also prevents replacing the 16-byte magic header with the password nonce. (This is rather unfortunately as it means the nonce has to be stored outside the database, because there simply is no other place inside the sqlite3 file where it can be safely stored).

The consequence of all of this is that portable code that wants to usable on iOS generally ends up doing this everywhere (or if not in all versions, then special casing it for iOS).

This is where Database::isUnencrypted breaks: it simply looks for "SQLite format 3\0" at the beginning of the file, and that simply isn't a usable indicator as to whether the database is actually encrypted all thanks to Apple's magic OS hacks.

As for an alternative: really the only reliable way to tell given the above is to attempt to open it without a key and force a query to read it (e.g. PRAGMA user_version, or perhaps more robust, selecting something from sqlite_master). But I really don't know if that is suitable for what this method is trying to achieve.