Friday, 26 February 2016

Accepting payments with Stripe in APEX, Part 3

Hooking everything up in APEX

In part 1 of the series, we set up a form that would accept payment information. In part 2 we set up a PL/SQL API. Now, the final step is that we want to actually charge the card after the user submits the payment information, which is relatively straight forward.

First, we can set up a couple of substitution strings for our stripe secret and the wallet information




Next, add a new (hidden) item to the page that we can set the token into it (I called mine "P1_PMT_TOKEN"). Once that is on the page, we can modify our existing dynamic action (from part 1), so the code now looks like:

var handler = StripeCheckout.configure({
    key: 'pk_test_oXgwgmJbBmhzODwHxoKE8zAz',
    locale: 'auto',
    token: function(token) {
        $s('P1_PMT_TOKEN', token.id);
    }
});

handler.open({
    name: 'Demo Site',
    description: '2 widgets',
    currency: "aud",
    amount: 2000
});

Now, after they click the Pay button, we'll want to trigger the charge - where we need to call some PL/SQL. This can be done either from a page submission, or more than likely you will want to trigger this from a dynamic action. This is done easily with a change action on your P1_PMT_TOKEN page item.

begin

    stripe_api.set_secret(:STRIPE_TOKEN);
    
    stripe_api.charge_card (
        p_amount => 2000
      , p_source => :P1_PMT_TOKEN
      , p_description => 'Charged from a D.A'
      , p_wallet_path => :WALLET_PATH
      , p_wallet_password => :WALLET_PASSWORD
    );

end;

Now, if we submit a payment and look in our dashboard we will see the payment appear.



Finally, you may like to parse the response to return some more valuable information. Such as, the charge response includes a status field which according to the documentation can either be "succeeded" or "failed". APEX comes with APEX_JSON to work with JSON objects since version 5, so we can easily modify our API to return this (the response could also be an error object, which does NOT return a status field).

To return the status from our charge API call, we convert the procedure to a function so that it now looks like this

function charge_card (
    p_amount in NUMBER
  , p_currency in varchar2 default 'aud'
  , p_source in varchar2
  , p_description in varchar2
  , p_wallet_path in varchar2
  , p_wallet_password in varchar2
) 
return 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 => p_wallet_path
          , p_wallet_pwd => p_wallet_password
          , 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)
        );
    
    apex_json.parse(l_charge_resp);
    
    return apex_json.get_varchar2(p_path => 'status');
    
end charge_card;

And updating our pay dynamic action to (with another page item to return the status into):

begin

    stripe_api.set_secret(:STRIPE_TOKEN);
    
    :P1_PMT_STATUS := 
        stripe_api.charge_card (
            p_amount => 2000
          , p_source => :P1_PMT_TOKEN
          , p_description => 'Charged from a D.A'
          , p_wallet_path => :WALLET_PATH
          , p_wallet_password => :WALLET_PASSWORD
        );

end;



Expanding from this going forward, you would more than likely want to return more than the status. You may want to also log the charges made locally (even though the info is all available from Stripe). This also needs better checking to cater for the situation of an error being returned instead of a Charge response.

Well, this series has just been one small component of what is possible with the Stripe API (I could be here all year if I set up examples of all components of the API). It makes it dead easy to integrate with - and if you are planning to offer same payment processing, I believe Stripe is a very good option!

Thursday, 25 February 2016

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.

Thursday, 11 February 2016

Accepting payments with Stripe in APEX, Part 1

I've just been investigating a payment gateway for an APEX application. I already knew about the recent startup Stripe, and their fees seemed just as good (if not better) than the other well-known contenders - I figured there would be a good place to start.

Now, I haven't actually worked with the others, but so far, I'm very impressed with the system/API available. From my little bit of research, all the card information will be stored on Stripe's servers, so we need not deal with storing and encrypting customers card information. With that, we are left with this general workflow:

  1. Include a form for customers to enter there card information
  2. The form gets submitted to Stripe's servers
  3. Returned is a token. We use this to then complete the charge

For this first part, I'll be focusing on the form. In particular, Stripe provides a form for us that we can re-use. Check out the documentation on the form here - https://stripe.com/docs/tutorials/checkout. Keep in mind, you can just as easily develop your own form if you have your own design in mind.

The basic example (for a form) they give on the docs is:

<form action="" method="POST">
  <script
    src="https://checkout.stripe.com/checkout.js" class="stripe-button"
    data-key="[redacted]"
    data-amount="2000"
    data-name="Demo Site"
    data-description="2 widgets"
    data-image=""
    data-locale="auto">
  </script>
</form>

Side note: In your account, you can retrieve your API tokens (and test tokens) from: https://dashboard.stripe.com/account/apikeys

So, if you add that to a static region on your page, you will see a nice checkout button and form:




What would then happen after submitting the payment information, is the form would get submitted, with some additional parameters being sent in the POST request. Being on APEX, this won't do since the whole page is wrapped around a form - resulting in this being an embedded form (unsupported).

Looking at the documentation some more, they have a more advanced example giving us more control over what happens once the Pay button is selected.

We still need to leave a reference to checkout.js on our page, so for now this can live in our region where the pay button will live. The next part will be to add a regular APEX button to our region - with the action being Defined by a Dynamic Action.

Then, we need a dynamic action for when the button is clicked - with the true action being to execute a block of JavaScript code (the handler could be set up on page load, but just chucked it in one spot for simplicity)

var handler = StripeCheckout.configure({
    key: '[redacted]',
    locale: 'auto',
    token: function(token) {
        console.log(token);
    }
});

handler.open({
    name: 'Demo Site',
    description: '2 widgets',
    currency: "aud",
    amount: 2000
});


So, now with our advanced implementation, we are left with much the same form:




(This is just test card information supplied with by Stripe - see: https://stripe.com/docs/testing)

So, when the user submits the payment information, the token function will be called. With that we are returned with the following information:


Further, if we look in our dashboard on Stripe for the account, we should see this activity logged:



So, with that information, you would then want to initiate a request to a PL/SQL process to make the actual charge to the customers card. A post for another day.