Implementing RSA encryption and decryption functions in SQL Server (second edition)

Tag: Lan greater than Val Chinese shadow ESC Char supports BLE

/***************************************************

Herowang (The wall that makes you glimpse the shadow)

Date: 2010.1.5

NOTE: Reprint please keep this information

For more information, please visit my blog: Blog.csdn.net/herowang

****************************************************/

/*

This modification adds support for Unicode, but is still displayed as a binary data after encryption, because the Unicode encoding obtained after RSA encryption is not displayed, so the ciphertext still uses the input data display.

Need special attention: if you want to encrypt the Chinese, then the two prime numbers selected is larger, two prime scores better than, that is, greater than the Unicode maximum encoding value

Also modified the first version of the partial function name

*/

Implementing the RSA encryption algorithm in SQL Server

--Determining whether 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 @[email protected]+1

End

Return @flg

End

Go

--Test Example:

SELECT [dbo]. [F_primenumtest] (23)--1

SELECT [dbo]. [F_primenumtest] (24)--0

SELECT [dbo]. [F_primenumtest] (25)--0

SELECT [dbo]. [F_primenumtest] (26)--0

SELECT [dbo]. [F_primenumtest] (27)--0

--to determine whether the two-digit is a mutual

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 @[email protected],@[email protected]% @num1, @[email protected]

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) + ' is not a prime number, please re-select the data '

Return

End

If Dbo.f_primenumtest (@q) =0

Begin

Print cast (@q as varchar) + ' is not a prime number, please re-select the data '

Return

End

print ' Please select a pair from the following data as key '

Select @[email protected]*@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 @[email protected]+1

End

End

Set @[email protected]+1

End

End

/* Cryptographic function description, @key one of the passwords selected in the previous stored procedure, @p, @q the two number selected when generating the key pair. Gets the Unicode value for each character, and then encrypts it, producing bits of bytes of data */

If object_id (' F_rsaencry ') is not null

Drop function F_rsaencry

Go

Create function F_rsaencry

(@s varchar (), @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, @k2 int, @leftchar int

Select @leftchar =unicode (left (@s,1)), @[email Protected]/2,@i=1

While @k2 >0

Begin

Set @i= (CAST (Power (@leftchar, 2) as bigint) *@i)% (@p*@q)

Set @[email protected]

End

Set @i= (@leftchar *@i)% (@p*@q)

Set @tmp = ' '

Select @tmp =case when @i%16 between and then char (@i%16+55) Else cast (@i%16 as varchar) [email protected],@[email p Rotected]/16

From (select number from master.dbo.spt_values where type= ' P ' and number<10) K

ORDER BY number Desc

Set @[email protected]+right (@tmp, 6)

Set @s=stuff (@s,1,1, ")

End

Return @crypt

End

---decryption: @key Another number in the password pair selected for one stored procedure, @p, @q the two number 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) if ' A ' then 10

When ' B ' then 11

When ' C ' then 12

When the ' D ' then 13

When ' E ' then 14

When the ' F ' then 15

else substring (left (@s,6), number, 1)

end* Power (+ 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 @[email protected]/2,@j=1

While @k2 >0

Begin

Set @j= (CAST (Power (@leftchar, 2) as bigint) *@j)% (@p*@q)

Set @[email protected]

End

Set @j= (@leftchar *@j)% (@p*@q)

Set @[email Protected]+nchar (@j)

Set @s=stuff (@s,1,6, ")

End

Return @crypt

End

How to use:

1, first use P_createkey to generate a pair of keys, parameters of two parameters

2, call the corresponding encryption, decryption

--"Test"

If object_id (' TB ') is not null

DROP table TB

Go

CREATE table TB (ID int identity (), 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

--Running results

/*

ID COL

----------- ---------------------------------------------

1 00359b00e6e000eaf5

2 01075300931b0010a4007edc004b340074a6004b34

*/

SELECT *, after decryption =dbo.f_rsadecry (col,35039,1163,59) from TB

--Test example

/*

ID col after decryption

----------- ------------------------------------------- -----------

1 00359b00e6e000eaf5 Chinese

2 01075300931b0010a4007edc004b340074a6004b34 Chinese

*/

Implementing RSA encryption and decryption functions in SQL Server (second edition)