更新时间:2023-02-17 16:45:08
您可以创建一个CLR UDF 。在SQL Server
请参考下面的详细信息链接:
的 http://blog.sqlauthority.com / 2008/10/19 / SQL服务器引进到CLR-简单的例子 - 的 - CLR的存储过程/
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