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
Improve this question
9
  • 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. Commented Jul 5, 2020 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. Commented Jul 5, 2020 at 11:16
  • 3
    HASHBYTES is a hashing function and hashing is not encryption. You cannot "decrypt" a hash to obtain the original value. Commented Jul 5, 2020 at 11:21
  • Sorry I meant encrype. I know it's one way. The thing is that I know for sure ( according to : 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 Commented Jul 5, 2020 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. Commented Jul 5, 2020 at 11:23

1 Answer 1

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

thanks for the detailed answer. all columns result starts with 0x... while I know for sure it should start with 4ad... :\
No, look at the rest of the data. The 0x at the start is simply indicating that the result is hexadecimal.
By the way, I suggest editing your question to remove the "encryption" tag.
Done. Removed the tag
Are you satisfied with my answer or are you still not quite sure?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.