C# 解密来自 SQL Server EncryptByPassPhrase 的字节

Question

在C#中复制T-SQL DecryptByPassPhrase之后，我无法使用MSSQL进行简单的加密以在C#中解密。某些列中的加密值是必需的，因为表会定期导出到 Excel 和 Access 中，因此简单的加密足以"阻止"值，而无需开发人员参与（重新）执行视图等。

在 SQL Server 2012 中：

    select EncryptByPassPhrase( N'hello' , N'world'  ) 
-- returns 0x01000000AA959FFB3A8E4B06B734051437E198C8B72000A058ACE91D617123DA102287EB

在 C# 中：

byte[] buf = System.Text.Encoding.UTF8.GetBytes( "0x010000003A95FA870ED699A5F90D33C2BF01491D9132F61BA162998E96F37117AF5DA0905D51EB6FB298EC88" );
// bytes emitted from the database
var cp = new TripleDESCryptoServiceProvider();
var m = new MemoryStream(buf);
cp.Key = System.Text.Encoding.UTF8.GetBytes( "hello" ); // throws
cp.IV = System.Text.Encoding.UTF8.GetBytes( "hello" ); // throws
CryptoStream cs = new CryptoStream( m , cp.CreateDecryptor( cp.Key , cp.IV ) , CryptoStreamMode.Read );
StreamReader reader = new StreamReader( cs );
string plainText = reader.ReadToEnd();

工作 C# 代码应该是什么样子的？

谢谢。

Answer 1

>SQL Server 2017使用SHA256哈希密码+ AES-256加密

旧版本使用密码的 SHA1 哈希 + 3DES-128 加密

IV 大小与块大小相同：AES = 128 位，3DES = 64 位

填充模式：PKCS #7密码模式：全血细胞

服务器加密的数据 2017 以"0x02"开头，旧版本以"0x01"开头。

// Example decrypt:
// UInt32 - "magic" (0xbaadf00d): 0d f0 ad ba
// UInt16 - unknown (always zero): 00 00
// UInt16 - decrypted data length (16): 10 00
// byte[] - decrypted data: 4c 65 74 54 68 65 53 75 6e 53 68 69 6e 69 6e 67
DecryptCombined("0x02000000266AD4F387FA9474E825B013B0232E73A398A5F72B79BC90D63BD1E45AE3AA5518828D187125BECC285D55FA7CAFED61", "Radames");
DecryptCombined("0x010000007854E155CEE338D5E34808BA95367D506B97C63FB5114DD4CE687FE457C1B5D5", "banana");

void DecryptCombined(string FromSql, string Password)
{
    // Encode password as UTF16-LE
    byte[] passwordBytes = Encoding.Unicode.GetBytes(Password);
    // Remove leading "0x"
    FromSql = FromSql.Substring(2);
    int version = BitConverter.ToInt32(StringToByteArray(FromSql.Substring(0, 8)), 0);
    byte[] encrypted = null;
    HashAlgorithm hashAlgo = null;
    SymmetricAlgorithm cryptoAlgo = null;
    int keySize = (version == 1 ? 16 : 32);
    if (version == 1)
    {
        hashAlgo = SHA1.Create();
        cryptoAlgo = TripleDES.Create();
        cryptoAlgo.IV = StringToByteArray(FromSql.Substring(8, 16));
        encrypted = StringToByteArray(FromSql.Substring(24));
    }
    else if (version == 2)
    {
        hashAlgo = SHA256.Create();
        cryptoAlgo = Aes.Create();
        cryptoAlgo.IV = StringToByteArray(FromSql.Substring(8, 32));
        encrypted = StringToByteArray(FromSql.Substring(40));
    }
    else
    {
        throw new Exception("Unsupported encryption");
    }
    cryptoAlgo.Padding = PaddingMode.PKCS7;
    cryptoAlgo.Mode = CipherMode.CBC;
    hashAlgo.TransformFinalBlock(passwordBytes, 0, passwordBytes.Length);
    cryptoAlgo.Key = hashAlgo.Hash.Take(keySize).ToArray();
    byte[] decrypted = cryptoAlgo.CreateDecryptor().TransformFinalBlock(encrypted, 0, encrypted.Length);
    int decryptLength = BitConverter.ToInt16(decrypted, 6);
    UInt32 magic = BitConverter.ToUInt32(decrypted, 0);
    if (magic != 0xbaadf00d)
    {
        throw new Exception("Decrypt failed");
    }
    byte[] decryptedData = decrypted.Skip(8).ToArray();
    bool isUtf16 = (Array.IndexOf(decryptedData, (byte)0) != -1);
    string decryptText = (isUtf16 ? Encoding.Unicode.GetString(decryptedData) : Encoding.UTF8.GetString(decryptedData));
    Console.WriteLine("Result: {0}", decryptText);
}
// Method taken from https://stackoverflow.com/questions/321370/how-can-i-convert-a-hex-string-to-a-byte-array?answertab=votes#tab-top
public static byte[] StringToByteArray(string hex)
{
    return Enumerable.Range(0, hex.Length)
                     .Where(x => x % 2 == 0)
                     .Select(x => Convert.ToByte(hex.Substring(x, 2), 16))
                     .ToArray();
}

Answer 2

我相信您关注的链接提出了一种加密和解密的新方法，模仿了SQL EncryptByPassPhrase的制作方式。因此，如果您也在 C# 中加密，则只能在 C# 中使用解密。

既然你已经在SQL中使用了EncryptByPassPhrase，那么我建议在传递给C#代码之前只在SQL中使用DecryptByPassPhrase。

您的 hello 世界加密和解密示例：

Declare @lEncryptedText VARBINARY(256) = (select ENCRYPTBYPASSPHRASE('hello','world'))
SELECT @lEncryptedText --Encrypted VALUE for world
SELECT CONVERT(VARCHAR(100),DECRYPTBYPASSPHRASE('hello',@lEncryptedText)) --Decrypted Value

Answer 3

下面是完美的 C# 解决方案，其输出与 SQL 函数中的 EncryptByPassPhrase 和 DecryptByPassPhrase 相同。

程序.cs

using SQLServerCrypto;
static void Main()
{
            // EncryptByPassPhrase 
            var passphrase = "test1234";
            var encrypted = SQLServerCryptoMethod.EncryptByPassPhrase(@passphrase, "Hello world.");
            System.Console.WriteLine(encrypted.ToString().ToUpper());
            // DecryptByPassPhrase 
            var ciphertext = "0x0100000038C94F7223E0BA2F772B611857F9D45DAF781607CC77F4A856CF08CC2DB9DF14A0593259CB3A4A2BFEDB485C002CA04B6A98BEB1B47EB107";
            var password = "test1234";
            var decrypted = SQLServerCryptoMethod.DecryptByPassPhraseWithoutVerification(password, ciphertext);
            Console.WriteLine(decrypted);
}

SQLServerCryptoMethod.cs

using System;
using System.Linq;
namespace SQLServerCrypto
{
    public static class SQLServerCryptoMethod
    {
        // SQL Server: https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql
        public static HexString EncryptByPassPhrase(string passphrase, string cleartext, int add_authenticator, string authenticator)
            => EncryptByPassPhrase(passphrase, cleartext, add_authenticator, authenticator, SQLServerCryptoVersion.V1);
        public static HexString EncryptByPassPhrase(string passphrase, string cleartext, SQLServerCryptoVersion sqlServerCryptoVersion)
             => EncryptByPassPhrase(passphrase, cleartext, 0, string.Empty, sqlServerCryptoVersion);
        public static HexString EncryptByPassPhrase(string passphrase, string cleartext)
             => EncryptByPassPhrase(passphrase, cleartext, 0, string.Empty, SQLServerCryptoVersion.V1);
        public static HexString EncryptByPassPhrase(string passphrase, string cleartext, int add_authenticator, string authenticator, SQLServerCryptoVersion sqlServerCryptoVersion)
        {
            var sqlServerCryptoAlgorithm = new SQLServerCryptoAlgorithm(sqlServerCryptoVersion);
            sqlServerCryptoAlgorithm.SetKeyFromPassPhrase(passphrase);
            byte[] header = new SQLServerCryptoHeader() {
                Version = sqlServerCryptoVersion,
                InitializationVector = sqlServerCryptoAlgorithm.Symmetric.IV
            };
            var sqlServerCryptoMessage = new SQLServerCryptoMessage()
            {
                AddAuthenticator = add_authenticator > 0,
                Authenticator = authenticator
            };
            sqlServerCryptoMessage.CreateFromClearText(cleartext);
            byte[] message = sqlServerCryptoMessage;
            
            var encryptedMessage = sqlServerCryptoAlgorithm.Symmetric
                .CreateEncryptor()
                .TransformFinalBlock(message, 0, message.Length);
            return new HexString(header.Concat(encryptedMessage).ToArray());
        }
        // SQL Server: https://learn.microsoft.com/en-us/sql/t-sql/functions/decryptbypassphrase-transact-sql
        public static string DecryptByPassPhrase(string passphrase, string ciphertext) 
            => DecryptByPassPhrase(passphrase, new HexString(ciphertext), 0, string.Empty, true);
        public static string DecryptByPassPhrase(string passphrase, string ciphertext, int add_authenticator, string authenticator)   
            => DecryptByPassPhrase(passphrase, new HexString(ciphertext), add_authenticator, authenticator, true);
        public static string DecryptByPassPhraseWithoutVerification(string passphrase, string ciphertext)
          => DecryptByPassPhrase(passphrase, new HexString(ciphertext), 0, string.Empty, false);
        public static string DecryptByPassPhrase(string passphrase, HexString ciphertext, int add_authenticator, string authenticator, bool verify)
        {
            byte[] ciphertextBytes = ciphertext.ToByteArray();
            var version = (SQLServerCryptoVersion)ciphertextBytes[0];
            var sqlServerCryptoAlgorithm = new SQLServerCryptoAlgorithm(version);
            sqlServerCryptoAlgorithm.SetKeyFromPassPhrase(passphrase);
            var versionAndReservedSize = 4;
            var ivSize = sqlServerCryptoAlgorithm.KeySize / 2;
            var header = new SQLServerCryptoHeader
            {
                Version = version,
                InitializationVector = ciphertextBytes.Skip(versionAndReservedSize).Take(ivSize).ToArray()
            };
            sqlServerCryptoAlgorithm.Symmetric.IV = header.InitializationVector;
            var encryptedMessage = ciphertextBytes.Skip(versionAndReservedSize+ivSize).ToArray();
                       
            var decryptedMessage = sqlServerCryptoAlgorithm.Symmetric
                .CreateDecryptor()
                .TransformFinalBlock(encryptedMessage, 0, encryptedMessage.Length);
            // Message
            var sqlServerCryptoMessage = new SQLServerCryptoMessage()
            {
                AddAuthenticator = add_authenticator > 0,
                Authenticator = authenticator
            };
            sqlServerCryptoMessage.CreateFromDecryptedMessage(decryptedMessage, verify);
            
            return ByteArray2String(sqlServerCryptoMessage.MessageBytes);
        }
              
        private static string ByteArray2String(byte[] array)
            => array.Aggregate(string.Empty, (a, b) => a + Convert.ToChar(b));
    }
}

SQLServerCryptoMessage.cs

using System;
using System.IO;
using System.Text;
using System.Linq;
using System.Security.Cryptography;
namespace SQLServerCrypto
{
    public class SQLServerCryptoMessage
    {
        private const uint MAGIC_NUMBER = 0xbaad_f00d;
        public uint MagicNumber { get; private set; }
        public ushort IntegrityBytesLength { get; private set; }
        public ushort PlainTextLength { get; private set; }
        public byte[] IntegrityBytes;
        public byte[] MessageBytes;
        public bool AddAuthenticator = false;
        private string _authenticator;
        public string Authenticator
        {
            get => _authenticator;
            set =>
                _authenticator = value.Length > 128 ?
                    throw new ArgumentOutOfRangeException("The size of the authenticator string should not exceed 128 bytes.")
                    : value;
        }
        
        public SQLServerCryptoMessage() =>  MagicNumber = MAGIC_NUMBER;
        
        public void CreateFromClearText(string cleartext)
        {
            MessageBytes = Encoding.ASCII.GetBytes(cleartext);
            if (MessageBytes.Length > 8000)
                throw new ArgumentOutOfRangeException("The size of the cleartext string should not exceed 8000 bytes.");
            MagicNumber = MAGIC_NUMBER;
            IntegrityBytesLength = 0;
            PlainTextLength = (ushort)MessageBytes.Length;
            if (AddAuthenticator)
            {
                var integrityMessage = MessageBytes.Concat(Encoding.ASCII.GetBytes(Authenticator)).ToArray();
                IntegrityBytes = SHA1.Create().ComputeHash(integrityMessage);
                IntegrityBytesLength = (ushort)IntegrityBytes.Length;
            }
        }
        public void CreateFromDecryptedMessage(byte[] decryptedMessage, bool verify = true)
        {
            MagicNumber = BitConverter.ToUInt32(decryptedMessage, 0);
            IntegrityBytesLength = BitConverter.ToUInt16(decryptedMessage, 4);
            PlainTextLength = BitConverter.ToUInt16(decryptedMessage, 6);
            var messageWithoutHeader = decryptedMessage.Skip(8);
            if (IntegrityBytesLength > 0 || IntegrityBytesLength < 0xffff)
                IntegrityBytes = messageWithoutHeader.Take(IntegrityBytesLength).ToArray();
            if (IntegrityBytesLength != 0xffff)
                MessageBytes = messageWithoutHeader.Skip(IntegrityBytesLength).ToArray();
            else
                MessageBytes = messageWithoutHeader.ToArray();
            if (verify)
                VerifyMessage();
        }
        
        private void VerifyMessage()
        {
            if (MagicNumber != MAGIC_NUMBER)
                throw new Exception("Message integrity error. Magic numbers are different.");
            var integrityMessage = MessageBytes.Concat(Encoding.ASCII.GetBytes(Authenticator)).ToArray();
            var hash = SHA1.Create().ComputeHash(integrityMessage);
            if (IntegrityBytes.Length > 0 && !hash.SequenceEqual(IntegrityBytes))
                throw new Exception("Message integrity error. Invalid authenticator.");
            
            if (PlainTextLength != MessageBytes.Length)
                throw new Exception("Message integrity error. Invalid message length.");
        }
        public static implicit operator byte[](SQLServerCryptoMessage sqlServerCryptoMessage) => sqlServerCryptoMessage.ToByteArray();
        
        public byte[] ToByteArray()
        {
            byte[] result;
            using (var memoryStream = new MemoryStream())
            {
                using (var binaryWriter = new BinaryWriter(memoryStream))
                {
                    binaryWriter.Write(MagicNumber);
                    binaryWriter.Write(IntegrityBytesLength);
                    binaryWriter.Write(PlainTextLength);
                    if (IntegrityBytes != null)
                        binaryWriter.Write(IntegrityBytes);
                    if (MessageBytes != null)
                        binaryWriter.Write(MessageBytes);
                }
                result = memoryStream.ToArray();
            }
            return result;
        }
    }
}

SQLServerCryptoAlghorithm.cs

using System;
using System.Text;
using System.Linq;
using System.Security.Cryptography;
namespace SQLServerCrypto
{
    public class SQLServerCryptoAlgorithm
    {
        public readonly SQLServerCryptoVersion Version;
        public readonly HashAlgorithm Hash;
        public readonly SymmetricAlgorithm Symmetric;
        public readonly int KeySize;
        public SQLServerCryptoAlgorithm(SQLServerCryptoVersion sqlCryptoVersion)
        {
            Version = sqlCryptoVersion;
            switch (Version)
            {
                case SQLServerCryptoVersion.V1:
                    Hash = SHA1.Create();
                    Symmetric = TripleDES.Create();
                    KeySize = 16;
                    break;
                case SQLServerCryptoVersion.V2:
                    Hash = SHA256.Create();
                    Symmetric = Aes.Create();
                    KeySize = 32;
                    break;
                default:
                    throw new Exception("Unsupported SQLServerCryptoVersion");
            }
            Symmetric.Padding = PaddingMode.PKCS7;
            Symmetric.Mode = CipherMode.CBC;
        }
        public void SetKeyFromPassPhrase(string passphrase)
            => Symmetric.Key = Hash
                .ComputeHash(Encoding.Unicode.GetBytes(passphrase))
                .Take(KeySize)
                .ToArray();
    }
}

SQLServerCryptoHeader.cs

using System.Collections.Generic;
namespace SQLServerCrypto
{
    public class SQLServerCryptoHeader
    {
        public SQLServerCryptoVersion Version = SQLServerCryptoVersion.V1;
        public byte[] Reserved = { 0, 0, 0 };
        public byte[] InitializationVector = { };
        public SQLServerCryptoHeader(SQLServerCryptoVersion sqlServerCryptoVersion = SQLServerCryptoVersion.V1)
            => Version = sqlServerCryptoVersion;
        public static implicit operator byte[] (SQLServerCryptoHeader sqlServerCryptoHeader) => sqlServerCryptoHeader.ToByteArray();
        public byte[] ToByteArray()
        {
            var result = new List<byte>();
            result.Add((byte)Version);
            result.AddRange(Reserved);
            result.AddRange(InitializationVector);
            return result.ToArray();
        }
    }
}

SQLServerCryptoVersion.cs

namespace SQLServerCrypto
{
    public enum SQLServerCryptoVersion
    {
        ///<summary>
        /// TripleDES/SHA1
        /// </summary> 
        V1 = 0x01,
        /// <summary>
        /// AES256/SHA256
        /// </summary>
        V2 = 0x02
    }
}

十六进制字符串.cs

using System;
using System.Linq;
using System.Text.RegularExpressions;
namespace SQLServerCrypto
{
    public sealed class HexString
    {
        private readonly byte[] _byteArray;
        private readonly Regex RegExValidation = new Regex("^[0-9a-fA-F]+$", RegexOptions.Compiled);
        private const string PREFIX = "0x";
        public HexString(string hexString)
        {
            if (string.IsNullOrEmpty(hexString))
                throw new ArgumentException("Input string is null or empty.");
            hexString = RemovePrefix(hexString);
            if (hexString.Length % 2 != 0)
                throw new ArgumentException("Invalid number of hexcharacters.");
            if (!RegExValidation.IsMatch(hexString))
                throw new ArgumentException("Input string does not contain hexadecimal characters.");
            _byteArray = HexStringToByteArray(hexString);
        }
        public HexString(byte[] byteArray)
        {
            if (byteArray == null)
                throw new ArgumentException("Input array is null.");
            if (byteArray.Length == 0)
                throw new ArgumentException("Input array is empty.");
            _byteArray = byteArray;
        }
        public string ValueWithoutPrefix => ByteArrayToHexString(_byteArray);
        
        public string ValueWithPrefix => PREFIX + ValueWithoutPrefix;
        
        public override string ToString() => ValueWithPrefix;
        public byte[] ToByteArray() => _byteArray;
        public static implicit operator string(HexString hexString) => hexString.ToString();
        public static implicit operator byte[](HexString hexString) => hexString._byteArray;
        private static string RemovePrefix(string input) => input.StartsWith(PREFIX) ? input.Remove(0, 2) : input;
        
        // https://stackoverflow.com/questions/321370/how-can-i-convert-a-hex-string-to-a-byte-array#321404
        // Looks nice but could be faster.
        private static byte[] HexStringToByteArray(string hexString) => Enumerable.Range(0, hexString.Length) 
               .Where(x => x % 2 == 0)
               .Select(x => Convert.ToByte(hexString.Substring(x, 2), 16))
               .ToArray();
        private static string ByteArrayToHexString(byte[] byteArray) =>
            BitConverter.ToString(byteArray).Replace("-", string.Empty).ToLower();
    }
}

有关完整的源项目，请参阅以下链接。https://github.com/persec10000/Test_Invoice2QrCodeWithCryptedLink