making https (webservice) requests from PL/SQL without a wallet

While developing the application for my kscope15 presentation I discovered that my Oracle XE database is unable to handle the certificate for Linked-in. This has to do with the algorithm used to sign the Linked-in certificate. Oracle XE 11 has a bug that prevents it from handling the certificate correctly.

The solution lies in creating a proxy in between your database and the final API endpoint.
Like this:

1. your pl/sql program uses UTL_HTTP or APEX_WEB_SERVICE.MAKE_REST_REQUEST to do the API-request to your Apache proxy.

2. The proxy uses its configuration to forward the request to the final endpoint

3. The endpoint replies to your proxy

4. Your proxy reverse proxies it to the requesting code inside your database

In this drawing the green numbers are regular HTTP requests and the orange numbers represent HTTPS requests and responses.

This all sounds very promising. Getting rid of the Oracle wallet seems like a good idea. However you can set the greens to be HTTPS as well, in which case you only need to have one certificate in your wallet.

Considerations

• My Oracle database server uses a private IP range.

• My Apache server is accessible from the internet, but has a second network interface connected to the private IP range.

Setting up your proxy

Setting up the proxy consists of creating a new (virtual) site on your server. I chose to use the domain "revprox.local" because this domain will never get resolved into a real IP-number:

I now need to edit the httpd.conf for my apache server:

RewriteEngine On
ProxyVia On

## proxy for linkedin
ProxyRequests Off
SSLProxyEngine On


  Order deny,allow
  Allow from all


ProxyPass        /www.linkedin.com/     https://www.linkedin.com/
ProxyPassReverse /www.linkedin.com/     https://www.linkedin.com/
ProxyPass        /api.linkedin.com/     https://api.linkedin.com/
ProxyPassReverse /api.linkedin.com/     https://api.linkedin.com/

As you see in the linked-in API documentation, all API endpoint either are on https://www.linkedin.com/ or on http://api.linkedin.com. It is therefore sufficient to define these two in my httpd.conf.

Setting up your database

As I mentioned before, the url http://revprox.local will never resolve into anything useful. We must tell the database what to do when a request for revprox.local comes around.

We now only need to alter the /etc/hosts file as thus:

192.168.1.1     revprox.local

The trick lies in the last line:

• 192.168.1.1 is the private IP number of my apache-proxy

• In the example I removed extra lines that are not relevant for my story

Making a web-request

Now we set up the proxy and made changes to the /etc/hosts file we can actually start using them.

For example, when getting an oauth2 token from linked-in, the documentation tells us to make a request to:

https://www.linkedin.com/uas/oauth2/accessToken

Instead we will be stubborn and use:

http://revprox.local/www.linkedin.com/uas/oauth2/accessToken

as the API endpoint.

That's all folks.