BASE64 Encode and Decode in T-SQL

------------------------------------------------------------------------------------------------------------------------------------------
-- Created By Daniel Payne (For Assess2Achieve 2003) info@assess2achieve.com 
-- Modified By Aaron West, tallpeak@hotmail.com 15 June 2005
-- Free To Use, Copy, Distribute, Modify
--
-- Functions;
--
-- base64_encode( @plain_text   varchar(6000) ) RETURNS varchar(8000)
-- base64_decode( @encoded_text varchar(8000) ) RETURNS varchar(6000)
--
-- Performance;
--
-- 600,000 Characters Encoded/Decoded per min on P4 1500mHz
--
------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------
-- base64_encode_block  
------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (select 1 
           from   dbo.sysobjects 
           where  id = object_id('[dbo].[base64_encode_block]')
          )
  DROP FUNCTION [dbo].[base64_encode_block]
GO
------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION base64_encode_block
(
  @input  varchar(3),
  @length int 
)
RETURNS  
          char(4)
AS BEGIN
--method variables
DECLARE
  @result char(4),
  @map    char(64),
  @char_1 char(1),
  @char_2 char(1),
  @char_3 char(1), 
  @number int,
  @int_1  int,
  @int_2  int,
  @int_3  int, 
  @b64_1  int,
  @b64_2  int,
  @b64_3  int, 
  @b64_4  int 
--The Standard character map for BASE64
SET @map = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'
--the input has to be determined to decide on how to encode characters
if @length = 3 BEGIN
  --use all three characters
  SET @char_1 = SUBSTRING(@input, 1, 1)
  SET @char_2 = SUBSTRING(@input, 2, 1)
  SET @char_3 = SUBSTRING(@input, 3, 1)
end else if @length = 2 BEGIN 
  --use the first two
  SET @char_1 = SUBSTRING(@input, 1, 1)
  SET @char_2 = SUBSTRING(@input, 2, 1)
  SET @char_3 = char(0)
end else if @length = 1 BEGIN 
  --use the first one
  SET @char_1 = SUBSTRING(@input, 1, 1)
  SET @char_2 = char(0)
  SET @char_3 = char(0)
END
--Shift masks - 8 Bit
--  00000001 00000000 00000000 = 65536
--  00000000 00000001 00000000 =   255
--  00000000 00000000 00000001 =     1
--shift the integers left into a single number by multplication and addition into a single number
SET @number = (ASCII(@char_1) * 65536) + (ASCII(@char_2) * 256) + (ASCII(@char_3) * 1)
--Filter masks - 6 Bit
-- 111111 000000 000000 000000 = 16515072
-- 000000 111111 000000 000000 =   258048
-- 000000 000000 111111 000000 =     4032
-- 000000 000000 000000 111111 =       63
--Shift masks - 6 Bit
-- 000001 000000 000000 000000 =   262144
-- 000000 000001 000000 000000 =     4096
-- 000000 000000 000001 000000 =       64
-- 000000 000000 000000 000001 =        1
--divide into four 6 bit integer
SET  @b64_1  = (@number & ( 16515072 )) / 262144
SET  @b64_2  = (@number & (   258048 )) /   4096
SET  @b64_3  = (@number & (     4032 )) /     64
SET  @b64_4  = (@number & (       63 )) /      1       
--if three characters in use all four outputs
if @length = 3 BEGIN
  --select from map
  SELECT @result =
       substring(@MAP, @b64_1+1, 1)  + 
       substring(@MAP, @b64_2+1, 1)  +
       substring(@MAP, @b64_3+1, 1)  +
       substring(@MAP, @b64_4+1, 1)   
end else if @length = 2 BEGIN 
  --if the last segment is 000000 then use '=' a BASE64 standard for no data
  SELECT @result =
       substring(@MAP, @b64_1+1, 1)  + 
       substring(@MAP, @b64_2+1, 1)  +
       substring(@MAP, @b64_3+1, 1)  +
       CASE @b64_4 WHEN 0 THEN '=' ELSE substring(@MAP, @b64_4, 1) END  
end else if @length = 1 BEGIN 
  --if only one character in then the last two might be =
  SELECT @result =
       substring(@MAP, @b64_1+1, 1)  + 
       substring(@MAP, @b64_2+1, 1)  +
       CASE @b64_3 WHEN 0 THEN '=' ELSE substring(@MAP, @b64_3, 1) END +
       CASE @b64_4 WHEN 0 THEN '=' ELSE substring(@MAP, @b64_4, 1) END  
END
--return the result
RETURN @result
END
GO

------------------------------------------------------------------------------------------------------------------------------------------
-- base64_encode  
------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (select 1 
           from   dbo.sysobjects 
           where  id = object_id('[dbo].[base64_encode]')
          )
  DROP FUNCTION [dbo].[base64_encode]
GO
------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION base64_encode
(
  @plain_text varchar(6000)
)
RETURNS  
          varchar(8000)
AS BEGIN
--local variables
DECLARE
  @output            varchar(8000),
  @input_length      integer,
  @block_no          integer,
  @block_count       integer,
  @block_start       integer,
  @block_lenght      integer,
  @block             char(3),
  @encoded_text      char(4)             
--initialise variables
SET @output   = ''
SET @block_no = 1
--set length and count
SET @input_length      = LEN( REPLACE(@plain_text, ' ', '#') )
SET @block_count       = CEILING(@input_length / 3.0) 
--for each block
WHILE @block_no <= @block_count  BEGIN
  --get the starting point
  SET @block_start = (@block_no *3) - 2
  --cut out the block
  SET @block = SUBSTRING(@plain_text, @block_start, 3)
  --if the last block work out its length, otherwise it is three
  IF @block_no = @block_count 
    SET @block_lenght = 3 - ((@block_count * 3) - @input_length)
  ELSE
    SET @block_lenght = 3
  --encode the 3 character block
  SET @encoded_text = dbo.base64_encode_block( @block, @block_lenght )
  --add to the output
  SET @output = @output + @encoded_text
  --increment the counter
  SET @block_no = @block_no + 1
END
--return the result
RETURN @output
END
go

------------------------------------------------------------------------------------------------------------------------------------------
-- base64_decode_block  
------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (select 1 
           from   dbo.sysobjects 
           where  id = object_id('[dbo].[base64_decode_block]')
          )
  DROP FUNCTION [dbo].[base64_decode_block]
GO
------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION dbo.base64_decode_block
(
  @input  varchar(4)
)
RETURNS  
          char(3)
AS BEGIN
--local variables
DECLARE
  @result char(4),
  @map    char(256),
  @char_1 char(1),
  @char_2 char(1),
  @char_3 char(1), 
  @char_4 char(1), 
  @map_1  char(4),
  @map_2  char(4),
  @map_3  char(4), 
  @map_4  char(4), 
  @number int,
  @int_1  int,
  @int_2  int,
  @int_3  int, 
  @b64_1  int,
  @b64_2  int,
  @b64_3  int, 
  @b64_4  int 
--if running case in-sensitive servers the ABC map will not work, as 'a' = 'A'
--'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/' 
SET @map =  '065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 085 086 087 088 089 090 ' +
            '097 098 099 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 ' +
            '048 049 050 051 052 053 054 055 056 057 '                                                                 + 
            '043 047 '
--break up the input
SET @char_1 = SUBSTRING(@input, 1, 1)
SET @char_2 = SUBSTRING(@input, 2, 1)
SET @char_3 = SUBSTRING(@input, 3, 1)
SET @char_4 = SUBSTRING(@input, 4, 1)
--convert to numbers
SET @map_1 = CAST(ASCII(@char_1) AS varchar(4))
SET @map_2 = CAST(ASCII(@char_2) AS varchar(4))
SET @map_3 = CAST(ASCII(@char_3) AS varchar(4))
SET @map_4 = CAST(ASCII(@char_4) AS varchar(4))
--convert to '000 ' numbers, NOTE: space at end
IF LEN(@map_1) = 2 SET @map_1 = '0' + @map_1 + ' ' ELSE SET @map_1 = @map_1 + ' '
IF LEN(@map_2) = 2 SET @map_2 = '0' + @map_2 + ' ' ELSE SET @map_2 = @map_2 + ' '
IF LEN(@map_3) = 2 SET @map_3 = '0' + @map_3 + ' ' ELSE SET @map_3 = @map_3 + ' '
IF LEN(@map_4) = 2 SET @map_4 = '0' + @map_4 + ' ' ELSE SET @map_4 = @map_4 + ' '
--find the position in the map, NOTE: divide by four to get BASE64 integer encode number
SET @b64_1 = CHARINDEX(@map_1, @map) / 4
SET @b64_2 = CHARINDEX(@map_2, @map) / 4
SET @b64_3 = CHARINDEX(@map_3, @map) / 4
SET @b64_4 = CHARINDEX(@map_4, @map) / 4
--Shift masks
--  000001 000000 000000 000000 = 262144
--  000000 000001 000000 000000 =   4096
--  000000 000000 000001 000000 =     64
--  000000 000000 000000 000001 =      1
--shift the integers left into a single number by multplication and addition
SET @number = (@b64_1 * 262144) + (@b64_2 * 4096) + (@b64_3 * 64) + (@b64_4 * 1)
-- Filter masks
-- 11111111 00000000 00000000 = 16711680
-- 00000000 11111111 00000000 =    65280
-- 00000000 00000000 11111111 =      255
-- Shift masks, divide to move to the right
-- 00000001 00000000 00000000 =    65536
-- 00000000 00000001 00000000 =      256
-- 00000000 00000000 00000001 =        1
--get the integer representation as three 8 bit numbers
SET  @int_1  = (@number & ( 16711680 )) / 65536
SET  @int_2  = (@number & (    65280 )) /   256
SET  @int_3  = (@number & (      255 )) /     1
--if a ZERO it indicates '=' was used so therefore there is no output BUG FIX Thanks Aaron
SELECT @result =
       CASE @int_1 WHEN 0 THEN ' ' ELSE char(@int_1) END + 
       CASE @int_2 WHEN 0 THEN ' ' ELSE char(@int_2) END +
       CASE @int_3 WHEN 0 THEN ' ' ELSE char(@int_3) END 
--return the result
RETURN @result
END
GO

------------------------------------------------------------------------------------------------------------------------------------------
-- base64_decode  
------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (select 1 
           from   dbo.sysobjects 
           where  id = object_id('[dbo].[base64_decode]')
          )
  DROP FUNCTION [dbo].[base64_decode]
GO
------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION base64_decode
(
  @encoded_text varchar(8000)
)
RETURNS  
          varchar(6000)
AS BEGIN
--local variables
DECLARE
  @output           varchar(8000),
  @block_no         integer,
  @block_count      integer,
  @block_start      integer,
  @block            char(4),
  @decoded_text     char(3)             
--initialise 
SET @output   = ''
SET @block_no = 0
--get the number of blocks to be decoded
SET @block_count = (LEN(@encoded_text) / 4) 
--for each block BUG FIX thanks Aaron
WHILE @block_no < @block_count  BEGIN
  --get the start
  SET @block_start = (@block_no *4) + 1
  --cut out the block
  SET @block = SUBSTRING(@encoded_text, @block_start, 4)
  --decode the text
  SET @decoded_text = dbo.base64_decode_block( @block )
  --add to output
  SET @output = @output + @decoded_text
  --increment the block number
  SET @block_no = @block_no + 1
END
--return the decoded string MODIFIED 15 June 2005 By Daniel
RETURN RTRIM(@output)
END
go

------------------------------------------------------------------------------------------------------------------------------------------
-- Example of using Encode/Decode  
------------------------------------------------------------------------------------------------------------------------------------------
--SELECT  'Hello World'                           + ' -> ' +
--        dbo.base64_encode('Hello World')        + ' -> ' +
--        dbo.base64_decode('SGVsbG8gV29ybGQA')
------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------
-- Testing of the Encode/Decode 
------------------------------------------------------------------------------------------------------------------------------------------
--
--SELECT dbo.base64_encode_block( 'Hel', 3 ), 'SGVs' 'Test Vector'
--SELECT dbo.base64_encode_block( 'lo',  2 ), 'bG8=' 'Test Vector'
--
--SELECT 'Hello World -> SGVsbG8gV29ybGQ=',  dbo.base64_encode('Hello World')                        'Test Vector' 
--SELECT '   ',                              dbo.base64_encode('  ')                                 'Test Vector' 
--SELECT '  ..',                             dbo.base64_encode('  ..')                               'Test Vector' 
--SELECT 'one',           dbo.base64_encode('one')             'Test Vector' 
--
--SELECT 'Bas' 'Test Vector', dbo.base64_decode_block('YmFz')   
--SELECT 'e64' 'Test Vector', dbo.base64_decode_block('ZTY0')    
--
--SELECT 'SGVsbG8gV29ybGQ= -> Hello World'                            'Test Vector', dbo.base64_decode('SGVsbG8gV29ybGQ=')                         
--SELECT 'PGVsZW1lbnQ+b25lPC9lbGVtZW50Pg== -> one' 'Test Vector', dbo.base64_decode('PGVsZW1lbnQ+b25lPC9lbGVtZW50Pg==')
--
--SELECT  dbo.base64_decode(dbo.base64_encode('one'))
--SELECT  dbo.base64_decode(dbo.base64_encode('   3 Spaces Followed By ->ØÁiÞî-Ac!%_-UÛU9§-¾ÙhG´-ÓäúÃS-v,~Ù-ª´©]î-cÏôCî<- Is OK                                  THE END IS NIGH'))
--SELECT  '->!"#$%&' + '''' + '()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~ ¡¢£¤¥¦§¨©ª«®¯°±²³´µ•¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷ùúûüýþÿAaAaAaCcCc<-' + char(13) + char(10) + dbo.base64_decode(dbo.base64_encode('->!"#$%&' + '''' + '()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~ ¡¢£¤¥¦§¨©ª«®¯°±²³´µ•¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷ùúûüýþÿAaAaAaCcCc<-'))
--ADDED 15 June 2005 By Daniel
--SELECT  dbo.base64_decode(dbo.base64_encode('.'))     + '<-'
--SELECT  dbo.base64_decode(dbo.base64_encode('..'))    + '<-'
--SELECT  dbo.base64_decode(dbo.base64_encode('...'))   + '<-'
--SELECT  dbo.base64_decode(dbo.base64_encode('....'))  + '<-'
--SELECT  dbo.base64_decode(dbo.base64_encode('.....')) + '<-'
------------------------------------------------------------------------------------------------------------------------------------------