Thinking Anew

Oracle APEX and REST Without the Pain

Photo by <a href= target=_blank>Hisham Abo-hamad</a> on Unsplash

Earlier this year (2017), I had the privilege to share with the community, some of the work and experiences I gained helping to build some of the blocks that form the foundation of In my presentations, I had shared some of the challenges I had integrating RESTful web services with Oracle Application Express (APEX). A crippling issue involved the use of Oracle Database Express Edition (Oracle XE) and communicating with web services over a secured protocol. The by-product of that experience led to the development of a simple Node.js application, which I named Suez.

Traffic flowing through the Internet can be sniffed and used for nefarious purposes. Therefore we are encouraged, whenever possible, to use a secured network transport protocol to safeguard against these attacks. It is almost mandatory when the communication between client and server contains secrets or personally identifiable information (PII), e.g passwords, secure tokens and users’ credit card details.

For content served over web protocols, we use Transport Layer Security (TLS) or Secure Sockets Layer (SSL). These involve the use of digitally-signed certificates belonging to a trust chain that allows systems to verify identity and provides keys to communicate securely using encryption. Most Certification Authorities (CA) offer these for an annual fee, but more recently, a consortium led by Mozilla and a few other partners, created a new CA that made SSL certificates available to everyone. Let’s Encrypt is an open CA that provides services and tools to automatically verify and sign certificate requests for free!

Many API providers offer web services over HTTPS and consumers are highly encouraged, and sometimes forced to use this protocol for accessing content. The Oracle Wallet is used to store and manage SSL certificates that are required for establishing connections (SSL handshakes). For Oracle Database Standard and Enterprise Editions, a tool known as the Oracle Wallet Manager (OWM) is used to conveniently add and remove certificates. Not surprisingly, this is absent in the free Express Edition. A workaround using OpenSSL was prescribed by Hazrul Nizam’s blog post. Good read!

However, like any other web technology, SSL is prone to weaknesses and vulnerabilities and hence, it continues to evolve. For example, older SSL certificates that were signed with a weaker hashing algorithm SHA-1, have been made obsolete as it was been proven to be prone to collisions. For older versions of the Oracle DB, an active Oracle Support contract allows you access to patches that upgrade the wallet to handle newer hashes. However, if you are developing using Oracle XE, then you are out of luck. Well… kinda.

Without any upgrades to support newer hash algorithms, we were forced to improvise a solution for integrating Slack and This is done through a proxy that accepts communications on a non-secure HTTP channel, relay the request and then pipe the response back to the caller. However, rather than write code for every API that needed to be accessed, I wrote Suez.

Suez usage example

Suez relies on Steven Chim’s work for creating simple web proxies using Node.js, and that in turn is powered by Nodejitsu’s node-http-proxy. It uses the request’s hostname value, looks up the configuration and then routes the call to the intended API target. Instructions on how to configure and deploy the application is detailed in the project’s README.

I am hoping that this small utility would help developers get started on integrating cool APIs and build even cooler APEX applications. That said, I would end by emphasizing that orthdox approaches to security are always preferred. If budget allows, continue to rely on SSL certificates not only for securing end-to-end communications, but also, verifying that your application is talking to authorized servers.