在C#中的AES加密在T-SQL解密 [英] AES encrypt in c# decrypt in T-SQL
问题描述
我写了下面的代码解密一些敏感的数据,在大多数的,我需要使用T-SQL查询数据,我可以不解密由该代码加密的数据的情况。所以我的问题是我怎么能写在T-SQL函数的工作方式相同像它在C#中工作,我将消耗,在存储过程。
在此先感谢
加密功能:
公共静态字符串加密(字符串文本)
{
如果(string.IsNullOrEmpty(EncryptionKey设置))
返回的String.Empty;
如果(string.IsNullOrEmpty(文本))
返回的String.Empty;
VAR clearBytes = Encoding.Unicode.GetBytes(文本);
使用(VAR加密= Aes.Create())
{
变种PDB =新Rfc2898DeriveBytes(EncryptionKey设置,新的byte []
{
0x49,
0x76,
0x61,
0x6e,
为0x20,
送出0x4d,
0x65,
0x64,
0x76,
0x65 ,
0x64,
0x65,
0x76
});
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
使用(VAR毫秒=新的MemoryStream())
{
使用(VAR CS =新的CryptoStream(MS,encryptor.CreateEncryptor(),CryptoStreamMode.Write))
$ { b $ b cs.Write(clearBytes,0,clearBytes.Length);
}
文本= Convert.ToBase64String(ms.ToArray());
}
}
返回文本;
}
解密功能:
公共静态字符串解密(字符串文本)
{
如果(string.IsNullOrEmpty(EncryptionKey设置))
返回的String.Empty;
如果(string.IsNullOrEmpty(文本))
返回的String.Empty;
VAR cipherBytes = Convert.FromBase64String(文本);
使用(VAR加密= Aes.Create())
{
变种PDB =新Rfc2898DeriveBytes(EncryptionKey设置,新的byte []
{
0x49,
0x76,
0x61,
0x6e,
为0x20,
送出0x4d,
0x65,
0x64,
0x76,
0x65 ,
0x64,
0x65,
0x76
});
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
使用(VAR毫秒=新的MemoryStream())
{
使用(VAR CS =新的CryptoStream(MS,encryptor.CreateDecryptor(),CryptoStreamMode.Write))
$ { b $ b cs.Write(cipherBytes,0,cipherBytes.Length);
}
文本= Encoding.Unicode.GetString(ms.ToArray());
}
}
返回文本;
}
您可以创建一个CLR UDF 。在SQL Server
请参考下面的详细信息链接:
的 http://blog.sqlauthority.com / 2008/10/19 / SQL服务器引进到CLR-简单的例子 - 的 - CLR的存储过程/
<一个HREF =https://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.90).aspx相对=nofollow> https://msdn.microsoft.com/en-us/库/ w2kae45k(v = VS.90)的.aspx
I have written the following code to decrypt some sensitive data, in most of the cases i need to query data using T-SQL where i can't decrypt the the data that is encrypted by this code. so my question is this how can i write a function in T-SQL that work the same way as like it work in C#, I will consume that in Stored procedures.
thanks in Advance
Encryption Function:
public static string Encrypt(string text)
{
if (string.IsNullOrEmpty(EncryptionKey))
return string.Empty;
if (string.IsNullOrEmpty(text))
return string.Empty;
var clearBytes = Encoding.Unicode.GetBytes(text);
using (var encryptor = Aes.Create())
{
var pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[]
{
0x49,
0x76,
0x61,
0x6e,
0x20,
0x4d,
0x65,
0x64,
0x76,
0x65,
0x64,
0x65,
0x76
});
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (var ms = new MemoryStream())
{
using (var cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
{
cs.Write(clearBytes, 0, clearBytes.Length);
}
text = Convert.ToBase64String(ms.ToArray());
}
}
return text;
}
Decryption Function:
public static string Decrypt(string text)
{
if (string.IsNullOrEmpty(EncryptionKey))
return string.Empty;
if (string.IsNullOrEmpty(text))
return string.Empty;
var cipherBytes = Convert.FromBase64String(text);
using (var encryptor = Aes.Create())
{
var pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[]
{
0x49,
0x76,
0x61,
0x6e,
0x20,
0x4d,
0x65,
0x64,
0x76,
0x65,
0x64,
0x65,
0x76
});
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (var ms = new MemoryStream())
{
using (var cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
{
cs.Write(cipherBytes, 0, cipherBytes.Length);
}
text = Encoding.Unicode.GetString(ms.ToArray());
}
}
return text;
}
You can create a CLR UDF in SQL Server.
Refer following links for more info:
https://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.90).aspx
这篇关于在C#中的AES加密在T-SQL解密的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!