SPSE IMAP and DB connection

As another of the exercises of the mocked exam, it is necessary to retrieve information from an e-mail account via IMAP.

Before going into the exercise itself several things are needed. First of all we need to install the database, I chose Mysql just because is the most known for me but you can use any other, it is up to you. 

After installing the database you will need the connector for python and if you are using linux (which is quite probable) you will need the -dev packages as well. Don't panic, step by step you will find how to install all the staff just googling.

After installing the DBMS is recommended as well to create a new database user and grant it the convenient permissions. I leave all this set up to you and I will concentrate this post in solving strictly the exercise itself.

First of all start Mysqld and connect as root in order to create the database, and grant permissions to our user previously created:

$ sudo /etc/init.d/mysql start

$ mysql -u root -p

Insert the password when prompted for it. We shouldn't start the database as root in a real environment but create another user with the minimum privileges and run the DBMS using that user. As it is just an example, please, skip that detail.

Now we need to create a new DB, lets call it "mail" (original, isn't? :)).

mysql> CREATE DATABASE mail;
Query OK, 1 row affected (0.00 sec)

And grant permission on the database to our user previously created user.

mysql> GRANT ALL PRIVILEGES ON mail.* TO [user];
Query OK, 0 rows affected (0.00 sec)

Now we exit from the database and enter again using our [user]

$ mike@mike-SATELLITE-S50-B:~$ mysql -u [user] -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

We select the database and create the table:


mysql> use mail
Database changed

mysql> CREATE TABLE emails (num_mail VARCHAR(10), mail_content MEDIUMTEXT);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_mail |
+----------------+
| emails         |
+----------------+
1 row in set (0.00 sec)

Note the table is created with two columns, the num_mail is intended to store an index of the e-mail and the mail_content which will keep the content of the e-mail.

With all the tasks before, we have our database ready. Now go ahead with the code. Don't forget our objective is retrieving the e-mail and store it into the database table we have just created. Therefore you need an e-mail account and enable the IMAP connection for that account. I chose Gmail but again this is up to you. I spent sometime looking for free disposable email with IMAP support without success... If you are more lucky, please, leave a comment!. I leave to you how to enable IMAP for the platform of your election.

Now check the code at the bottom of the next link:

https://docs.python.org/2/library/imaplib.html

With some little changes we come to the next piece of code:

#!/usr/bin/env python

import getpass, imaplib
import mysql.connector

DATABASE = 'mail'
USER = 'usernamehere'
PASS = 'passwordhere'

MAIL_SERVER = "imap.gmail.com"
MAIL_ACCOUNT = "mailhere@gmail.com"

# Connecting to the database and preparing the query
print "[i] connecting to the database:" + DATABASE + "...",
cnx = mysql.connector.connect(user=USER, password=PASS, host='127.0.0.1', database=DATABASE)
cursor = cnx.cursor()
print "[ OK ]"

print "[i] defining the query...",
new_entry = ("INSERT INTO emails (num_mail, mail_content) VALUES (%s, %s)")
print "[ OK ]"

print "[i] connecting to the mail server " + MAIL_SERVER + "...",
M = imaplib.IMAP4_SSL(MAIL_SERVER)
print "[ OK ]"

print "[i] logging in and selecting inbox...",
M.login(MAIL_ACCOUNT, getpass.getpass())
M.select()
print "[ OK ]"

print "[i] Searching e-mails...",
typ, data = M.search(None, 'ALL')
print "[ OK ]"

for num in data[0].split():
    print "[i] fetching e-mail " + str(num) + "...",
    typ, data = M.fetch(num, '(RFC822)')
    print "[ OK ]"
    print "[i] constructing data tuple and executing query...",   
    info = (num,data[0][1])
    cursor.execute(new_entry,info)
    print "[ OK ]"

print "[i] committing the changes made and closing database connection!...",
cnx.commit()
cursor.close()
cnx.close()
print "[ OK ]"

print "[i] closing IMAP connection!...",
M.close()
M.logout()
print "[ OK ]"

I have tried to develop this piece of code self-explaining through 'print' statements so, there shouldn't be too many questions.

The previous script opens both connections, the one to the database and the one to the IMAP Server. After that, it performs the login and fetches the inbox. The next step is seek for all the e-mails in the inbox. For each e-mail, it retrieves its content and execute the query that adds the e-mail index and the content to the database.

to inspect the content in the database just perform a select in the table emails.

mysql> SELECT * FROM emails;