Emailing from an RdbHost Account

Emailing is not, strictly speaking, a critical website activity, but it is quite common, and web application builders need a straightforward way to safely send email from web applications.

Email is sometimes subject to abuse by spammers, and a feature to send email should be able to control key aspects of the sending, to avoid such abuse.

The RdbHost JavaScript emailing module fulfills those expectations, providing a flexible way to send email from a client app while avoiding misuse by anonymous third parties (ie: spammers). Like everything at RdbHost, sending an email involves an SQL query, and typically that query would be white-listed.

A third-party web service, Mailgun or Postmark, would be involved, using your account on that service. Your API key for that service is stored in a private table in your account, pulled by the SQL query with all other details necessary for the email, and sent to the web-service by our proxy, and the response from that returned to the user. The API key is never revealed to the user.

Four Examples

All examples use this content, after including the source.

Rdbhost.config_email('you@example.com', 'mailgun');
var f = Rdbhost.Email.fixed_wrapper,
    c = Rdbhost.Email.column_wrapper;

.

Payment Reminder to Overdue Accounts

var p = Rdbhost.Email.preauth()
        .query('SELECT o.idx, o.name, o.email,' +
               '  replace(replace(t.msg, \'{name}\', name),' +
               '         \'{delay}\', o.delaydays) ' +
               '  FROM overdue_accounts o, templates t ' + 
               ' WHERE delaydays > %s;')
         .params([60 /* days */])
         .email(c('name'), c('email'), f('Supplier'), 
                f('supplier@yourdomain.com'), c('msg'), 
                'Overdue Payment Reminder')

The .email() function creates a new bigger query with the provided query as a subquery.

The c() function includes that content as a column name, instead of as a literal, and the value is pulled from the subquery column by that name. The parameter c('name') means that the name field from the subquery provides the name in the email.

The f() function 'fixes' the value into the query text, so the value (escaped) becomes part of the white-list entry.
The above request would pull one record for each overdue account and submit each record to the emailing webservice; this webservice's response is returned to the user, one record per record. The returned records include idx and result, where the idx is copied from the source data, and result is either 'SUCCESS' or error value.

The query must have an idx column. The type can be anything that coerces to VARCHAR.

p.then(function(d) {
        var sucNum = _.count(d.result_sets[0].rows,
             function(r) {
                  r.result === 'SUCCESS';
             }),
        var failNum = d.result_sets[0].rows.length - sucNum;
        window.console.log(
           '{0} emails sent. {1} emails failed.'
           .replace('{0}', sucNum).replace('{1}', failNum));
    })
    .catch(function(e) {
        window.console.log('error in query '+e.message);
    });

The above code would receive the data or error produced by the call. This code would be relevant, though minimal, to each of the examples here.

.

Email a Specific User

var p = Rdbhost.Email.preauth()
         .email(sender_name, f('you@yourdomain.com'), 
                f('Supplier'), f('supplier@yourdomain.com'), 
                message, subject);

The webservices require that the sender email to be an address registered to the account, so we use the sender's name (entered by user) with your registered email. The recipient must be 'Supplier supplier@yourdomain.com', as that value is white-listed in the query.

.

Total Folly

var p = Rdbhost.Email.preauth()
         .email(sender_name, f('you@yourdomain.com'), 
                recip_name, recip_email, 
                message, subject, attachments);

This query allows anybody to send any email to any recipient, with any subject line, and any attachments. Basically an invitation to spammers, so don't.

.

Safer

var p = Rdbhost.Email.preauth()
         .query('SELECT 1 AS idx FROM pw_list ' +
                '  WHERE password = %s')
         .params([passwd])
         .email(sender_name, f('you@yourdomain.com'), 
                recip_name, recip_email, 
                message, subject, attachments);

This is like the above, but requires the user to provide a password, checked against a pw_list table. The password match allows the query to return a record, and an email to be sent. Without a password match, the query returns nothing, and no email is sent. The (sub)query provides no fields to the email, but just enables the email.

Like all1 RdbHost modules, the JavaScript module includes all code to implement the functionality. Just include the module in the page, call the relevant functions, and test; The module will create server-side tables and other resources as necessary to make the emailing work.

Links:
www.rdbhost.com
demo page

  1. ok, not quite all, but we will get there.

comments powered by Disqus