Using a proxy with the JavaScript fetch API and UTL_HTTP

January 16, 2024 | 5 minute read
Text Size 100%:

This short post details how to use a proxy server for outgoing API requests, covering both UTL_HTTP and the In-Database JavaScript fetch() API. The code in this article was tested on Oracle Database 23c Free, using the container image from https://container-registry.oracle.com/ords/ocr/ba/database/free.

For the purpose of writing this article, some mock infrastructure was created in Oracle Cloud Infrastructure (OCI). A VM running a proxy server is available to the database host. The database host has been created in a private subnet for security reasons. Security lists and -groups ensure that Internet access is exclusively allowed via the proxy. The proxy has, of course, been hardened in a similar way.

Here is proof:

[opc@proxydemo-database ~]$ curl --fail -sLo /dev/null https://www.oracle.com; echo "return code: $?"                                         
return code: 7                                                      

As per man 1 curl return code 7 means Failed to connect to host, which is expected. Setting https_proxy leads to the desired result:

[opc@proxydemo-database ~]$ export https_proxy=http://proxydemo-proxy.public.proxydemovcn.oraclevcn.com:3128
[opc@proxydemo-database ~]$ curl --fail -sLo /dev/null https://www.oracle.com; echo "return code: $?"
return code: 0

Let's try accessing some APIs via PL/SQL and In-Database JavaScript.

Defining the Network Access Control Entry (ACE)

To keep this example short, I'd like to invoke IPify to get my public IP address in JSON format. This relatively simple example is based on an earlier article. If you have any questions about the following code, please refer to the post for details.

Let's assume DEMOUSER is going to invoke ipify.org. The first step is to allow the account to perform HTTP traffic. To do so, you need to create 2 ACEs:

  1. The first one allows http_proxy traffic to the proxy host
  2. The second one allows the actual API invocation

Connect to the PDB in question and execute the following anonymous PL/SQL block as a user with SYSDBA privileges:

BEGIN
    -- step 1: allow HTTP access to the PROXY
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => 'proxydemo-proxy.public.proxydemovcn.oraclevcn.com',
        lower_port => 3128,
        upper_port => 3128,
        ace  =>  xs$ace_type(
            privilege_list => xs$name_list('http_proxy'),
            principal_name => 'DEMOUSER',
            principal_type => xs_acl.ptype_db
        )
    );

    -- step 2: now enable access to the actual host
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => 'api.ipify.org',
        ace  =>  xs$ace_type(
            privilege_list => xs$name_list('http'),
            principal_name => 'DEMOUSER',
            principal_type => xs_acl.ptype_db
        )
    );

    COMMIT;
END;
/

Review the Database Security Guide for more details about the use of the xs$ace_type and the privilege_list parameter. Make sure to change the parameter values as appropriate for your environment.

The stage is now set.

Using a proxy with UTL_HTTP

The JavaScript fetch() API sits on top of UTL_HTTP, so it makes sense to start with it. Let's try accessing IPify to learn more about my system's public IP address. A new feature in Oracle Database 23c allows me to use the operating system's certificate store rather than a wallet to connect to TLS-protected resources. Setting wallet_path to 'system:' (including the : at the end!) is such a time saver!

set serveroutput on
declare
    ip_addr         varchar2(32767);
begin

    ip_addr := utl_http.request(
        url             => 'https://api.ipify.org/?format=json',
        wallet_path     => 'system:',
        proxy           => 'proxydemo-proxy.public.proxydemovcn.oraclevcn.com:3128'
    );

    dbms_output.put_line(json_serialize(JSON(ip_addr) pretty));
end;
/

Your client session should have returned the IPv4 address of your Internet Gateway.

Using a proxy with the fetch() API

The JavaScript fetch() call is a standardised, well-established API. Multilingual Engine (MLE), powered by GraalVM, supports the API, allowing you to use JavaScript function calls inside the database. The MLE-specific implementation details can be found in the API documentation. The following function represents a typical example invoking the fetch() API.

create or replace function fetch_me_if_you_can
return JSON
as mle language javascript
q'~
    await import ("mle-js-fetch");
    const response = await fetch('https://api.ipify.org/?format=json');
    if (response.ok) {
        const data = await response.json();
        return data;
    } else {
        throw new Error(
          'unexpected network error trying to query the web server'
        );
    }
~';
/

Once the function has been created, it can be used very much like the earlier example:

declare
    ip_addr JSON;
begin
    utl_http.set_wallet('system:');
    utl_http.set_proxy('proxydemo-proxy.public.proxydemovcn.oraclevcn.com:3128');

    ip_addr := fetch_me_if_you_can;

    dbms_output.put_line(json_serialize(ip_addr pretty));
end;
/

Your client session should have returned the IPv4 address of your Internet Gateway.

Summary

Using the fetch() API behind a proxy server isn't too difficult, provided the prerequisites are met. This article demonstrated how to prepare the Access Control Entries (ACEs) before calling the UTL_HTTP functions and the fetch() API.

Martin Bach

Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.


Previous Post

FAQ: Understanding the transition to Oracle Cloud Infrastructure

Mike Conway | 2 min read

Next Post


Fast-path to developing with Oracle Application Express and Multilingual Engine

Martin Bach | 5 min read