在SQL SERVER中实现RSA加解密函数

红太狼 2021-08-24 17:53 470阅读 0赞

基本含义：RSA公开密钥密码体制。所谓的公开密钥密码体制就是使用不同的加密密钥与解密密钥，是一种“由已知加密密钥推导出解密密钥在计算上是不可行的”密码体制。

在SQL SERVER中实现RSA加密算法

\--判断是否为素数

if object_id('f_primeNumTest') is not null
    
      drop function f_primeNumTest
    
    Go
    
    create function [dbo].[f_primeNumTest]
    
    (@p int)
    
    returns bit
    
    begin
    
      declare @flg bit,@i int
    
      select @flg=1, @i=2
    
      while @i<=sqrt(@p)
    
      begin
    
         if(@p%@i=0  )
    
         begin
    
            set @flg=0
    
           break
    
         end 
    
         set @i=@i+1
    
      end
    
      return @flg
    
    end
    
    go

\--判断两个数是否互素

if object_id('f_isNumsPrime') is not null
    
      drop function f_isNumsPrime
    
    go
    
    create function f_isNumsPrime
    
    (@num1 int,@num2 int)
    
    returns bit
    
    begin
    
      declare @tmp int,@flg bit
    
      set @flg=1
    
      while (@num2%@num1<>0)
    
      begin
    
        select @tmp=@num1,@num1=@num2%@num1,@num2=@tmp
    
      end
    
      if @num1=1
    
         set @flg=0
    
      return @flg
    
    end
    
    go

\--产生密钥对

if object_id('p_createKey') is not null
    
      drop proc p_createKey
    
    go
    
    create proc p_createKey
    
    @p int,@q int
    
    as
    
    begin
    
        declare @n bigint,@t bigint,@flag int,@d int
    
        if dbo.f_primeNumTest(@p)=0
    
        begin
    
           print cast(@p as varchar)+'不是素数，请重新选择数据'
    
           return
    
        end
    
        if dbo.f_primeNumTest(@q)=0
    
        begin
    
           print cast(@q as varchar)+'不是素数，请重新选择数据'
    
           return
    
      end
    
      print '请从下列数据中选择其中一对，作为密钥'
    
        select @n=@p*@q,@t=(@p-1)*(@q-1)
    
        declare @e int
    
        set @e=2
    
        while @e<@t
    
        begin
    
           if dbo.f_isNumsPrime(@e,@t)=0
    
           begin
    
              set @d=2
    
           while @d<@n
    
                begin
    
                  if(@e*@d%@t=1)
    
                     print cast(@e as varchar)+space(5)+cast(@d as varchar)
    
                  set @d=@d+1
    
                end
    
        end
    
           set @e=@e+1       
    
        end
    
    end

\--加密函数

[@key][key] 为上一个存储过程中选择的密码中的一个,[@p][p] ,[@q][q] 产生密钥对时选择的两个数。获取每一个字符的unicode值，然后进行加密，产生个字节的位数据

if object_id('f_RSAEncry') is not null
    
      drop function f_RSAEncry
    
    go
    
    create function f_RSAEncry
    
     (@s varchar(100),@key int ,@p int ,@q int)
    
    returns nvarchar(4000)
    
    as
    
    begin
    
       declare @crypt varchar(8000)
    
         set @crypt=''
    
       while len(@s)>0
    
       begin
    
                  declare @i bigint,@tmp varchar(10),@k2 int,@leftchar int
    
                  select @leftchar=unicode(left(@s,1)),@k2=@key/2,@i=1
    
                  while @k2>0
    
                  begin
    
                         set @i=(cast(power(@leftchar,2) as bigint)*@i)%(@p*@q)
    
                         set @k2=@k2-1
    
                  end 
    
                  set @i=(@leftchar*@i)%(@p*@q)   
    
                  set @tmp=''
    
                  select @tmp=case when @i%16 between 10 and 15 then char( @i%16+55) else cast(@i%16 as varchar) end+@tmp,@i=@i/16
    
                  from (select number from master.dbo.spt_values where type='p'  and number<10 )K
    
                  order by  number desc
    
                  set @crypt=@crypt+right(@tmp,6)
    
                  set @s=stuff(@s,1,1,'')
    
      end
    
      return @crypt
    
    end

\--解密

[@key][key] 为一个存储过程中选择的密码对中另一个数字,[@p][p] ,@q 产生密钥对时选择的两个数

if object_id('f_RSADecry') is not null
    
      drop function f_RSADecry
    
    go
    
    create function f_RSADecry
    
     (@s nvarchar(4000),@key int ,@p int ,@q int)
    
    returns nvarchar(4000)
    
    as
    
    begin
    
      declare @crypt varchar(8000)
    
      set @crypt=''
    
      while len(@s)>0
    
        begin
    
           declare @leftchar bigint
    
           select @leftchar=sum(data1)
    
           from (select case upper(substring(left(@s,6), number, 1)) when 'A' then 10
    
                                                        when 'B' then 11
    
                                                        when 'C' then 12
    
                                                        when 'D' then 13
    
                                                        when 'E' then 14
    
                                                        when 'F' then 15
    
                    else substring(left(@s,6), number, 1)
    
                    end* power(16, len(left(@s,6)) - number) data1
    
             from (select number from master.dbo.spt_values where type='p')K
    
             where number <= len(left(@s,6))
    
         ) L
    
        declare @k2 int,@j bigint
    
           select @k2=@key/2,@j=1
    
        while @k2>0
    
           begin
    
          set @j=(cast(power(@leftchar,2)as bigint)*@j)%(@p*@q)
    
          set @k2=@k2-1
    
        end
    
        set @j=(@leftchar*@j)%(@p*@q)
    
        set @crypt=@crypt+nchar(@j)
    
        set @s=stuff(@s,1,6,'')
    
        end
    
      return @crypt
    
    end

使用方法：

1、先使用p\_createkey生成一对密钥，参数为两个参数

2、调用相应进行加密、解密

编写触发器，使两表有同步加密的效果

USE [test]
    
    GO
    
    /****** Object:  Trigger [dbo].[trig_insert]    Script Date: 07/24/2018 14:33:43 ******/
    
    SET ANSI_NULLS ON
    
    GO
    
    SET QUOTED_IDENTIFIER ON
    
    GO
    
    --创建insert触发器
    
    ALTER trigger [dbo].[trig_insert]
    
    on [dbo].[tt]
    
    after insert,update,delete
    
    as
    
    begin
    
        if object_id(N'ttt',N'U') is null
    
            create table ttt(id int,name varchar(50),description varchar(50));
    
        truncate table ttt;
    
    insert into ttt(id,name,description) select id,name,dbo.f_RSAEncry(description,779,1163,59) from tt
    
    end

\--创建解密视图

create view ttt_view  with encryption as select id,name,dbo.f_RSADecry(description,35039,1163,59) description from ttt;

[key]: https://my.oschina.net/llczhang
[p]: https://my.oschina.net/u/2690525
[q]: https://my.oschina.net/qqqqq