Email MySql Alerts Using AWS SES - Part 1
935 Views, 1 Favorites, 0 Comments
Email MySql Alerts Using AWS SES - Part 1
![aws_ses_mysql.png](/proxy/?url=https://content.instructables.com/FUC/E1DR/KM3GC7JJ/FUCE1DRKM3GC7JJ.png&filename=aws_ses_mysql.png)
![How to use AWS SES to track user comments on your web site](/proxy/?url=https://content.instructables.com/F0N/74AG/KM3GC9F6/F0N74AGKM3GC9F6.jpg&filename=How to use AWS SES to track user comments on your web site)
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
Configuring AWS SES
![aws_ses_mysql3.png](/proxy/?url=https://content.instructables.com/FVH/O4VA/KM3GC7NS/FVHO4VAKM3GC7NS.png&filename=aws_ses_mysql3.png)
First log into AWS and follow the steps in the image above.
Next, create your SMTP Credentials
Encrypt Your SMTP Credentials and Create a Configuration File
![aws_ses_mysql4.png](/proxy/?url=https://content.instructables.com/FFW/DM59/KM3GC7PT/FFWDM59KM3GC7PT.png&filename=aws_ses_mysql4.png)
![aws_ses_mysql4.png](/proxy/?url=https://content.instructables.com/FVC/XZLS/KM3GC9V0/FVCXZLSKM3GC9V0.png&filename=aws_ses_mysql4.png)
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: info@YOURDOMAIN.com
RCPT TO: info@YOURDOMAIN.com
DATA
From: Youtube Video
To: info@YOURDOMAIN.com
Subject: Monitoring Comments
xxxxxcommentszxxxx new comments have been added from the CMP web form
.
QUIT
Send a Test Email
![aws_ses_mysql5.png](/proxy/?url=https://content.instructables.com/FXA/6U82/KM3GC81W/FXA6U82KM3GC81W.png&filename=aws_ses_mysql5.png)
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
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
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.