Sqlite-net: SQLiteException: file is not a database

[fgiacomelli]

Steps to reproduce

We was not able to reproduce the problem explicitly.

Actual behaviour

We have a lot of crashes logged in appcenter about the problem "file is not a database" and others with different messages, but I suspect that all the problems could be related to the lock mechanism.
Others sqlite related errors are:

• SQLite.SQLiteException: Could not open database file:

• SQLite.SQLiteException: Busy

• System.InvalidOperationException: Cannot begin a transaction while already in a transaction

we don't use async connection inside transactions, using a unique encrypted connection in the app, created with:

• OpenFlags: SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex

Configuration

Operating system:
Android, all versions from API 25 to API 29

Library Version:
1.7.335

.NET Version:
NETStandard.Library 2.0.3

StackTrace

020-05-11 07:58:17.5910|Trace|BugReportingManager - CurrentDomain_UnhandledException - line: 149 | * Current Domain Unhandled Exception *
2020-05-11 07:58:17.5928|Error|BugReportingManager - PreparaCrashReport - line: 134 | SQLite.SQLiteException: file is not a database
at SQLite.SQLite3.Prepare2 (SQLitePCL.sqlite3 db, System.String query) [0x00021] in C:\Jenkins\workspace\1.0.76\sqlite-net\src\SQLite.cs:4530
at SQLite.SQLiteCommand.Prepare () [0x00011] in C:\Jenkins\workspace\1.0.76\sqlite-net\src\SQLite.cs:3219
at SQLite.SQLiteCommand+d__121[T].MoveNext () [0x00079] in C:\Jenkins\workspace\1.0.76\sqlite-net\src\SQLite.cs:3115 at System.Collections.Generic.List1[T].AddEnumerable (System.Collections.Generic.IEnumerable1[T] enumerable) [0x00059] in /Users/builder/jenkins/workspace/archive-mono/2019-10/android/release/external/corefx/src/Common/src/CoreLib/System/Collections/Generic/List.cs:1108 at System.Collections.Generic.List1[T]..ctor (System.Collections.Generic.IEnumerable1[T] collection) [0x00062] in /Users/builder/jenkins/workspace/archive-mono/2019-10/android/release/external/corefx/src/Common/src/CoreLib/System/Collections/Generic/List.cs:87 at System.Linq.Enumerable.ToList[TSource] (System.Collections.Generic.IEnumerable1[T] source) [0x00018] in /Users/builder/jenkins/workspace/archive-mono/2019-10/android/release/external/corefx/src/System.Linq/src/System/Linq/ToCollection.cs:30
at SQLite.SQLiteCommand.ExecuteQuery[T] () [0x0001c] in C:\Jenkins\workspace\1.0.76\sqlite-net\src\SQLite.cs:3079
at SQLite.SQLiteConnection.Query[T] (System.String query, System.Object[] args) [0x00008] in C:\Jenkins\workspace\1.0.76\sqlite-net\src\SQLite.cs:1071

[Raystorm7]

Same problem here.
We have a encrypted DB and we use the sqlite-net-sqlcipher package.

[akuehntopf]

Happens here as well in our app.
We are using sqlite-net-sqlcipher.

Error:

Exception in async method.SQLite.SQLiteException: file is not a database
at SQLite.SQLite3.Prepare2 (SQLitePCL.sqlite3 db, System.String query) [0x0001b] in :0
at SQLite.SQLiteCommand.Prepare () [0x00011] in :0

Repro steps:

1. Install sqlite-net-sqlcipher 1.5.231
2. Start app so that it creates its database file
3. Upgrade to latest sqlite-net-sqlcipher 1.7.335
4. Start app. Exception is triggered

[juanmalm]

Same problem here on UWP

[Mcgeecorp]

Same here on Windows 10 targeting .Net Framework 4.7.2

Nuget packages:

• sqlite-net-sqlcipher v1.7.335
• SQLItePCLRaw.bundle_sqlcipher v1.1.4

Stepping back to the previous stable sqlite-net-sqlcipher release (v1.6.292) works fine.

[xhashimks]

@Mcgeecorp
In 1.6292 - we get issue https://github.com/praeclarum/sqlite-net/issues/865.

Hence, I tried 1.7-beta and it worked. But i am facing issue while running this in iOS14 beta. Throwing below exception

{SQLite.SQLiteException: Row
at SQLite.SQLiteCommand.ExecuteNonQuery () [0x000ca] in :0
at SQLite.SQLiteConnection.Execute (System.String query, System.Object[] args) [0x00039] in :0
at SQLite.SQLiteConnection.SetKey (System.String key) [0x00026] in :0
at SQLite.SQLiteConnection..ctor (SQLite.SQLiteConnectionString connectionString) [0x0011a] in :0
at SQLite.SQLiteConnectionWithLock..ctor (SQLite.SQLiteConnectionString connectionString) [0x0000b] in :0
at SQLite.SQLiteConnectionPool+Entry.Connect () [0x0001c] in :0
at SQLite.SQLiteConnectionPool.GetConnection (SQLite.SQLiteConnectionString connectionString) [0x00048] in :0
at SQLite.SQLiteAsyncConnection.GetConnection () [0x00005] in :0
at SQLite.SQLiteAsyncConnection+<>c__DisplayClass32_01[T].b__0 () [0x00000] in :0 at System.Threading.Tasks.Task1[TResult].InnerInvoke () [0x0000f] in /Library/Frameworks/Xamarin.iOS.framework/Versions/Current/src/Xamarin.iOS/external/corert/src/System.Private.CoreLib/src/System/Threading/Tasks/Future.cs:534
at System.Threading.Tasks.Task.Execute () [0x00000] in /Library/Frameworks/Xamarin.iOS.framework/Versions/Current/src/Xamarin.iOS/external/corert/src/System.Private.CoreLib/src/System/Threading/Tasks/Task.cs:2319 }

var sqlConnectionOptions = new SQLiteConnectionString(dbPath, true, "TestKey");
database = new SQLiteAsyncConnection(sqlConnectionOptions);
database.CreateTableAsync().Wait(); // Exception throwing in this line

public class UserData
{
[PrimaryKey]
public string UserId { get; set; }
public int RollNo { get; set; }
public string Created { get; set; }
public string Updated { get; set; }
public bool IsActive { get; set; }
}

[praeclarum]

Hi everyone, I'm looking at this now, sorry for the trouble.

@fgiacomelli are you also using an encrypted database as others?

[fgiacomelli]

Hi @praeclarum , yes I'm already using an encrypted database

[ericsink]

@praeclarum if this starts looking a problem down near SQLitePCLRaw, lemme know how I can help.

[xhashimks]

@praeclarum In my case, I am using an encrypted database created using Sqlite-net-pcl 1.5.231. Now I am using the sqlite-pcl-sqlcipher 1.7.335 as part of the iOS14 qualification. So while running the app, when this new plugin tries to access the already encrypted DB, I am getting the file is not a database exception.

[sjlombardo]

I believe that the SQLiteException: Row issue may be unrelated to the other problems. The SetKey methods in SQLite.cs should use ExecuteScalar instead of Execute to handle the "ok" result set that is returned from PRAGMA key as of SQLCipher 4.3.0.

[xhashimks]

@sjlombardo I am not using that version now because that will not work properly for iOS14. Hence I upgraded back to 1.7.335.

@praeclarum - Sorry for re-writing this again, as I don't want the actual issue get covered with other comments. So finally below is the my current issue.

I was using an encrypted database created using Sqlite-net-pcl 1.5.231. Now I am using the sqlite-pcl-sqlcipher 1.7.335 as part of the iOS14 qualification. So while running the app, when this new plugin tries to access the already encrypted DB which was created using the sqlite-net-pcl, I am getting the file is not a database exception.

[sjlombardo]

@xhashimks yes that is why I was saying the Row error is different. As far as I understand it if you were previously using sqlite-net-pcl then your database is likely not even 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.

[praeclarum]

Hello again! After some investigation I have good news and bad news.

Good News!

The bug is understood! The problem lies with opening Cipher v3 databases using the now packages v4 SQLCipher. Fortunately, there are workarounds that you can use today with 1.7 of this library.

You can do this using code by issue PRAGMAs to the connection string. There are two options:

1. Enter a compatibility mode

You can tell SQL to always use v3 compatibility:

var cstring = new SQLiteConnectionString (
                path,
                storeDateTimeAsTicks: true,
                key: key,
                postKeyAction: c => {
                    c.Execute ("PRAGMA cipher_compatibility = 3");
                }
                );

2. One-time convert your database to v4

If you convert your database to v4 compatibility, then everything will just work. There is a full guide on how to do this for cipher.

var cstring = new SQLiteConnectionString (
                path,
                storeDateTimeAsTicks: true,
                key: key,
                postKeyAction: c => {
                    c.ExecuteScalar<int> ("PRAGMA cipher_migrate");
                }
                );

The bad news...

I am not sure how to handle this automatically. I could take either of the two options (I prefer the compatibility mode one), but they both have downsides.

The downside of compat is that I will force even new databases to use v3 options and not benefit from new enhancements.

The downside of the conversion is that it is trick to do if people override the defaults and it comes with a performance penalty if I run the conversion every time you open the database.

Please comment!

I am not sure how to proceed. Do you want automatic handling of this? If so, which of this methods should be the default. Please comment again :-)

[xhashimks]

@praeclarum
Thank you so much for the solutions you have provided. However, I tried the compatibility option and still facing the issue.

First of all, i was using earlier encrypted SQlite db created using sqlite-net-pcl version 1.5.231. Now when I try to access the same db using Sqlite-net-sqlcipher version 1.7.335, getting file is not a database error. Can you please help me in this? I am stuck here. Attaching the sample encrypted db created using sqlite-net-pcl 1.5.231. Remove the .zip extension from the attached file.
pwd of db while creation : test_key

testDB.db3.zip

Code snippet for SQlite-net-pcl
database = new SQLiteAsyncConnection(dbPath, true, "test_key");

Code snippet used for sqlite-pcl-sqlcipher

 var sqliteConnectionString = new SQLiteConnectionString(dbPath, true, "test_key",
                postKeyAction: c => { c.Execute("PRAGMA cipher_compatibility = 3");});
            database = new SQLiteAsyncConnection(sqliteConnectionString);

[sjlombardo]

@praeclarum -A strong case should be made for not doing anything automatically. It should be up to the application to handle how it will handle the update.

For example, in addition to the stated issue of automatically setting compatibility = 3, that users will never get the additional security benefits, there are other problems. Setting that flag on behalf of the application will not work for anyone using custom SQLCipher settings. As we saw with the automatic WAL mode issue this will likely break things for a number of users.

In addition, 1.7.335 has been available for 3 months, and in beta before that starting almost a year ago. Anyone who has created a new database using 1.7.335 has already created databases using SQLCipher 4. If you set the compatibility PRAGMA automatically it will cause the reverse problem, where those apps currently using SQLCipher 4 will not be able to open their databases anymore.

Separate issues exist for PRAGMA cipher_migrate. It is very undesirable to call cipher_migrate every time a connection is open. It would effectively double the time that it takes to open every connection (which is already deliberately extremely slow due to key derivation). For any attempt to open a database with an incorrect key (e.g. with an application that allows the user to input a key at open) it could slow things down by a factor of 3-4x. As a result, while more complicated, the recommended protocol is to attempt to open the database using the standard key, and only if that fails, then attempt to migrate. This isolates the performance impact as much as possible.

That said, any use of cipher_migrate should really be coordinated by the application. Migration of databases requires substantial time and storage space to accomplish. For large databases it could take a couple minutes or more to migrate as every page is re-encrypted to a temporary file and then moved back. Typically an application using SQLCipher will provide a mechanism to inform the user that an upgrade is occurring, execute the migration in the background, display a processing screen, and take measures to prevent other attempted access to the database during that time. None of those tasks can be effectively implemented at the library level. Finally, anyone using custom SQLCipher settings would again be out of luck with this approach.

While this change may be unexpected to some users, the update to 1.7.335 does include a major version update for SQLCipher. Developers must be cognizant of their implementation choices, especially with respect to security and a component like SQLCipher. They should decide on the best approach for their application, with a strong preference to coordinated migration to more secure database settings, and explicitly opt-in to being less secure.

[xhashimks]

@praeclarum, @sjlombardo
Sorry for posting again. I am stuck here and having issue with this in upgrade scenarios. Please help

https://github.com/praeclarum/sqlite-net/issues/955#issuecomment-678955097

[sjlombardo]

@xhashimks I checked the file you uploaded and it does not appear to be a standard SQLCipher 3 database (at least one that can be opened with the provided key). Thus, migration or compatibility mode will not work. You said you weren't using sqlite-net-sqlcipher before, how were you bringing in the library? Were you using any other settings?

[xhashimks]

@sjlombardo Thank you so much for your findings. I was using an 1.5.231 version of Sqlite-net-pcl in my iOS application. But this is causing exceptions in iOS14, so thought of upgrading the library. On checking the github, I saw the sqlite-net-sqlcipher for encrypted db and started using it.
And this was the code used for creating encrypted db using sqlite-net-pcl 1.5.231 version

database = new SQLiteAsyncConnection(dbPath, true, "test_key");

[chaoyebugao]

Same problem on iOS under Xamarin development. Problem exists like: the sqlcipher db on app's 1st run and installation, every thing is okay. Then when we run with second build/debug, file is not a database exception occurred. On the other platform, Android all is fine, so odd!

[YMobileAppDev]

Hi There,

I applied the workaround that you have suggested above, but I am still getting the "file is not a database" issue.
Could you please check & let me know if there is any other workaround?

[sjlombardo]

@YMobileAppDev you'd need to provide more details about the problem. You should also check out #986 - it is possible that if you were not properly including SQLCipher in a previous version of your app (e.g. due to incorrect or conflicting bundle packages) then you might not have been using SQLCipher at all.

[YMobileAppDev]

Hi,

We are getting an error when we are trying to create a database connecting using below code.
SQLiteConnection Database = new SQLiteConnection(new SQLiteConnectionString(databasePath: "system.db", openFlags: SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.FullMutex | SQLiteOpenFlags.Create, storeDateTimeAsTicks: false, key: "xyz"));