Email MySql Alerts Using AWS SES - Part 1

In this instructable, we will show you how to use Amazon Simple Email Service to send emails when new records have been added to a MySQL database

Amazon Simple Email Service enables you to send and receive email using a reliable and scalable email platform.

Supplies

• Your own domain with DSN edit capabilities
• Your own web facing Linux Server
• An AWS account
• Some knowledge of Bash / Linux and MYSQL

Step 1: Configuring AWS SES

First log into AWS and follow the steps in the image above.

Next, create your SMTP Credentials

Step 2: Encrypt Your SMTP Credentials and Create a Configuration File

You will need to log into your server using a terminal access client such as Putty,

echo -n "<your_smtp_username>" | openssl enc -base64
displays YOUR_ENCRYPTED_USERNAME

echo -n "<your_smtp_password>" | openssl enc -base64

YOUR_ENCRYPTED_PASSWORD

Next , create a file using nano ,

nano youtube_input.cfg

replace the fields as shown in the image with your details

youtube_input.cfg

EHLO YOURDOMAIN.com

AUTH LOGIN

YOUR_ENCRYPTED_USERNAME

YOUR_ENCRYPTED_PASSWORD

MAIL FROM: [e-mail]

RCPT TO: [e-mail]

DATA

From: Youtube Video

To: [e-mail]

Subject: Monitoring Comments

xxxxxcommentszxxxx new comments have been added from the CMP web form

.

QUIT

Step 3: Send a Test Email

On the CLI, type the following

emailcfg=$(cat ~/youtube_input.cfg)

This assigns the contents of your configuration file to the variable $emailcfg

then

openssl s_client -crlf -quiet -starttls smtp -connect email-smtp.eu-west-1.amazonaws.com:587 <<< $emailcfg 2>&1

You will need to change the AWS zone email-smtp.eu-west-1.amazonaws.com:587 to that which is in your SES SMTP settings

Step 4: Create a Bash Script

Create the following tables in MySQL.These are used to store comments from a web form. 
Please customise,  

MariaDB [projects]> describe comments
    -> ;
+-------------+--------------+------+-----+-------------------+-----------------------------+
| Field       | Type         | Null | Key | Default           | Extra                       |
+-------------+--------------+------+-----+-------------------+-----------------------------+
| id          | int(10)      | NO   | PRI | NULL              | auto_increment              |
| description | varchar(500) | YES  |     | NULL              |                             |
| owner       | varchar(30)  | NO   |     | NULL              |                             |
| reg_date    | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| status      | varchar(10)  | YES  |     | OPEN              |                             |
| duedate     | datetime     | YES  |     | NULL              |                             |
| purpose     | varchar(500) | YES  |     | NULL              |                             |
| budget      | int(11)      | YES  |     | NULL              |                             |
| url         | varchar(200) | YES  |     | NULL              |                             |
| tags        | varchar(200) | YES  |     | NULL              |                             |
| email       | varchar(30)  | YES  |     | NULL              |                             |
| comments    | varchar(500) | YES  |     | NULL              |                             |
| email_enc   | blob         | YES  |     | NULL              |                             |
+-------------+--------------+------+-----+-------------------+-----------------------------+
MariaDB [projects]> describe comments_monitor;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| recent_id | int(11)      | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
create one record in comments_monitor
insert into comments_monitor ('recent_id') values (0);

Create a bash script as follows: nano monitor_comments_mysql_ses.sh

test=$(mysql -u <mysqlusername> -p<mysqlpassword> -D projects -e "select max(id) from comments;")
#do not use mysql root user credentials
#strip out text and spaces
test="${test//max(id)/}"
test="${test// /}"
#strip out newline
test=$(echo ${test} | tr -d '\n')


recent_id=$(mysql -u <mysqlusername> -p<mysqlpassword> -D projects -e "select recent_id from comments_monitor where id = 1;")
recent_id="${recent_id//recent_id/}"
recent_id="${recent_id// /}"
recent_id=$(echo ${recent_id} | tr -d '\n')

if [ "$test" -gt  "$recent_id" ]
then
        number_of_new_comments=$(echo "$(($test-$recent_id))")
        emailcfg=$(cat <dir>/<ses_config_file>)
        emailcfg="${emailcfg//xxxxxcommentszxxxx/$number_of_new_comments}"
        openssl s_client -crlf -quiet -starttls smtp -connect email-smtp.eu-west-1.amazonaws.com:587 <<< $emailcfg 2>&1
        mysql -u <mysqlusername> -p<mysqlpassword> -D projects -e "update comments_monitor set recent_id = ${test} where id = 1;"

fi

Step 5: Add to Crontab

On the command line interface, CLI, type crontab -e then add the following

SHELL=/bin/bash

20,40 * * * * cd ~/;./monitor_comments_mysql_ses.sh >/dev/null 2>&1

This assumes that monitor_comments_mysql_ses.sh was created in the home directory.

Some security considerations include

• Not storing SES,MYSQL credentials etc on insecure folders (web etc)
• Use tight MYSQL user accounts with restricted access, not root.
• Using HTTPS
• Web code is OWASP compliant etc.