is it possible to convert Hashbyte back to string?

Question

is it possible to convert Hashbyte back to string?

mandavli

SSCrazy

Points: 2122
More actions
May 1, 2017 at 7:53 am

#321187

We have unique key in table which is hashbye sha1.
This value is combination of multiple columns from a different table. one of the records is a problem and I need to able decode that Hash Byte.
is there a tsql function I can use to convert it back to string values?

Viewing 7 posts - 1 through 7 (of 7 total)

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

sgmunson SSC Guru Points: 110647 More actions · Answer 1

mandavli - Monday, May 1, 2017 7:53 AM
We have unique key in table which is hashbye sha1.
This value is combination of multiple columns from a different table. one of the records is a problem and I need to able decode that Hash Byte.
is there a tsql function I can use to convert it back to string values?

The purpose of computing a hash value is never to be able to re-construct the data, as it's utility as a security mechanism would drop to zero, and no, there is no such function.

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

Phil Parkin SSC Guru Points: 247159 More actions · Answer 2

The HASHBYTES function is a many-to-one function. So its reverse, if it were even possible, would be a one-to-many function – not very useful.

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 3

If you know the algorithm used then you could theoretically use a rainbow table to find the original values, somewhat elaborate exercise.

Phil Parkin SSC Guru Points: 247159 More actions · Answer 4

Eirikur Eiriksson - Monday, May 1, 2017 8:52 AM
If you know the algorithm used then you could theoretically use a rainbow table to find the original values, somewhat elaborate exercise.

It was interesting to read about rainbow tables, thank you. Perhaps not quite the snappy solution the OP was looking for though

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 5

Phil Parkin - Monday, May 1, 2017 9:33 AM
Eirikur Eiriksson - Monday, May 1, 2017 8:52 AM
If you know the algorithm used then you could theoretically use a rainbow table to find the original values, somewhat elaborate exercise.
It was interesting to read about rainbow tables, thank you. Perhaps not quite the snappy solution the OP was looking for though

You are right, somewhat elaborate solution and normally not part of my standard SQL toolkit, used those many times for other activities

Eric M Russell SSC Guru Points: 125596 More actions · Answer 6

mandavli - Monday, May 1, 2017 7:53 AM
We have unique key in table which is hashbye sha1.
This value is combination of multiple columns from a different table. one of the records is a problem and I need to able decode that Hash Byte.
is there a tsql function I can use to convert it back to string values?

My understanding is that you have something like TableB with a column containing a hash of several columns from TableA. So, I'm guessing this column is a computed semi-natural foreign key, and you now want to join rows in TableB back to the related row in TableA. I don't know the column expression used for the hash, so in the example below I'll just use "Col1 + Col2 + Col3". For this to work, you'll need to know what the actual expression is and substitute it.

To select a row from TableA using a specific hash id:
SELECT * FROM TableA WHERE HASHBYTES ( 'SHA1', Col1 + Col2 + Col3 ) = 0x6CDD0A23A8E677557C9B2514D7BA307BE8BDD117;

Or to join multiple rows in TableB to TableA:
SELECT B.HashID, A.* FROM TableA AS A JOIN TableB AS B ON B.HashID = HASHBYTES ( 'SHA1', A.Col1 + A.Col2 + A.Col3 );

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

is it possible to convert Hashbyte back to string?

Cookies on SQLServerCentral