CLR函数 SQL直接调用 C# DLL MSSQL DES 加解密

（原：http://topic.csdn.net/u/20100929/17/21bfc421-4ace-435f-baea-4d352d1015e3.html）

因业务升级，将原用户DES密文转换为MD5，需要在SQL中进行DES解密操作，故使用CLR函数实现，特此记录，以备后用。--by RYHAN

C#程序集 dll 文件

 1 using System;
 2 using System.IO;
 3 using System.Text;
 4 using System.Data;
 5 using System.Data.SqlTypes;
 6 using System.Security.Cryptography;
 7 using Microsoft.SqlServer.Server;
 8 
 9 public class DES
10 {
11     [SqlFunction(IsDeterministic=true, IsPrecise=true)]
12     public static SqlString DESEncrypt(SqlString text, SqlString key)
13     {
14         if (text.IsNull || key.IsNull || key.Value.Length < 8)
15             return null;
16 
17         return (SqlString)_DESEncrypt(Encoding.Default.GetBytes((string)text),
18             Encoding.Default.GetBytes((string)key), 
19             new byte[] { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF });
20     }
21     // Encrypt with DES
22     private static string _DESEncrypt(byte[] text, byte[] key, byte[] iv)
23     {
24         string entext;
25 
26         using (MemoryStream mstream = new MemoryStream())
27         {
28             DESCryptoServiceProvider des = new DESCryptoServiceProvider();
29             CryptoStream estream = new CryptoStream(mstream, des.CreateEncryptor(key, iv), CryptoStreamMode.Write);
30             try
31             {
32                 estream.Write(text, 0, text.Length);
33                 estream.FlushFinalBlock();
34                 entext = Convert.ToBase64String(mstream.ToArray());
35             }
36             finally
37             {
38                 estream.Close();
39                 des.Clear();
40             }
41         }
42 
43         return entext;
44     }
45 
46     [SqlFunction(IsDeterministic=true, IsPrecise=true)]
47     public static SqlString DESDecrypt(SqlString text, SqlString key)
48     {
49         if (text.IsNull || key.IsNull || key.Value.Length < 8)
50             return null;
51 
52         return (SqlString)_DESDecrypt(Convert.FromBase64String((string)text),
53             Encoding.Default.GetBytes((string)key),
54             new byte[] { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF });
55     }
56     // Decrypt with DES
57     private static string _DESDecrypt(byte[] text, byte[] key, byte[] iv)
58     {
59         string detext;
60 
61         using (MemoryStream mstream = new MemoryStream())
62         {
63             DESCryptoServiceProvider des = new DESCryptoServiceProvider();
64             CryptoStream estream = new CryptoStream(mstream, des.CreateDecryptor(key, iv), CryptoStreamMode.Write);
65             try
66             {
67                 estream.Write(text, 0, text.Length);
68                 estream.FlushFinalBlock();
69                 detext = Encoding.Default.GetString(mstream.ToArray());
70             }
71             finally
72             {
73                 estream.Close();
74                 des.Clear();
75             }
76         }
77 
78         return detext;
79     }
80 }

SQL CLR 函数

 1  use master 
 2  go 
 3  -- 启用 SQL Server 的 CLR 功能 
 4  exec sp_configure 'show advanced options',1; 
 5  go 
 6  reconfigure 
 7  go 
 8  exec sp_configure 'clr enabled',1; 
 9  go 
10  reconfigure 
11  go 
12   
13  if object_id('dbo.des_encrypt','FS') is not null 
14   drop function dbo.des_encrypt; 
15  go 
16   
17  if object_id('dbo.des_decrypt','FS') is not null 
18   drop function dbo.des_decrypt; 
19  go 
20   
21  -- 创建程序集 
22  if exists (select * from sys.assemblies where name='DES') 
23   drop assembly DES; 
24  go 
25  create assembly DES authorization dbo 
26  from 'C:\clrDES.dll' -- dll 文件路径 
27  with permission_set=safe; 
28  go 
29   
30  create function dbo.des_encrypt (@text nvarchar(max), @key nvarchar(128)) 
31  returns nvarchar(max) 
32  as external name DES.DES.DESEncrypt; 
33  go 
34   
35  -- select dbo.des_encrypt(N'hello world', N'88888888'); 
36   
37  create function dbo.des_decrypt (@text nvarchar(max), @key nvarchar(128)) 
38  returns nvarchar(max) 
39  as external name DES.DES.DESDecrypt; 
40  go 
41   
42  -- select dbo.des_decrypt(N'+GeLDT6kAxZlm2pnFX8X4w==',N'88888888');

因业务升级，将原用户DES密文转换为MD5，需要在SQL中进行DES解密操作，故使用CLR函数实现，特此记录，以备后用。--by RYHAN