SQL2005中部署.net3DES加密函數(shù)_MySQL

SQL2005支持在SQL SERVER里部署.net應用程序了，那么以前一直寫在.net里的一些運算如加密等已經(jīng)完全可以移到sql里來進行了，而不需要再從程序中運算了，使數(shù)據(jù)庫可以和程序相對獨立開來，方便了不少。
前陣子，需要給數(shù)據(jù)加密，所以寫了個3DES加密函數(shù)部署到SQL2005中，當然,現(xiàn)在秘鑰也作為一個表存在在數(shù)據(jù)庫中,至于這個密鑰,以后可以考慮用usb-key使用rsa加密或者別的方式進行加密,以保證數(shù)據(jù)安全。現(xiàn)在先說怎么實現(xiàn)部署到sqlserver中的加密函數(shù)吧。
新建一個項目。VB中的database-->SQL Server Project，我新建了以后叫做DESCryptoService的項目，之后，會讓你添加數(shù)據(jù)庫引用，選擇你要部署到的數(shù)據(jù)庫。
寫入如下代碼，就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

完成之后選擇生成，部署即可

至于測試，是否可用，打開查詢分析器，執(zhí)行下面語句，
SELECT dbo.DecryptFromBase64String(dbo.EncryptToBase64String('123456','12345678'),'12345678')
如果出現(xiàn)正確的值：123456,說明成功了

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com