AIC-Project/Server/Auth/AccountStore.cs

using System;
using System.Security.Cryptography;
using System.Text;
using Microsoft.Data.Sqlite;

namespace XWorld.Server.Auth
{
    // SQLite 账号库。保持单条长连接（内存库测试 + 文件库生产均可），低频鉴权用 lock 串行化。
    public sealed class AccountStore : IDisposable
    {
        const int SaltLen = 16;
        const int HashLen = 32;
        const int Iterations = 100_000;

        readonly SqliteConnection _conn;
        readonly object _lock = new object();

        // dbPath: 文件路径(生产) 或完整连接串(以 "Data Source=" 开头，测试用内存库)
        public AccountStore(string dbPath)
        {
            string cs = dbPath.StartsWith("Data Source=", StringComparison.OrdinalIgnoreCase)
                ? dbPath
                : "Data Source=" + dbPath;
            _conn = new SqliteConnection(cs);
            _conn.Open();
            Exec("PRAGMA journal_mode=WAL;");
            Exec(@"CREATE TABLE IF NOT EXISTS accounts(
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL COLLATE NOCASE,
                pwd_hash BLOB NOT NULL,
                salt BLOB NOT NULL,
                iter INTEGER NOT NULL,
                created_at INTEGER NOT NULL);");
        }

        public int CreateAccount(string username, string password)
        {
            if (string.IsNullOrWhiteSpace(username)) throw new ArgumentException("username");
            if (string.IsNullOrEmpty(password)) throw new ArgumentException("password");
            byte[] salt = RandomNumberGenerator.GetBytes(SaltLen);
            byte[] hash = Rfc2898DeriveBytes.Pbkdf2(
                Encoding.UTF8.GetBytes(password), salt, Iterations, HashAlgorithmName.SHA256, HashLen);
            lock (_lock)
            {
                using var cmd = _conn.CreateCommand();
                cmd.CommandText = "INSERT INTO accounts(username,pwd_hash,salt,iter,created_at) VALUES($u,$h,$s,$i,$t);";
                cmd.Parameters.AddWithValue("$u", username);
                cmd.Parameters.AddWithValue("$h", hash);
                cmd.Parameters.AddWithValue("$s", salt);
                cmd.Parameters.AddWithValue("$i", Iterations);
                cmd.Parameters.AddWithValue("$t", DateTimeOffset.UtcNow.ToUnixTimeSeconds());
                try { cmd.ExecuteNonQuery(); }
                catch (SqliteException e) when (e.SqliteErrorCode == 19) // SQLITE_CONSTRAINT (UNIQUE)
                { throw new DuplicateUsernameException(username); }

                using var idCmd = _conn.CreateCommand();
                idCmd.CommandText = "SELECT last_insert_rowid();";
                return (int)(long)idCmd.ExecuteScalar();
            }
        }

        public int? VerifyCredentials(string username, string password)
        {
            if (string.IsNullOrWhiteSpace(username) || string.IsNullOrEmpty(password)) return null;
            lock (_lock)
            {
                using var cmd = _conn.CreateCommand();
                cmd.CommandText = "SELECT id,pwd_hash,salt,iter FROM accounts WHERE username=$u COLLATE NOCASE;";
                cmd.Parameters.AddWithValue("$u", username);
                using var r = cmd.ExecuteReader();
                if (!r.Read()) return null;
                int id = (int)r.GetInt64(0);
                byte[] stored = (byte[])r["pwd_hash"];
                byte[] salt = (byte[])r["salt"];
                int iter = (int)r.GetInt64(3);
                byte[] cand = Rfc2898DeriveBytes.Pbkdf2(
                    Encoding.UTF8.GetBytes(password), salt, iter, HashAlgorithmName.SHA256, stored.Length);
                return CryptographicOperations.FixedTimeEquals(cand, stored) ? id : (int?)null;
            }
        }

        // 测试辅助：原始库字节是否包含某序列（验证明文不入库）
        public bool DebugRawContains(byte[] needle)
        {
            lock (_lock)
            {
                using var cmd = _conn.CreateCommand();
                cmd.CommandText = "SELECT pwd_hash,salt FROM accounts;";
                using var r = cmd.ExecuteReader();
                while (r.Read())
                {
                    if (Contains((byte[])r["pwd_hash"], needle) || Contains((byte[])r["salt"], needle))
                        return true;
                }
                return false;
            }
        }

        static bool Contains(byte[] hay, byte[] needle)
        {
            if (needle.Length == 0 || hay.Length < needle.Length) return false;
            for (int i = 0; i <= hay.Length - needle.Length; i++)
            {
                bool ok = true;
                for (int j = 0; j < needle.Length; j++) if (hay[i + j] != needle[j]) { ok = false; break; }
                if (ok) return true;
            }
            return false;
        }

        void Exec(string sql)
        {
            using var cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }

        public void Dispose() => _conn?.Dispose();
    }
}