问题描述
遵循在C#中复制T-SQL DecryptByPassPhrase,我是无法使用 MSSQL 进行简单加密以在 C# 中解密.某些列中的加密值是必需的,因为该表会定期导出到 Excel 和 Access 中,因此简单的加密足以阻止"值,而无需让开发人员(重新)执行视图等.
Following Replicate T-SQL DecryptByPassPhrase in C#, I am unable to get a simple encryption with MSSQL to descrypt in C#. The encrypted values in certain columns is necessary because the table is exported into Excel and Access on a regular basis so simple encryption is more than enough to "block" values without having to involve developers to (re)do views, etc.
在 SQL Server 2012 中:
In 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# 代码应该是什么样的?
What should working C# code look like?
谢谢.
推荐答案
SQL Server 2017 使用 SHA256 密码哈希 + AES-256 加密
SQL Server 2017 uses SHA256 hashing of password + AES-256 encryption
旧版本使用密码的 SHA1 散列 + 3DES-128 加密
Older versions use SHA1 hashing of password + 3DES-128 encryption
IV 大小与块大小相同:AES = 128 位,3DES = 64 位
IV size is the same as block size: AES = 128 bits, 3DES = 64 bits
填充模式:PKCS #7密码模式:CBC
Padding mode: PKCS #7 Cipher mode: CBC
服务器 2017 加密的数据以0x02"开头,旧版本以0x01"开头.
Data encrypted by server 2017 starts with "0x02", older versions start with "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();
}
这篇关于C# 从 SQL Server EncryptByPassPhrase 解密字节?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!