-
-
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. | |
*/ |
@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
@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: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 literal0x6436363666313831373664663465323961363463376534303531396131373531
, and the message goes through a similar conversion from ASCII/UTF-8, shown here.To replicate this in SQL Server, you need the following: