I am City Man

最近需要試圖加密  SQLite database , 然後就發現加密 Db 失敗….

參考微軟的官方文件 Specify the key section ,發現 Sqlite 不支援 DB 加密 ,需要透過第三方套件去執行.

The method for encrypting and decrypting existing databases varies depending on which solution you're using. For example, you need to use the sqlcipher_export() function on SQLCipher. Check your solution's documentation for details.

以下就是簡單的範例.

1. If you want to build a encrypting database, just create it and set your connection string, like this:Data Source = encryptedName.db; Password=YourPassword

2. if you want to change password in a encrypting database

// you can use this in startup.cs
using var changePasswordDb = new DBContext(
    new SqliteConnection(
            new SqliteConnectionStringBuilder()
                {
                     DataSource = "encryptedName.db",
                     Mode = SqliteOpenMode.ReadWriteCreate,
                     Password = oldPassword
                }.ToString()
        ));
changePasswordDb.Database.ExecuteSqlRaw($"PRAGMA rekey = {newPassword}");

// or use SqliteConnection
var connection = new SqliteConnection("Data Source =encryptedName.db.db;Password=yourPassword");
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "PRAGMA rekey = " + newPassword;
command.ExecuteNonQuery();  

3. if you want to encrypt a plaintext SQLite database , SQLCipher is not support directly. so you can use sqlcipher_export() to get the goal. 
    look this How to encrypt a plaintext SQLite database to use SQLCipher (and avoid “file is encrypted or is not a database” errors)

// Example
command.CommandText = "ATTACH DATABASE 'encryptedName.db' AS encrypted KEY 'YourNewPassword';SELECT sqlcipher_export('encrypted');DETACH DATABASE encryptedName;";

補充 : if you have some Performance Issue in Asp.net Core and Entity Framework Core , you need to check to these

• Performance with SQLite and Microsoft.EntityFrameworkCore.Sqlite.Core and SQLitePCLRaw.bundle_e_sqlcipher
• Entity Framework Core connection management
• Microsoft.Data.Sqlite: Pool Connections #13837
• if you have other Performance Issue , you can check this SQLCipher Performance Optimization
   
•  

// 假設遇到 加密 Sqlite DB 效能問題.  可以使用此去避免 Asp.net Core 每次重建 Db Connection.
services.AddSingleton<MyDefaultObjectPoolProvider>();
services.AddSingleton<ObjectPool<SqliteConnection>>(provider =>
               {
                   var dbInfo = provider.GetRequiredService<MySqliteDbInfo>();
                   var objectPoolProvider = provider.GetRequiredService<DefaultObjectPoolProvider>();
                   objectPoolProvider.MaximumRetained = 100;
                   var connectionString = dbInfo.ToString();
                   return objectPoolProvider.Create(new SqliteConnectionObjectPolicy(connectionString));
               });
services.AddScoped<MySqliteConnectionWrapper>();
services.AddScoped( provider =>
                 {
                       var wrapper = provider.GetRequiredService<MySqliteConnectionWrapper>();
                       return new MyDbContext(new DbContextOptionsBuilder<MyDbContext>().UseSqlite(wrapper.Connection).Options);
                });