UTF8编码的Base64解密 MSSQL实现-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

UTF8编码的Base64解密 MSSQL实现

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:57 PM

base64mssqlutf8accomplishcodingDecrypt

加密解密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 --

加密解密UTF8编码的BASE64串 UTF8编码的Base64解密 MSSQL实现

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

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

MySQL String Types: Storage, Performance, and Best PracticesMay 10, 2025 am 12:02 AM

MySQLstringtypesimpactstorageandperformanceasfollows:1)CHARisfixed-length,alwaysusingthesamestoragespace,whichcanbefasterbutlessspace-efficient.2)VARCHARisvariable-length,morespace-efficientbutpotentiallyslower.3)TEXTisforlargetext,storedoutsiderows,

Understanding MySQL String Types: VARCHAR, TEXT, CHAR, and MoreMay 10, 2025 am 12:02 AM

MySQLstringtypesincludeVARCHAR,TEXT,CHAR,ENUM,andSET.1)VARCHARisversatileforvariable-lengthstringsuptoaspecifiedlimit.2)TEXTisidealforlargetextstoragewithoutadefinedlength.3)CHARisfixed-length,suitableforconsistentdatalikecodes.4)ENUMenforcesdatainte

What are the String Data Types in MySQL?May 10, 2025 am 12:01 AM

MySQLoffersvariousstringdatatypes:1)CHARforfixed-lengthstrings,2)VARCHARforvariable-lengthtext,3)BINARYandVARBINARYforbinarydata,4)BLOBandTEXTforlargedata,and5)ENUMandSETforcontrolledinput.Eachtypehasspecificusesandperformancecharacteristics,sochoose

How to Grant Permissions to New MySQL UsersMay 09, 2025 am 12:16 AM

TograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)

How to Add Users in MySQL: A Step-by-Step GuideMay 09, 2025 am 12:14 AM

ToaddusersinMySQLeffectivelyandsecurely,followthesesteps:1)UsetheCREATEUSERstatementtoaddanewuser,specifyingthehostandastrongpassword.2)GrantnecessaryprivilegesusingtheGRANTstatement,adheringtotheprincipleofleastprivilege.3)Implementsecuritymeasuresl

MySQL: Adding a new user with complex permissionsMay 09, 2025 am 12:09 AM

ToaddanewuserwithcomplexpermissionsinMySQL,followthesesteps:1)CreatetheuserwithCREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';.2)Grantreadaccesstoalltablesin'mydatabase'withGRANTSELECTONmydatabase.TO'newuser'@'localhost';.3)Grantwriteaccessto'

MySQL: String Data Types and CollationsMay 09, 2025 am 12:08 AM

The string data types in MySQL include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT. The collations determine the comparison and sorting of strings. 1.CHAR is suitable for fixed-length strings, VARCHAR is suitable for variable-length strings. 2.BINARY and VARBINARY are used for binary data, and BLOB and TEXT are used for large object data. 3. Sorting rules such as utf8mb4_unicode_ci ignores upper and lower case and is suitable for user names; utf8mb4_bin is case sensitive and is suitable for fields that require precise comparison.

MySQL: What length should I use for VARCHARs?May 09, 2025 am 12:06 AM

The best MySQLVARCHAR column length selection should be based on data analysis, consider future growth, evaluate performance impacts, and character set requirements. 1) Analyze the data to determine typical lengths; 2) Reserve future expansion space; 3) Pay attention to the impact of large lengths on performance; 4) Consider the impact of character sets on storage. Through these steps, the efficiency and scalability of the database can be optimized.

See all articles