Accepting payments with Stripe in APEX, Part 2

Adding the PL/SQL API

In the previous part of this series, we hooked up the payment form so that users can enter their credit card information to accept payment. After this, the next thing you need to do is to charge the user for the goods or services. In the payment form, it's all on the client side using JavaScript; This time, we need to make some requests in PL/SQL. There is no client libraries for PL/SQL, so we need to set up some to make requests to the Stripe API.

All the Stripe API's have the base URL: https://api.stripe.com/ - so we first need to set up an ACL. Just whilst figuring everything out, we will assign this to the schema you are developing out of (down the track, when we switch to APEX_WEB_SERVICE, we can also assign the ACL to the APEX schema).

DECLARE

    l_filename varchar2(30) := 'stripe.com.xml';
    l_schema varchar2(20) := '[SCHEMA?]';

BEGIN

    BEGIN
        DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
            acl => l_filename
        );

    EXCEPTION WHEN OTHERS THEN
        NULL; -- ACL does not exist yet
    END;

    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
          acl           => l_filename
        , description   => 'All requests to test utl_http'
        , principal     => l_schema -- schema name
        , is_grant      => TRUE
        , privilege     => 'connect'
    );

    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
          acl       => l_filename
        , principal => l_schema -- schema name
        , is_grant  => TRUE
        , privilege => 'resolve'
    );

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
          acl           => l_filename
        , host          => 'api.stripe.com'
        , lower_port    => 443
    );

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
        acl             => l_filename
      , host            => 'api.stripe.com'
      , lower_port      => 80
  );


END;
/

Now, with that, we can set up a generic procedure to output the contents of a HTTP request:

CREATE OR REPLACE PROCEDURE output_html (p_url IN VARCHAR2) AS
    l_http_request UTL_HTTP.req;
    l_http_response UTL_HTTP.resp;
    l_text VARCHAR2(32767);
BEGIN
    
    l_http_request := UTL_HTTP.begin_request(p_url);
    l_http_response := UTL_HTTP.get_response(l_http_request);

    BEGIN
        LOOP
            UTL_HTTP.read_text(l_http_response, l_text, 32766);
            DBMS_OUTPUT.put_line (l_text);
        END LOOP;
        
        EXCEPTION
            WHEN UTL_HTTP.end_of_body 
            THEN
                UTL_HTTP.end_response(l_http_response);
    END;
    
    EXCEPTION
        WHEN OTHERS THEN
            UTL_HTTP.end_response(l_http_response);
            RAISE;
END output_html;
/

set serveroutput on

begin
    output_html('http://api.stripe.com');
end;
/

{
  "error": {
    "message" : "The Stripe API is only accessible over HTTPS.
Please see <https://stripe.com/docs> for more information.",
    "type":
"invalid_request_error"
  }
}

So, that error message is pretty self explanatory. The API only accepts requests over HTTPS. We haven't set up a wallet yet, so we will get an error if trying to do the same request over HTTPS. Being this example is based on setting up procedures on an XE instance - and XE does not come with the wallet manager, you will need to turn to the command line tool openSSL, which can achieve the same result. This process is documented on this blog: https://blog.hazrulnizam.com/openssl-workaround-oracle-xe-wallet/.

To recap, grab the certificate chain from https://api.stripe.com (I saved mine as "api.stripe.com.cert"). This should be the Base64 encoded certificate chain. Then on the command line, run:

openssl pkcs12 -export -in api.stripe.com.cert -out ewallet.p12 -nokey

Once that is done, place the file in a suitable location. I placed mine to $ORACLE_HOME/wallets/stripe/. Now, if all worked well, we should be able to do a request over HTTPS. Just this time, with setting the wallet.

begin
    utl_http.set_Wallet(
        path => 'file:/u01/app/oracle/product/11.2.0/xe/wallets/stripe'
      , password => '[password for the wallet]'
    );
    output_html('https://api.stripe.com');
end;
/

{
  "error": {
    "type": "invalid_request_error",
    "message": "You did not
provide an API key. You need to provide your API key in the Authorization
header, using Bearer auth (e.g. 'Authorization: Bearer YOUR_SECRET_KEY'). See
https://stripe.com/docs/api#authentication for details, or we can help at
https://support.stripe.com/."
  }
}

I mentioned earlier about switching the ACL to the APEX schema. We could just as well started these tests with APEX_WEB_SERVICE and APEX schema on the ACL, but it's just the path I tend to take when testing HTTP requests out for the first time.

declare
    l_resp CLOB;
begin
    l_resp := apex_web_service.make_rest_request(
        p_url => 'https://api.stripe.com'
      , p_http_method => 'GET'
      , p_wallet_path => 'file:/u01/app/oracle/product/11.2.0/xe/wallets/stripe'
      , p_wallet_pwd => '[password for the wallet]'    
    );

    dbms_output.put_line(l_resp);
end;
/

Now that we have that all in place, we can flesh out a bit more of a PL/SQL API. First, reviewing the authentication documentation, you will see the BASIC authentication is supported where the username will be your secret token and the password left blank (NULL).

Following on from the previous post, the components you would most likely want to include in your request to complete the charge:

• amount
• currency
• source
• description (also sent to the user if email receipts are enabled)

With that then, we come up with a initial package:

create or replace package STRIPE_API
as

    procedure set_secret(
        p_secret in varchar2
    );

    procedure charge_card (
        p_amount in NUMBER
      , p_currency in varchar2 default 'aud'
      , p_source in varchar2
      , p_description in varchar2
    );

end STRIPE_API;

/

The basic implementation of charge_card looks like:

procedure charge_card (
    p_amount in NUMBER
  , p_currency in varchar2 default 'aud'
  , p_source in varchar2
  , p_description in varchar2
) 
as

    l_charge_resp CLOB;

begin
    
    l_charge_resp := 
        apex_web_service.make_rest_request(
            p_url => 'https://api.stripe.com/v1/charges'
          , p_http_method => 'POST'  
          , p_wallet_path => 'file:/u01/app/oracle/product/11.2.0/xe/wallets/stripe'
          , p_wallet_pwd => '[password for the wallet]' 
          , p_username => g_secret_token
          , p_parm_name => apex_util.string_to_table('amount:currency:source:description')
          , p_parm_value => apex_util.string_to_table(p_amount ||':'||p_currency||':'||p_source||':'||p_description)
        );
    
    dbms_output.put_line(l_charge_resp);    
    
end charge_card;
/

I've chucked this code at: https://github.com/tschf/pafs, so refer to that for the most up to date implementation.

Note: the above is just doing a dbms_output.put_line so we can see the response object whilst developing the API. Down the track you will likely want to modify this to return some information from the response object, to give the user meaningful feedback that it succeeded/failed.

So, then we can create a charge with the following calls:

begin

    stripe_api.set_secret('[secret removed]');
    
    stripe_api.charge_card(
        p_amount => 300
      , p_source => '[token_removed]'
      , p_description => 'Test the API'
    );

end;

Now, if we look in our dashboard in Stripe, under Payments, will we see that the charge has succeeded.

Stay tuned for the 3rd and final part - Hooking this API up to APEX.