SQL2005中部署.net 3DES加密函数_MySQL
前阵子,需要给数据加密,所以写了个3DES加密函数部署到SQL2005中,当然,现在秘钥也作为一个表存在在数据库中,至于这个密钥,以后可以考虑用usb-key使用rsa加密或者别的方式进行加密,以保证数据安全。现在先说怎么实现部署到sqlserver中的加密函数吧。
新建一个项目。VB中的database-->SQL Server Project,我新建了以后叫做DESCryptoService的项目,之后,会让你添加数据库引用,选择你要部署到的数据库。
写入如下代码,就OK了
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Imports System.Xml
Imports System.Text
Imports System.Security.Cryptography
Partial Public Class EncryptToBase64DecryptFromBase64Class EncryptToBase64DecryptFromBase64
Public Shared Function EncryptToBase64String()Function EncryptToBase64String(ByVal stringToEncryptsql As SqlString, ByVal SEncryptionKeysql As SqlString) As SqlString
Dim stringToEncrypt As String = CType(stringToEncryptsql, String)
Dim SEncryptionKey As String = CType(SEncryptionKeysql, String)
Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
Dim key() As Byte = {}
Try
key = System.Text.Encoding.UTF8.GetBytes(Left(SEncryptionKey, 8))
Dim des As New DESCryptoServiceProvider()
Dim inputByteArray() As Byte = Encoding.UTF8.GetBytes(stringToEncrypt)
Dim ms As New MemoryStream()
Dim cs As New CryptoStream(ms, des.CreateEncryptor(key, IV), CryptoStreamMode.Write)
cs.Write(inputByteArray, 0, inputByteArray.Length)
cs.FlushFinalBlock()
Return Convert.ToBase64String(ms.ToArray())
Catch e As Exception
Return CType(e.Message, SqlString)
End Try
End Function
Public Shared Function DecryptFromBase64String()Function DecryptFromBase64String(ByVal stringToDecryptsql As SqlString, ByVal sEncryptionKeysql As SqlString) As SqlString
Dim stringToDecrypt As String = CType(stringToDecryptsql, String)
Dim sEncryptionKey As String = CType(sEncryptionKeysql, String)
Dim inputByteArray(stringToDecrypt.Length) As Byte
Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
Dim key() As Byte = {}
Try
key = System.Text.Encoding.UTF8.GetBytes(Left(sEncryptionKey, 8))
Dim des As New DESCryptoServiceProvider()
inputByteArray = Convert.FromBase64String(stringToDecrypt)
Dim ms As New MemoryStream()
Dim cs As New CryptoStream(ms, des.CreateDecryptor(key, IV), CryptoStreamMode.Write)
cs.Write(inputByteArray, 0, inputByteArray.Length)
cs.FlushFinalBlock()
Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
Return encoding.GetString(ms.ToArray())
Catch e As Exception
Return CType(e.Message, SqlString)
End Try
End Function
End Class
完成之后选择生成,部署即可
至于测试,是否可用,打开查询分析器,执行下面语句,
SELECT dbo.DecryptFromBase64String(dbo.EncryptToBase64String('123456','12345678'),'12345678')
如果出现正确的值:123456,说明成功了