添加密码加密SQL存储过程的函数(原创)
很多时候我们写的SQL数据库存储过程是以明文的方式保存在SQL中的,用户可以直接用一条sp_helptext 命令就可以看到你的存储过程内容,里面的逻辑一清二楚,知识保护也就相当脆弱!为此,我特地写了一个数据库的加密和解密函数。通过密码加密和解密,在程序代码中使用密码解密,在SQL存储过程中加密后再存储。这样别人就看不到你的SQL逻辑了。只要密码不告诉别人,别人就只能看到你加密后显示的一堆乱码。没有密码,即使知道解密方法和加密后的乱码也手足无措!
操作方法
- 01
/*加密函數 ALTER function [dbo].[encode](@password varchar(16),@sqltxt varchar(4000)) returns varchar(4000) as begin declare @i integer=0,@j integer=0,@returns varchar(4000),@char char,@lensql integer,@lenpwd integer select @lenpwd=LEN(@password),@lensql=LEN(@sqltxt),@returns='',@char='',@i=1,@j=1 while @j<=@lensql begin select @i=1,@char=char(CASE @i%2 WHEN 1 THEN ascii(SUBSTRING(@sqltxt,@j,1))^ascii(SUBSTRING(@password,@i,1))+1 ELSE ascii(SUBSTRING(@sqltxt,@j,1))^ascii(SUBSTRING(@password,@i,1))-1 end) select @i=@i+1 while @i<=@lenpwd begin set @char=char(CASE @i%2 WHEN 1 THEN ascii(@char)^ascii(SUBSTRING(@password,@i,1))+1 ELSE ascii(@char)^ascii(SUBSTRING(@password,@i,1))-1 end) select @i=@i+1 end select @j=@j+1,@returns=@returns+@char end return @returns end --*/ /*解密函數 ALTER function [dbo].[decode](@password varchar(16),@sqltxt varchar(4000)) returns varchar(4000) as begin declare @i integer=0,@j integer=0,@returns varchar(4000),@char char,@lensql integer,@lenpwd integer select @lenpwd=LEN(@password),@lensql=LEN(@sqltxt),@returns='',@char='',@j=1 while @j<=@lensql BEGIN select @i=@lenpwd,@char=char(CASE @i%2 WHEN 1 THEN (ascii(SUBSTRING(@sqltxt,@j,1))-1)^ascii(SUBSTRING(@password,@i,1)) ELSE (ascii(SUBSTRING(@sqltxt,@j,1))+1)^ascii(SUBSTRING(@password,@i,1)) end) select @i=@i-1 while @i>0 begin select @char=char(CASE @i%2 WHEN 1 THEN (ascii(@char)-1)^ascii(SUBSTRING(@password,@i,1)) ELSE (ascii(@char)+1)^ascii(SUBSTRING(@password,@i,1)) end) select @i=@i-1 end select @j=@j+1,@returns=@returns+@char end return @returns end --*/ --測試 declare @sql varchar(4000) select @sql=dbo.encode('a','select top 3 * from employees') print @sql print dbo.decode('a',@sql)