Introduction

In 2015, I wrote an entry about doing multifactor authentication with PL/SQL (link: https://storm-petrel.com/orablog/2015/11/29/oracle-apex-multifactor-authentication/)

The SMS services that I based my work on has disappeared from the internet landscape, rendering my code and efforts futile.

So it was time to start again. The advantage was that I got to use better RESTful tools and JSON parsers.

The variance this time is that I put 100% of the code on Github

Why MFA?

Why use multifactor authentication (two-factor or 2FA) authentication? Well, frankly it is the right thing to do as I wrote in 2015…

The United States Office of Personnel Management is notifying 2.7 million people that their data were stolen. The blame lays at the feet of the people responsible for these data. While the ultimate responsibility rests with the executives who did not fund investment into data security, I shoulder that responsibility daily.

Github

https://github.com/cmoore-sp/pl-sql_nexmo_sms

Nexmo Multifactor Authentication

The last time I visited this topic, I used some random generators to create a 4-digit code that I then sent via text message to a phone. With the assistance of an Oracle global temporary table and precise timestamps, I did the heavy-ish lift on generating the code, storing the code, sending the code, and verifying the code and the timestamps. Not tough, but the Nexmo approach takes some of the effort.

The process initiates with MFA request to the phone. You get a message ID back. You do NOT get the code.

When the user keys in the code, you (the programmer) has to take the message ID generated from the request and the 4-digit code entered by the user. You send these off to Nexmo. They send back a status code. Zero is good. Not zero is bad.

Interesting Annoying Stuff

The tool apex_json.get_varchar2() does NOT like a dash in the “field name”. The vendor, Nexmo, put dashes in the field names for JSON, but did NOT for the XML interface. So a little replace() action is need to make Oracle happy.

To recap – this is acceptable but does not match Nexmo’s data definition

			r_response.client_ref :=
				apex_json.get_varchar2(
					p_values => l_values, 
					p_path => 'messages[' || trim(to_char(json_row)) ||'].clientRef'
				);

This is not acceptable to Oracle, but does match Nexmo’s data definition

			r_response.client_ref :=
				apex_json.get_varchar2(
					p_values => l_values, 
					p_path => 'messages[' || trim(to_char(json_row)) ||'].client-ref'
				);

Oh well, the solution is just as easy as changing the JSON data:

l_return := replace(l_return, 'client-ref', 'clientRef');

Oracle PL/SQL SMS and MFA

A few quick hints about the code…

Sending SMS text looks rather like this:

begin
	-- validate the parameters
	sms_ok(p_sms,'send_sms','Y');
	l_text := msg_ok(p_msg,'send_sms');

	-- structure the URL
	l_url := g_sms_uri 	||
			'api_key=' 	|| g_key || amp ||
			'api_secret=' 	|| g_secret || amp ||
			'to=' 		|| p_sms || amp ||
			'from=' 		|| g_sender || amp ||
			'text=' 		|| l_text ;
	if p_client_ref is not null then
		l_url := l_url		|| amp ||
			'client-ref='	|| p_client_ref;
	end if; -- client reference is not null
	
	l_return := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
			 p_url              => l_url
			,p_http_method      => 'GET'
			,p_wallet_path		=> g_wallet_path
			,p_wallet_pwd		=> g_wallet_pwd
			);
...

APEX Application

I whipped up a quick APEX 5.1 application to show the integration of the package and the Nexmo API. It is also posted at Github. It will send an SMS message, do two-factor authentication, and show you your balance.

Requirements

1. You MUST have an account with Nexmo.com. You can get a demo account for free.
2. You MUST put your own Oracle Wallet Path and Wallet password into the packages and compile them (G_WALLET_PATH and G_WALLET_PWD)
3. You MUST manually install the Root and Intermediate SSL certs for nexmo.com in your wallet
   • ROOT: Digicert Global Root CA, v3, valid through 09NOV2031
   • Intermediate: Digicert SHA2 Secure Server CA, valid thorugh 08MAR2013
4. If you are on Oracle 12c, DO NOT install the SSL cert for *.nexmo.com
5. You’ll need to create the table that is in the package body. It is in the commented out section.
6. You’ll need to put in your own G_KEY and G_SECRET at the top of the package body.

-\qed