如何实现对数据库单个字段进行加密-数据库专栏,SQL Server

create view v_rand

as

select c=unicode(cast(round(rand()*255,0) as tinyint))

go

create function f_jmstr(@str varchar(8000),@type bit)returns varchar(8000)

/*

*参数说明

*str:要加密的字符串或已经加密后的字符

*type:操作类型–0加密–解密

*返回值说明

*当操作类型为加密时(type–0):返回为加密后的str,即存放于数据库中的字符串

*当操作类型为解密时(type–1):返回为实际字符串,即加密字符串解密后的原来字符串

*/

as

begin

         declare @re varchar(8000)–返回值

         declare @c int–加密字符

         declare @i int

/*

*加密方法为原字符异或一个随机ascii字符

*/

    if @type=0–加密

    begin

                   select @c=c,@re=,@i=len(@str) from v_rand

                   while @i>0

                       select @re=nchar(unicode(substring(@str,@i,1))^@c^@i)+@re

                                     ,@i=@i-1

                   set @re=@re+nchar(@c)

    end

    else–解密

    begin

                   select @i=len(@str)-1,@c=unicode(substring(@str,@i+1,1)),@re=

                   while @i>0

                            select @re=nchar(unicode(substring(@str,@i,1))^@c^@i)+@re ,@i=@i-1

         end

         return(@re)

end

go

–测试

declare @tempstr varchar(20)

set @tempstr=  1 2   3aa

select dbo.f_jmstr(dbo.f_jmstr(@tempstr,0),1)

输出结果

  1 2   3aa

(完)