-
/*This modification adds unicode support, but it is still displayed as hexadecimal data after encryption. Because the unicode encoding obtained after RSA encryption cannot be displayed, the ciphertext is still displayed in hexadecimal data.
Special attention is needed: If you want to encrypt Chinese, the two prime numbers selected must be larger. The scores of the two prime numbers should preferably be greater than 65536, which is greater than the maximum encoding value of Unicode.
*/
Implementing RSA Encryption Algorithm in SQL SERVER (Second Edition)
-- Determine whether it is a prime number
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
-- Determine whether two numbers are relatively prime
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
--Generate key pair
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)+'Not a prime number, please reselect the data'
return
end
if dbo.f_primeNumTest(@q)=0
begin
print cast(@q as varchar)+'Not a prime number, please reselect the data'
return
end
print 'Please select one pair from the following data as the key'
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
/*Encryption function description, @key is one of the passwords selected in the previous stored procedure, @p, @q are the two numbers selected when generating the key pair. Get the unicode value of each character and then encrypt it to generate 3 bytes of 16-bit data*/
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
--Decryption: @key is another number in the password pair selected in a stored procedure, @p, @q are the two numbers selected when generating the key pair
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
【test】
if object_id('tb') is not null
drop table tb
go
create table tb(id int identity(1,1),col varchar(100))
go
insert into tb values(dbo.f_RSAEncry('Chinese',779,1163,59))
insert into tb values(dbo.f_RSAEncry('Chinese',779,1163,59))
select * from tb
id col
1 00359B00E6E000EAF5
2 01075300931B0010A4007EDC004B340074A6004B34
select *, after decryption=dbo.f_RSADecry(col,35039,1163,59)
from tb
id col after decryption
1 00359B00E6E000EAF5 Chinese
2 01075300931B0010A4007EDC004B340074A6004B34 Chinese
1 0 0
(Please rate the article)
-