-
-
Save rmalayter/3130462 to your computer and use it in GitHub Desktop.
CREATE FUNCTION dbo.HMAC ( | |
@algo VARCHAR(20) | |
,@key VARBINARY(MAX) | |
,@data VARBINARY(MAX) | |
) | |
/* This function only takes VARBINARY parameters instead of VARCHAR | |
to prevent problems with implicit conversion from NVARCHAR to VARCHAR | |
which result in incorrect hashes for inputs including non-ASCII characters. | |
Always cast @key and @data parameters to VARBINARY when using this function. | |
Tested against HMAC vectors for MD5 and SHA1 from RFC 2202 */ | |
/* | |
List of secure hash algorithms (parameter @algo) supported by MSSQL | |
version. This is what is passed to the HASHBYTES system function. | |
Omit insecure hash algorithms such as MD2 through MD5 | |
2005-2008R2: SHA1 | |
2012-2016: SHA1 | SHA2_256 | SHA2_512 | |
*/ | |
RETURNS VARBINARY(64) | |
AS | |
BEGIN | |
DECLARE @ipad BIGINT | |
DECLARE @opad BIGINT | |
DECLARE @i VARBINARY(64) | |
DECLARE @o VARBINARY(64) | |
DECLARE @pos INTEGER | |
--SQL 2005 only allows XOR operations on integer types, so use bigint and interate 8 times | |
SET @ipad = cast(0x3636363636363636 AS BIGINT) --constants from HMAC definition | |
SET @opad = cast(0x5C5C5C5C5C5C5C5C AS BIGINT) | |
IF len(@key) > 64 --if the key is grater than 512 bits we hash it first per HMAC definition | |
SET @key = cast(hashbytes(@algo, @key) AS BINARY (64)) | |
ELSE | |
SET @key = cast(@key AS BINARY (64)) --otherwise pad it out to 512 bits with zeros | |
SET @pos = 1 | |
SET @i = cast('' AS VARBINARY(64)) --initialize as empty binary value | |
WHILE @pos <= 57 | |
BEGIN | |
SET @i = @i + cast((substring(@key, @pos, 8) ^ @ipad) AS VARBINARY(64)) | |
SET @pos = @pos + 8 | |
END | |
SET @pos = 1 | |
SET @o = cast('' AS VARBINARY(64)) --initialize as empty binary value | |
WHILE @pos <= 57 | |
BEGIN | |
SET @o = @o + cast((substring(@key, @pos, 8) ^ @opad) AS VARBINARY(64)) | |
SET @pos = @pos + 8 | |
END | |
RETURN hashbytes(@algo, @o + hashbytes(@algo, @i + @data)) | |
END | |
GO | |
GRANT EXECUTE | |
ON dbo.HMAC | |
TO PUBLIC | |
GO | |
/* | |
Copyright © 2012 Ryan Malayter. All Rights Reserved. | |
Redistribution and use in source and binary forms, with or without | |
modification, are permitted provided that the following conditions are | |
met: | |
1. Redistributions of source code must retain the above copyright | |
notice, this list of conditions and the following disclaimer. | |
2. Redistributions in binary form must reproduce the above copyright | |
notice, this list of conditions and the following disclaimer in the | |
documentation and/or other materials provided with the distribution. | |
3. The name of the author may not be used to endorse or promote products | |
derived from this software without specific prior written permission. | |
THIS SOFTWARE IS PROVIDED BY Ryan Malayter "AS IS" AND ANY EXPRESS OR | |
IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED | |
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE | |
DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, | |
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES | |
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR | |
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) | |
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, | |
STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN | |
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE | |
POSSIBILITY OF SUCH DAMAGE. | |
*/ |
@EJocys You may be interested to know that your implementation does not appear to be a correct implementation of the standard, at least for SHA2_256. Here is an example:
SELECT dbo.Security_HMAC('SHA2_256', 0x63727970746969, 0x68656c6c6f21);
-- Outputs
0xAFC84D55FD2C9E2E294C24188EA2015E6695E365B79D2071DB6D0A44959200D7
Equivalent demonstration in CyberChef and Cryptii, (as well as the other implementations in this thread) show an output of
0xd6cbb383c40418c408740af2727bf8c08231609a3827dba31538e0d11ab4b1d4
@EJocys Your "Padding with zeros on the right" is the problem. Casting to varbinary doesn't pad anything. Changing that line to this works for my test case:
IF LEN(@key) < @blockSize
SET @key = SUBSTRING(CAST(@key AS binary(128)), 0, @blockSize);
Thank you. Excellent finding and fix. Tested to make sure that C# implementation returns same result as CyberChef and Cryptii.
Updated original code: https://github.com/JocysCom/ShellScripts/tree/master/Tester/Scripts/HMAC_for_SQL/Data/Functions
I will update Internet example with the Note.
Also added example inside HMAC.sql:
-- Example 1:
DECLARE @key varbinary(max) = CAST(N'Password' AS varbinary(max))
DECLARE @message varbinary(max) = CAST(N'Message' AS varbinary(max))
SELECT @key AS [Key], @message AS [Message]
-- Return hash: 0xD28A366CDA742EB767AB56B7B11893EE73BA2CD54792D5ACF9189D54F36E60EE
SELECT [Security].HMAC('SHA2_256', @key, @message) AS [Hash]
-- Example 2:
DECLARE @key varbinary(max) = 0x63727970746969
DECLARE @message varbinary(max) = 0x68656c6c6f21
SELECT @key AS [Key], @message AS [Message]
-- Return hash: 0xD6CBB383C40418C408740AF2727BF8C08231609A3827DBA31538E0D11AB4B1D4
SELECT [Security].HMAC('SHA2_256', @key, @message) AS [Hash]
C# Test Example:
var key = StringToByteArray("0x63727970746969");
var data = StringToByteArray("0x68656C6C6F21");
var algorithm = new System.Security.Cryptography.HMACSHA256();
algorithm.Key = key;
var hash = algorithm.ComputeHash(data);
Console.WriteLine(" Hash: {0}", string.Join("", hash.Select(x => x.ToString("X2"))));
public static byte[] StringToByteArray(string hex)
{
if (hex.StartsWith("0x"))
hex = hex.Substring(2);
hex = hex.Replace(":", "").Replace("-", "");
var chars = hex.Length;
var bytes = new byte[chars / 2];
for (int i = 0; i < chars; i += 2)
bytes[i / 2] = Convert.ToByte(hex.Substring(i, 2), 16);
return bytes;
}
Hi,
Why Function is returning different result when doing a cross check with Online tool "HMAC-SHA256",
here what i am running
DECLARE @key varbinary(max) = CAST(N'd666f18176df4e29a64c7e40519a1751' AS varbinary(max))
DECLARE @message varbinary(max) = CAST(N'1e0950435dc94529cffdb67b1b679f3320082021' AS varbinary(max))
SELECT @key AS [Key], @message AS [Message]
-- Return hash.
SELECT [dbo].HMAC('SHA2_256', @key, @message) AS [Hash]
Return Hash Value: 0xD4367202498312F068300882B0EFDA582137A9DB6D98ADB14E2B6BA2DCE60A09
BUT online tool Return base64: CgM3WVX9eTHtJggajqlBoeJuEHLGEUJYidHgKTxuUn0=
I know i must be doing something wrong but can you tell me where i am making a mistake?
Thanks
@bet365mj You're either misusing your online tool or you're misunderstanding your CASTs.
Here is what you are calculating with SQL Server: CyberChef Example
The inputs shown are what is output by your first SELECT
statement.
If you want SQL Server to calculate ac9454b759103d77f8ad0ba7d43a3bd5c006f77e297a42c7e31d590f3bed13ad
, as shown here, then you should not be converting your key and message from SQL Server unicode text -- use the literal binary values as follows:
DECLARE @key varbinary(max) = 0xd666f18176df4e29a64c7e40519a1751;
DECLARE @message varbinary(max) = 0x1e0950435dc94529cffdb67b1b679f3320082021;
SELECT @key AS [Key], @message AS [Message]
-- Return hash.
SELECT [dbo].HMAC('SHA2_256', @key, @message) AS [Hash]
-- Returns 0xAC9454B759103D77F8AD0BA7D43A3BD5C006F77E297A42C7E31D590F3BED13AD
However, the output of your online tool (converted from Base64) is 0a03375955fd7931ed26081a8ea941a1e26e1072c611425889d1e0293c6e527d
. This is what you get when converting your key and message from ASCII or UTF-8, which suggests that your online tool is expecting text input and not a hexadecimal representation of binary. In other words, your key is being interpreted as binary literal 0x6436363666313831373664663465323961363463376534303531396131373531
, and the message goes through a similar conversion from ASCII/UTF-8, shown here.
To replicate this in SQL Server, you need the following:
DECLARE @key varbinary(max) = CAST('d666f18176df4e29a64c7e40519a1751' AS varbinary(max))
DECLARE @message varbinary(max) = CAST('1e0950435dc94529cffdb67b1b679f3320082021' AS varbinary(max))
SELECT @key AS [Key], @message AS [Message]
-- Return hash.
SELECT [dbo].HMAC('SHA2_256', @key, @message) AS [Hash]
-- Returns 0x0A03375955FD7931ED26081A8EA941A1E26E1072C611425889D1E0293C6E527D
@NReilingh
Thanks for the prompt rely, I was using HMAC-SHA256 when encoding key.
I am calling my API server from SQL Server and server needs HMAC-SHA256 coded key. When i came across your script, i thought it is returning value in base64 but in fact it is returning in plain text.
`
DECLARE @b64 varbinary(max)
DECLARE @key varbinary(max) = CAST('d666f18176df4e29a64c7e40519a1751' AS varbinary(max))
DECLARE @message varbinary(max) = CAST('1e0950435dc94529cffdb67b1b679f3320082021' AS varbinary(max))
-- Return hash.
SELECT [dbo].HMAC('SHA2_256', @key, @message)
-- Returns 0a03375955fd7931ed26081a8ea941a1e26e1072c611425889d1e0293c6e527d
--Convert to base64
SELECT @b64 = [dbo].HMAC('SHA2_256', @key, @message)
SELECT cast(N'' as xml).value('xs:base64Binary(sql:variable("@b64"))', 'varchar(128)');
--Return: CgM3WVX9eTHtJggajqlBoeJuEHLGEUJYidHgKTxuUn0= "This is what i wanted"
`
Thanks again
MJ
@rmalayter:
Note: When creating this solution I was trying to provide backwards compatibility option too i.e. ability to secure/hash passwords on a database when adding new columns or changing its schema in any way can't be easily done due to technical restrictions or limited developing resources. This is why optional "@security int" property is present on [Security_HashPassword] procedure. It allows to re-use old password column for the hash+salt storage and allows to mix secured and unsecured data during transition process (you can easily tell if column contains base64 string just by its fixed size).