Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BASE64 Encode and Decode in T-SQL - optimized Expand / Collapse
Author
Message
Posted Saturday, October 13, 2007 8:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:09 AM
Points: 143, Visits: 147
Comments posted to this topic are about the item BASE64 Encode and Decode in T-SQL - optimized
Post #410316
Posted Wednesday, November 12, 2008 11:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 12:32 PM
Points: 1, 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.
Post #601583
Posted Tuesday, March 17, 2009 8:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 17, 2009 7:58 PM
Points: 1, Visits: 0

Thanks!
It's very useful for me.
Post #678047
Posted Wednesday, April 14, 2010 11:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:26 AM
Points: 164, Visits: 254
Thanks for this. I nearly used this function.
Post #903759
Posted Wednesday, January 16, 2013 2:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:09 AM
Points: 143, Visits: 147

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...)
Post #1408075
Posted Friday, December 12, 2014 11:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:09 AM
Points: 143, Visits: 147
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.

Post #1643876
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse