May 1, 2017 at 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?
May 1, 2017 at 8:03 am
mandavli - Monday, May 1, 2017 7:53 AMWe 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)
May 1, 2017 at 8:31 am
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.
May 1, 2017 at 8:53 am
If you know the algorithm used then you could theoretically use a rainbow table to find the original values, somewhat elaborate exercise.
May 1, 2017 at 9:33 am
Eirikur Eiriksson - Monday, May 1, 2017 8:52 AMIf 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
May 1, 2017 at 10:13 am
Phil Parkin - Monday, May 1, 2017 9:33 AMEirikur Eiriksson - Monday, May 1, 2017 8:52 AMIf 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
May 1, 2017 at 1:23 pm
mandavli - Monday, May 1, 2017 7:53 AMWe 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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy