sqliteentity-framework-coremauisqlcipher

Entity Framework Core and SQLite: 'attempt to write a readonly database'


I am migrating a Xamarin app to MAUI and I had issue with the database.

I have two database plaintext and encrypted, if add password to my app plaintext transform to encrypted and if I remove password encrypted transform to plaintext.

The app starts with plaintext and it works normally, than I transform to encrypted and it also works normally, then I transform back to plaintext and I am adding some data; first entry is getting added normally, but with second and third, I am getting this exception

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 8: 'attempt to write a readonly database'.>

 at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)  
 at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()  
 at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)  
 at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()  
 at Microsoft.Data.Sqlite.SqliteCommand.ExecuteScalar()  
 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteScalar(RelationalCommandParameterObject parameterObject)  
 at Microsoft.EntityFrameworkCore.Sqlite.Migrations.Internal.SqliteHistoryRepository.AcquireDatabaseLock()  
 at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)  
 at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)  
 at Database.Context.EncryptedDbContext.Create() in Database/Context/EncryptedDbContext.cs:line 130  
 at Database.Utils.UnitOfWork..ctor() in Database/Utils/UnitOfWork.cs:line 28  
 at Database.Module.DataServices.DataServiceBase`2.<Update>d__6[[Features.Accounts.Entities.Account, Features, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null],[Database.Entities.AccountEntity, Database, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]].MoveNext() in Module/DataServices/DataServiceBase.cs:line 44

This is my setup, connection with plaintext I am getting here:

private SqliteConnection GetConnection(string databasePath)
{
    var connection = new SqliteConnection($"Filename={databasePath};Mode=ReadWriteCreate;");
    connection.Open();

    return connection;
}

Connection with encrypted I am getting here:

private SqliteConnection GetConnection(string databasePath)
{
    var connection = new SqliteConnection($"Filename={databasePath};Mode=ReadWriteCreate;");
    connection.Open();
            
    var command = connection.CreateCommand();
    command.CommandText = "SELECT quote($password);";
    command.Parameters.AddWithValue("$password", Password);

    var quotedPassword = (string) command.ExecuteScalar();
            
    command.CommandText = "PRAGMA key = " + quotedPassword;
    command.Parameters.Clear();

    command.ExecuteNonQuery();
            
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "PRAGMA cipher_compatibility = 3;";
        cmd.ExecuteNonQuery();
    }

    return connection;
}

Here I am transform database from plaintext to encrypted:

private SqliteConnection GetEncryptedConnection(string plainTextDbPath, string encryptedDbPath)
{   
    var connection = new SqliteConnection($"Filename={plainTextDbPath};Mode=ReadWriteCreate;");
    connection.Open();
            
    var attachCommand = connection.CreateCommand();
    attachCommand.CommandText = $"ATTACH DATABASE '{encryptedDbPath}' AS {Encrypted} KEY '{Password}';";
    attachCommand.ExecuteNonQuery();
            
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = $"PRAGMA {Encrypted}.cipher_compatibility = 3;";
        cmd.ExecuteNonQuery();
    }

    attachCommand.CommandText = $"SELECT sqlcipher_export('{Encrypted}');";
    attachCommand.ExecuteNonQuery();

    attachCommand.CommandText = $"DETACH DATABASE {Encrypted};";
    attachCommand.ExecuteNonQuery();

    return connection;
}

Here I am transforming the database from encrypted back to plaintext:

private SqliteConnection GetDecryptedConnection(string plainTextDbPath, string encryptedDbPath)
{                
    var connection = new SqliteConnection($"Filename={encryptedDbPath};Mode=ReadWriteCreate");
    connection.Open();
            
    var command = connection.CreateCommand();
    command.CommandText = $"PRAGMA key = '{_password}'";

    command.ExecuteNonQuery();
            
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "PRAGMA cipher_compatibility = 3;";
        cmd.ExecuteNonQuery();
    }
            
    command.CommandText = $"ATTACH DATABASE '{plainTextDbPath}' AS {PlainText} KEY '';";
    command.ExecuteNonQuery();

    command.CommandText = $"SELECT sqlcipher_export('{PlainText}');";
    command.ExecuteScalar();

    command.CommandText = $"DETACH DATABASE {PlainText};";
    command.ExecuteNonQuery();

    return connection;
}

I know that is no issue with accessing the filesystem, and also not really sure if it is migration issue, also this issue same on Android and iOS.


Solution

  • It was issue with pooling on EF Core, so just disabling it in my connection strings helped me

    var connection = new SqliteConnection($"Filename{databasePath};Mode=ReadWriteCreate;Pooling=False");