How to Create a Local Secret Data Vault for .NET Applications: SQLiteCipher with Dapper

Using a Configuration File

You can also store secrets in a local configuration file and encrypt it.

Create a JSON file (e.g., appsettings.secret.json):

{
    "SecretKey": "YourSecretValue"
}

Also, Update appsetting.json

"ConnectionStrings": {
  "DefaultConnection": "localvault.db"
}

Load the Configuration at program.cs:

using LocalVaultWebApi.Data;
using Microsoft.Extensions.DependencyInjection;
using SQLitePCL;
using System;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();

//Batteries_V2.Init(); // Initialize SQLitePCL (needed for SQLCipher)

// Load secrets file
var secretConfig = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.secret.json", optional: true)
    .Build();

var secretValue = secretConfig["SecretKey"];

builder.Services.AddSingleton<DapperContext>(provider =>
    new DapperContext(builder.Configuration, secretValue));

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

builder.Services.AddCors(options =>
{
    options.AddPolicy("AllowAll",
        builder =>
        {
            builder.AllowAnyOrigin()
                   .AllowAnyMethod()
                   .AllowAnyHeader();
        });
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}
else
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseCors("AllowAll");

app.UseHttpsRedirection();

app.MapControllers();

app.Run();

Encrypt the Configuration: You may want to encrypt the sensitive values in this file to prevent unauthorized access.

Using a Local Database (SQLite with SQLiteCipher)

If you require a more complex solution, consider storing secrets in a local database.

Set Up SQLite: Install the SQLite package via NuGet:

dotnet add package Dapper
dotnet add package Microsoft.Data.Sqlite
dotnet add package SQLitePCLRaw.bundle_e_sqlcipher

Create a DapperContext.cs:

using Microsoft.Data.Sqlite;
using System.Data;
using System.Data.SQLite;

namespace LocalVaultWebApi.Data
{
    public class DapperContext
    {
        private readonly IConfiguration _configuration;
        private readonly string _connectionString;
        private readonly string _secretValue;
        public DapperContext(IConfiguration configuration, string secretValue)
        {
            _configuration = configuration;
            _secretValue = secretValue;
            _connectionString = _configuration.GetConnectionString("DefaultConnection");

            // Initialize SQLiteCipher (this can be done here or in a more central location)
            SQLitePCL.Batteries_V2.Init();  // This ensures SQLiteCipher is ready
        }

        public IDbConnection CreateConnection()
        {
            var connectionString = new SqliteConnectionStringBuilder
            {
                DataSource = _connectionString,  // Path to your SQLite database file
                Mode = SqliteOpenMode.ReadWriteCreate  // Allow reading and writing to the DB
            }.ToString();

            var connection = new SqliteConnection(connectionString);
            connection.Open();

            // Set the encryption key (PRAGMA key) after opening the connection
            using (var command = connection.CreateCommand())
            {
                command.CommandText = $"PRAGMA key = '{_secretValue}';";  // Set your encryption password
                command.ExecuteNonQuery();
            }

            return connection;  // Return the opened, encrypted connection ready for Dapper
        }
    }
}

Create Secret Class as a Model:

public class Secret
{
    public int Id { get; set; }
    public string Key { get; set; }
    public string Value { get; set; }
}

public class SecretRequest
{
    public string Key { get; set; }
    public string Value { get; set; }
}

Create a Controller to Access Secret Vualt

[Route("api/[controller]/[action]")]
[ApiController]
public class SecretController : ControllerBase
{
    private readonly DapperContext _dapperContext;

    public SecretController(DapperContext dapperContext)
    {
        _dapperContext = dapperContext;
    }

    [HttpGet]
    public IActionResult CreateDatabase()
    {
        var result = 0;

       using var connection = _dapperContext.CreateConnection();
       try
        {
            string sql2 = "CREATE TABLE IF NOT EXISTS Secret (Id INTEGER PRIMARY KEY, key TEXT,value TEXT)";

            connection.Execute(sql2);
        }
        catch (Exception ex) 
        { 
        
        }
        return Ok(true);
    }

    [HttpGet]
    public IActionResult GetSecret(string key)
    {
        var result =new Secret();


        using (var connection = _dapperContext.CreateConnection())
        {              

            string sql = "SELECT * From Secret Where key=@key";

            result = connection.QueryFirstOrDefault<Secret>(sql, new { key });
        }

        return Ok(result);
    }

    [HttpPost]
    public IActionResult StoreSecret(SecretRequest request_data)
    {
        var result = 0;

        using (var connection = _dapperContext.CreateConnection())
        {
            
            string sql = "INSERT INTO Secret(Key,Value) VALUES (@Key,@Value)";

            result =  connection.Execute(sql, new { request_data.Key, request_data.Value });
        }

        return Ok(result);
    }

}