How to Avoid Spamming Users From Your Applications

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.

Does your application send out emails? Lots of emails?

Do 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.

My application 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

My application 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, the class manager package, or the site admin utilities package, I simply call apex_mail.send directly.

Like I said, 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 the email-related code.

Consider the situation at our application. We currently have 72,000 users registered at our application, and of those, 30,000 have opted-in for emails. We have development, test/stage, and production environments for our application. 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 lie the 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;

• 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 am doing a batch email, I want to wait until 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).

Once you’ve gotten 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 placed 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;