Base64 decode in SAP ASE using XML Services

[lambert-giese]

I wish to Base64-decode data in SAP ASE using SQL. I can do the inverse (Base64-encode binary data) using the following approach:

-- PREPARATION: get HEX bytes of ASCII string as test input for Base64-encode
select bintostr(convert(varbinary(16384), 'test'))
-- returns 74657374

-- WORKS: Base64 from HEX
select xmlextract('//C1/text()', c_xml) from (select (select cast(strtobin('74657374') as varbinary(16384)) for xml option 'binary=base64')) t(c_xml)
-- returns dGVzdA==

However, the following approach of using the XMLTABLE function with an explicit data conversion back to VARBINARY doesn't decode the Base64-encoded data, but instead returns the hex-bytes of the Base64 characters:

-- FAILS: HEX from Base64
select c_bin from xmltable('/rows/row' passing '<rows><row><col>dGVzdA==</col></row></rows>' columns col varbinary(16384)) t(c_bin)
-- returns 0x6447567a64413d3d instead of 0x74657374

Is there any fix to the last query or alternative approach to get back the original binary value 0x74657374?

[lambert-giese]

Thanks a lot Robert, specifically for the working code sample.

[lambert-giese]

Robert, I noticed that

select dbo.base64_decode('TWFu');

would result in MaT instead of Man on my server, which has a case-insensitive sort order. I guess that makes locate() not distinguish any more between upper and lowercase, hence giving wrong results for lowercase Base64 chars in my input.

In fact, I was experimenting with my own UDF before reading your answer, and my attempt to work around this same problem was to convert both input Base64 chars and Base64 alphabet to binary before performing the lookups, so locate (or charindex, which I used in my version) would look for the exact byte value.

What do you think? Here's your code with my modifications to make it work on my Sort Order = 101, utf8_nocase:

create or replace function base64_decode(@enc_char varchar(16384))
returns varbinary(16384) as
begin
    declare @alphabet varbinary(65)
        set @alphabet = convert(varbinary(65), 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=')
    declare @i int 
        set @i = 0
    declare @enc1 int
    declare @enc2 int
    declare @enc3 int
    declare @enc4 int
    declare @chr1 int
    declare @chr2 int
    declare @chr3 int
    declare @dec varbinary(16384)
    declare @enc varbinary(16384)
    set @enc = convert(varbinary(16384), @enc_char)

    while @i < len(@enc)
    begin
        set @enc1 = locate( @alphabet, substring(@enc, @i + 1, 1)) - 1
        set @enc2 = locate( @alphabet, substring(@enc, @i + 2, 1)) - 1
        set @enc3 = locate( @alphabet, substring(@enc, @i + 3, 1)) - 1
        set @enc4 = locate( @alphabet, substring(@enc, @i + 4, 1)) - 1

        set @chr1 = ( (@enc1 * 4) & 0xFF )  | (@enc2 / 16)
        set @chr2 = ( (@enc2 & 0x0F) * 16 ) | (@enc3 / 4 )
        set @chr3 = ( (@enc3 & 0x03) * 64 ) |  @enc4

        set @dec = @dec || cast(@chr1  as varbinary(1)) 

        if ( @enc3 != 64 and @enc3 is not null ) 
          set @dec = @dec || cast(@chr2  as varbinary(1)) 

        if ( @enc4 != 64 and @enc4 is not null ) 
          set @dec = @dec || cast(@chr3  as varbinary(1)) 

        set @i = @i + 4
    end

    return  @dec

end

[Former Member]

Hi lambert.boskamp,

It works on case-sensitive sort order, too. Should be good enough.