Skip to content

@rmalayter /HMAC.sql
Last active

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
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.
*/
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.