SQLiteConnection throws error in ctor on iOS 14

[AdamBowler]

When constructing the SQLiteConnection object on a device running iOS 14 the following error is throw

SQLite.SQLiteException: not an error
at SQLite.SQLiteCommand.ExecuteNonQuery () [0x000cd] in <d1788edcec634c19b907698bb77ed371>:0 \n  
at SQLite.SQLiteConnection.Execute (System.String query, System.Object[] args) [0x00039] in <d1788edcec634c19b907698bb77ed371>:0 \n  
at SQLite.SQLiteConnection.SetKey (System.String key) [0x00026] in <d1788edcec634c19b907698bb77ed371>:0 \n  
at SQLite.SQLiteConnection..ctor (SQLite.SQLiteConnectionString connectionString) [0x00126] in <d1788edcec634c19b907698bb77ed371>:0 \n  
at Expenses.Classes.SQLiteCrossPlatform.GetConnection () [0x00079] in /Users/StarLord/Documents/app/MyApp/Classes/SQLiteCrossPlatform.cs:48 "

Here is my code for the ctor

var sqLiteConnectionString = new SQLiteConnectionString(encryptedDbPath, true, AppConstants.KeyEncryption);
var encryptedDb = new SQLiteConnection(sqLiteConnectionString);

This only occurs on iOS 14 and not on any other version of iOS.

[AdamBowler]

After investigating I've found that when this was implemented in our product the dev didn't add the sqlite-net-sqlcipher package or SQLitePCLRaw.bundle_sqlcipher which is what's referenced in the readme on the version of sqlite-net we are on (1.5.231), when they migrated the data into a DB with a key for encryption.

What would this mean to the level of encryption to the database? Would it still be encrypted at all?

After adding the sqlite-net-sqlcipher package I am getting an error stating the file is encrypted or not a database is there anyway to access the old database after adding the sqlite-net-sqlcipher package?

[xhashimks]

This is the same issue in my case also. I was using Sqlite-net-pcl earlier with which i created encrypted db. Now for iOS14, i started using sqlite-net-sqlcipher and when I tried to use the previously created encrypted db using this sqlcipher, i get 'file is not a database' error. I am stuck here.

[sjlombardo]

@AdamBowler if you were previously using sqlite-net-pcl then your database may not even be encrypted even if you provided a key parameter to the constructor. You should extract a database of a device or simulator to check. If it is not encrypted you would need to perform steps to encrypt it. If it is encrypted you would need to either migrate it from SQLCipher 4 or setup backwards compatibility.

[sjlombardo]

@AdamBowler Issue #991 is reporting a similar error, but I have not been able to reproduce a difference in behavior between iOS 13 and 14 with a test application so far. If you or @xhashimks can provide a standalone project that reliably reproduces the reported issue it would be helpful.

[praeclarum]

@AdamBowler if you were not using sql cipher then the database is not encrypted.

The reported exception is troubling and I'll investigate.

[sjlombardo]

While investigating similar reports we have discovered that iOS 13 and 14 appear to have changed the behavior of the system iOS SQLite library such that using PRAGMA key is no longer a no-op and instead causes the underlying file to be encrypted using an undefined scheme. This seems to be private and undocumented, not a public feature of the iOS ecosystem. The encryption is unrelated to and incompatible with SQLCipher. When an application using sqlite-net-pcl with the system library is run on iOS 13+ and triggers a PRAGMA key statement (by passing a key parameter to the constructor) the resulting database will be encrypted with this non-SQLCipher encryption. The behavior of the iOS System Library behavior changed under iOS 14 so that the string "ok" is returned as a row from PRAGMA key, which is consistent with more recent upstream SQLite changes related to that PRAGMA. That is causing the "Row" or "Not an error" exceptions. Under iOS 11 and 12 no encryption functionality is present, so a database from the same application run on those iOS versions will be unencrypted.

[ericsink]

Wow.

[AdamBowler]

Thanks @sjlombardo and @praeclarum for your assistance, I'll inform my company. Good news is we've switched to sqlite-net-pcl so it wont be an issue going forwarded.

[mingxli]

@praeclarum @ericsink @sjlombardo Thank you guys for the great libraries.

Unfortunately, I am having the same issue. After reading through this thread, #955, #972 and here, I am still unable to resolve my issue. I have uploaded a sample project, which demonstrate the "file is not a database" issue. Any help would be greatly appreciated.

[nelutzu91]

Hey guys, adding postKeyAction: c => c.Execute ("PRAGMA cipher_compatibility = 3") as stated here solved my issue.