BASE64 Encode and Decode in T-SQL - optimized

  • Aaron West

    SSC Eights!

    Points: 808

    Comments posted to this topic are about the item BASE64 Encode and Decode in T-SQL - optimized

  • dpassey

    SSC Journeyman

    Points: 91

    I came across a bug(?) in this function. I'm using it in my proc to decode the base64 encoded string. My string is this:

    'This is a really long sentence to see how this thing will crash or if it even will crash I am now at 152 letters and still conting'.

    It get encoded via an Actionscript function to this:

    'VGhpcyBpcyBhIHJlYWxseSBsb25nIHNlbnRlbmNlIHRvIHNlZSBob3cgdGhpcyB0aGluZyB3aWxs

    IGNyYXNoIG9yIGlmIGl0IGV2ZW4gd2lsbCBjcmFzaCBJIGFtIG5vdyBhdCAxNTIgbGV0dGVycyBh

    bmQgc3RpbGwgY29udGluZy4='

    What the proc sees is this:

    This is a really long sentence to see how this thing willò7&6‚÷"–b—BWfVâv–ÆÂ7&6‚’Òæ÷rBS"ÆWGFW'2P¿nd still conting.

    For some reason the function bombs at around the 59th place in the string but then picks back up later. Any ideas why? The column is defined as varchar(500) so no insert/update problems there.

    Thanks.

  • lyout

    Valued Member

    Points: 69

    😛

    Thanks!

    It's very useful for me.

  • ZA_Crafty

    Ten Centuries

    Points: 1228

    Thanks for this. I nearly used this function.

  • Aaron West

    SSC Eights!

    Points: 808

    Remove whitespace before calling the function. Use SUBSTITUTE

    If you uncomment this code it would return null:

    --IF @encoded_text COLLATE LATIN1_GENERAL_BIN

    -- LIKE '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=]%'

    -- COLLATE LATIN1_GENERAL_BIN

    -- RETURN NULL

    try:

    SET @encoded_text = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(

    @encoded_text, ' ',''),CHAR(9),''),CHAR(10),''),CHAR(13),'')

    (Okay so 5 years for me to bother to check the discussion is a bit long. I use Oracle lately...)

  • Aaron West

    SSC Eights!

    Points: 808

    Fixed version below.

    -- Original functions Created By Daniel Payne

    -- Modified by Aaron West, tallpeak@hotmail.com

    -- About 3X encode speed, 7X decode speed for 'Hello World'

    -- Fixed a bug or two with end-of-block handling

    -- 12/12/2014: removing whitespaces, and checking input for correctness

    IF object_id('[dbo].[base64_decode]') IS NOT NULL

    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_start int,

    @encoded_length int,

    @decoded_length int,

    @mapr binary(122)

    SET @encoded_text = REPLACE(REPLACE(REPLACE(REPLACE(

    @encoded_text, ' ',''),CHAR(9),''),CHAR(10),''),CHAR(13),'')

    IF @encoded_text COLLATE LATIN1_GENERAL_BIN

    LIKE '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=]%'

    COLLATE LATIN1_GENERAL_BIN

    BEGIN

    RETURN CAST('Invalid Base64 data found in ' + @encoded_text AS INT)

    END

    --IF LEN(@encoded_text) & 3 > 0

    -- RETURN NULL

    SET @output = ''

    -- The nth byte of @mapr contains the base64 value

    -- of the character with an ASCII value of n.

    -- EG, 65th byte = 0x00 = 0 = value of 'A'

    SET @mapr =

    0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF -- 1-33

    + 0xFFFFFFFFFFFFFFFFFFFF3EFFFFFF3F3435363738393A3B3C3DFFFFFF00FFFFFF -- 33-64

    + 0x000102030405060708090A0B0C0D0E0F10111213141516171819FFFFFFFFFFFF -- 65-96

    + 0x1A1B1C1D1E1F202122232425262728292A2B2C2D2E2F30313233 -- 97-122

    --get the number of blocks to be decoded

    SET @encoded_length = LEN(@encoded_text)

    SET @decoded_length = @encoded_length / 4 * 3

    --for each block

    SET @block_start = 1

    WHILE @block_start < @encoded_length BEGIN

    --decode the block and add to output

    --BINARY values between 1 and 4 bytes can be implicitly cast to INT

    SET @output = @output + CAST(CAST(CAST(

    substring( @mapr, ascii( substring( @encoded_text, @block_start , 1) ), 1) * 262144

    + substring( @mapr, ascii( substring( @encoded_text, @block_start + 1, 1) ), 1) * 4096

    + substring( @mapr, ascii( substring( @encoded_text, @block_start + 2, 1) ), 1) * 64

    + substring( @mapr, ascii( substring( @encoded_text, @block_start + 3, 1) ), 1)

    AS INTEGER) AS BINARY(3)) AS VARCHAR(3))

    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

    --IF SUBSTRING(@output, @decoded_length, 1) = CHAR(0)

    -- SET @decoded_length = @decoded_length - 1

    --return the decoded string

    RETURN LEFT(@output, @decoded_length)

    END

    GO

    IF object_id('[dbo].[base64_encode]') IS NOT NULL

    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_start integer,

    @partial_block_start integer, -- position of last 0, 1 or 2 characters

    @partial_block_length integer,

    @block_val integer,

    @map char(64)

    SET @map = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'

    --initialise variables

    SET @output = ''

    --set length and count

    SET @input_length = LEN( @plain_text + '#' ) - 1

    SET @partial_block_length = @input_length % 3

    SET @partial_block_start = @input_length - @partial_block_length

    SET @block_start = 1

    --for each block

    WHILE @block_start < @partial_block_start BEGIN

    SET @block_val = CAST(SUBSTRING(@plain_text, @block_start, 3) AS BINARY(3))

    --encode the 3 character block and add to the output

    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)

    --increment the counter

    SET @block_start = @block_start + 3

    END

    IF @partial_block_length > 0

    BEGIN

    SET @block_val = CAST(SUBSTRING(@plain_text, @block_start, @partial_block_length)

    + REPLICATE(CHAR(0), 3 - @partial_block_length) AS BINARY(3))

    SET @output = @output

    + SUBSTRING(@map, @block_val / 262144 + 1, 1)

    + SUBSTRING(@map, (@block_val / 4096 & 63) + 1, 1)

    + CASE WHEN @partial_block_length < 2

    THEN REPLACE(SUBSTRING(@map, (@block_val / 64 & 63 ) + 1, 1), 'A', '=')

    ELSE SUBSTRING(@map, (@block_val / 64 & 63 ) + 1, 1) END

    + CASE WHEN @partial_block_length < 3

    THEN REPLACE(SUBSTRING(@map, (@block_val & 63) + 1, 1), 'A', '=')

    ELSE SUBSTRING(@map, (@block_val & 63) + 1, 1) END

    END

    --return the result

    RETURN @output

    END

    GO

    print dbo.base64_decode('VGhpcyBpcyBhIHJlYWxseSBsb25nIHNlbnRlbmNlIHRvIHNlZSBob3cgdGhpcyB0aGluZyB3aWxs

    IGNyYXNoIG9yIGlmIGl0IGV2ZW4gd2lsbCBjcmFzaCBJIGFtIG5vdyBhdCAxNTIgbGV0dGVycyBh

    bmQgc3RpbGwgY29udGluZy4=')

    -- This is a really long sentence to see how this thing will crash or if it even will crash I am now at 152 letters and still conting.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply