EncryptedDatabase.cs

using SQLitePCL;

namespace SQLiteEncrypted.ConsoleJournal;

internal sealed class EncryptedDatabase : IDisposable
{
    private sqlite3? _handle;

    private EncryptedDatabase(sqlite3 handle)
    {
        _handle = handle;
    }

    internal sqlite3 Handle => _handle
        ?? throw new ObjectDisposedException(nameof(EncryptedDatabase));

    public static EncryptedDatabase Open(
        string path,
        string activationKey,
        string databaseKey)
    {
        ArgumentException.ThrowIfNullOrWhiteSpace(path);
        ArgumentException.ThrowIfNullOrWhiteSpace(activationKey);
        ArgumentException.ThrowIfNullOrWhiteSpace(databaseKey);

        if (activationKey.StartsWith("see-", StringComparison.Ordinal))
        {
            throw new ArgumentException(
                "SQLITE_ENCRYPTED_ACTIVATION_KEY must not include the see- PRAGMA prefix.",
                nameof(activationKey));
        }

        sqlite3? handle = null;
        try
        {
            Check(raw.sqlite3_open(path, out handle), handle, "open the database");
            var database = new EncryptedDatabase(handle);

            Check(raw.sqlite3_busy_timeout(database.Handle, 5_000), database.Handle,
                "set the busy timeout");
            database.Execute(
                $"PRAGMA activate_extensions = {SqlLiteral($"see-{activationKey}")}");

            var keyResult = database.ReadSingleText(
                $"PRAGMA key = {SqlLiteral(databaseKey)}");
            if (!string.Equals(keyResult, "ok", StringComparison.Ordinal))
            {
                throw new InvalidOperationException(
                    "SEE activation or key loading failed. Verify that " +
                    "SQLITE_ENCRYPTED_ACTIVATION_KEY is valid for this platform; " +
                    "PRAGMA key did not return ok.");
            }

            // Loading a key and decrypting an existing database are separate operations.
            // Reading the schema makes a wrong key fail before migrations or application SQL.
            _ = database.ReadSingleInt64("SELECT count(*) FROM sqlite_master");
            handle = null;
            return database;
        }
        catch
        {
            if (handle is not null)
            {
                _ = raw.sqlite3_close_v2(handle);
            }
            throw;
        }
    }

    internal void Execute(string sql)
    {
        Check(raw.sqlite3_exec(Handle, sql), Handle, sql);
    }

    internal string? ReadSingleText(string sql)
    {
        Check(raw.sqlite3_prepare_v2(Handle, sql, out var statement), Handle, sql);
        using (statement)
        {
            var result = raw.sqlite3_step(statement);
            if (result == raw.SQLITE_DONE)
            {
                return null;
            }

            Check(result, Handle, sql, raw.SQLITE_ROW);
            var value = raw.sqlite3_column_text(statement, 0).utf8_to_string();
            Check(raw.sqlite3_step(statement), Handle, sql, raw.SQLITE_DONE);
            return value;
        }
    }

    internal long ReadSingleInt64(string sql)
    {
        Check(raw.sqlite3_prepare_v2(Handle, sql, out var statement), Handle, sql);
        using (statement)
        {
            Check(raw.sqlite3_step(statement), Handle, sql, raw.SQLITE_ROW);
            var value = raw.sqlite3_column_int64(statement, 0);
            Check(raw.sqlite3_step(statement), Handle, sql, raw.SQLITE_DONE);
            return value;
        }
    }

    internal static string SqlLiteral(string value) =>
        $"'{value.Replace("'", "''", StringComparison.Ordinal)}'";

    internal static void Check(
        int result,
        sqlite3? database,
        string operation,
        int expected = raw.SQLITE_OK)
    {
        if (result == expected)
        {
            return;
        }

        var detail = database is null
            ? raw.sqlite3_errstr(result).utf8_to_string()
            : raw.sqlite3_errmsg(database).utf8_to_string();
        throw new InvalidOperationException(
            $"Failed to {operation}: SQLite result {result} ({detail}).");
    }

    public void Dispose()
    {
        if (_handle is null)
        {
            return;
        }

        var handle = _handle;
        _handle = null;
        Check(raw.sqlite3_close_v2(handle), handle, "close the database");
    }
}