0

I (have to) use base64Binary to convert my base64 encoded string into bytes. In most cases it works good enough, but from time to time it returns NULL . For example this works like a charm:

DECLARE @Base64String VARCHAR(MAX)
SET @Base64String = 'qwerqwerqwerqwer'

declare @Base64Binary VARBINARY(MAX)
set @Base64Binary = cast('' as xml).value('xs:base64Binary(sql:variable("@Base64String"))', 'VARBINARY(max)');

select @Base64Binary as 'base64'

Result is 0xAB07ABAB07ABAB07ABAB07AB and that's ok for me. But if I set SET @Base64String = 'qwerqwerqwerqwe=' then I get NULL as result. Why? I pass pretty valid base64 string and expect not null value. I've tried to find some workaround, but no luck. How can I made xs:base64Binary to return valid varbinary value for such input strings?

Igor V Savchenko
  • 1,056
  • 1
  • 17
  • 30

1 Answers1

4

Having had a little look at this, I would suggest that qwerqwerqwerqwe= is not a valid base64 string.

Decoding qwerqwerqwerqwe= using a base64 conversion tool in C# renders the following:

0xAB07ABAB07ABAB07ABAB07

Encoding this in SQL server actually gives the output qwerqwerqwerqwc=:

DECLARE @Base64String VARCHAR(MAX)
DECLARE @Base64Binary VARBINARY(MAX)

SET @Base64Binary = 0xAB07ABAB07ABAB07ABAB07
PRINT @Base64Binary
SET @Base64String = CAST('' AS XML).value('xs:base64Binary(sql:variable("@Base64Binary"))', 'VARCHAR(max)');
PRINT @Base64String

I would suggest that the reason that SQL Server is returning NULL to you is that the base64 string you are working with is not actually valid.

Martin
  • 14,189
  • 1
  • 26
  • 43
  • thx for your answer, maybe you have some ideas how to determine validity of base64 string? The most obvious way is regular expression (like in this question: http://stackoverflow.com/questions/475074/regex-to-parse-or-validate-base64-data). But some strings that pass regxeps still return NULL in case of xs:base64Binary conversion. For example qwerqwerqwerq=== or qwerqw== or qwerqwe= – Igor V Savchenko Jun 09 '15 at 17:24
  • or maybe you have some ideas how to get validation rules whick uses in xs:base64Binary? it there any way to get sources of it? – Igor V Savchenko Jun 09 '15 at 17:26
  • @IgorVSavchenko How are you obtaining these base64 strings? It may be useful to look at where they are coming from. The validation of them is fairly straight-forward - if they are `NULL` when converted using the above in SQL Server then they are not valid. – Martin Jun 10 '15 at 07:48
  • these are third party values. Yeah, I've stopped at validation using xs:base64Binary. And if it returns null, then I add '/' symbol(s) to initial data instead of '='. Up to multiple of four length as before. After that all (surprisingly) starts working correctly. This way is ok for me because in my case base64 stores png images and replacing '=' with '/' at the end of string has absolutely no visible impact on resulting image. – Igor V Savchenko Jun 10 '15 at 09:32