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