Code your own PL/SQL HMAC-SHA1 function

Important Note: Since this article was published - Oracle include the DBMS_CRYPTO package with Exadata Express - so you may not need this.  However, if you have an older, non-enterprise edition of the Oracle database this may still be useful to you!

At JMJ we use Oracle's Exadata Express cloud service to integrate and pull together all the data from the various applications we use - Atlassian Service Desk, Quickbooks etc. Exadata Express comes pre-built with APEX and ORDS and out of the box provides great functionality for building applications, calling third party SOAP and REST services and hosting REST web services. The APEX_WEB_SERVICE package provides a simple API for accessing external services from sites such as ERP Cloud and Quickbooks Online.

However, there is always a catch - and with Exadata Express it comes when trying to call a web service that uses OAUTH 1.0 and requires the HMAC-SHA1 signature algorithm. If that sounds like an alien language to you - you're not alone. A brief read through the HMAC-SHA1 standard located at https://tools.ietf.org/html/rfc2104 shows us that this isn't straightforward.

Put simply, sites requiring OAUTH 1.0 such as Quickbooks need your web service call (URL, method and parameters) to be encoded using a key to generate a signature. This signature is then sent over HTTPS along with your web service call so that the service provider knows that the message was sent by you.

​Many of the sites using OAUTH recommend using a standard library to calculate the signature, and this is sound advice. The Enterprise Editions of the Oracle Database come with the DBMS_CRYPTO built-in package. This contains the following handy MAC function:

DBMS_CRYPTO.mac(UTL_I18N.string_to_raw(l_oauth_base_string, 'AL32UTF8') 
               ,DBMS_CRYPTO.hmac_sh1 
               ,UTL_I18N.string_to_raw(l_oauth_key, 'AL32UTF8'));

​​All good - except this package is not available to us in Exadata Express!  That makes our task a little harder.

So where did we look next? Following the recommendation to re-use a pre-built library, there are several Java HMAC-SHA1 examples on Github. Here's an example of how Java can calculate an HMAC-SHA1 signature:

 publicstatic String calculateRFC2104HMAC(String data, String key)
 throwsSignatureException, NoSuchAlgorithmException, InvalidKeyException
 {
   SecretKeySpecsigningKey = new SecretKeySpec(key.getBytes()
                                             , HMAC_SHA1_ALGORITHM);
   Macmac = Mac.getInstance(HMAC_SHA1_ALGORITHM);
   mac.init(signingKey);
   returntoHexString(mac.doFinal(data.getBytes()));
 }

​This gave us hope - perhaps we could create a Java Stored Procedure and call this from PL/SQL in Exadata Express to generate our signature? Only - hopes dashed again - it turns out Exadata Express doesn't allow you to create Java Stored Procedures.  The only remaining option was to look at building our own procedure to do this task.

First we needed to be able to generate an SHA1 hash. Here's where we were fortunate as there are open-source packages available to use.   One is found here: https://github.com/vadimonus/plsql-hash. The SHA1 function was placed into a package in Exadata Express:

CREATE OR REPLACE package jmj_hash_util_pkg AS
  FUNCTION sha1(p_buffer in raw) RETURN sha1_checksum_raw;
END jmj_hash_util_pkg;

Next we reviewed the HMAC-SHA1 standards document, and checked this against pseudocode and test cases on Wikipedia.  Here's the resulting HMAC_SHA1 function we built to generate the signatures:

FUNCTION hmac_sha1(p_message IN VARCHAR2, p_key IN VARCHAR2)
  RETURN VARCHAR2 IS

     -- Binaries
     v_ohsh   RAW(32767);
     v_kpad   RAW(32767);
     v_ipad   RAW(32767);
     v_opad   RAW(32767);
     v_ihsh   RAW(32767);
     v_skey   RAW(32767);
     v_key_in VARCHAR2(1000);
     v_key    RAW(32767);

     -- Result as hex in the form of text
     v_return VARCHAR2(2000);

  BEGIN

    v_key_in := p_key;

    -- Keys longer than 64 bytes must first be hashed
    IF utl_raw.length(sys.utl_raw.cast_to_raw(v_key_in)) > 64 THEN
      v_key := jmj_hash_util_pkg.sha1(sys.utl_raw.cast_to_raw(v_key_in));
    ELSE
      v_key := sys.utl_raw.cast_to_raw(v_key_in);
    END IF;

    -- Pad the key with zeroes to 64 bytes (128 characters)
    v_kpad := jmj_util_pkg.pad_key(64,'00');

    -- XOR with 64 characters of 0x36 HEX
    v_ipad := jmj_util_pkg.xor(v_ipad,'36');

    -- XOR with 64 characters of 0x5c HEX
    v_opad := jmj_util_pkg.xor(v_kpad,'5c');

    -- Inner HMAC1 hash with inner key and message
    SELECT jmj_hash_util_pkg.sha1(v_ipad || nvl(sys.utl_raw.cast_to_raw(p_message),'') )
    INTO   v_ihsh FROM dual;  

    -- Outer HMAC1 hash with inner hash
    SELECT jmj_hash_util_pkg.sha1(v_opad || v_ihsh )
    INTO   v_ohsh FROM dual;

    -- Convert back to HEX for return value
    SELECT lower(rawtohex(v_ohsh))
    INTO   v_return FROM dual;

    RETURN v_return;

  END;

​But was it right? We tested this against the Wikipedia sample use cases here: https://en.wikipedia.org/wiki/Hash-based_message_authentication_code. ​

SELECT jmj_hmac_pkg.hmac_sha1('','') actual 
,      'fbdb1d1b18aa6c08324b7d64b71fb76370690e1d' answer
FROM   dual
UNION ALL
SELECT jmj_hmac_pkg.hmac_sha1('The quick brown fox jumps over the lazy dog','key') actual
,      'de7c9b85b8b78aa6bc8a7a36f70a90701c9db4d9' answer
FROM   dual
UNION ALL
SELECT jmj_hmac_pkg.hmac_sha1('ABC','iDho0Xmt0PT4J3Ke1BCMbg4voR58CvOJ5miMShLbYAgFegwtk2kJiJM2TOiJR1CzWyL3QUC1VTBdS6IU')
  ,   'f5191e56e2aa2c73f8be0c717478c47b6dcae8c5' -- LONG KEY TEST
FROM dual

ACTUAL                                   ANSWER                                  
---------------------------------------- ----------------------------------------
fbdb1d1b18aa6c08324b7d64b71fb76370690e1d fbdb1d1b18aa6c08324b7d64b71fb76370690e1d
de7c9b85b8b78aa6bc8a7a36f70a90701c9db4d9 de7c9b85b8b78aa6bc8a7a36f70a90701c9db4d9
f5191e56e2aa2c73f8be0c717478c47b6dcae8c5 f5191e56e2aa2c73f8be0c717478c47b6dcae8c5

​All good - everything matched. We were able to apply this signature to our web service call and successfully connect to Quickbooks. Problem solved, even without DBMS_CRYPTO, and importantly without the need for an Enterprise Edition of the database!

We hope you find this helpful if like us you're a big fan of Exadata Express and the endless possibilities it provides for integration. We're always interested in integration work so please reach out if you need a hand with getting your Apex integration up and running!