UTF8编码的Base64解密 MSSQL实现
时间：2021-07-01 10:21:17
帮助过：3人阅读
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
UTF8编码的Base64解密 MSSQL实现
标签：
