How to Base64 encode extended character sets properly using SQL/T-SQL

Question

I am having trouble getting the following values to base64 encode in a fashion that decodes. I have systems (.NET and C++/Objective C) that base64 encode into the following values.

e.g.:

2LPZhNin2YUg2KzbjNix2KfZhg== <-- سلام جیران

В России Base64 кодирует вас --> 0JIg0KDQvtGB0YHQuNC4IEJhc2U2NCDQutC+0LTQuNGA0YPQtdGCINCy0LDRgQ==

❤️💥🤪🦌🎅⛄🎄🤐🙈🙉🙊💩 --> 4p2k77iP8J+SpfCfpKrwn6aM8J+OheKbhPCfjoTwn6SQ8J+ZiPCfmYnwn5mK8J+SqQ==

I found one function posted on stack overflow that could decode these properly (which was meant to fix a field that mangled a decode (or something akin to that): Convert text value in SQL Server from UTF8 to ISO 8859-1, but I have found nothing to encode these extended characters (unicode?) values using SQL/T-SQL. I can do it in .NET and c++ app running on an ios device, but encoding results from many posted answers results in garbage when decoded.

Am I asking SQL to solve a problem it cannot? Does my collation settings need adjusting?

Here is a list of other posts I have attempted to glean a solution from and adapt, but alas that did not lead me to any answers:

https://www.sqlservercentral.com/scripts/base64-encode-and-decode-in-t-sql-optimized

Convert text value in SQL Server from UTF8 to ISO 8859-1

https://www.jrevell.com/base64-encoding-and-decoding-with-sql-server-unicode-characters/

Here is some sample SQL I am playing with to test encodes and decodes (horrible column names and all)

NOTE: You'll need the functions listed in the sites above to get this to run as is

NOTE 2: Whenever a varchar was seen in those functions for an non-encoded value I made it into an nvarchar

DECLARE @hwDecoded AS NVARCHAR(100) = N'Hello Base64'
DECLARE @hwEncoded AS NVARCHAR(100) = 'SGVsbG8gQmFzZTY0'

DECLARE @persianDecoded AS NVARCHAR(100) = N'سلام جیران'
DECLARE @persianEncoded AS NVARCHAR(100) = '2LPZhNin2YUg2KzbjNix2KfZhg=='

DECLARE @russianDecoded AS NVARCHAR(100) = N'В России Base64 кодирует вас'
DECLARE @russianEncoded AS NVARCHAR(100) = '0JIg0KDQvtGB0YHQuNC4IEJhc2U2NCDQutC+0LTQuNGA0YPQtdGCINCy0LDRgQ=='

DECLARE @emojiDecoded AS NVARCHAR(200) = N'❤️💥🤪🦌🎅⛄🎄🤐🙈🙉🙊💩'
DECLARE @emojiEncoded AS NVARCHAR(200) = '4p2k77iP8J+SpfCfpKrwn6aM8J+OheKbhPCfjoTwn6SQ8J+ZiPCfmYnwn5mK8J+SqQ=='

DECLARE @Decoded AS NVARCHAR(100)
DECLARE @Encoded AS NVARCHAR(100)

SET @Decoded = @hwDecoded
SET @Encoded = @hwEncoded
SELECT @Decoded AS Decoded_ExpectedOutput
,@Encoded AS Encoded_ExpectedOutput
,dbo.DecodeUTF8String(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Encoded"))','VARBINARY(MAX)')) AS Function_DecodeUTF8String_Output
,CAST( CAST((SELECT @Encoded FOR XML PATH('')) AS XML).value('.','varbinary(256)') AS varchar(256)) AS XML_PATH_VARCHAR_Decode_Output
,CAST( CAST((SELECT @Encoded FOR XML PATH('')) AS XML).value('.','varbinary(256)') AS nvarchar(256)) AS XML_PATH_NVARCHAR_Decode_Output
,dbo.fn_str_TO_BASE64(@Decoded) as fn_str_TO_BASE64_Output
,dbo.base64_encode(@Decoded) as base64_encode_Output
,(SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'VARCHAR(MAX)') Base64Encoding FROM (SELECT CAST(@Decoded AS VARBINARY(MAX)) AS bin) AS bin_sql_server_temp) AS SingleLineVarChar
,(SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(MAX)') Base64Encoding FROM (SELECT CAST(@Decoded AS VARBINARY(MAX)) AS bin) AS bin_sql_server_temp) AS SingleLineNVarChar
WHERE @Decoded = dbo.DecodeUTF8String(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Encoded"))','VARBINARY(MAX)'))

SET @Decoded = @persianDecoded 
SET @Encoded = @persianEncoded
SELECT @Decoded AS Decoded_ExpectedOutput
,@Encoded AS Encoded_ExpectedOutput
,dbo.DecodeUTF8String(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Encoded"))','VARBINARY(MAX)')) AS Function_DecodeUTF8String_Output
,CAST( CAST((SELECT @Encoded FOR XML PATH('')) AS XML).value('.','varbinary(256)') AS varchar(256)) AS XML_PATH_VARCHAR_Decode_Output
,CAST( CAST((SELECT @Encoded FOR XML PATH('')) AS XML).value('.','varbinary(256)') AS nvarchar(256)) AS XML_PATH_NVARCHAR_Decode_Output
,dbo.fn_str_TO_BASE64(@Decoded) as fn_str_TO_BASE64_Output
,dbo.base64_encode(@Decoded) as base64_encode_Output
,(SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'VARCHAR(MAX)') Base64Encoding FROM (SELECT CAST(@Decoded AS VARBINARY(MAX)) AS bin) AS bin_sql_server_temp) AS SingleLineVarChar
,(SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(MAX)') Base64Encoding FROM (SELECT CAST(@Decoded AS VARBINARY(MAX)) AS bin) AS bin_sql_server_temp) AS SingleLineNVarChar
WHERE @Decoded = dbo.DecodeUTF8String(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Encoded"))','VARBINARY(MAX)'))

SET @Decoded = @russianDecoded 
SET @Encoded = @russianEncoded
SELECT @Decoded AS Decoded_ExpectedOutput
,@Encoded AS Encoded_ExpectedOutput
,dbo.DecodeUTF8String(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Encoded"))','VARBINARY(MAX)')) AS Function_DecodeUTF8String_Output
,CAST( CAST((SELECT @Encoded FOR XML PATH('')) AS XML).value('.','varbinary(256)') AS varchar(256)) AS XML_PATH_VARCHAR_Decode_Output
,CAST( CAST((SELECT @Encoded FOR XML PATH('')) AS XML).value('.','varbinary(256)') AS nvarchar(256)) AS XML_PATH_NVARCHAR_Decode_Output
,dbo.fn_str_TO_BASE64(@Decoded) as fn_str_TO_BASE64_Output
,dbo.base64_encode(@Decoded) as base64_encode_Output
WHERE @Decoded = dbo.DecodeUTF8String(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Encoded"))','VARBINARY(MAX)'))

SET @Decoded = @emojiDecoded 
SET @Encoded = @emojiEncoded
SELECT @Decoded AS Decoded_ExpectedOutput
,@Encoded AS Encoded_ExpectedOutput
,dbo.DecodeUTF8String(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Encoded"))','VARBINARY(MAX)')) AS Function_DecodeUTF8String_Output
,CAST( CAST((SELECT @Encoded FOR XML PATH('')) AS XML).value('.','varbinary(256)') AS varchar(256)) AS XML_PATH_VARCHAR_Decode_Output
,CAST( CAST((SELECT @Encoded FOR XML PATH('')) AS XML).value('.','varbinary(256)') AS nvarchar(256)) AS XML_PATH_NVARCHAR_Decode_Output
,dbo.fn_str_TO_BASE64(@Decoded) as fn_str_TO_BASE64_Output
,dbo.base64_encode(@Decoded) as base64_encode_Output
WHERE @Decoded = dbo.DecodeUTF8String(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Encoded"))','VARBINARY(MAX)'))

I guess I could have created a function for testing each value tested. Everthing seems to really choke dealing with this extended characters sets. Any nudges in the right direction are very welcome.

Answer 1

Your application encodes unicode chars in utf8 and sql server encodes unicode chars in ucs2/utf16.

Base64 takes binary input and encodes it to an ascii character string. Obviously, utf8 (variable byte size) is not the same as utf16 (fixed 2 bytes), and the base64 encodings of utf8 and utf16 will be different. If you want sql server to produce the same base64 output as your application does, then you have to find a way to make sql server convert unicode chars from ucs2/utf16 to utf8 encoding. In plain words you need "utf8 bytes"

1. You could use a CLR function that encodes to utf8.
2. use a tsql function (reinventing the wheel ?)

An example of a tsql function can be found at: https://gist.github.com/sevaa/f084a0a5a994c3bc28e518d5c708d5f6

create function [dbo].[ToUTF8](@s nvarchar(max))
returns varbinary(max)
as
begin
    declare @i int = 1, @n int = datalength(@s)/2, @r varbinary(max) = 0x, @c int, @d varbinary(4)
    while @i <= @n
    begin
        set @c = unicode(substring(@s, @i, 1))
        if (@c & 0xFC00) = 0xD800
        begin
            set @i += 1
            set @c = ((@c & 0x3FF) * 0x400) | 0x10000 | (unicode(substring(@s, @i, 1)) & 0x3FF)
        end

        if @c < 0x80
            set @d = cast(@c as binary(1))
        if @c >= 0x80 and @c < 0x800 
            set @d = cast(((@c * 4) & 0xFF00) | (@c & 0x3F) | 0xC080 as binary(2))
        if @c >= 0x800 and @c < 0x10000
            set @d = cast(((@c * 0x10) & 0xFF0000) | ((@c * 4) & 0x3F00) | (@c & 0x3F) | 0xe08080 as binary(3))
        if @c >= 0x10000
            set @d = cast(((@c * 0x40) & 0xFF000000) | ((@c * 0x10) & 0x3F0000) | ((@c * 4) & 0x3F00) | (@c & 0x3F) | 0xf0808080 as binary(4))

        set @r += @d
        set @i += 1
    end
    return @r
end 

. caveat: i am not related, affined, with the developer who created the tsql function (i have not used it before). If the function works for you, give him credit.

DECLARE @emojiDecoded AS NVARCHAR(200) = N'❤️💥🤪🦌🎅⛄🎄🤐🙈🙉🙊💩';

SELECT CAST('' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("src.utf8bytes")))', 'VARCHAR(MAX)') emojibase64EncodedFromUtf8
FROM
(
SELECT dbo.ToUTF8(@emojiDecoded) AS utf8bytes
) AS src;
--4p2k77iP8J+SpfCfpKrwn6aM8J+OheKbhPCfjoTwn6SQ8J+ZiPCfmYnwn5mK8J+SqQ==