I've been looking at how to decode Base64 with SQL Server and after searching around many solutions online (some from here) seem to be based upon this sort of method.

SELECT CAST(CAST('Base64StringHere' as XML ).value('.','varbinary(max)') AS VARCHAR(250))

When I have ASCII text this works perfectly. However when I have the following French text it gets corrupted (presumably due to the restrictions of VARCHAR).

Où est le café le plus proche?
T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=

And provides the following output.

Où est le café le plus proche?

I thought the relatively simple fix would be to change the CAST to NVARCHAR but this results in corruption again.

SELECT CAST(CAST('T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=' as XML ).value('.','varbinary(max)') AS NVARCHAR(250) )

썏₹獥⁴敬挠晡꧃氠⁥汰獵瀠潲档㽥

My search engine skills may be failing me, but I cannot seem to find someone else who has my problem.

Any thoughts?

up vote 8 down vote accepted

The problem is that you encoded a UTF-8 encoded string into Base64. So, decoding the Base64 gives you back the original UTF-8 sequence of bytes. SQL Server uses UTF-16 Little Endian only for NVARCHAR data, and even for XML. Hence, ù is the 8-bit version of the two-byte UTF-8 sequence for ù (0xC3 and 0xB9).

Fortunately, it is possible to convert a UTF-8 encoded string into UTF-16, or even into a non-Unicode Code Page (IF the Code Page supports all characters being converted). The trick is to convert the Base64 decoded bytes, in their text representation (even with incorrectly converted characters) into XML. The trick to this trick is that you need to add the <?xml ...> declaration (typically omitted) and specify the source encoding:

DECLARE @Base64Value NVARCHAR(500) = N'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=';

DECLARE @BinaryValue VARBINARY(500) =
    CONVERT(XML, @Base64Value).value('.','varbinary(max)');

DECLARE @IntermediaryValue VARCHAR(500) = CONVERT(VARCHAR(500), @BinaryValue);

SELECT @BinaryValue, @IntermediaryValue;
-- 0x4FC3B920657374206C6520636166C3A9206C6520706C75732070726F6368653F
-- Oֳ¹ est le cafֳ© le plus proche?


-- This is to NVARCHAR, which will always work:
SELECT CONVERT(NVARCHAR(500),
    CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
              );
-- Où est le café le plus proche?


-- This is to VARCHAR, but "success" will depend on the Code Page
-- specified by the default Collation of the current Database:
SELECT CONVERT(VARCHAR(500),
    CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
              );

 -- In a DB with a Latin1_General Collation it works:
 -- Où est le café le plus proche?


 -- In a DB with a Hebrew Collation, it gets the following error:
 /*
   Msg 6355, Level 16, State 1, Line XXXXX
   Conversion of one or more characters from XML to target collation impossible
 */

Please note that this trick only works for converting from various source encodings into UTF-16 Little Endian (as that is how the XML datatype in SQL Server stores strings internally). This method cannot be used to convert UTF-16 into UTF-8 or some other non-SQL Server-supported encoding.


Below is an Inline-TVF encapsulating the steps shown above:

GO
CREATE FUNCTION dbo.ConvertBase64EncodedUTF8ToUTF16LE
(
  @Base64EncodedUTF8String VARCHAR(8000)
)
RETURNS TABLE
AS RETURN

    SELECT 
        CONVERT(NVARCHAR(500),
                CONVERT(XML,
                        '<?xml version="1.0" encoding="UTF-8"?>' +
                        CONVERT(VARCHAR(500),
                                CONVERT(XML, @Base64EncodedUTF8String)
                                  .value('.','varbinary(max)')
                               )
                       )
               ) AS [DecodedValue];

GO

And then test:

SELECT *
FROM   dbo.ConvertBase64EncodedUTF8ToUTF16LE(
          'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=');
-- Où est le café le plus proche?

Just use with CROSS APPLY if doing a set-based operation.

  • Fantastic work, I really appreciate your effort, thanks! I'll wrap that up into a function for convenience. – Geesh_SO Jul 13 at 21:15
  • @Geesh_SO Thanks, and you are welcome. Yes, it should be easy to wrap into a function. I would recommend an inline-TVF. I might even be able to work that up for you. But, I have to ask, why are you passing in Base64 encoded UTF-8 strings in the first place? – Solomon Rutzky Jul 13 at 21:17
  • It was as an investigation as a possible solution to get Unicode strings saved to the database using a C# Entity Framework web app that targets both SQL Server and MySQL. I could get SQL Server to handle the unicode strings just fine, but the same codebase downgraded the strings in MySQL despite the generated SQL looking fine. This Base64 solution is seamless when referenced in the C# codebase, but does make analysing the tables manually a bit more of a pain. stackoverflow.com/questions/51282125/… – Geesh_SO Jul 14 at 14:16
  • 1
    @Geesh_SO I have updated my answer with the Inline TVF definition. Also, while I have already answered the related question on S.O., I will state here that given the problem you are trying to solve (characters not saving correctly in MySQL), I can say that using Base64 encoding to store the UTF-8 input is almost certainly the wrong solution. With the data being Base64 encoded it is effectively unusable (outside of app code). You should fix the client connection issue with MySQL rather than crippling your application. – Solomon Rutzky Jul 15 at 19:45

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.