这几天需要对系统中的一些关键数据进行加密。用VB.NET编写CLR函数,然后在存储过程中略加修改即完成任务。
在测试过程中遇到不少问题,比如说不熟悉流对象(初学VB.NET),不熟悉编码转换,最重要的是加密的密钥长度限制,可能很多人会遇到。
下面的代码中实现了散列加密算法和对称加密算法,用的都是.NET安全算法的工厂类,并实现常用的Base64编码和解码。
常用的散列加密算法有:MD5,SHA(SHA1),RIPEMD160,SHA256,SHA384,SHA512,MACTripleDES
常用的对称加密算法有:DES,RC2,Rijndael,TripleDES。对称加密算法有一个key和一个IV,不同的算法要求的key和IV长度不一样,而且同一个字符串在不同的编码下长度也不一样,这一点要特别注意。
经测试,不同编码对应的key和IV长度对应关系如下:
贴代码:
- Imports System
- Imports System.Data
- Imports System.Data.sqlClient
- Imports Microsoft.sqlServer.Server
- Imports System.Runtime.InteropServices
- Imports System.Security
- Imports System.Data.sqlTypes
- Imports System.Security.Cryptography
- Namespace SOP.Security
- Public Class Security
- '散列加密算法
- <Microsoft.sqlServer.Server.sqlFunction()> _
- Public Shared Function HashAlgorithm(ByVal Input As sqlString,ByVal Algorithm As sqlString,ByVal Charset As sqlString) As sqlString
- Dim _md5 As System.Security.Cryptography.HashAlgorithm = System.Security.Cryptography.HashAlgorithm.Create(Algorithm.Value)
- Dim fs() As Byte,sb As New System.Text.StringBuilder
- fs = _md5.ComputeHash(getBytes(Input.Value,Charset.Value))
- For i As Integer = 0 To fs.Length - 1
- sb.Append(fs(i).ToString("x2"))
- Next
- Return sb.ToString()
- End Function
- '对称加密算法
- <Microsoft.sqlServer.Server.sqlFunction()> _
- Public Shared Function SymmetricEncrypt(ByVal Input As sqlString,ByVal Key As sqlString,ByVal IV As sqlString,ByVal Charset As sqlString) As sqlString
- Dim sa As System.Security.Cryptography.SymmetricAlgorithm = System.Security.Cryptography.SymmetricAlgorithm.Create(Algorithm.Value) ' New System.Security.Cryptography.DESCryptoServiceProvider
- Dim data() As Byte
- sa.Key = getBytes(Key.Value,Charset.Value)
- sa.IV = getBytes(IV.Value,Charset.Value)
- '将数据转换成字节
- data = getBytes(Input.Value,Charset.Value)
- Using ms As New System.IO.MemoryStream
- '将数据加密,并准备写入ms内存流中
- Dim cs As New System.Security.Cryptography.CryptoStream(ms,sa.CreateEncryptor,CryptoStreamMode.Write)
- '写入加密数据至缓存区
- cs.Write(data,data.Length)
- '将数据更新到流对象
- cs.FlushFinalBlock()
- '关闭
- cs.Close()
- '用base64编码一次
- Return Convert.ToBase64String(ms.ToArray)
- End Using
- End Function
- '对称解密算法
- <Microsoft.sqlServer.Server.sqlFunction()> _
- Public Shared Function SymmetricDecrypt(ByVal Input As sqlString,ByVal Charset As sqlString) As sqlString
- Dim sa As System.Security.Cryptography.SymmetricAlgorithm = System.Security.Cryptography.SymmetricAlgorithm.Create(Algorithm.Value) ' System.Security.Cryptography.DESCryptoServiceProvider
- Dim data() As Byte
- sa.Key = getBytes(Key.Value,Charset.Value)
- '将数据用base64解码一次
- data = Convert.FromBase64String(Input.Value)
- Using ms As New System.IO.MemoryStream()
- '将数据加密,sa.CreateDecryptor,data.Length)
- '将数据更新到流对象
- cs.FlushFinalBlock()
- '关闭
- cs.Close()
- '再转换回字符串
- Return getString(ms.ToArray,Charset.Value)
- End Using
- End Function
- 'Base64
- <Microsoft.sqlServer.Server.sqlFunction()> _
- Public Shared Function Base64Encode(ByVal Input As sqlString,ByVal Charset As sqlString) As sqlString
- Return Convert.ToBase64String(getBytes(Input.Value,Charset.Value))
- End Function
- <Microsoft.sqlServer.Server.sqlFunction()> _
- Public Shared Function Base64Decode(ByVal Input As sqlString,ByVal Charset As sqlString) As sqlString
- Return getString(Convert.FromBase64String(Input.Value),Charset.Value)
- End Function
- '转换字符串至指定编码的字节
- Private Shared Function getBytes(ByVal Input As String,ByVal Charset As String) As Byte()
- Select Case Charset
- Case "UTF-16","Unicode","UTF16"
- Return System.Text.Encoding.Unicode.GetBytes(Input)
- Case "UTF-7","UTF7"
- Return System.Text.Encoding.UTF7.GetBytes(Input)
- Case "UTF-8","UTF8"
- Return System.Text.Encoding.UTF8.GetBytes(Input)
- Case "UTF-32","UTF32"
- Return System.Text.Encoding.UTF32.GetBytes(Input)
- Case "ASCII"
- Return System.Text.Encoding.ASCII.GetBytes(Input)
- Case "","Default"
- Return System.Text.Encoding.Default.GetBytes(Input)
- Case Else
- Return System.Text.Encoding.Default.GetBytes(Input)
- End Select
- End Function
- '字节转换至指定编码的字符串
- Private Shared Function getString(ByVal Input() As Byte,ByVal Charset As String) As String
- Select Case Charset
- Case "UTF-16","UTF16"
- Return System.Text.Encoding.Unicode.GetString(Input)
- Case "UTF-7","UTF7"
- Return System.Text.Encoding.UTF7.GetString(Input)
- Case "UTF-8","UTF8"
- Return System.Text.Encoding.UTF8.GetString(Input)
- Case "UTF-32","UTF32"
- Return System.Text.Encoding.UTF32.GetString(Input)
- Case "ASCII"
- Return System.Text.Encoding.ASCII.GetString(Input)
- Case "","Default"
- Return System.Text.Encoding.Default.GetString(Input)
- Case Else
- Return System.Text.Encoding.Default.GetString(Input)
- End Select
- End Function
- End Class
- End Namespace
以下是在sql SERVER2005中安装CLR函数的方法:
- alter database sop set TRUSTWORTHY on;
- exec sp_changedbowner sa
- go
- CREATE ASSEMBLY [SOP.Security]
- AUTHORIZATION [dbo]
- FROM 'E:\hsl\sop\sop.ScriptEngine.dll'
- WITH PERMISSION_SET = unsafe
- go
- CREATE ASSEMBLY [SOP.Security]
- AUTHORIZATION [dbo]
- WITH PERMISSION_SET = UNSAFE
- go
- Create FUNCTION [dbo].[SymmetricDecrypt](@Input [nvarchar](4000),@Key [nvarchar](4000),@IV [nvarchar](4000),@Algorithm [nvarchar](4000),@Charset [nvarchar](4000))
- RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
- AS
- EXTERNAL NAME [SOP.Security].[SOP.Security.Security].[SymmetricDecrypt]
- go
- Create FUNCTION [dbo].[SymmetricEncrypt](@Input [nvarchar](4000),@Charset [nvarchar](4000))
- RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
- AS
- EXTERNAL NAME [SOP.Security].[SOP.Security.Security].[SymmetricEncrypt]
- GO
- Create FUNCTION [dbo].[HashAlgorithm](@Input [nvarchar](4000),@Charset [nvarchar](4000))
- RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
- AS
- EXTERNAL NAME [SOP.Security].[SOP.Security.Security].[HashAlgorithm]
- GO
- Create FUNCTION [dbo].[Base64Encode](@Input [nvarchar](4000),@Charset [nvarchar](4000))
- RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
- AS
- EXTERNAL NAME [SOP.Security].[SOP.Security.Security].[Base64Encode]
- GO
- Create FUNCTION [dbo].[Base64Decode](@Input [nvarchar](4000),@Charset [nvarchar](4000))
- RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
- AS
- EXTERNAL NAME [SOP.Security].[SOP.Security.Security].[Base64Decode]
- go
调用示例:
- DECLARE @key VARCHAR(500),@VI VARCHAR(500),@s nVARCHAR(MAX),@d nVARCHAR(MAX),@d1 nVARCHAR(MAX)
- SELECT @key='55523423123456785552342312345678',@VI='55523423123456785552342312345678',@s='定义全局变量,并改写afterFinalSubmit方法'
- SELECT @d=dbo.SymmetricEncrypt(@s,left(@key,4),left(@VI,2),'TripleDES','UTF32')
- PRINT @d
- 输出:
- e2TRpdUG4+bY1Glg0roDFinHcKU+s2aKxNnVSdfSKPt4i8bN8leTWqWelGtMndbv2cDp2vacfMN8WmzwYyr6l1Q2pdO3Xsx6xykVcpfGXk2sjS//jO4/AyrDWT7h+HRECmETmS3M2poZhbEYsrNerA/hny/J1xzO
由于不太了解.net和加密算法,此文只是我简单测试后的结果,如有错误,请及时指出。