SQL Server数据的Aes加密存入与解密取出

key_194

已于 2023-03-22 18:36:12 修改

阅读量3.2k

点赞数 2

分类专栏： SQL Server加解密存取文章标签：安全数据库 c# sqlserver Powered by 金山文档

于 2023-03-22 16:33:16 首次发布

本文链接：https://blog.csdn.net/key_149/article/details/129702655

版权

SQL Server加解密存取专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近在做winfrom的毕设，边做边学，由于这个东西折磨了我一天，所以写一篇学习心得记录一下这天的收获，顺便吐槽一下这个气人代码；

由于本人是个菜鸡所以如果有缺陷或不足的地方欢迎大佬指出。

另：项目环境为：VS2022 、SQL Server 2019；编程语言为：C#

一、目前已知的SQL Server的加密方法

通过学习，我了解到的加解密方法无非两种：

通过SQL Server自带的Aes加密对数据库的内容进行加解密；

通过VS自带的System.Security.Cryptography这个命名空间中的Aes类对数据进行加解密

吐槽：

我本人查到的目前就这俩，而且这俩性质不一样，但保护数据内容不被泄露的角度来说是一致的，我就放一块说了；

说实话SQL自带加密的资料实在是不多，资料书上的也没有提过SQL Server加密方面的内容，论坛博客上虽然有，但总感觉有些凌乱，作为一个数据库入门的小白而言实在不太友好，故这方面没有太大进展；

二、通过VS编程加密

密钥与向量：

Aes作为传统的对称密码就不需要多介绍了（我也忘得快差不多了），密钥是通过自己随便编的一串字符经过扩展、字循环、字节代换等一系列变换最终成为一个真正意义上的32位（byte）密钥

实际代码层面上只需要用一串字符转换成byte数组后填充到32位就行，当然在VS提供的代码库中密钥是可以自己生成的

向量，这个问题在最基础的AES中是没有的，换句话说AES本身是没有向量这个概念的（我就说看微软文档的时候半天找不到向量的概念，最后翻书也不在加密算法的介绍里）

那向量是怎么来的呢？答案就是分组密码；当数据太多单次加密不过来时，就会把数据分成好几个块，分别进行加密；可密钥就一个，频繁使用破解几率就会增加，所以添加了向量这个概念，配合密钥加密；简单来说就是给分组密码的工作模式多加了一层防护（给加密上强度）

实际代码操作层面上，向量和密钥的制作是一样的（微软Aes库中密码本好像默认是CBC，但也有人编程采用的是EBC，算法向量长度都是16位），加解密时只需将密钥和向量一同放进函数中即可（简单到有种哆啦A梦道具的既视感）

库与环境与说明：

上文提到引用Aes库种的类和函数，必然要在使用前将命名空间引用过来，这个库包含很多加密算法的基类

using System.Security.Cryptography;//引用加密库的基类

代码内容改编自微软示例文档Aes 构造函数，会融合一部分我在学习过程中查阅的资料

代码出于我当时的需求编写，并不是简单的加解密存入取出SQL Server数据库中内容，但我会尽力挑出符合的部分说明，如有无法剔除的会特意标注；

最后再次强调本项目环境：编译器是VS2022 、数据库为SQL Server 2019、编程语言为：C#、创建的项目是WinFramework（具体项目创建和使用请查资料，B站，CSDN上都有详细的介绍）

密钥的自动生成：

    //using作用：
    //(一).作为指令，用于为命名空间创建别名或导入其他命名空间中定义的类型。
    //(二).作为语句，用于定义一个范围，在此范围的末尾将释放对象。
    //这里using作用是第二条
    using (Aes myAes = Aes.Create())//使用Aes类 创建对象myAes，对象内容为Aes.Create()即创建一个对称加密的对象
    {
        byte[] key = myAes.Key;//可以直接在函数中引用（如下文），此处作为说明单独提出来了；
        byte[] kIV = myAes.IV;//同上
        //myAes.Key和myAes.IV分别作为密钥和向量存在，此时密钥和向量已经生成,可以看到，密钥和向量都是以byte数组形式存在的
        //在引用时可直接使用例如：
        byte[] encrypted = EncryptStringToBytes_Aes(original, Key, kIV);
        //其中EncryptStringToBytes_Aes是自己编写的函数，形参分别是：原文，密钥，向量
    }

密钥文件的读取与文件格式的转换

出于任务需要，文件需要将密钥写入txt文件，使用时再取出，这里有需要特别注意的点：

格式问题：如下文代码所示，创建了一个文件读取对象，并将读取的文件存入字符串中

        public string ReadTXT(string FilePath)
        {
            try
            {
                //创建文件读取对象实例，用于读取文件，形参分别为文件路径和文件编码格式，其中Default是ANSI编码格式
                StreamReader readstring = new StreamReader(FilePath, System.Text.Encoding.Default);
                string myfile;
                myfile = readstring.ReadToEnd();
                //此句读取到尾时，已把光标指针移动到文件结尾,读取文件的编码格式只要不是UTF-8其他都行，否则在显示时会出现乱码，UTF8编码格式不影响代码运行
                //SQL server 默认格式gbk 20936格式
                readstring.Close();
                return myfile;
            }
            catch
            {
                return null;
            }
        }

字符串从txt文件读取时问题：如下文代码所示，这是一个button控件的内容，其中无关内容已被删除，在从txt文件中读取存入的密钥如上文的myAes.Key和myAes.IV时，其长度从原本的32、16变成了31、15（编码格式都是ANSI即System.Text.Encoding.Default.GetBytes(）形式获取byte数组）

        private void button4_Click(object sender, EventArgs e)//加密
        {
            //C#只能采用固定路径读取文件
            //tip: @可消除转义，如@"\"可以使"\"成为普通的字符
            byte[] rkey = new byte[32];
            byte[] rIV = new byte[16];
            byte[] key = System.Text.Encoding.Default.GetBytes("这里是读取的密钥字符串");//转换回byte是31位
            key.CopyTo(rkey, 0);//复制不足添0，成32位
            byte[] kIV = System.Text.Encoding.Default.GetBytes("这里是读取的向量字符串");//转换回byte是15位
            kIV.CopyTo(rIV, 0);//复制不足添0，成16位
            //经过填充新的变量key、kIV可以作为密钥和向量使用
        }

SQL Server数据库取出时的编码问题：这是这篇文章的核心也是最关键的地方（我为了这瓶醋，包的这顿饺），数据库在加密的时候并不困难，只要按照微软示例文档Aes 构造函数的方法将密钥，向量和提取的字符串放进去就行，如下图所示：（抹去了与加密、编码无关内容）

        private void button1_Click(object sender, EventArgs e)//点击后进行加密的模块
        {
            using (Aes EAes = Aes.Create())
                {
                    //EncryptStringToBytes_Aes是自定义的加密函数，其内容就是微软文档的内容，形参分别是明文，密钥，向量
                    byte[] encrypted = EncryptStringToBytes_Aes(original, rkey, rIV);
                    MessageBox.Show("此时存入的数据流的长度为：" + encrypted.Length.ToString());
                    //错误示范
                    string temp = System.Text.Encoding.Default.GetString(encrypted);
                    //打开数据库将数据存入表中...    
                }

        }

可以看到这段代码采用的字符串转换是通过System.Text.Encoding.Default.GetString()方式进行的，此时可以看到存入的字符串长度为：

解密本应同理，下面附上解密代码：（抹去了与解密、编码无关内容）

        private void button2_Click(object sender, EventArgs e)//解密
        {
            string dc = string.Empty;//此处dc是从数据库取出的字符串
            //...略去将数据库内容取出并赋值给dc的过程
            //如下行所示此时dc已经是数据库中取出的字符串了，其中de是通过System.Text.Encoding.Default.GetBytes()得到dc的byte流
            //错误示范
            byte[] de = System.Text.Encoding.Default.GetBytes(dc);
            MessageBox.Show("此时取出的数据流的长度为：" + de.Length.ToString());
            //下面DecryptStringFromBytes_Aes是自定义的解密函数，其内容就是微软文档里的内容
            //de是密文的比特流，rkey是密钥，rIV是向量，因为是Aes对称加密，所以密钥相同；
             string roundtrip = DecryptStringFromBytes_Aes(de, rkey, rIV);
            
        }

但解密的时候就会出现错误！如图：

会出现这个无法解决的问题；

查看转换成byte流的长度是就会发现：

存入前它的长度为16，取出后实际上它的byte长度是14，这是完全不符的

存取前后的长度都不一致，也就会造成读取的字符串不是完整的块问题了，从这个思路出发其实有两个疑点（其实马后炮的角度看问题是第六个，这里先不提）：

1数据库存入/取出时会将某些数据抹去，取出时这些数据就消失了，造成了数据的不完整

2数据编码时会出现问题导致前后内容不一致

这里第二条最好验证，先验证第二条：

        private void button1_Click(object sender, EventArgs e)
        {
            string str = "123456";
            textBox1.Text = str;
            byte[] decrypt = System.Text.Encoding.Default.GetBytes(str);
            MessageBox.Show(decrypt.Length.ToString());
            string str2 = System.Text.Encoding.Default.GetString(decrypt);
            textBox2.Text = str2;
            byte[] bytes = System.Text.Encoding.Default.GetBytes(str2);
            MessageBox.Show(bytes.Length.ToString());
        }

可以看到字符串同样编码转到byte数组，再从byte数组转回字符串，再转回来，byte长度都是不变的，且字符串的值也不会更改；

现在疑点2排除了，要证明疑点1就好办了，既然编码转换不会改变byte流，那我直接存入比特流就可以了（由于技术问题我实际是byte数组转成字符串存入），于是将数据库对应列改成varbinary二进制类型（字符串通过SQL数据库自带的类型转换CAST转换成二进制）；

这里我的逻辑是：byte数组转换字符串没问题，通过字符串经过SQL自带的类型转换成二进制数据也就是流的形式，都是二进制数，就没有存入\取出丢失数据的问题了吧？（如果真的有，SQL Server就别干了）

事与愿违；结果都是“不是完整的数据块”（不管是问题还是截图结果都一样，再复现过程太麻烦就不了）

此时两个疑点都证明了凶手不是他们（最起码在这个bug里是这样的）

注：按照时间线来说不论是疑点1还是疑点2都是后面的事；当遇到问题时，最简单的办法就是百度一下，有帖子说是：

（这是第一时间尝试的方案）在加密和解密时，都将输入文件的大小写入（在CBC模式下）到创建文件的前4个字节中。因此，解密时，必须忽略前4个字节

这个我试了：除了第三行是自己加的，剩下都是官网文档的内容，将流从第4个位置开始读（从0数，也就是第五个byte开始）

                using (MemoryStream msDecrypt = new MemoryStream(cipherText))
                {
                    msDecrypt.Seek(4, SeekOrigin.Begin);
                    using (CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read))
                    {
                        using (StreamReader srDecrypt = new StreamReader(csDecrypt))
                        {
                            plaintext = srDecrypt.ReadToEnd();
                        }
                    }
                }

结果是一致的，依旧是数据不是完整的块，由于测试的数据流有16位，我从0开始试到了16，包括用空内容去加密，也是从0试到了14（空的内容加密后就14位），除去12位bug有变化，剩下没有区别，超出16位的后面就没有内容了；

另：（叠甲）我不是说帖子的内容有误，只是当前的现实排除了“在加密和解密时，都将输入文件的大小写入（在CBC模式下）到创建文件的前4个字节中”的状况

（第二时间方案）按照上一个思路我怀疑是编码字符串的时候有像半个字符，或者有

“ ”、"\n"之类的内容被丢弃的内容，于是通过代码填充了内容二进制的0

            byte[] dz = new byte[16]; 
            byte[] de = System.Text.Encoding.Default.GetBytes(dc);//错误示范
            de.CopyTo(dz, 0);
            MessageBox.Show("此时取出的数据流的长度为：" + de.Length.ToString());
            using (Aes EAes = Aes.Create())
                {
                    //DecryptStringFromBytes_Aes自己定义的解密函数，内容和官方文档一致
                    string roundtrip = DecryptStringFromBytes_Aes(dz, rkey, rIV);
                }

可以看到确实是16位，但结果就是如下图所示，填充无效；

由于学识有限，填充方面的思路止步于此，由此拓展引发了之后的疑点1和疑点2去实践；

吐糟：说实话，这折腾了一个通宵的时间，当时以及气迷心了，开始走一些邪门歪道了，包括数据库自带的加密就是上头后收集到的方案

相继失败后，无意中发现了CSDN这个帖子：SQLServer CLR 函数AES加密解密代码

这篇文章确实做到了数据库内容的加解密存取，其实经过仔细研究就可以发现，这篇文章的代码和官方文档在关键步骤上是一致的，唯一不同的是在字符串编码时，采取的是

Convert.ToBase64String()//编码成字符串
Convert.FromBase64String()//编码成byte[]

System.Text.Encoding.Default.GetString()//编码成字符串
System.Text.Encoding.Default.GetBytes()//编码成byte[]

这两个编码方式是和官方文档实例唯一有出入的关键点，所以将官方文档的编码方式替换后，代码完美完成了自己的任务，顺利完成了加解密；（我当时想到了用Convert转换，怎么就没想到用ToBase64String()和FromBase64String()呢？）

现在复现这个问题的过程中，可以看到下图所示这是用System.Text.Encoding.Default.GetString()，编码格式的存入，Convert.FromBase64String()，编码格式取出的异常报告；

说明数据库只接受Base-64字符的表示；还记得在小标题“密钥文件的读取与文件格式的转换”的“格式问题”中提到过编码显示的问题吗？类推下来很有可能就是不同文件存入时的编码格式不同导致的问题；

在使用效果相同的情况下，其内在核心不同造成了内部数据的不同；

其实进入数据库可以发现两个编码本质上的不同：

第一个是System.Text.Encoding.Default.GetString()模式下的存入，后面是Convert.ToBase64String()模式下的存入；在数据库自己加密数据时也是后面2-4数据的大致加密样子（最起码不是第一行中的乱码），第一个是看似加密成功的失败品（因为解密不了）；这样子更能确定bug是编码问题导致的；

注：官方文档也是可以正常运行的，即System.Text.Encoding.Default.GetString()\System.Text.Encoding.Default.GetBytes()编码方式只要不存入数据库，其加解密数据通过变量传递是可以完成解密的

三、总结：

不同文件有不同格式，要注意相同效果下其本质数据格式的不同；

数据库采用的Base-64编码，所以最好采用

Convert.ToBase64String()

Convert.FromBase64String()

来解决问题

相同模式下编码是不改变内容的

数据库存储转变变量时用CAST和CONVERT（此CONVERT是SQL的函数）

AES只有一个密钥，密码本模式需要向量

多搜，多试，方法总比想法多；

补充一条System.Text.Encoding.Default.GetString()\System.Text.Encoding.Default.GetBytes()

在存入、读取txt时也会造成数据丢失；

四、附官方文档(+个人内容翻译+笔记)

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

namespace Aes_Example
{
    class AesExample
    {
        public static void Main()
        {
            string original = "Here is some data to encrypt!";//明文

            // Create a new instance of the Aes class.  //创建Aes的新实例类
            //This generates a new key and initialization vector (IV). // 这将生成一个新的密钥和初始化向量IV（微软Aes库采用密码本CBC模式）
            using (Aes myAes = Aes.Create())
            {
                //myAes.Key为bite类型
                // Encrypt the string to an array of bytes. //将字符串加密为字节数组
                byte[] encrypted = EncryptStringToBytes_Aes(original, myAes.Key, myAes.IV);

                // Decrypt the bytes to a string.//将字节解密为字符串
                string roundtrip = DecryptStringFromBytes_Aes(encrypted, myAes.Key, myAes.IV);

                //Display the original data and the decrypted data. //显示原始数据和解密数据 这两行在编程过程中没有，文档演示采用的
                Console.WriteLine("Original:   {0}", original);
                Console.WriteLine("Round Trip: {0}", roundtrip);
            }
        }
        static byte[] EncryptStringToBytes_Aes(string plainText, byte[] Key, byte[] IV)
        {
            // Check arguments.//检查冲突
            if (plainText == null || plainText.Length <= 0)
                throw new ArgumentNullException("plainText");//plainText是带入的string变量，原形是原文；这里是抛出问题，出bug的时候用的
            if (Key == null || Key.Length <= 0)
                throw new ArgumentNullException("Key");
            if (IV == null || IV.Length <= 0)
                throw new ArgumentNullException("IV");
            byte[] encrypted;

            // Create an Aes object //创建Aes对象
            // with the specified key and IV.//使用指定的key和IV
            using (Aes aesAlg = Aes.Create())
            {
                aesAlg.Key = Key;//这里的Key就是设定的密钥
                aesAlg.IV = IV;//同理向量

                // Create an encryptor to perform the stream transform.//创建一个加密器来执行流转换
                ICryptoTransform encryptor = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV);//ICryptoTransform接口，用来定义加密转换的基本操作

                // Create the streams used for encryption.//创建用于加密的流
                using (MemoryStream msEncrypt = new MemoryStream())//MemoryStream创建一个流，其后备存储为内存
                {
                    using (CryptoStream csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))// CryptoStream定义将数据流链接到加密转换的流
                    //第一个参数是上一行创建的流；第二个参数是创建的接口包含密钥和向量；第三个参数指定了加密流的模式 （读/写）
                    {
                        using (StreamWriter swEncrypt = new StreamWriter(csEncrypt))//将字符写入到特定的编码中
                        {
                            //Write all data to the stream.//将所有数据写入流
                            swEncrypt.Write(plainText);//参数为明文
                        }
                        encrypted = msEncrypt.ToArray();//bite类型承接加密后的明文；msEncrypt.ToArray()，将流内容写入字节数组
                    }
                }
            }

            // Return the encrypted bytes from the memory stream.//从内存流中返回加密的字节
            return encrypted;
        }
        //加密和解密的方法一样，解密就不翻译了;
        static string DecryptStringFromBytes_Aes(byte[] cipherText, byte[] Key, byte[] IV)
        {
            // Check arguments.
            if (cipherText == null || cipherText.Length <= 0)
                throw new ArgumentNullException("cipherText");
            if (Key == null || Key.Length <= 0)
                throw new ArgumentNullException("Key");
            if (IV == null || IV.Length <= 0)
                throw new ArgumentNullException("IV");

            // Declare the string used to hold
            // the decrypted text.
            string plaintext = null;

            // Create an Aes object
            // with the specified key and IV.
            using (Aes aesAlg = Aes.Create())
            {
                aesAlg.Key = Key;
                aesAlg.IV = IV;

                // Create a decryptor to perform the stream transform.
                ICryptoTransform decryptor = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV);

                // Create the streams used for decryption.
                using (MemoryStream msDecrypt = new MemoryStream(cipherText))
                {
                    using (CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read))
                    {
                        using (StreamReader srDecrypt = new StreamReader(csDecrypt))
                        {

                            // Read the decrypted bytes from the decrypting stream
                            // and place them in a string.
                            plaintext = srDecrypt.ReadToEnd();
                        }
                    }
                }
            }

            return plaintext;//还原的内容
        }
    }
}