0

I have data I need to hash. I have list of numbers to compare for hashed numbers. as far as someone could tell me, the data was hashed with SHA_256

I have only one example for input and output and I need to find out the hashing logic in SQL Server.

This is the original number: 02229747 And this is the hash number: 4ad54f5b376038f49a44d411e6d551ae4c8dd147c8605a7eec32ba850080b326

I have tried using the following but I can't manage to get the same result.

declare @number bigint = 022529747  
DECLARE @HashId varbinary(50) =  HashBytes('SHA2_256', cast(@number as varbinary(50)))
select @HashId
JumpIntoTheWater
  • 1,306
  • 2
  • 19
  • 46
  • Do you mean that `4ad54f5b376038f49a44d411e6d551ae4c8dd147c8605a7eec32ba850080b326` is the encrypted value? You say it's the "decrypted number", however, that it neither a number nor looks decrypted. – Thom A Jul 05 '20 at 11:15
  • As for decrypting the values, unless you know what the encryption algorithm is, you can't decrypt it; that's the point. If you don't know how to decrypt the value, you don't know for a reason. Encryption Algorithms would be pointless if *anyone* could undo it. Hashing is *meant* to be a one way process. – Thom A Jul 05 '20 at 11:16
  • 3
    `HASHBYTES` is a hashing function and hashing is not encryption. You cannot "decrypt" a hash to obtain the original value. – Dan Guzman Jul 05 '20 at 11:21
  • Sorry I meant encrype. I know it's one way. The thing is that I know for sure ( according to : https://xorbin.com/tools/sha256-hash-calculator) that it was encrypted by `sha2_256` because I have an example for the input and output. I thought that would be a hint for helping me – JumpIntoTheWater Jul 05 '20 at 11:23
  • 1
    Hashing and encryption are two very different things. The first is a one way street (meaning the original value can't be computed back from the hashed value) while the second is a two way street - encryped value can be decrypted back to the original value. – Zohar Peled Jul 05 '20 at 11:23
  • SHA stands for Secure Hash Algorithm. It's not an encryption. – Zohar Peled Jul 05 '20 at 11:24
  • @ZoharPeled thanks for the correction. I meant HASH algorithm – JumpIntoTheWater Jul 05 '20 at 11:27
  • It seems that you have small input space for hash. It is not safe [Is it easy to crack a hashed phone number?](https://crypto.stackexchange.com/q/81636/18298) and [Secure hashing when the input comes from a small space](https://crypto.stackexchange.com/q/61694/18298) – kelalaka Jul 05 '20 at 15:34
  • @kelalaka thanks. I'll take it into consideration. – JumpIntoTheWater Jul 06 '20 at 06:09

1 Answers1

2

Hashbytes can take a number of different algorithms as input. Try each one:

select 
a = hashbytes('MD2', '022529747'),
b = hashbytes('MD4', '022529747'),
c = hashbytes('MD5', '022529747'),
d = hashbytes('SHA', '022529747'),
e = hashbytes('SHA1', '022529747'),
f = hashbytes('SHA2_256', '022529747'),
g = hashbytes('SHA2_512', '022529747')

Column f returns the value you are looking for, so the algorithm used was SHA2_256. Note that I am putting the data in as a string (varchar), not an integer (bigint).. The bytes which represent 022529747 as a varchar are very different from the bytes which represent 022529747 as a bigint.

Background:

Hashing and encryption are different.

SHA stands for "secure hashing algorithm". It takes some input, and produces an output hash. If the input changes, the hash changes (with the limit of the birthday problem. But you can't go backwards. You can't take the output hash, and turn it back into the input data. The best you can do is try every different possible input, and see if that input generates the hash. See this 3Blue1Brown video for an illustrative explanation.

SHA is a family of cryptographic hash functions, but don't let the name fool you. "Cryptographic" doesn't mean the same thing as "encryption". It just means that it's "hard to guess" what the input data might be based on the output, because the output appears random. See This thread for the difference between a hash function and a cryptographic hash function

AES stands for "advanced encryption standard". This is a symmetric key encryption. Data encrypted with AES can be decrypted back to the original input. The "symmetric" part means one key is used to both encrypt, and decrypt (compared to, e.g., PGP encryption, which uses different keys to encrypt and decrypt).

The SQL hashbytes function can use a number of different algorithms, but none of the are reversible. They are all hashing algorithms, not encryption algorithms.

If you need encryption and decryption in code, the correct SQL functionality to use is EncryptByKey and DecryptByKey

allmhuran
  • 4,154
  • 1
  • 8
  • 27