Skip to content
Create a gist now

Instantly share code, notes, and snippets.

@rmalayter /HMAC.sql
Last active Mar 17, 2016

HMAC function in Microsoft T-SQL
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.
*/
@thj-dk
thj-dk commented Dec 9, 2015

Are you sure that this function works correctly? Using test case 1 in RFC 4231, i'm not able to produce the correct output.

SELECT dbo.HMAC('SHA2_512', 0x0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b, 0x4869205468657265)
-- Output: 0x9656975EE5DE55E75F2976ECCE9A04501060B9DC22A6EDA2EAEF638966280182477FE09F080B2BF564649CAD42AF8607A2BD8D02979DF3A980F15E2326A0A22A

Am I missing something?

@Brooho
Brooho commented Mar 17, 2016

It works correctly with a lot of hash, like SHA1, SHA2_256, MD5.
But with SHA2_512 I have a problem.
For example I send
SELECT dbo.HMAC ('SHA2_512', CAST('key' as VARBINARY(MAX)), CAST('mess' as VARBINARY(MAX)));

and have result
C2D19170351753D62345A60079F544AFF30C33E2AEF6B33C716814420DE3D3CE97B4E302C9203D7CDF07ACAD192328B88D16F361FA3CC4742ED19886DB6F15AB

But! If I use http://www.freeformatter.com/hmac-generator.html#ad-output
I get
abd1d3be0cc4a178ee43380f7f6d386f6aff97e769e88f756db81cda9c7af0fc706ba3632c134443b50b8ed77c267565b495e88fed41396dfdbe8a68aa3785cc

Something wrong with 'SHA2_512'.

Can you help to fix the mistake?

I use SQL Server 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.