Base64 String Encoding C# vs TSQL

2
votes

I have base64 string encoding/decoding functions in C# and TSQL, my problem is encoded result from C# differs from encoding result in TSQL.

My goal is:

  1. C# encoded string should be decoded in TSQL and vice-versa
  2. Both functions should have an ability to encode/decode unicode characters

C#

Convert.ToBase64String(Encoding.UTF8.GetBytes("test"));

Result: dGVzdA==

TSQL

SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(MAX)')   Base64Encoding
FROM 
(
    SELECT CAST(N'test' AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp

Result: dABlAHMAdAA=

Any idea how to match the results?

2
c#tsqlbase64

2 Answers

5
votes

SQL Server is using UTF-16. Your C# code is using UTF-8, hence the difference.

To get the C# code to use UTF-16, you can do this:

Convert.ToBase64String(Encoding.Unicode.GetBytes("test"))

Result: dABlAHMAdAA=
1
votes

To get your desire result Use below TSQL

SELECT CAST('test' as varbinary(max)) FOR XML PATH(''), BINARY BASE64