Specifies an expression that evaluates to a character or binary string to be hashed. The output conforms to the algorithm standard: 128 bits (16 bytes) for MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32 bytes) for SHA2_256, and 512 bits (64 bytes) for SHA2_512.
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.
I have found the solution else where:
SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'HelloWorld')), 3, 32)
SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'Hello World'),2)
Use master.dbo.fn_varbintohexsubstring(0, HashBytes('SHA1', @input), 1, 0)
instead of master.dbo.fn_varbintohexstr
and then substringing
the result.
In fact fn_varbintohexstr
calls fn_varbintohexsubstring
internally. The first argument of fn_varbintohexsubstring
tells it to add 0xF
as the prefix or not. fn_varbintohexstr
calls fn_varbintohexsubstring
with 1
as the first argument internaly.
Because you don't need 0xF
, call fn_varbintohexsubstring
directly.
Contrary to what David Knight says, these two alternatives return the same response in MS SQL 2008:
SELECT CONVERT(VARCHAR(32),HashBytes('MD5', 'Hello World'),2)
SELECT UPPER(master.dbo.fn_varbintohexsubstring(0, HashBytes('MD5', 'Hello World'), 1, 0))
So it looks like the first one is a better choice, starting from version 2008.
convert(varchar(34), HASHBYTES('MD5','Hello World'),1)
(1 for converting hexadecimal to string)
convert this to lower and remove 0x from the start of the string by substring:
substring(lower(convert(varchar(34), HASHBYTES('MD5','Hello World'),1)),3,32)
exactly the same as what we get in C# after converting bytes to string
With personal experience of using the following code within a Stored Procedure which Hashed a SP Variable I can confirm, although undocumented, this combination works 100% as per my example:
@var=SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA2_512', @SPvar)), 3, 128)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With