用于数据库加密的存储过程

实现了一个用于数据库加密的存储过程。密钥采用一个byte 的tinyint类型，进行异或。程序再运行一遍即为解密过程。set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[encrydata]-- Add the parameters for the stored procedure hereASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON; -- Insert statements for procedure here if not exists(select * from sysobjects where type ='U' and name='post_trial') create table post_trial(PostHead varchar(2048) null,nCertificate varchar(2) null,Name varchar(30)null,NetIP varchar(60) null);else drop table post_trial;create table post_trial(PostHead varchar(2048) null,nCertificate varchar(2) null,Name varchar(30)null,NetIP varchar(60) null);--end--endif insert into post_trial(PostHead,nCertificate,Name,NetIP) select top 50000 PostHead,Certificate,Name,NetIP from Post_history01; --set @ascl=cast(floor(rand(datepart(ms,getdate())*1000)*255) as varbinary(8)); --print 'random ascl '+ cast(@ascl as varchar(4)); -- declare @tablename varchar(100);--set @tablename = 'PW'+cast(datepart(hour,getdate())as varchar(32))+cast(datepart(minute,getdate())as varchar(32))+cast(datepart(second,getdate())as varchar(32));--print 'random table '+@tablename;--exec ('select top 100 into ' + @tablename +' from '+@table);--exec ('alter table '+ @tablename +' alter column PostHead image') ; set @tablename='post_trial'; set @table = @tablename; if not exists(select 1 from syscolumns where id= (select id from sysobjects where type='U' and name=@table) and name='rownum') begin select @rownum='alter table '+@table+' add rownum bigint identity(1,1)';print @rownum;exec(@rownum); end--end if --exec('select '+@count+'=count(*) from '+@table) ; select @sql=N'select @count=count(*) from '+ @table;print @sql exec sp_executesql @sql, N'@count bigint out ,@table varchar(50)',@count out,@table print @count if @count>0 begin set @j=1;while @count >= @j beginset @idx=cast(@j as varchar(10));select @sql=N'select @orgistr=Name from '+@table +N' where rownum='+@idx;print @sqlexec sp_executesql @sql, N'@orgistr varchar(8000) out,@table varchar(50),@idx varchar(10)',@orgistr out,@table,@idx;--exec(@sql);print 'org str '+@orgistr;set @i=len(@orgistr); print @i; while @i>0 beginset @strchar=cast(substring(@orgistr,@i,1) as varbinary(8)); select @encrystr= nchar(unicode(substring(@orgistr,@i,1))^@c)+@encrystr;set @i=@i-1;end--end whileprint 'encry str '+@encrystr;print 'update '+@table+' set Name='''+@encrystr+''' where rownum= '+@idx;exec('update '+@table+' set Name= '''+@encrystr+''' where rownum= '+@idx);set @encrystr='';set @j=@j+1;end--end whileend --end if --exec('select * from '+@table ); /*select @i=len(@encrystr),@dencrystr=''; print @i; while @i>0 begin select @dencrystr=nchar(unicode(substring(@encrystr,@i,1))^@c)+@dencrystr ,@i=@i-1 end print @dencrystr;*/END