Calculating Hash-based Message Authentication Codes with SQL Anywhere

I have been spending a lot of time recently preparing SQL Anywhere for use on Amazon EC2 . Deep in the nuts-and-blots of the Amazon Web Services system, Hash-based Message Authentication Codes (HMAC) are used for authenticating all requests. Usually this authentication is handled automatically by the tools that you are using (ex. AWS Management Console ). However, if you want to talk directly to the Amazon Web Services through HTTP, you will need the ability to apply an HMAC code to each of your requests. This is exactly the situation I found myself in the other day when I wanted to see if SQL Anywhere could interact directly with Amazon S3 (hopefully my findings on this will be the subject of a future post).

HMAC (RFC 2104 ) is a method for creating an authentication code on a message using well-known hashing functions such as SHA-1 and MD5. In an HMAC setup, a secret key is combined with the message before the hashing operation. The resulting HMAC code is sent along with the message as a means of authenticating it. The recipient, who also knows the secret key, can check if the message was tampered with during delivery by calculating the HMAC code of the message and making sure they match.

The algorithim is:

• K be a secret key padded to the right with extra zeros to the block size of the hash function
• m be the message to be authenticated
• ∥ denote concatenation
• ⊕ denote exclusive or (XOR)
• opad be the outer padding (0x5c5c5c…5c5c, one-block-long hexadecimal constant)
• ipad be the inner padding (0x363636…3636, one-block-long hexadecimal constant)

Then HMAC(K,m) is mathematically defined by HMAC(K,m) = H((K ⊕ opad) ∥ H((K ⊕ ipad) ∥ m)).

While SQL Anywhere does not have a built-in HMAC function, it does contain all the primitive functions required to assemble one. Here is my implementation of an HMAC function that supports SHA-1, and MD5.

CREATE FUNCTION HMAC("key" LONG VARCHAR, "message" LONG VARCHAR, hash_method VARCHAR(4))
RETURNS LONG VARCHAR
BEGIN
 DECLARE intermediate_result LONG VARCHAR;
 IF LENGTH("key") > 64 THEN
 SET "key" = HASH("key", hash_method);
 EXECUTE IMMEDIATE ('SET "key" = 0x' || "key");
 END IF;
 SET intermediate_result = HASH(CAST((CAST(CAST(REPEAT(0x36, 64) as LONG BINARY) as LONG VARBIT) ^ CAST(CAST("key" as LONG BINARY) as LONG VARBIT)) as LONG BINARY) || "message", hash_method);
 EXECUTE IMMEDIATE ('set intermediate_result = 0x' || intermediate_result);
 RETURN HASH(CAST((CAST(CAST(REPEAT(0x5C, 64) as LONG BINARY) as LONG VARBIT) ^ CAST(CAST("key" as LONG BINARY) as LONG VARBIT)) as LONG BINARY) || intermediate_result, hash_method);
END;

For example, to calculate the HMAC on the message Hello, World! with the key my-key using SHA1 hashing, you would use:

HMAC('my-key', 'Hello, World!', 'SHA1')

This produces result of 56c27707311f55fa7e5f8d94d8a2e9786864bbd2. Typically the code is Base64 encoded before it is sent. The Base64 encoding can be calculated using:

BASE64_Encode('56c27707311f55fa7e5f8d94d8a2e9786864bbd2')

producing NTZjMjc3MDczMTFmNTVmYTdlNWY4ZDk0ZDhhMmU5Nzg2ODY0YmJkMg==.

To see how this would be used in an actual example, imagine we wanted to send the message Hello, World! to a server who we knew shared our secret key. We could now send the following HTTP request:

http://some.server.com?
message=Hello%2C+World%21&
HMAC=NTZjMjc3MDczMTFmNTVmYTdlNWY4ZDk0ZDhhMmU5Nzg2ODY0YmJkMg%3D%3D

When the message is received at some.server.com, the system can use the shared secret key to calculate the HMAC on the received message. If the calcuated HMAC differs from the HMAC sent along with the request, then the server knows that ether the message has been tampered with, or that the original sender did not know the correct secret-key. In either case, the server should discard the message as fraudulent.

I have provided a modest test suite (based on the test found in RFC 2202 ). If the tests pass, the final select should return no results.

CREATE TABLE HMACTest (
"key" LONG VARCHAR,
"message" LONG VARCHAR,
"method" VARCHAR(4),
"digest" LONG VARCHAR
);
INSERT INTO HMACTest VALUES (0x0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b, 'Hi There', 'MD5', '9294727a3638bb1c13f48ef8158bfc9d');
INSERT INTO HMACTest VALUES ('Jefe', 'what do ya want for nothing?', 'MD5', '750c783e6ab0b503eaa86e310a5db738');
INSERT INTO HMACTest VALUES (0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa, REPEAT(0xdd, 50), 'MD5', '56be34521d144c88dbb8c733f0e8b3f6');
INSERT INTO HMACTest VALUES (0x0102030405060708090a0b0c0d0e0f10111213141516171819, REPEAT(0xcd, 50), 'MD5', '697eaf0aca3a3aea3a75164746ffaa79');
INSERT INTO HMACTest VALUES (0x0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c, 'Test With Truncation', 'MD5', '56461ef2342edc00f9bab995690efd4c');
INSERT INTO HMACTest VALUES (REPEAT(0xaa, 80), 'Test Using Larger Than Block-Size Key - Hash Key First', 'MD5', '6b1ab7fe4bd7bf8f0b62e6ce61b9d0cd');
INSERT INTO HMACTest VALUES (REPEAT(0xaa, 80), 'Test Using Larger Than Block-Size Key and Larger Than One Block-Size Data', 'MD5', '6f630fad67cda0ee1fb1f562db3aa53e');
INSERT INTO HMACTest VALUES (0x0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b, 'Hi There', 'SHA1', 'b617318655057264e28bc0b6fb378c8ef146be00');
INSERT INTO HMACTest VALUES ('Jefe', 'what do ya want for nothing?', 'SHA1', 'effcdf6ae5eb2fa2d27416d5f184df9c259a7c79');
INSERT INTO HMACTest VALUES (0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa, REPEAT(0xdd, 50), 'SHA1', '125d7342b9ac11cd91a39af48aa17b4f63f175d3');
INSERT INTO HMACTest VALUES (0x0102030405060708090a0b0c0d0e0f10111213141516171819, REPEAT(0xcd, 50), 'SHA1', '4c9007f4026250c6bc8414f9bf50c86c2d7235da');
INSERT INTO HMACTest VALUES (0x0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c, 'Test With Truncation', 'SHA1', '4c1a03424b55e07fe7f27be1d58bb9324a9a5a04');
INSERT INTO HMACTest VALUES (REPEAT(0xaa, 80), 'Test Using Larger Than Block-Size Key - Hash Key First', 'SHA1', 'aa4ae5e15272d00e95705637ce8a3b55ed402112');
INSERT INTO HMACTest VALUES (REPEAT(0xaa, 80), 'Test Using Larger Than Block-Size Key and Larger Than One Block-Size Data', 'SHA1', 'e8e99d0f45237d786d6bbaa7965c7808bbff1a91');
SELECT * FROM HMACTest WHERE digest != HMAC("key", "message", method);