We are looking to support both versions v3 and v4 of the sqlcipher db in our app. We want to create any new instances of the db in v4, but want to open pre-existing v3 dbs in v3 (ie without any migration). To do this, here is the sequence of steps that we found working, with a relatively recent version of the sqlcipher code:
sqlite3 handle;
bool success = false;
for (int versionToTry = 4; versionToTry >= 3; versionToTry--)
{
sqlite3_open(dbPath, &handle); //error-checking omitted for clarity
sqlite3_key(handle, key, keyLength); //error-checking omitted for clarity
if (versionToTry == 3)
sqlite3_exec(handle, "PRAGMA cipher_compatibility = 3;", NULL, NULL, NULL); //error-checking omitted for clarity
// check that we can actually operate on the db
sqlite3_exec(handle, "create table if not exists KeyValTable ...");
int errCode = sqlite3_errcode(handle);
if (errCode == SQLITE_OK)
{
success = true;
break;
}
else
{
sqlite3_close(handle);
handle = nullptr;
}
}
// proceed if success is true
I see that the step to actually access the db with a non-empty query (eg “create table…”) is a necessary step; without this, the version determination will be faulty. E.g., if it’s a v3 db, doing sqlite3_exec with an empty query string will succeed. And doing sqlite3_exec(handle, “PRAGMA cipher_compatibility = 3”) on a v4 db also will succeed.
A few questions/requests here:
Is the above sequence the recommended way, or if there a better approach?
Is it guaranteed that every single db-access on a v3 db will fail if it’s not preceded by the compatibility pragma?
** If yes, would appreciate any insight on why this is the case.
In my tests I see sqlcipher returns SQLITE_NOTADB (26) error when I attempt to open db with the wrong version. Is this a guaranteed error that sqlcipher returns for version-mismatch, so we can compare against this exact error-code? This would make the logic more precise, so would be preferred.
Is the above sequence the recommended way, or if there a better approach?
This approach will work in general, however, I would suggest not attempting to create a table within the database to test whether the password is correct. Instead, execute a query such as:
SELECT count(*) FROM sqlite_master;
This table will always be present.
Is it guaranteed that every single db-access on a v3 db will fail if it’s not preceded by the compatibility pragma?
You will need to specify the compatibility PRAGMA for the connection to a v3 database, but that configuration will be valid until you close that connection.
In my tests I see sqlcipher returns SQLITE_NOTADB (26) error when I attempt to open db with the wrong version. Is this a guaranteed error that sqlcipher returns for version-mismatch, so we can compare against this exact error-code? This would make the logic more precise, so would be preferred.
SQLITE_NOTADB will be returned when SQLCipher is not able to open the database with the supplied password material. There are of course other valid reasons why this error might occur. For example, a invalid password would also produce this same result code.
To re-ask one of the questions, with further explanation: Is it guaranteed that every single db-access on a v3 db will fail if it’s not preceded by the compatibility pragma?
I understand that by using “PRAGMA cipher_compatibility = 3;”, I can correctly use a v3 db. My question here is the other way around: If I don’t specify the compatibility pragma, and proceed to access a v3 db (with current sqlcipher code) with a sqlite3_exec call, will the sqlite3_exec call definitely fail with an error?
The reason I ask this is that we are using a “sanity-check” sqlite3_exec call to “shake out” any version-mismatch errors (as you can see from my sample code above). If it so happens that this initial sqlite3_exec call magically succeeds for some reason, even when it should not have (it’s a v3 db but we haven’t specified the compatibility pragma), my logic ends up assuming that it is a v4 db, and proceed to access it as such, without the compatibility pragma. We don’t want subsequent calls to fail because of the version-mismatch while the initial call succeeded. Hope this makes sense!
If the SQLCipher library you are using is version 4+, and if you attempt to open a SQLCipher 3 database without setting a compatibility mode, attempting a cipher_migrate, or manually setting the runtime PRAGMA’s associated with the compatibility mode, it will fail when you attempt to read/write data to the database.