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.
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");