Skip to main content

How to avoid spamming users from your applications

Does your application send out emails? Lots of emails?

Did you ever get that feeling like someone punched you in the stomach when you realize that you mistakenly sent out hundreds or thousands of emails to your users when you didn't mean to?

I have. It's a terrible feeling. And these days, in the age of GDPR, there can be real consequences for invading the privacy of your users. This post explores how to make sure that, at least when you are developing and testing your code, you do not inadvertently spam your users.

The Oracle Dev Gym sends out lots of different kinds of emails to those players who have opted-in for them, such as:
  • Results of the quiz you just completed
  • Confirmation of sign-up in a class
  • Reminder to take our weekly tournament quizzes
  • Hourly reports to site admins with any new errors in our log
  • Weekly activity summaries to quizmasters
The Dev Gym is an Oracle Application Express app, so we are able to happily and easily take advantage of the APEX_MAIL package, and its SEND procedure. Here's a diagram of what our email workflow could  look like:
In other words, wherever and whenever I need to send an email, whether it is from the quiz manager package or the class manager package or the site admin utilities package, I simply call apex_mail.send directly.

Like I say, I could take this approach. But that would be a really bad idea. Why?

With multiple accesses to the "core" send email procedure, it is difficult to put any kind of controls in place regarding email delivery, and also more challenging to debug and enhance email-related code.

Consider the situation at the Dev Gym. We currently have 72,000 users registered at the Dev Gym, and of those 30,000 have opted-in for emails. We have development, test/stage and production environments for the Dev Gym. Here's the rule we need to follow regarding emails:
When running in development or test, our users should never get an email. Specifically, unless the email "send to" is one of our developers, redirect all emails to the admin email address.
In other words, we still need to see the emails to verify the format and other behaviors, but those emails should never "escape" from the development team.

If APEX_MAIL.SEND is called from dozens of locations in my code, then I need to go to each of those locations and apply my rule. Therein lies madness and the inevitable mistake that results in emails spamming users.

So instead, we follow this rule when writing email-related code:
Never call APEX_MAIL.SEND directly. Instead call qdb_communication_mgr.send_email. 
Our own send_email procedure, in turn, calls APEX_MAIL.SEND. With this approach, the flow of email requests look like this:
By following this rule, we ensure that APEX_MAIL.SEND is called in just one place in our entire code base: inside our own wrapper, the qdb_communication_mgr.send_email procedure (the "qdb" prefix translates to "quiz database").

Here's a greatly simplified version of this procedure :
PROCEDURE send_mail (
   to_user_id_in      IN INTEGER,
   subject_in         IN VARCHAR2,
   html_in            IN CLOB,
   push_to_queue_in   IN BOOLEAN DEFAULT TRUE)
IS
   l_email_id   INTEGER;
   l_text       CLOB;

   /* Just a placeholder for this demonstration */ 
   FUNCTION is_developer (user_id_in IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN TRUE;
   END;

   FUNCTION user_email_address (user_id_in IN INTEGER)
      RETURN VARCHAR2
   IS
      l_email_address   qdb_users.email_address%TYPE;
   BEGIN
      SELECT email_address
        INTO l_email_address
        FROM qdb_users
       WHERE user_id = user_id_in;

      RETURN l_email_address;
   END;

   FUNCTION send_to_email_address (user_id_in IN INTEGER)
      RETURN VARCHAR2
   IS
      c_email_address   qdb_users.email_address%TYPE
                           := user_email_address (user_id_in);
   BEGIN
      RETURN CASE
                WHEN qdb_utilities.in_production THEN c_email_address
                WHEN is_developer (user_id_in) THEN c_email_address
                ELSE qdb_config.admin_email_address ()
             END;
   END;
BEGIN
   l_email_id :=
      apex_mail.send (p_to          => send_to_email_address (to_user_id_in),
                      p_from        => 'noreply@oracle.com',
                      p_subj        => subject_in,
                      p_body        => l_text,
                      p_body_html   => html_in,
                      p_cc          => NULL,
                      p_bcc         => NULL);

   IF push_to_queue_in
   THEN
      apex_mail.push_queue;
   END IF;
END send_mail;
Here are the highlights:
  • I create a nested function to return the email address to which the email will be sent. Inside that function, I have all the logic to implement my requirement.
  • The qdb_utilities package contains a function that returns TRUE if I am currently running this code in production.
  • Since the only call to apex_mail.send occurs within my send_mail procedure, I am now 100% protected from accidentally sending out an email to users when it was not intended.
  • I don't always push the email out of the queue. For example, when I doing a batch email, I want to wait till everything is put on the queue, and then push it. If I am notifying a user of a specific event or accomplishment, I might want to push immediately.
So: nothing terribly fancy, no rocket science. Just another demonstration of how encapsulating low-level functionality to control access to that functionality gives you flexibility and control that is otherwise very difficult to achieve.

This particular encapsulation is just another example of the fundamental rule of the SmartDB paradigm: create a "hard shell"  PL/SQL API around your data structures (and, in this case, infrastructure or plumbing). More info on SmartDB

Once you've got your encapsulation layer in place, it becomes really easy to add more functionality to your email management features. For example, we recently added our own email queue, so we can re-send an email if there was an error, and also verify that emails were produced and sent as expected.

Since we knew for a fact that APEX_MAIL.SEND is only called in one place, we could easily ensure that all email attempts were place in the queue as follows:
PROCEDURE send_mail (
   to_user_id_in      IN INTEGER,
   subject_in         IN VARCHAR2,
   html_in            IN CLOB,
   push_to_queue_in   IN BOOLEAN DEFAULT TRUE)
IS
...
BEGIN
   write_to_queue (
     email_sent     => 'N',
     to_address     => send_to_email_address (to_user_id_in),
     from_address   => 'noreply@oracle.com',
     subject        => subject_in,
     body           => l_text,
     body_html      => html_in,
     cc             => NULL,
     bcc            => NULL,
     email_id_out   => l_email_queue_id);

   l_email_id :=
      apex_mail.send (p_to          => send_to_email_address (to_user_id_in),
                      p_from        => 'noreply@oracle.com',
                      p_subj        => subject_in,
                      p_body        => l_text,
                      p_body_html   => html_in,
                      p_cc          => NULL,
                      p_bcc         => NULL);

   IF push_to_queue_in
   THEN
      apex_mail.push_queue;
   END IF;

   mark_email_sent (l_email_queue_id);
END send_mail;
If you have not already protected your users and yourself from the trauma of an accidental spamming,  I hope that you can learn from and use the approach we've taken.

Have you taken care of this problem in another way? Do you have suggestions for improving upon what we've done? Please let me know in the comments section.

Comments

  1. Added option: build in a layer to allow a transaction to be rolled back, so mail isn't sent before a failure.

    ReplyDelete
  2. Great article!

    I would add that we actually need a separate self-written-wrapper for any method which generates something external - whether it is an email, a HTTP/FTP-request or a SMS/instant message (a particular case of an HTTP call).

    By having such a wrapper, we have much more control `in between`, and we can do there whatever we want. We can log the events, group them, redirect, disable, filter (so-called `stop-list`) and so on.

    #SmartDB in action!

    ReplyDelete
    Replies
    1. I agree, Denis. I used to suggest to students in my trainings that they never call a built-in package subprogram directly, always use your own API on top of it. That may be a bit extreme, but in this context of "external content generation" - yes, great idea.

      Delete
  3. Excellent solution! I've set up something similar at my current workplace. The only suggestion I have for improvement is to create a separate schema for the e-mail procedure (and related objects such as tables) and make sure only that schema has execute privilege on APEX_MAIL.SEND (and other Oracle supplied e-mail packages) to ensure that it can't be used outside of the custom procedure.

    Also, as part of the process for refreshing non-production environments, you may want to consider removing all e-mail addresses or changing them to the test e-mail address for the environment. This adds an extra level of safety and has the added benefit of complying with any requirements for protecting confidentiality of user's personal data.

    ReplyDelete
    Replies
    1. Excellent ideas, thanks for adding the wisdom of your experience to this post.

      Delete
    2. Our API checks sys_context('userenv','db_name'), and in dev/test it redirects the email to a test address, annotating the top of the email with what *would* have happened.

      Nothing worse than sending test messages to the client ;p

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel