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!