Conversion of Base64 MD5 checksum to hexadecimal value

Question

Post reply

Conversion of Base64 MD5 checksum to hexadecimal value

christine.lawrie

Ten Centuries

Points: 1146
More actions
October 27, 2010 at 9:31 pm

#230473

Hi everyone,
I'm using the fciv utility to obtain a MD5 hash checksum value for a datafile, and store the checksum in an XML database. I then need to insert the checksum from the XML into a SQL table. I can do the insert from the XML into the database, but the database requires the checksum in hexadecimal, and fciv writes the hash value to the XML database in Base64 encoding.
Does anyone know how I can translate the Base64 to hexadecimal?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

diamondgm SSCrazy Points: 2824 More actions · Answer 1

Hi,

I would try Base64 => Varbinary => Hex

I don't think there is a direct conversion but I may be wrong.

Good luck!

christine.lawrie Ten Centuries Points: 1146 More actions · Answer 2

I've got the Base64 -> varbinary sorted... you wouldn't know about the -> Hex conversion would you?

I've been butting my head against this for about a week now, there's a blood stain on the wall and everything.

WayneS SSC Guru Points: 95460 More actions · Answer 3

christine.lawrie (10/27/2010)
I've got the Base64 -> varbinary sorted... you wouldn't know about the -> Hex conversion would you?
I've been butting my head against this for about a week now, there's a blood stain on the wall and everything.

Like this?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

christine.lawrie Ten Centuries Points: 1146 More actions · Answer 4

christine.lawrie

Ten Centuries

Points: 1146

October 27, 2010 at 11:17 pm

#1243341

Indeed...

diamondgm SSCrazy Points: 2824 More actions · Answer 5

Try:

-- Convert binary value in a variable to hexstring:

declare @hexbin varbinary(max);

set @hexbin = 0xabcedf012439;

select '0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@hexbin") )', 'varchar(max)');

go

SOURCE: http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx

PS.

Have not tested this code

diamondgm SSCrazy Points: 2824 More actions · Answer 6

Ah... Found this one in some old code of mine too...

master.dbo.fn_varbintohexstr()

christine.lawrie Ten Centuries Points: 1146 More actions · Answer 7

You beat me to it... I had just found the lovely fn_varbintohexstr().

So, if I read the XML node into a varbinary, I can then run the function over the varbinary, and pass on the hexadecimal formatted MD5 checksum.

The people that want the #$!!!!!!! checksum will be very happy.

diamondgm SSCrazy Points: 2824 More actions · Answer 8

Now all that's left for you is to clean up the blood 😀

WayneS SSC Guru Points: 95460 More actions · Answer 9

diamondgm (10/28/2010)
Now all that's left for you is to clean up the blood 😀

Agreed... I left a mess behind! :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2