OAuth and the PL/SQL

OAuth signatures in PL/SQL

This article is intended as first of a series to explain how to interact with those services wich require oauth authentication, most famous being LinkedIn, Twitter etc.

The OAuth standard is a little complex, and explaining it is out of the scope of this article, there are some very good tutorials out there, which include:

1. Authenticating Requests with OAuth
2. Amazon Products API Request Signing

 

In order to test the code in this article we will use the OAuth test cases as in the wiki page: http://wiki.oauth.net/TestCases

 

Consumer Secret	Token Secret	Base String	Signature
cs		bs	egQqG5AJep5sJ7anhXju1unge2I=
cs	ts	bs	VZVjXceV7JgPq/dOTnNmEfO0Fv8=
kd94hf93k423kf44	pfkkdhi9sl3r4s00	GET&http%3A%2F%2Fphotos.example.net%2Fphotos&file%3Dvacation.jpg%26oauth_consumer_key%3Ddpf43f3p2l4k3l03%26oauth_nonce%3Dkllo9940pd9333jh%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D1191242096%26oauth_token%3Dnnch734d00sl2jdk%26oauth_version%3D1.0%26size%3Doriginal	tR3+Ty81lMeYAr/Fid0kMTYa/WM=

 

First step is to create a function that is able to calculate an oauth signature, which is -by far- the most challenging task. Most common encription type in oauth is HMAC-SHA1.

In Oracle tere is a package named DBMS_CRYPTO that does it all.

The MAC function of this package accepts the following parameters:

 

DBMS_CRYPTO.MAC (

  src IN RAW,

  typ IN PLS_INTEGER,

  key IN RAW)

RETURN RAW;

 

In our case the first parameter is the base string, the second is the constant DBMS_CRYPTO.HMAC_SH1 and the third is the consumer secret + token secret.

Before calling the function we need to convert the VARCHAR2 string we have to a RAW type. This is accomplished by another Oracle package called UTL_I18N with the function STRING_TO_RAW:

 

UTL_I18N.STRING_TO_RAW(     data          IN VARCHAR2 CHARACTER SET ANY_CS,    dst_charset   IN VARCHAR2 DEFAULT NULL) RETURN RAW;

So for example, we calculate the signature of the first test case. Since the charset must always be ‘AL32UTF8’ the base string and consumer secret will be first converted to RAW:

 

select UTL_I18N.STRING_TO_RAW('bs','AL32UTF8') base_string_raw from dual;

BASE_STRING_RAW

——————————————–

6273

 

select UTL_I18N.STRING_TO_RAW('cs','AL32UTF8') base_string_raw from dual;

BASE_STRING_RAW

———————————————

6373

 

then:

 

declare

  oauth_signature VARCHAR2(50);

begin

  select

       dbms_crypto.mac(

               UTL_I18N.STRING_TO_RAW('bs','AL32UTF8')

               , DBMS_CRYPTO.HMAC_SH1

               , UTL_I18N.STRING_TO_RAW('cs&','AL32UTF8'))

       into oauth_signature

       from dual;

  dbms_output.put_line(oauth_signature);

end;

/

 

Note the ‘&’ at the end of the consumer secret. The OAuth standard explains this (9.4.1.Generating Signature: […] “is set to the concatenated encoded values of the Consumer Secret and Token Secret, separated by a ‘&’ character (ASCII code 38), even if either secret is empty.”).

Now we have a signature like 7A042A1B90097A9E6C27B6A78578EED6E9E07B62 which needs to be base 64 ecoded.

Another Oracle package named UTL_ENCODE, has a function base64_encode to do this; then the result must be converted to a VARCHAR2 with UTL_RAW.cast_to_varchar2.

 

declare

oauth_signature VARCHAR2(100);

begin

select  UTL_RAW.cast_to_varchar2(

 UTL_ENCODE.base64_encode(

 dbms_crypto.mac(

  UTL_I18N.STRING_TO_RAW('bs','AL32UTF8')

  , DBMS_CRYPTO.HMAC_SH1

  , UTL_I18N.STRING_TO_RAW('cs&','AL32UTF8'))))

 into oauth_signature

 from dual;

dbms_output.put_line(oauth_signature);

end;

/

egQqG5AJep5sJ7anhXju1unge2I=

 

Thanks to Jimbo1 in the Google Checkout Forum for publishing how to generate the HMAC

Now let’s compute the second example.

 

declare

  oauth_signature VARCHAR2(100);

begin

  select  UTL_RAW.cast_to_varchar2(

       UTL_ENCODE.base64_encode(

       dbms_crypto.mac(

               UTL_I18N.STRING_TO_RAW('bs','AL32UTF8')

               , DBMS_CRYPTO.HMAC_SH1

               , UTL_I18N.STRING_TO_RAW('cs&ts','AL32UTF8'))))

       into oauth_signature

       from dual;

  dbms_output.put_line(oauth_signature);

end;

/

VZVjXceV7JgPq/dOTnNmEfO0Fv8=

 

If SQL*Plus prompts you for the value of ts you need to SET DEFINE OFF before running the block of code.

 

Now we can create a function oauth_signature that always returns the corresponding VARCHAR2 signature string:

 

create or replace function oauth_signature(p_base_string in varchar2, p_consumer_secret in varchar2, p_token_secret in varchar2)

return varchar2

is

v_oauth_signature VARCHAR2(100);

begin

select  UTL_RAW.cast_to_varchar2(

 UTL_ENCODE.base64_encode(

 dbms_crypto.mac(

  UTL_I18N.STRING_TO_RAW('bs','AL32UTF8')

  , DBMS_CRYPTO.HMAC_SH1

  , UTL_I18N.STRING_TO_RAW('cs&ts','AL32UTF8'))))

 into v_oauth_signature

 from dual;

return v_oauth_signature;

end;

/

 

which we’ll call from a SELECT:

select oauth_signature('bs', 'cs', null) from dual; –first example

select oauth_signature('bs', 'cs', 'ts') from dual;  –second example

select oauth_signature('GET&http%3A%2F%2Fphotos.example.net%2Fphotos&file%3Dvacation.jpg%26oauth_consumer_key%3Ddpf43f3p2l4k3

l03%26oauth_nonce%3Dkllo9940pd9333jh%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D1191242096%26oauth_token%3Dnnch734d0

0sl2jdk%26oauth_version%3D1.0%26size%3Doriginal', 'kd94hf93k423kf44', 'pfkkdhi9sl3r4s00') from dual; –third example