Azure DB - Converting Base64 Encoded into Binary Found in Databricks Python

Question

I am going nuts on this. We have a base64 encoded value:

"+G1mQl3A30MKQsCBxMXwyMOc/AZEvpcRRI13mUOy3SNDEFjyQuVQ0UKHFsdCJ/HCQqEvuEE5Lb5AAg=="

We needed decoded to binary. In python (databricks), we get this which is the correct answer (using

base64.b64decode):

"\xf8mfB]\xc0\xdfC\nB\xc0\x81\xc4\xc5\xf0\xc8\xc3\x9c\xfc\x06D\xbe\x97\x11D\x8dw\x99C\xb2\xdd#C\x10X\xf2B\xe5P\xd1B\x87\x16\xc7B'\xf1\xc2B\xa1/\xb8A9-\xbe@\x02"

However when using this in TSQL:

SELECT CAST(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(400)')

I get this:

"0xF86D66425DC0DF430A42C081C4C5F0C8C39CFC0644BE9711448D779943B2DD23431058F242E550D1428716C74227F1C242A12FB841392DBE4002"

Which is wrong wrong wrong.

I am pulling my hair out on this one. I will mow your lawn, give your reddit gold, name my third born child after you, you name it, if you can crack this. Thanks for any help in advance.

Answer 1

As far as I can see they are the exact same binary value (DB Fiddle).

Just Python displays binary data in some weird and wonderful way with a mix of ASCII characters, escape codes, and hex codes whereas SQL Server just gives you the hex representation for each byte.

You can do

print(
        binascii.hexlify(
            base64.b64decode("+G1mQl3A30MKQsCBxMXwyMOc/AZEvpcRRI13mUOy3SNDEFjyQuVQ0UKHFsdCJ/HCQqEvuEE5Lb5AAg==")
        )
    )

to see the same hex representation as SQL Server in Python (Python Fiddle)

b'f86d66425dc0df430a42c081c4c5f0c8c39cfc0644be9711448d779943b2dd23431058f242e550d1428716c74227f1c242a12fb841392dbe4002'

For the reverse operation you can try the following

DECLARE @str VARCHAR(8000) = '+G1mQl3A30MKQsCBxMXwyMOc/AZEvpcRRI13mUOy3SNDEFjyQuVQ0UKHFsdCJ/HCQqEvuEE5Lb5AAg=='
DECLARE @bin VARBINARY(8000) = CAST(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(8000)')


DECLARE @ExpectedPythonRepresentation VARCHAR(8000) = 'b"\xf8mfB]\xc0\xdfC\nB\xc0\x81\xc4\xc5\xf0\xc8\xc3\x9c\xfc\x06D\xbe\x97\x11D\x8dw\x99C\xb2\xdd#C\x10X\xf2B\xe5P\xd1B\x87\x16\xc7B''\xf1\xc2B\xa1/\xb8A9-\xbe@\x02"'
DECLARE @CalculatedPythonRepresentation VARCHAR(8000)

SELECT @CalculatedPythonRepresentation = STRING_AGG(pyth, '') WITHIN GROUP (ORDER BY value)
FROM 
(
SELECT CASE Char
         WHEN 0x0A THEN '\n'
         WHEN 0x0D THEN '\r'
         WHEN 0x09 THEN '\t'
         WHEN 0x5C THEN '\\'
         ELSE
           CASE
             WHEN Char BETWEEN 0x20 AND 0x7E THEN CHAR(Char)
             ELSE '\x' + lower(CONVERT(CHAR(2), Char, 2))
           END
       END AS pyth,
       value 
FROM GENERATE_SERIES(1, DATALENGTH(@bin))
CROSS APPLY (VALUES (SUBSTRING(@bin,value,1))) V(Char)
) Mapped


IF CHARINDEX('''', @CalculatedPythonRepresentation) > 0 AND CHARINDEX('"', @CalculatedPythonRepresentation) = 0
    SET @CalculatedPythonRepresentation = 'b"' + @CalculatedPythonRepresentation + '"'
ELSE 
    SET @CalculatedPythonRepresentation = 'b''' + REPLACE(@CalculatedPythonRepresentation, '''', '\''') + ''''

SELECT CASE
         WHEN @CalculatedPythonRepresentation = @ExpectedPythonRepresentation THEN 'Same'
         ELSE 'Not Same'
       END 

Thanks to @Paul White for explaining the logic that

If only a single quote appears, the double quote is used as delimiter, otherwise single quote is the delimiter and escaped as necessary. Implementation appears to be bytearray_repr in github.com/python/cpython/blob/main/Objects/bytearrayobject.c