UTF8编码的Base64解密MSSQL实现

加密解密UTF8编码的BASE64串 无 GOCREATE FUNCTION [dbo].[c_GetUTF8Code] ( @char Nchar )RETURNS intAS--UTF8转码BEGIN Declare @Code int Select @Code=Cast(Unicode(@char) as int) Declare @Utf8Code int Set @Utf8Code=0 if(@Code128) begin --0-127 --

GO

CREATE FUNCTION [dbo].[c_GetUTF8Code] 
 ( 
 @char Nchar
 )
RETURNS int
AS

--UTF8转码
BEGIN
 Declare @Code int
 Select @Code=Cast(Unicode(@char) as int)
 Declare @Utf8Code int
 Set @Utf8Code=0
 if(@Code<128)
 begin
 --0-127
 --0000-007F
 --0xxxxxxx
 --01100010 Unocide
 --01100010 UTF-8
 Set @Utf8Code=@Code 
 end
 else if(@Code>127 and @Code<2048)
 begin
 --128-2047
 --0080-07FF
 --110xxx xx10xx xxxx
 --110 7 F F
 Declare @C1 int
 Declare @C2 int
 Declare @C3 int
 Select @C1=@Code/0x100 
 Select @C2=(@Code%0x100)/0x10
 Select @C3=@Code%0x10
 Select @Utf8Code=0xC080+0x400*@C1+0x100*(@C2/4)+0x10*(@C2%4)+@C3
 end
 else if(@Code>2047 and @Code<65536)
 begin
 --2047-65535
 --0110 0010 0001 0001
 --1110 xxxx 10xx xxxx 10xx xxxx
 --1110 0110 1000 1000 1001 0001
 Declare @C11 int
 Declare @C12 int
 Declare @C13 int
 Declare @C14 int
 Select @C11=@Code/0x1000
 Select @C12=(@Code%0x1000)/0x100
 Select @C13=(@Code%0x100)/0x10
 Select @C14=@Code%0x10
 Select @Utf8Code=0xE08080+0x10000*@C11+0x400*@C12+0x100*(@C13/4)+0x10*(@C13%4)+@C14 
 end
 return @Utf8Code
End

 

 

GO

CREATE FUNCTION [dbo].[base64_utf8encode] 
( 
 @plain_text varchar(max) 
) 
RETURNS varchar(max) 
AS BEGIN

--Base64解密
 DECLARE @output varchar(max)
 DECLARE @block_start integer
 DECLARE @map char(64) 
 SET @map='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/' 
 SET @output=''
 SET @block_start=0
 Declare @plain_textLength int
 Set @plain_textLength=Len(@plain_text)
 Declare @RestTransfer int--转码数累积
 Declare @RestTransferLenth int
 Set @RestTransfer=0
 Set @RestTransferLenth=0
 Declare @CodeInt int
 Declare @block_val BINARY(3)
 WHILE @block_start<@plain_textLength
 BEGIN 
 Set @CodeInt=0
 SELECT @CodeInt= [dbo].[c_GetUTF8Code](SubString(@plain_text,@block_start+1,1))
 Declare @CodeTransfer int
 Set @CodeTransfer=0
 --0-127 1位
 --128-2047 2位
 --2047-65535 3位
 if(@CodeInt<128)
 begin
 --+1位
 if(@RestTransferLenth=0 or @RestTransferLenth=1)
 begin
 Set @RestTransfer=@RestTransfer*0x100+@CodeInt
 Set @RestTransferLenth=@RestTransferLenth+1
 end
 else if(@RestTransferLenth=2)
 begin
 Set @CodeTransfer=@RestTransfer*0x100+@CodeInt
 Set @RestTransfer=0
 Set @RestTransferLenth=0
 end
 end
 else if(@CodeInt>127 and @CodeInt<2048)
 begin
 --+2位
 if(@RestTransferLenth=0)
 begin
 Set @RestTransfer=@CodeInt
 Set @RestTransferLenth=2
 end
 else if(@RestTransferLenth=1)
 begin
 Set @CodeTransfer=0x10000*@RestTransfer+@CodeInt
 Set @RestTransfer=0
 Set @RestTransferLenth=0
 end
 else if(@RestTransferLenth=2)
 begin
 Set @CodeTransfer=0x100*@RestTransfer+@CodeInt/0x100
 Set @RestTransfer=@CodeInt%0x100
 Set @RestTransferLenth=1
 end
 end
 else if(@CodeInt>2047)
 begin
 --+3位
 if(@RestTransferLenth=0)
 begin
 Set @CodeTransfer=@CodeInt
 Set @RestTransfer=0
 Set @RestTransferLenth=0
 end
 else if(@RestTransferLenth=1)
 begin
 Set @CodeTransfer=0x10000*@RestTransfer+@CodeInt/0x100
 Set @RestTransfer=@CodeInt%0x100
 Set @RestTransferLenth=1
 end
 else if(@RestTransferLenth=2)
 begin
 --剩余部分十六进制右移两位与新数据前两位之和
 Set @CodeTransfer=0x100*@RestTransfer+@CodeInt/0x10000
 Set @RestTransfer=@CodeInt%0x10000
 Set @RestTransferLenth=2
 end
 end
 ---累积到3位，执行加密转换
 if(@CodeTransfer>0x100000)
 begin
 SET @block_val = CAST(@CodeTransfer AS BINARY(3)) 
 SET @output = @output 
 + SUBSTRING(@map , @block_val/262144 +1,1)
 + SUBSTRING(@map ,(@block_val/4096&63)+1,1)
 + SUBSTRING(@map ,(@block_val/64 &63)+1,1)
 + SUBSTRING(@map ,(@block_val&63) +1,1)
 end
 SET @block_start=@block_start+1 
 END 
 IF @RestTransferLenth>0 
 BEGIN 
 
 SET @block_val=Cast(@RestTransfer*(Case @RestTransferLenth When 1 Then 65536 Else 256 end) as BINARY(3))
 SET @output=@output 
 +SUBSTRING(@map , @block_val/262144+1, 1) 
 +SUBSTRING(@map ,(@block_val/4096 &63)+1,1) 
 +CASE WHEN @RestTransferLenth =1
 THEN REPLACE(SUBSTRING(@map ,(@block_val/64&63)+1,1),'A','=') 
 ELSE SUBSTRING(@map ,(@block_val/64&63)+1,1)
 END
 +CASE WHEN @RestTransferLenth=1 
 THEN '=' 
 ELSE REPLACE(SUBSTRING(@map ,(@block_val&63)+1,1),'A','=')
 END 
 END
 RETURN @output 
END

 

 

 






GO

CREATE FUNCTION [dbo].[base64_utf8decode] 
 ( 
 @encoded_text varchar(max) 
 ) 
 RETURNS varchar(max) 
 AS BEGIN 

--BASE64加密
DECLARE @output varchar(max)
DECLARE @block_start int
DECLARE @encoded_length int
DECLARE @decoded_length int 
DECLARE @mapr binary(122) 
SET @output = '' 
SET @mapr = 
 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF -- 1-33 
 +0xFFFFFFFFFFFFFFFFFFFF3EFFFFFF3F3435363738393A3B3C3DFFFFFF00FFFFFF -- 33-64 
 +0x000102030405060708090A0B0C0D0E0F10111213141516171819FFFFFFFFFFFF -- 65-96 
 +0x1A1B1C1D1E1F202122232425262728292A2B2C2D2E2F30313233-- 97-122 
SET @encoded_length=LEN(@encoded_text) 
SET @decoded_length=@encoded_length/4*3
SET @block_start=1 
Declare @Code int
Set @Code=0
Declare @CodeLength int--累计连接数，1，2，3
Set @CodeLength =0
WHILE @block_start<@encoded_length
BEGIN
 Declare @Integer Integer
 Set @Integer=substring(@mapr,Unicode(substring(@encoded_text,@block_start ,1)),1)*262144 
 + substring(@mapr,Unicode(substring(@encoded_text,@block_start+1,1)),1)*4096 
 + substring(@mapr,Unicode(substring(@encoded_text,@block_start+2,1)),1)*64 
 + substring(@mapr,Unicode(substring(@encoded_text,@block_start+3,1)),1)
 Declare @C1 int
 Declare @C2 int
 Declare @C3 int
 --0xFF FF FF
 Set @C1=@Integer/0x10000
 Set @C2=(@Integer/0x100)%0x100
 Set @C3=@Integer%0x100
 -------------------------------------@C1
 if(@C1<0x80)
 begin
 if(@CodeLength=2)
 begin
 --128-2047
 --0080-07FF
 --110x xx xx 10xx xxxx
 Set @Code=((@Code%0x2000)/0x100)*0x10+@Code%0x40
 SET @output=@output+NCHAR(@Code)
 --print @Code 
 Set @Code=0
 Set @CodeLength=0
 end
 SET @output=@output+CAST(Cast(@C1 AS BINARY(1))AS VARCHAR(1))
 end
 else
 begin
 --码字连接
 Set @Code=@Code*0x100+@C1
 SET @CodeLength=@CodeLength+1
 if(@CodeLength=3)
 begin
 --0110 0010 0001 0001
 --1110 xxxx 10xx xxxx 10xx xxxx
 --1110 0110 1000 1000 1001 0001
 Set @Code=((@Code%0x100000)/0x10000)*0x1000+((@Code%0x4000)/0x100)*0x40+@Code%0x40
 SET @output=@output+NCHAR(@Code)
 Set @Code=0
 Set @CodeLength=0
 end
 end
 -------------------------------------@C2
 if(@C2<0x80)
 begin
 if(@CodeLength=2)
 begin
 --128-2047
 --0080-07FF
 --110x xx xx 10xx xxxx
 Set @Code=((@Code%0x2000)/0x100)*0x10+@Code%0x40
 SET @output=@output+NCHAR(@Code)
 --print @Code 
 Set @Code=0
 Set @CodeLength=0
 end
 SET @output=@output+CAST(Cast(@C2 AS BINARY(1))AS VARCHAR(1))
 end
 else
 begin
 --码字连接
 Set @Code=@Code*0x100+@C2
 SET @CodeLength=@CodeLength+1
 if(@CodeLength=3)
 begin
 --0110 0010 0001 0001
 --1110 xxxx 10xx xxxx 10xx xxxx
 --1110 0110 1000 1000 1001 0001
 Set @Code=((@Code%0x100000)/0x10000)*0x1000+((@Code%0x4000)/0x100)*0x40+@Code%0x40
 SET @output=@output+NCHAR(@Code)
 Set @Code=0
 Set @CodeLength=0
 end
 end
 -------------------------------------@C3
 if(@C3<0x80)
 begin
 if(@CodeLength=2)
 begin
 --128-2047
 --0080-07FF
 --110x xx xx 10xx xxxx
 Set @Code=((@Code%0x2000)/0x100)*0x10+@Code%0x40
 SET @output=@output+NCHAR(@Code)
 --print @Code 
 Set @Code=0
 Set @CodeLength=0
 end
 SET @output=@output+CAST(Cast(@C3 AS BINARY(1))AS VARCHAR(1))
 end
 else
 begin
 --码字连接
 Set @Code=@Code*0x100+@C3
 SET @CodeLength=@CodeLength+1
 if(@CodeLength=3)
 begin
 --0110 0010 0001 0001
 --1110 xxxx 10xx xxxx 10xx xxxx
 --1110 0110 1000 1000 1001 0001
 Set @Code=((@Code%0x100000)/0x10000)*0x1000+((@Code%0x4000)/0x100)*0x40+@Code%0x40
 SET @output=@output+NCHAR(@Code)
 Set @Code=0
 Set @CodeLength=0
 end
 end
 SET @block_start = @block_start + 4 
END 
IF RIGHT(@encoded_text,2)='=='
 SET @decoded_length=@decoded_length-2
ELSE IF RIGHT(@encoded_text,1)='='
 SET @decoded_length=@decoded_length-1
RETURN LEFT(@output ,@decoded_length) 
END