MIME64 Encoder and Decoder - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

MIME64 Encoder and Decoder

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 02:39:52

Here is a MIME64 encoder function written entirely in T-SQL
© 2006 Peter Larsson, Developer Workshop, all rights reserved

As long as the copyright notice is visible within the function declaration
and you include a note in the documentation of your system that these
functions are written by me, you may use these functions for free of charge.

If you intend to use these functions in a commercial application,
you are required to email me about your system and provide an email address
to the application manager.

If you like the code and use it just for fun, send an e-mail to me
and tell me about your experience with these functions.

I hope you enjoy these functions. I spent about two hours
writing them because I could not find them on the Internet.

CREATE FUNCTION dbo.fnMIME64Encode
(
	@RFC3548 BIT = 1,
	@PlainText VARCHAR(6000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
-- © 2006 Peter Larsson, Developer Workshop, all rights reserved

	DECLARE	@Characters VARCHAR(64), @Index SMALLINT,
		@m1 TINYINT, @m2 TINYINT, @m3 TINYINT, @m4 TINYINT,
		@MimeText VARCHAR(8000), @FinalBlock TINYINT

	SELECT	@Characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
		@FinalBlock = DATALENGTH(@PlainText) % 3,
		@PlainText = @PlainText + REPLICATE(CHAR(0), (3 - DATALENGTH(@PlainText) % 3) % 3),
		@Index = DATALENGTH(@PlainText) - 2,
		@MimeText = ''

	WHILE @Index > 0
		BEGIN
			SELECT	@m1 = (ASCII(SUBSTRING(@PlainText, @Index, 1)) & 252) / 4,
				@m2 = (ASCII(SUBSTRING(@PlainText, @Index, 1)) & 3) * 16 + (ASCII(SUBSTRING(@PlainText, @Index + 1, 1)) & 240) / 16,
				@m3 = (ASCII(SUBSTRING(@PlainText, @Index + 1, 1)) & 15) * 4 + (ASCII(SUBSTRING(@PlainText, @Index + 2, 1)) & 192) / 64,
				@m4 = ASCII(SUBSTRING(@PlainText, @Index + 2, 1)) & 63
 
			SELECT	@MimeText =	CASE
							WHEN @FinalBlock = 1 THEN SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + '=='
							WHEN @FinalBlock = 2 THEN SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + SUBSTRING(@Characters, @m3 + 1, 1) + '='
							ELSE SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + SUBSTRING(@Characters, @m3 + 1, 1) + SUBSTRING(@Characters, @m4 + 1, 1)
						END + @MimeText,
				@Index = @Index - 3,
				@FinalBlock = 0
		END

	IF @RFC3548 = 1
		BEGIN
			SELECT @Index = 1 + DATALENGTH(@MimeText) - DATALENGTH(@MimeText) % 76

			IF @Index > DATALENGTH(@MimeText)
				SELECT @Index = @Index - 76

			WHILE @Index > 1
				SELECT	@MimeText = STUFF(@MimeText, @Index, 0, CHAR(13) + CHAR(10)),
					@Index = @Index - 76
		END

	RETURN @MimeText
END

and of course the MIME64 decoder function

CREATE FUNCTION dbo.fnMIME64Decode
(
	@MimeText VARCHAR(8000)
)
RETURNS VARCHAR(6000)
AS

BEGIN
-- © 2006 Peter Larsson, Developer Workshop, all rights reserved

	DECLARE @Characters VARCHAR(64), @Index SMALLINT,
		@m1 TINYINT, @m2 TINYINT, @m3 SMALLINT, @m4 SMALLINT,
		@p1 TINYINT, @p2 TINYINT, @p3 TINYINT,
		@PlainText VARCHAR(6000), @Paddings TINYINT

	SELECT	@Characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
		@MimeText = REPLACE(REPLACE(@MimeText, CHAR(13), ''), CHAR(10), ''),
		@Index = DATALENGTH(@MimeText) - 3,
		@Paddings = DATALENGTH(@MimeText) - DATALENGTH(REPLACE(@MimeText, '=', '')),
		@PlainText = ''

	WHILE @Index > 0
		SELECT	@m1 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
			@m2 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 1, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
			@m3 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 2, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
			@m4 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 3, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
			@p1 = (@m1 & 63) * 4 + (@m2 & 48) / 16,
			@p2 = (@m2 & 15) * 16 + (@m3 & 60) / 4,
			@p3 = (@m3 & 3) * 64 + (@m4 & 63),
			@PlainText = CHAR(@p1) + CHAR(@p2) + CHAR(@p3) + @PlainText,
			@Index = @Index - 4

	RETURN	LEFT(@PlainText, DATALENGTH(@PlainText) - @Paddings)
END

Peter Larsson
Helsingborg, Sweden

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-08-22 : 05:46:50

Shouldn't the input type of encode and return type of decode be varbinary, not varchar?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 05:57:20

I don't think so. Because if the file is binary, there is no need to transform it.
I looked at http://rfc.net/rfc3548.html and could not find that file is to be binary.

I can also copy and paste mime64 encoded text from my web browser and run it through my function to get intended material.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources