September 18, 2019
Does your application send out emails? Lots of emails?
Have you ever felt like someone punched you in the stomach when you realize that you mistakenly sent out hundreds or thousands of emails to your application users?
I have. It’s a terrible feeling. And these days, in the age of the EU’s General Data Protection Regulation (GDPR), there can be real consequences for invading the privacy of your users. This article explores how to make sure that, at least when you are developing and testing your code, you do not inadvertently spam your users.
I lead a team that develops and supports the Oracle Dev Gym, which offers classes, workouts, and quizzes on a variety of Oracle technologies. We send out lots of different kinds of emails to those players who have opted in for them, such as:
The Dev Gym is an Oracle Application Express (Oracle APEX) app, so we are able to happily and easily take advantage of the
APEX_MAIL package and its
SEND procedure to do all the heavy lifting when it comes to emails. Under the covers, it uses
UTL_SMTP to get its heavy lifting done.
Emails are, by default, placed in a queue, and a job named
ORACLE_APEX_MAIL_QUEUE periodically sends all mail messages stored in the active mail queue.
Figure 1 shows a diagram of what our email workflow could look like:
Figure 1: Possible email workflow
In other words, wherever and whenever we need to send an email, whether it is from the quiz manager package, the class manager package, or the site admin utilities package, we could simply call
As I say, we could take this approach. But that would be a really bad idea.
Here’s why: With multiple accesses to the “core” procedure that sends the email, 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 registered users, and of those, 30,000 have opted in for emails. We have development, test/stage, and production environments at the Dev Gym.
We set the following rule for code when it comes to emails:
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.
APEX_MAIL.SEND is called from dozens of locations in our code, we need to go to each of those locations and apply our rule. Therein lies madness and the inevitable mistakes that result in emails that spam recipients.
So, instead, we follow this guideline when writing email-related code:
Well, OK, not exactly never. No, just once. The only place we will call the
APEX_MAIL.SEND procedure is from our very own
send_email procedure, defined in the
send_email procedure, in turn, calls
APEX_MAIL.SEND. With this approach, the flow of email requests then looks like Figure 2.
Figure 2: Email workflow with send_email
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
Here’ s a greatly simplified version of the
PROCEDURE send_email ( 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 dg_users.email_address%TYPE; BEGIN SELECT email_address INTO l_email_address FROM dg_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 dg_users.email_address%TYPE := user_email_address (user_id_in); BEGIN RETURN CASE WHEN dg_util_pkg.in_production THEN c_email_address WHEN is_developer (user_id_in) THEN c_email_address ELSE dg_util_pkg.admin_email_address () END; END; BEGIN l_email_id := apex_mail.send ( p_to => send_to_email_address (to_user_id_in), p_from => 'email@example.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;
Here are the highlights:
We create a nested function to return the email address to which the email will be sent. Inside that function, we have all the logic to implement the requirement.
dg_util_pkg package contains a function that returns
TRUE if we are currently running this code in production.
Since the only call to
apex_mail.send occurs within the
send_email procedure, we are now 100% protected from accidentally sending out an email to users when that was not intended or desired.
We don’t always push the email out of the queue. For example, when we are doing a batch email, we want to wait till everything is put into the queue, and then push it. If we are notifying a user of a specific event or accomplishment, we might want to push the email immediately.
So: nothing terribly fancy, no rocket science. Just another demonstration of how encapsulating low-level functionality to control access to that functionality provides flexibility and control that is otherwise very difficult to achieve.
With the encapsulation layer in place, it becomes really easy to add more functionality to the email management features. For example, at the Dev Gym, we recently decided to implement our own email queue. We realized we needed this when there was a failure to send emails at one point and we had no easy way to recover.
We wanted, instead, to keep track of all emails we (tried to) send through
APEX_MAIL.SEND, along with the actual HTML content of these emails. We could then resend the emails if there was a problem, verify that emails were produced and sent as expected, and confirm email content and formatting.
First we created a table to hold all the relevant information.
CREATE TABLE dg_emails ( email_id NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL, email_sent VARCHAR2 (1 BYTE) DEFAULT 'N', to_address VARCHAR2 (500 BYTE), from_address VARCHAR2 (500 BYTE), subject VARCHAR2 (1000 BYTE), body CLOB, body_html CLOB, cc VARCHAR2 (1000 BYTE), bcc VARCHAR2 (1000 BYTE), attachment BLOB, filename VARCHAR2 (1000 BYTE), mime_type VARCHAR2 (1000 BYTE), created_by VARCHAR2 (132 BYTE) NOT NULL ENABLE, changed_by VARCHAR2 (132 BYTE) NOT NULL ENABLE, created_on DATE NOT NULL ENABLE, changed_on DATE NOT NULL ENABLE, email_run_id NUMBER (*, 0) );
Then we added a procedure to load each email into our queue.
PROCEDURE write_to_queue (email_sent VARCHAR2, to_address VARCHAR2, from_address VARCHAR2, subject VARCHAR2, body CLOB, body_html CLOB, cc VARCHAR2, bcc VARCHAR2, attachment BLOB, filename VARCHAR2, mime_type VARCHAR2, email_run_id_in IN INTEGER DEFAULT NULL, email_id_out OUT INTEGER) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO dg_emails (email_sent, to_address, from_address, subject, body, body_html, cc, bcc, attachment, filename, mime_type, email_run_id) VALUES ('N', write_to_queue.to_address, write_to_queue.from_address, write_to_queue.subject, write_to_queue.body, write_to_queue.body_html, write_to_queue.cc, write_to_queue.bcc, write_to_queue.attachment, write_to_queue.filename, write_to_queue.mime_type, write_to_queue.email_run_id_in) RETURNING email_id INTO email_id_out; COMMIT; END;
Note that it is an autonomous transaction. We do that so even if something went wrong after this procedure completed, we would still have a record of the attempted email.
Of course, anytime you have a queue, you have to pay attention to its size and purge it now and then, so we added this procedure to the
PROCEDURE purge_queue (n_days_in IN INTEGER DEFAULT 7) IS BEGIN DELETE FROM dg_emails WHERE created_on < SYSDATE - n_days_; END;
If we need to retry sending the email, we now have an overloading of the original
send_email procedure. It queries the email details from the queue and pushes email out, using
PROCEDURE send_email (email_id_in IN INTEGER, push_to_queue_in IN BOOLEAN DEFAULT FALSE) IS l_apex_id INTEGER; l_email dg_emails%ROWTYPE; BEGIN SELECT * INTO l_email FROM dg_emails WHERE email_id = email_id_in; l_apex_id := apex_mail.send (p_to => send_to_email_address (l_email.to_address), p_from => l_email.from_address, p_subj => l_email.subject, p_body => l_email.body, p_body_html => l_email.body_html, p_cc => l_email.cc, p_bcc => l_email.bcc); IF push_to_queue_in THEN apex_mail.push_queue; END IF; mark_email_sent (email_id_in); END send_mail;
A sharp reader will now be thinking, “Wait a minute! You just violated your encapsulation rule. Right there in your
send_email overloading, you have a second call to
Well, you know about rules. They are made to be broken. It is true; we call
APEX_MAIL.SEND in both
send_email procedures. I offer these observations (you can call them excuses if you’ d like):
Both invocations are in the
dg_comm_mgr package. This makes it easier to keep things from getting out of control.
It is a very special-purpose procedure, distinct from the other
send_email procedure; in a way, it is “lower-level.”
No doubt about it—we could refactor the original
send_email procedure to incorporate the logic of the queue-driven send process, but we would end up with code that is harder to read and maintain.
So we break a rule to follow other important software quality guidelines.
We do, however, need to go back to the original
send_email procedure and make sure that the email information is put into the queue before the attempt is made to send it out into the world.
PROCEDURE send_email ( 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 => 'firstname.lastname@example.org', 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 => 'email@example.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;
All it takes is one careless enhancement or bug fix, and you’ve suddenly got company lawyers and GDPR experts knocking on your door. You don’t want to be that programmer.
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 in the Oracle Dev Gym.
Think also about other aspects of your application in which you have multiple pathways to the same or similar functionality, especially when involving invocations of procedures and functions provided by Oracle.
Apply the same techniques shown in this article proactively, and you are likely to save yourself from some nightmarish episodes in the future.
WORK OUT at the Oracle Dev Gym.
Illustration by Wes Rowell