Aquiis/1-Aquiis.Infrastructure/Data/SqlCipherConnectionInterceptor.cs at a95d668650202dd1a71b29bbf8d9c47fc15085be · xnodeoncode/Aquiis · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;

namespace Aquiis.Infrastructure.Data;

/// <summary>
/// EF Core connection interceptor that sets the SQLCipher encryption key on every connection open.
///
/// Performance note: accepts either a plaintext passphrase or a pre-derived raw key in SQLCipher's
/// "x'hexbytes'" format. Raw keys skip the per-connection PBKDF2 derivation entirely, which
/// eliminates 20–50 ms of CPU work per connection. Use SqlCipherKeyDerivation.DeriveRawKey()
/// at startup to produce the raw key from the user's passphrase, then pass it here.
/// </summary>
public class SqlCipherConnectionInterceptor : DbConnectionInterceptor
{
    private readonly string? _encryptionKey;

    /// <param name="encryptionKey">
    /// Either a plaintext passphrase (SQLCipher runs PBKDF2 internally, slow)
    /// or a pre-derived raw key in x'hexbytes' format (no PBKDF2, fast).
    /// </param>
    public SqlCipherConnectionInterceptor(string? encryptionKey)
    {
        _encryptionKey = encryptionKey;
    }

    public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData)
    {
        if (!string.IsNullOrEmpty(_encryptionKey))
        {
            using var cmd = connection.CreateCommand();

            if (_encryptionKey.StartsWith("x'", StringComparison.OrdinalIgnoreCase))
            {
                // Pre-derived raw key — SQLCipher loads it directly, no PBKDF2 (~0 ms)
                cmd.CommandText = $"PRAGMA key = \"{_encryptionKey}\";";
                cmd.ExecuteNonQuery();

                // Raw key still needs cipher params to match how the DB was encrypted
                cmd.CommandText = "PRAGMA cipher_page_size = 4096;";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "PRAGMA cipher_hmac_algorithm = HMAC_SHA512;";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA512;";
                cmd.ExecuteNonQuery();
            }
            else
            {
                // Passphrase fallback — SQLCipher runs PBKDF2(256000) internally (~20–50 ms)
                cmd.CommandText = $"PRAGMA key = '{_encryptionKey}';";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "PRAGMA cipher_page_size = 4096;";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "PRAGMA kdf_iter = 256000;";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "PRAGMA cipher_hmac_algorithm = HMAC_SHA512;";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA512;";
                cmd.ExecuteNonQuery();
            }
        }

        base.ConnectionOpened(connection, eventData);
    }

    public override async Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
    {
        if (!string.IsNullOrEmpty(_encryptionKey))
        {
            using var cmd = connection.CreateCommand();

            if (_encryptionKey.StartsWith("x'", StringComparison.OrdinalIgnoreCase))
            {
                // Pre-derived raw key — SQLCipher loads it directly, no PBKDF2 (~0 ms)
                cmd.CommandText = $"PRAGMA key = \"{_encryptionKey}\";";
                await cmd.ExecuteNonQueryAsync(cancellationToken);

                // Raw key still needs cipher params to match how the DB was encrypted
                cmd.CommandText = "PRAGMA cipher_page_size = 4096;";
                await cmd.ExecuteNonQueryAsync(cancellationToken);
                cmd.CommandText = "PRAGMA cipher_hmac_algorithm = HMAC_SHA512;";
                await cmd.ExecuteNonQueryAsync(cancellationToken);
                cmd.CommandText = "PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA512;";
                await cmd.ExecuteNonQueryAsync(cancellationToken);
            }
            else
            {
                // Passphrase fallback — SQLCipher runs PBKDF2(256000) internally (~20–50 ms)
                cmd.CommandText = $"PRAGMA key = '{_encryptionKey}';";
                await cmd.ExecuteNonQueryAsync(cancellationToken);

                cmd.CommandText = "PRAGMA cipher_page_size = 4096;";
                await cmd.ExecuteNonQueryAsync(cancellationToken);
                cmd.CommandText = "PRAGMA kdf_iter = 256000;";
                await cmd.ExecuteNonQueryAsync(cancellationToken);
                cmd.CommandText = "PRAGMA cipher_hmac_algorithm = HMAC_SHA512;";
                await cmd.ExecuteNonQueryAsync(cancellationToken);
                cmd.CommandText = "PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA512;";
                await cmd.ExecuteNonQueryAsync(cancellationToken);
            }
        }

        await base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
    }
}