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
  • 26,582
  • 7
  • 53
  • 87
Pacoletaco
  • 13
  • 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

2

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,604
  • 2
  • 22
  • 37
Franco Ugarte
  • 29
  • 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

From the website: http://blog.falafel.com/t-sql-easy-base64-encoding-and-decoding/

pcdev
  • 2,604
  • 2
  • 22
  • 37
dub stylee
  • 3,192
  • 5
  • 37
  • 58