SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BASE64 Encode and Decode in T-SQL - optimized


BASE64 Encode and Decode in T-SQL - optimized

Author
Message
Aaron West
Aaron West
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 161
Comments posted to this topic are about the item BASE64 Encode and Decode in T-SQL - optimized
dpassey
dpassey
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 3
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
lyout
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 0
:-P
Thanks!
It's very useful for me.
ZA_Crafty
ZA_Crafty
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 260
Thanks for this. I nearly used this function.
Aaron West
Aaron West
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 161
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
Aaron West
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 161
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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search