1

I need help in decoding a Base64 password column in my database. When I copy a single column content into https://www.base64decode.org/ it decodes perfectly. I just need to perform this on ~7000 rows in this column. Any help is greatly appreciated.

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Pacoletaco
  • 13
  • 1
  • 1
  • 4
  • I found this code that works when I paste in any value from the column. Now I just need to know how to use this to take from the password column and put into a new table as a decoded result. Thank you. – Pacoletaco Nov 01 '16 at 16:54
  • 1
    please explain with some data and explain expected result.your question is not clear – TheGameiswar Nov 01 '16 at 18:13
  • Have you attempted to do anything to solve your issue? – dfundako Nov 01 '16 at 19:06
  • Are you trying to decode from SQL Server Management Studio, or through your own application, or what? More information would help you get an answer. – dub stylee Nov 01 '16 at 19:09

2 Answers2

3

You can use the following (source):

declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)

set @source = convert(varbinary(max), 'Hello Base64')

set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)') set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)')

select convert(varchar(max), @source) as source_varchar, @source as source_binary, @encoded as encoded, @decoded as decoded_binary, convert(varchar(max), @decoded) as decoded_varchar

... but, creating a function for this, then:

create function fnDecodeBase64 (@encoded as varchar(max))
returns varchar(max)
as
begin

    declare @decoded varchar(max)

    set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)')

    return @decoded

end

So, you can use:

select dbo.fnDecodeBase64(some_column) from Some_Table

You can also use:

select convert(varchar(max),cast(N'' as xml).value('xs:base64Binary(sql:column("t.SomeColumn"))', 'varbinary(max)')) as converted_64_column
from SomeTable as t

The keywords: sql:column makes the difference

pcdev
  • 2,852
  • 2
  • 23
  • 39
Franco Ugarte
  • 49
  • 5
1

If you are trying to do this within Management Studio (or T-SQL directly), then you can accomplish it like this:

declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)

set @source = convert(varbinary(max), 'Hello Base64')

set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)')

select convert(varchar(max), @source) as source_varchar, @source as source_binary, @encoded as encoded, @decoded as decoded_binary, convert(varchar(max), @decoded) as decoded_varchar

Reference: T-SQL: Easy Base64 Encoding and Decoding

double-beep
  • 5,031
  • 17
  • 33
  • 41
dub stylee
  • 3,252
  • 5
  • 38
  • 59