JMJ CLOUD
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us

Our Blog

Code your own PL/SQL HMAC-SHA1 function

9/2/2017

4 Comments

 
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.
Picture

​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!
4 Comments
Dimitri link
12/19/2017 12:42:08 am

thanks for the solution - can you also share the content of jmj_util_pkg.xor and jmj_util_pkg.pad_key ?

Reply
Matt link
1/6/2018 06:46:25 pm

Hi Dmitri

Thanks for your interest - here's the full package listing.

Let me know if you need any more info.

Thanks
Matt

create or replace PACKAGE BODY jmj_hmac_pkg AS


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 that 64 bytes must first be hasheed
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)
SELECT nvl(
hextoraw(RPAD(rawtohex(v_key),128,'00'))
,hextoraw(RPAD('00',128,'00'))
)
INTO v_kpad FROM dual;

-- XOR with 64 characters of 0x36 HEX
SELECT UTL_RAW.bit_xor(v_kpad
,hextoraw(RPAD('36',128,'36'))
)
INTO v_ipad FROM dual;

-- XOR with 64 characters of 0x5c HEX
SELECT UTL_RAW.bit_xor(v_kpad
,hextoraw(RPAD('5c',128,'5c'))
)
INTO v_opad FROM dual;

-- 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;

/* UNIT TEST

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

*/

END jmj_hmac_pkg;

Reply
Dimitri Gielis link
1/11/2018 04:04:41 am

Hi Matt,

I was actually looking for jmj_hash_util_pkg.sha1

Can you post that too?

Thanks so much,
Dimitri

Reply
Dimitri Gielis link
1/11/2018 04:15:04 am

just wondered if you did something else than https://github.com/antonscheffer/as_crypto

Reply



Leave a Reply.

    RSS Feed

    Popular Posts

    - APEX Dog Food
    - Cloud ERP & APEX Mashup
    - Modernizing EBS with APEX
    - Easy APEX_WEB_SERVICE
    - Running APEX in RDS
    - ORDS What & Why?

    Categories

    All
    APEX
    AWS
    Fusion Cloud ERP
    INTEGRATION
    MS GRAPH
    OCI
    ORDS
    PaaS
    RAD
    REST
    SOAP

    Archives

    October 2021
    February 2021
    January 2021
    October 2020
    September 2020
    June 2020
    May 2020
    April 2020
    February 2020
    January 2020
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    September 2017
    August 2017
    July 2017
    June 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016

Company

About
Contact
Blog
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us