当前页面位置: » 丰搜网 » 文档中心 » 详细内容
sql2005中部署.net 3des加密函数
sql2005支持在sql server里部署.net应用程序了，那么以前一直写在.net里的一些运算如加密等已经完全可以移到sql里来进行了，而不需要再从程序中运算了，使数据库可以和程序相对独立开来，方便了不少。
前阵子，需要给数据加密，所以写了个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
<microsoft.sqlserver.server.sqlfunction()> _
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
<microsoft.sqlserver.server.sqlfunction()> _
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,说明成功了
