通过创建加密映射表，实现字符加密解密

1.数据库创建表结构

IF NOT EXISTS (SELECT *
               FROM sys.all_objects
               WHERE object_id=OBJECT_ID(N'[dbo].[CryptoMappings]')AND type IN ('U'))BEGIN
   CREATE TABLE [dbo].[CryptoMappings] (
  [Id] bigint  IDENTITY(1,1) NOT NULL,
  [OriginalText] nvarchar(50) COLLATE Chinese_PRC_CS_AI  NOT NULL,
  [EncryptedCode] varchar(20) COLLATE Chinese_PRC_CS_AI  NOT NULL,
  [KeyHash] varchar(64) COLLATE Chinese_PRC_CS_AI  NOT NULL,
  [InputLength] int  NOT NULL,
  [OutputLength] int  NOT NULL,
  [CreatedTime] datetime2(7) DEFAULT getutcdate() NULL,
  CONSTRAINT [PK__CryptoMa__3214EC075E1DCE66] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UK_EncryptedCode_Length] UNIQUE NONCLUSTERED ([EncryptedCode] ASC, [OutputLength] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
) 
END


IF(NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_EncryptedCode_KeyHash' AND object_id = OBJECT_ID('dbo.CryptoMappings')))
BEGIN
CREATE NONCLUSTERED INDEX [IX_EncryptedCode_KeyHash] ON [dbo].[CryptoMappings]
(
    [EncryptedCode] ASC,
    [KeyHash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END 

IF(NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_OriginalText_KeyHash' AND object_id = OBJECT_ID('dbo.CryptoMappings')))
BEGIN
CREATE NONCLUSTERED INDEX [IX_OriginalText_KeyHash] ON [dbo].[CryptoMappings]
(
    [OriginalText] ASC,
    [KeyHash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END

2.缓存数据库加密解密服务

/**************************************************************************
*   
*   =================================
*   CLR版本     ：4.0.30319.42000
*   命名空间    ：Common
*   文件名称    ：CachedDatabaseCrypto.cs
*   =================================
*   创 建 者    ：Rooki
*   创建日期    ：2025-10-24 12:52:24 
*   邮箱        ：2441494040@qq.com
*   功能描述    ：
*   使用说明    ：
*   =================================
*   修改者    ：
*   修改日期    ：
*   修改内容    ：
*   =================================
*  
***************************************************************************/
using System;
using System.Collections.Generic;

namespace Common
{
    /// <summary>
    /// 缓存数据库加密解密服务 
    /// </summary>
    public class CachedDatabaseCrypto
    {
        private const int DEFAULT_LENGTH = 9;

        // 内存缓存
        private static readonly Dictionary<string, string> encryptCache = new Dictionary<string, string>();
        private static readonly Dictionary<string, string> decryptCache = new Dictionary<string, string>();
        private static readonly object lockObj = new object();

        /// <summary>
        /// 加密字符串
        /// </summary>
        public static string Encrypt(string input, string key, int outputLength = DEFAULT_LENGTH)
        {
            lock (lockObj)
            {
                string cacheKey = $"{key}|{input}|{outputLength}";
                if (encryptCache.ContainsKey(cacheKey))
                    return encryptCache[cacheKey];

                string encrypted = DatabaseCrypto.EncryptDirect(input, key, outputLength);
                encryptCache[cacheKey] = encrypted;
                decryptCache[encrypted] = input;

                return encrypted;
            }
        }

        /// <summary>
        /// 解密字符串
        /// </summary>
        public static string Decrypt(string encrypted, string key, int outputLength = DEFAULT_LENGTH)
        {
            lock (lockObj)
            {
                if (decryptCache.ContainsKey(encrypted))
                    return decryptCache[encrypted];

                string decrypted = DatabaseCrypto.DecryptDirect(encrypted, key, outputLength);
                decryptCache[encrypted] = decrypted;
                encryptCache[$"{key}|{decrypted}|{outputLength}"] = encrypted;

                return decrypted;
            }
        }

        /// <summary>
        /// 清空缓存
        /// </summary>
        public static void ClearCache()
        {
            lock (lockObj)
            {
                encryptCache.Clear();
                decryptCache.Clear();
            }
        }

        /// <summary>
        /// 获取缓存统计
        /// </summary>
        public static void GetCacheStats()
        {
            lock (lockObj)
            {
                Console.WriteLine($"缓存统计: 加密缓存{encryptCache.Count}条, 解密缓存{decryptCache.Count}条");
            }
        }
    }
}

3. 数据库加密解密核心实现 - 支持任意输入输出长度（数据库字段长度需对应调整）

/**************************************************************************
*   
*   =================================
*   CLR版本     ：4.0.30319.42000
*   命名空间    ：Common
*   文件名称    ：CachedDatabaseCrypto.cs
*   =================================
*   创 建 者    ：Rooki
*   创建日期    ：2025-10-24 12:52:24 
*   邮箱        ：2441494040@qq.com
*   功能描述    ：基于数据库的加密解密服务
*   使用说明    ：使用前请确保CryptoMappings表已创建
*   =================================
*   修改者    ：
*   修改日期    ：
*   修改内容    ：
*   =================================
*  
***************************************************************************/
using Maticsoft.DBUtility;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;

namespace Common
{
    /// <summary>
    /// 数据库加密解密核心实现 - 支持任意输入输出长度（数据库字段长度需对应调整）
    /// </summary>
    public class DatabaseCrypto
    {
        private const string OUTPUT_CHARS = "23456789ABCDEFGHJKLMNPQRSTUVWXYZ";
        private const int DEFAULT_OUTPUT_LENGTH = 9;
        private const int MIN_OUTPUT_LENGTH = 5;
        private const int MAX_OUTPUT_LENGTH = 20;
        private const int MAX_INPUT_LENGTH = 500;

        // 在调用前设置连接字符串
        public static string ConnectionString = DbHelperSQL.connectionString;

        /// <summary>
        /// 加密字符串 - 任意输入长度，任意输出长度(5-20)
        /// </summary>
        public static string Encrypt(string input, string key, int outputLength = DEFAULT_OUTPUT_LENGTH)
        {
            ValidateParameters(input, outputLength, key);
            return CachedDatabaseCrypto.Encrypt(input, key, outputLength);
        }

        /// <summary>
        /// 解密字符串
        /// </summary>
        public static string Decrypt(string encrypted, string key, int outputLength = DEFAULT_OUTPUT_LENGTH)
        {
            ValidateDecryptParameters(encrypted, outputLength, key);
            return CachedDatabaseCrypto.Decrypt(encrypted, key, outputLength);
        }

        /// <summary>
        /// 直接加密方法（供缓存层调用）
        /// </summary>
        public static string EncryptDirect(string input, string key, int outputLength = DEFAULT_OUTPUT_LENGTH)
        {
            string keyHash = ComputeKeyHash(key);

            // 先检查是否已经存在映射
            string existingCode = FindExistingMapping(input, keyHash, outputLength);
            if (existingCode != null)
                return existingCode;

            // 生成新的唯一代码
            string encryptedCode = GenerateUniqueCode(input, key, outputLength);

            // 保存到数据库
            SaveMapping(input, encryptedCode, keyHash, outputLength);

            return encryptedCode;
        }

        /// <summary>
        /// 直接解密方法（供缓存层调用）
        /// </summary>
        public static string DecryptDirect(string encrypted, string key, int outputLength = DEFAULT_OUTPUT_LENGTH)
        {
            string keyHash = ComputeKeyHash(key);

            // 从数据库查找原始文本
            string originalText = FindOriginalText(encrypted, keyHash, outputLength);
            if (originalText != null)
                return originalText;

            throw new ArgumentException("无法解密：代码不存在或密钥错误");
        }

        #region 私有核心方法

        private static void ValidateParameters(string input, int outputLength, string key)
        {
            if (string.IsNullOrEmpty(input))
                throw new ArgumentException("输入不能为空");

            if (input.Length > MAX_INPUT_LENGTH)
                throw new ArgumentException($"输入长度不能超过{MAX_INPUT_LENGTH}个字符");

            if (outputLength < MIN_OUTPUT_LENGTH || outputLength > MAX_OUTPUT_LENGTH)
                throw new ArgumentException($"输出长度必须在{MIN_OUTPUT_LENGTH}-{MAX_OUTPUT_LENGTH}之间");

            if (string.IsNullOrEmpty(key))
                throw new ArgumentException("密钥不能为空");

            if (string.IsNullOrEmpty(ConnectionString))
                throw new ArgumentException("数据库连接字符串未设置");
        }

        private static void ValidateDecryptParameters(string encrypted, int outputLength, string key)
        {
            if (string.IsNullOrEmpty(encrypted))
                throw new ArgumentException("加密字符串不能为空");

            if (encrypted.Length != outputLength)
                throw new ArgumentException($"加密字符串长度必须为{outputLength}位");

            if (outputLength < MIN_OUTPUT_LENGTH || outputLength > MAX_OUTPUT_LENGTH)
                throw new ArgumentException($"输出长度必须在{MIN_OUTPUT_LENGTH}-{MAX_OUTPUT_LENGTH}之间");

            if (string.IsNullOrEmpty(key))
                throw new ArgumentException("密钥不能为空");

            if (string.IsNullOrEmpty(ConnectionString))
                throw new ArgumentException("数据库连接字符串未设置");
        }

        private static string ComputeKeyHash(string key)
        {
            using (SHA256 sha256 = SHA256.Create())
            {
                // 确保编码一致性
                byte[] keyBytes = Encoding.UTF8.GetBytes(key.Normalize(NormalizationForm.FormC));
                byte[] hash = sha256.ComputeHash(keyBytes);
                return ByteArrayToHexString(hash);
            }
        }

        private static string ByteArrayToHexString(byte[] bytes)
        {
            StringBuilder hex = new StringBuilder(bytes.Length * 2);
            foreach (byte b in bytes)
                hex.AppendFormat("{0:x2}", b);
            return hex.ToString();
        }

        private static string FindExistingMapping(string input, string keyHash, int outputLength)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                string sql = @"
                    SELECT EncryptedCode 
                    FROM CryptoMappings 
                    WHERE OriginalText = @OriginalText 
                    AND KeyHash = @KeyHash 
                    AND OutputLength = @OutputLength";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@OriginalText", input);
                    command.Parameters.AddWithValue("@KeyHash", keyHash);
                    command.Parameters.AddWithValue("@OutputLength", outputLength);

                    object result = command.ExecuteScalar();
                    return result?.ToString();
                }
            }
        }

        private static string FindOriginalText(string encryptedCode, string keyHash, int outputLength)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                string sql = @"
                    SELECT OriginalText 
                    FROM CryptoMappings 
                    WHERE EncryptedCode = @EncryptedCode 
                    AND KeyHash = @KeyHash 
                    AND OutputLength = @OutputLength";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@EncryptedCode", encryptedCode);
                    command.Parameters.AddWithValue("@KeyHash", keyHash);
                    command.Parameters.AddWithValue("@OutputLength", outputLength);

                    object result = command.ExecuteScalar();
                    return result?.ToString();
                }
            }
        }

        private static string GenerateUniqueCode(string input, string key, int outputLength)
        {
            string code;
            int attempts = 0;
            int maxAttempts = CalculateMaxAttempts(outputLength);

            do
            {
                code = GenerateCode(input, key, attempts, outputLength);
                attempts++;

                if (attempts > maxAttempts)
                    throw new Exception($"无法在{maxAttempts}次尝试内生成唯一{outputLength}位代码");

            } while (IsCodeExists(code, outputLength));

            return code;
        }

        private static int CalculateMaxAttempts(int outputLength)
        {
            // 短长度冲突概率高，需要更多尝试
            if (outputLength <= 6) return 500;
            if (outputLength <= 9) return 100;
            if (outputLength <= 12) return 50;
            return 20;
        }

        private static string GenerateCode(string input, string key, int salt, int outputLength)
        {
            using (HMACSHA256 hmac = new HMACSHA256(Encoding.UTF8.GetBytes(key.Normalize(NormalizationForm.FormC))))
            {
                // 标准化所有输入数据
                string normalizedInput = input.Normalize(NormalizationForm.FormC);
                string data = $"{normalizedInput}|{salt}|{outputLength}";

                byte[] hash = hmac.ComputeHash(Encoding.UTF8.GetBytes(data.Normalize(NormalizationForm.FormC)));
                long number = BitConverter.ToInt64(hash, 0);
                number = Math.Abs(number);

                return NumberToCode(number, outputLength);
            }
        }

        private static string NumberToCode(long number, int length)
        {
            char[] result = new char[length];
            int baseSize = OUTPUT_CHARS.Length;

            for (int i = length - 1; i >= 0; i--)
            {
                result[i] = OUTPUT_CHARS[(int)(number % baseSize)];
                number /= baseSize;
            }

            return new string(result);
        }

        private static bool IsCodeExists(string code, int outputLength)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                string sql = @"
                    SELECT COUNT(1) 
                    FROM CryptoMappings 
                    WHERE EncryptedCode = @EncryptedCode 
                    AND OutputLength = @OutputLength";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@EncryptedCode", code);
                    command.Parameters.AddWithValue("@OutputLength", outputLength);
                    int count = (int)command.ExecuteScalar();
                    return count > 0;
                }
            }
        }

        private static void SaveMapping(string originalText, string encryptedCode, string keyHash, int outputLength)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                string sql = @"
                    INSERT INTO CryptoMappings (OriginalText, EncryptedCode, KeyHash, InputLength, OutputLength) 
                    VALUES (@OriginalText, @EncryptedCode, @KeyHash, @InputLength, @OutputLength)";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@OriginalText", originalText);
                    command.Parameters.AddWithValue("@EncryptedCode", encryptedCode);
                    command.Parameters.AddWithValue("@KeyHash", keyHash);
                    command.Parameters.AddWithValue("@InputLength", originalText.Length);
                    command.Parameters.AddWithValue("@OutputLength", outputLength);

                    command.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #region 工具方法

        /// <summary>
        /// 批量加密
        /// </summary>
        public static Dictionary<string, string> EncryptBatch(string[] inputs, string key, int outputLength = DEFAULT_OUTPUT_LENGTH)
        {
            Dictionary<string, string> results = new Dictionary<string, string>();
            foreach (string input in inputs)
            {
                string encrypted = Encrypt(input, key, outputLength);
                results[input] = encrypted;
            }
            return results;
        }

        /// <summary>
        /// 批量解密
        /// </summary>
        public static Dictionary<string, string> DecryptBatch(string[] encryptedCodes, string key, int outputLength = DEFAULT_OUTPUT_LENGTH)
        {
            Dictionary<string, string> results = new Dictionary<string, string>();
            foreach (string encrypted in encryptedCodes)
            {
                try
                {
                    string decrypted = Decrypt(encrypted, key, outputLength);
                    results[encrypted] = decrypted;
                }
                catch (Exception ex)
                {
                    results[encrypted] = $"解密失败: {ex.Message}";
                }
            }
            return results;
        }

        /// <summary>
        /// 清理过期数据
        /// </summary>
        public static int CleanupOldData(DateTime olderThan)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                string sql = "DELETE FROM CryptoMappings WHERE CreatedTime < @OlderThan";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@OlderThan", olderThan);
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 获取统计信息
        /// </summary>
        public static void GetStats()
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                string sql = @"
                    SELECT 
                        OutputLength,
                        COUNT(*) as Count,
                        AVG(InputLength) as AvgInputLength,
                        MIN(InputLength) as MinInputLength,
                        MAX(InputLength) as MaxInputLength,
                        MIN(CreatedTime) as OldestRecord,
                        MAX(CreatedTime) as NewestRecord
                    FROM CryptoMappings
                    GROUP BY OutputLength
                    ORDER BY OutputLength";

                using (SqlCommand command = new SqlCommand(sql, connection))
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    Console.WriteLine("数据库统计:");
                    Console.WriteLine("============");
                    while (reader.Read())
                    {
                        DateTime oldest = reader["OldestRecord"] as DateTime? ?? DateTime.MinValue;
                        DateTime newest = reader["NewestRecord"] as DateTime? ?? DateTime.MinValue;

                        Console.WriteLine($"输出{reader["OutputLength"]}位: {reader["Count"]}条记录 " +
                                         $"(输入长度: {reader["MinInputLength"]}-{reader["MaxInputLength"]}, 平均: {reader["AvgInputLength"]:F1}) " +
                                         $"(时间: {oldest:MM-dd} ~ {newest:MM-dd})");
                    }
                }
            }
        }

        #endregion

        #region 测试方法

        /// <summary>
        /// 完整测试
        /// </summary>
        public static void Test(int outputLength = DEFAULT_OUTPUT_LENGTH)
        {
            if (string.IsNullOrEmpty(ConnectionString))
            {
                Console.WriteLine("错误: 请先设置DatabaseCrypto.ConnectionString");
                return;
            }

            string key = "ty88210900";

            Console.WriteLine($"加密测试 (输出长度: {outputLength}位):");
            Console.WriteLine("=====================================");

            CachedDatabaseCrypto.ClearCache();

            string[] testInputs = GetTestInputs(outputLength);

            bool allPassed = true;
            int successCount = 0;
            int failCount = 0;

            foreach (string input in testInputs)
            {
                try
                {
                    Console.WriteLine($"输入({input.Length}位): {input}");

                    string encrypted = Encrypt(input, key, outputLength);
                    Console.WriteLine($"加密({encrypted.Length}位): {encrypted}");

                    string decrypted = Decrypt(encrypted, key, outputLength);
                    Console.WriteLine($"解密({decrypted.Length}位): {decrypted}");

                    bool success = input == decrypted;
                    Console.WriteLine($"结果: {(success ? "✓ 成功" : "✗ 失败")}");
                    Console.WriteLine();

                    if (success)
                        successCount++;
                    else
                        failCount++;

                    if (!success) allPassed = false;
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"{input} -> 错误: {ex.Message}");
                    failCount++;
                    allPassed = false;
                }
            }

            Console.WriteLine($"测试结果: 成功 {successCount}, 失败 {failCount}");
            Console.WriteLine(allPassed ? "🎉 所有测试通过！" : "❌ 部分测试失败");

            GetStats();
            CachedDatabaseCrypto.GetCacheStats();
        }

        /// <summary>
        /// 多长度测试
        /// </summary>
        public static void TestMultipleLengths()
        {
            int[] lengths = { 5, 7, 9, 12, 15, 20 };
            foreach (int length in lengths)
            {
                Test(length);
                Console.WriteLine();
            }
        }

        /// <summary>
        /// 测试不同输入长度
        /// </summary>
        public static void TestDifferentInputLengths(int outputLength = DEFAULT_OUTPUT_LENGTH)
        {
            string key = "ty88210900";
            int[] inputLengths = { 1, 5, 10, 20, 50, 100, 200 };

            Console.WriteLine($"测试不同输入长度 (输出长度: {outputLength}位):");
            Console.WriteLine("===========================================");

            foreach (int inputLength in inputLengths)
            {
                try
                {
                    string input = new string('X', inputLength);
                    string encrypted = Encrypt(input, key, outputLength);
                    string decrypted = Decrypt(encrypted, key, outputLength);

                    bool success = input == decrypted;
                    Console.WriteLine($"输入{inputLength,3}位 -> 输出{encrypted.Length}位: {(success ? "✓" : "✗")} {encrypted}");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"输入{inputLength,3}位 -> 错误: {ex.Message}");
                }
            }
        }

        private static string[] GetTestInputs(int outputLength)
        {
            List<string> inputs = new List<string>();

            // 各种长度的测试数据
            inputs.Add("1");                                           // 1位
            inputs.Add("12345");                                       // 5位
            inputs.Add("HelloWorld");                                  // 10位
            inputs.Add("这是一个测试字符串");                           // 中文
            inputs.Add("Test@123_ABC");                                // 特殊字符
            inputs.Add(new string('A', 50));                           // 长字符串
            inputs.Add("vvGxyvGcT");                                   // 你的原始数据

            // 大小写敏感测试
            inputs.Add("AbCdEfGh");
            inputs.Add("abcdefgh");
            inputs.Add("ABCDEFGH");

            return inputs.ToArray();
        }

        #endregion
    }
}