October 13, 2007 at 8:57 am
Comments posted to this topic are about the item BASE64 Encode and Decode in T-SQL - optimized
November 12, 2008 at 11:24 am
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.
March 17, 2009 at 8:01 pm
😛
Thanks!
It's very useful for me.
April 14, 2010 at 11:46 pm
Thanks for this. I nearly used this function.
January 16, 2013 at 2:05 pm
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...)
December 12, 2014 at 11:15 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply