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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply