sqlServer使用程序集创建AES加密与解密函数

第一步：创建工程

创建一个C++的项目

第二步：添加一个函数的文件

文件中代码：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System;
using System.IO;
using System.Text;
using System.Security.Cryptography;

public partial class UserDefinedFunctions
{
    
    /**
     * AES 加密
     **/
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess = DataAccessKind.Read,
        IsDeterministic = true,
        Name = "encryptField")]
    public static string encryptField(string toEncrypt, string key)
    {
        //string key = "AD42F6697B035B75";
        byte[] keyArray = UTF8Encoding.UTF8.GetBytes(key);
        byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);

        RijndaelManaged rDel = new RijndaelManaged();
        rDel.Key = keyArray;
        rDel.Mode = CipherMode.ECB;
        rDel.Padding = PaddingMode.PKCS7;

        ICryptoTransform cTransform = rDel.CreateEncryptor();
        byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);

        return Convert.ToBase64String(resultArray, 0, resultArray.Length);
    }


    /**
     * AES 解密
     **/
    [Microsoft.SqlServer.Server.SqlFunction(
       DataAccess = DataAccessKind.Read,
       IsDeterministic = true,
       Name = "decryptField")]
    public static string decryptField(string toDecrypt,string key)
    {
        //判断是否是加密字符串,如果不是则直接返回return原始值
        if (!toDecrypt.Contains("mpw:")) {
            return toDecrypt;
        }
        //去除指定字符前缀
        toDecrypt = toDecrypt.Replace("mpw:", "");
        //string key = "AD42F6697B035B75";
        byte[] keyArray = UTF8Encoding.UTF8.GetBytes(key);
        byte[] toEncryptArray = Convert.FromBase64String(toDecrypt);

        RijndaelManaged rDel = new RijndaelManaged();
        rDel.Key = keyArray;
        rDel.Mode = CipherMode.ECB;
        rDel.Padding = PaddingMode.PKCS7;

        ICryptoTransform cTransform = rDel.CreateDecryptor();
        byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);

        return UTF8Encoding.UTF8.GetString(resultArray);
    }



}

最后点上方的启动进行编译

第三步：测试函数执行

解决方案下新增一个TestScript文件夹和一个sql脚本文件

直接在脚本中调用这两个新创建的加密和解密函数,如果需要断点可以使用断点调试的运行方式就可以在运行时进入代码

第四步：生成dll

对着解决方案邮件生成,到控制台中会输出编译的文件目录

第四步：dll加载到sqlServer成为自定义函数

确保开启了CLR功能

-- 开启 CLR
sp_configure 'clr enabled', 1;
RECONFIGURE WITH override
GO

将编译生成的dll文件存放到一个路径下,然后执行下方脚本来加载程序集

-- 注册程序集
CREATE ASSEMBLY [DhgCLR] FROM 'D:\DhgCLR.dll'
WITH PERMISSION_SET = SAFE; --指定程序集的权限

注册代码中的两个方法为对应的程序集

--注册[加密]标量函数
CREATE FUNCTION [dbo].[encryptField](@str NVARCHAR(1000),@key NVARCHAR(100))
RETURNS NVARCHAR(500)
WITH EXECUTE AS CALLER        --用于在用户在执行函数的时候对引用的对象进行权限检查
AS
EXTERNAL NAME [DhgCLR].[UserDefinedFunctions].[encryptField]    --EXTERNAL NAME 程序集名.类名.方法名
GO
--注册[解密]标量函数
CREATE FUNCTION [dbo].[decryptField](@str NVARCHAR(1000),@key NVARCHAR(100))
RETURNS NVARCHAR(500)
WITH EXECUTE AS CALLER        --用于在用户在执行函数的时候对引用的对象进行权限检查
AS
EXTERNAL NAME [DhgCLR].[UserDefinedFunctions].[decryptField]    --EXTERNAL NAME 程序集名.类名.方法名
GO

最后测试函数是否正常执行即可

-- 测试执行
-- 加密
select dbo.encryptField('66666','AD42F6697B035B75');
-- 解密
select dbo.decryptField('zNcviuc6Cn/sO+QDRbkJjw==','AD42F6697B035B75');