Friday, June 8, 2012

running Credit Card Charges for Rdbhost Applications.


More and more types of services that once were exclusively 'server-side' on your web-server, are now independently hosted web services, accessible via a web API.

One such task is handling credit card charges; Stripe.com handles credit card processing through a web API.  Rdbhost now supports handling charges for your hosted web application via Stripe.com.  This is the second web-service we support, after email.

This service works similarly to the email mode, in that a query pulls the input data for the charge, including CC Num, CCV, expiration, etc, formulates a Stripe request from it, submits it, retrieves the result from Stripe, calls a database function to store the result, and then returns a summary status record to the client, via the selected data format.  Any or all of the results of the initial query can be concealed from the client, for customer privacy and security reasons.

CC Charging uses a new mode, called 'charge'.  Each record returned by the submitted query must contain a certain set of fields, to include 'ccnum', 'amt', 'apikey', 'service' and 'currency'. One charge is run for each such record.

Since the actual charging is done by a third party email web-service, you need to set up an account on stripe.com; they will provide you the API key.

The result page is formatted just as the result page for email mode, with either 'success' or an error message for each record processed.


jQuery Rdbhost Plugin

The jQuery plugin does not have any specific methods for charging, but the $.postData method will suffice, and the example linked below shows how to use it that way.  This example only relies on database storage for the api key, as that needs protecting from the end user.

Example Payment Page
Rdbhost Documentation on Charge Feature


Saturday, June 2, 2012

and Now Email, too

Toward our goal of making Rdbhost.com a universal backend, suitable for supporting a wide range of websites with no custom server-side coding, we have taken a large step in adding emailing capability.

Now, clients can send email through a query request, without Rdbhost disclosing to the client who the recipients are. This can be useful in implementing password recovery systems and initial password emails, for example.

The implementation is in the form of a new mode, called 'email'. The Rdbhost request protocol has a new parameter called 'mode'.  The default mode is to just send the query results to the client.  For other modes, there will be some intermediate step that takes the query results, does something, and returns another set of results.  The first such mode to be implemented is email.  The query results are input to the emailer, and the success/failure status of each email attempt is returned as the result to the client.

Each record returned by the submitted query must contain a certain set of fields, to include 'To:', 'From:', 'apikey', 'service' and 'body'. One email is sent for each such record.

The actual emailing is done by a third party email webservice of your choice. So far, we support postmarkapp.com and emailyak.com. The 'service' field indicates which service you are using, and 'apikey' must contain the corresponding API key. Before using email on Rdbhost, you must set up an account on one of those services; they will provide you the API key.

A very simple 'proof of function' email query might look like:
SELECT 'me@example.com' AS "From:",
       'you@example2.com' AS "To:",
       'abcd12345xyz' AS "apikey", 
       'yak' AS "service",
       'test message' AS "Subject:",
       1 AS "Idx",
       'Test from Rdbhost.\nHey there' AS "body";
This will send one email to 'you@example2.com'.

The preceding example revealed the API key as well as the to: address. If you were to use this query verbatim, you would want to use keyword lookup and restrict it to white-listed roles (Preauth or Auth), to prevent the API Key from being disclosed. You would also need to deny SELECT priv to the reader role to the lookup table, to keep the query from being read.

This is a bit of trouble, and constrains other uses of the lookup table. It would be easier to just move the private elements out of the query statement into tables, and restrict access to those tables.

A more typical query might look like:
SELECT cust.email AS "To:",
      'me@example.com' AS "From:",
      'news this week' AS "Subject:",
      api.apikey AS "apikey",
      'yak' AS "service",
      tpl.body AS "body",
      cust.id AS "Idx"
 FROM customers AS cust, 
      email_service_info AS api, 
      email_templates AS tpl
WHERE email_templates.type = 'weeklynews';
This would send the contents of email_templates.body to each customer in the customers table.

After the emailng is complete, a page body is sent to the client indicating the apparent success or failure of the emailing. The page is in json or xml format, and conveys a list of email send events, each entry including a unique key and 'Success' or an error code as returned by their API. The preferred page format is specified as the format parameter..

The resulting page, in json, would resemble:
{
    "records": {
        "header": [
            [
                23, 
                "idx"
            ], 
            [
                705, 
                "result"
            ]
        ], 
        "rows": [
            [
                2, 
                "Success"
            ]
        ]
    }, 
    "row_count": [
        1, 
        "1 Rows Affected"
    ], 
    "status": [
        "complete", 
        "OK"
    ]
}

Be aware that this only reports immediate errors from the API. If the recipient address is bad, that will not be known until later, and you would need to use the email service's own reporting pages to evaluate that.

jQuery Rdbhost Plugin

The jQuery plugin does not have any specific methods for emailing, but the $.postData method will suffice, and the example linked below shows how to use it that way.  This example only relies on database storage for the api key, as that needs protecting from the end user.  Your applications may well retrieve From or To addresses from a database as well.  The example application cannot be used to send spam to arbitrary addresses, as the recipient is hard-coded into the query, and the query itself is white-listed (variants will not pass the white-list).

Example Emailing Page
Rdbhost Documentation on Emailing Feature