#!/usr/bin/perl #-------------------------------------------------- # Script to check mailbox for mail and insert the # messages as HTML into a MySQL database # blazonry.com # Created On: Thu Dec 28, 2000 10:35:03 # Last Updated: Wed Jan 03, 2001 12:44:30 #-------------------------------------------------- # This script was written to run on the same # machine that receives the mail. # If you want to check remote mail check out # the POP3Client module on CPAN in the Mail category #-------------------------------------------------- # I schedule this script to run every five minutes # using the following crontab entry: # */5 * * * * perl mail2mysql # #-------------------------------------------------- # My MySQL database was created using # the following SQL statement: # # CREATE TABLE techpage ( # id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # sender VARCHAR(128), # subject VARCHAR(128), # description TEXT, # ts_date TIMESTAMP, # PRIMARY KEY(id) # ); #-------------------------------------------------- # NOTE: # Requires mhonarc to be installed for parsing # of the actual mail mesages. I use mhonarc to # handle the multipart HTML messages and to convert # plain messages to HTML. # # mhonarc home page: http://www.mhonarc.org/ # This script tested and run with mhonarc v 2.4.7 #-------------------------------------------------- #OTHER REQUIREMENTS: use Mail::Util qw(read_mbox); use DBI; #-- CONFIGURE to match your database settings my $dsn = "DBI:mysql:tech"; my $user = "--username--"; my $pass = "--password--"; #-- CONFIGURE to match ypur mailbox my $mailfile = "/var/spool/mail/tech"; my $tmpfile = "/tmp/mk_tmp001.msg"; # mhonarc settings my $mhcmd = "/usr/bin/mhonarc"; my $mhopt = "-quiet -single"; # check for file if (-f $mailfile) { $db = DBI->connect($dsn, $user, $pass); # parse all messages into array @msgs = read_mbox($mailfile); #process each message individually foreach $msg (@msgs) { my $htmlmsg = ""; my $from=""; my $subject=""; my $date=""; # write out temp file open (OUT, ">$tmpfile"); print OUT @$msg; close(OUT); # use mhonarc to convert to html $htmlmsg = `$mhcmd $mhopt $tmpfile`; # PREPARE FOR INSERT INTO DATABASE #-- Clean up mhonarc stuff #-- remove header stuff... mhonarc wants to spit out #-- a full HTML page... I don't want that $htmlmsg =~ s/(.*?)//s; $htmlmsg =~ s/(.*?)//s; $htmlmsg =~ s/(.*?)//s; $htmlmsg =~ s///; #-- parse Header for from, subject and date if ($htmlmsg =~ m/(.*?)/s) { my $header = $1; if ($header =~ m/From<\/em>:(.*)/) { $from = $1; } if ($header =~ m/Subject<\/em>:(.*)/) { $subject = $1; } if ($header =~ m/Date<\/em>:(.*)/) { $date = $1; } $htmlmsg =~ s/(.*?)//s; } # remove all HTML comments $htmlmsg =~ s///g; # - double up apostrophes $htmlmsg =~ s/'/''/g; # INSERT INTO DATABASE $sql = " INSERT INTO techpage "; $sql = $sql . " (sender, subject, description) VALUES "; $sql = $sql . " ('$from','$subject','$htmlmsg') "; $db -> do($sql); } $db->disconnect; #-- THIS IS COMMENTTED OUT FOR YOUR PROTECTION #-- The mailbox file should be deleted after reading #-- it so the same message doesn't keep getting inserted #-- into the database. I comment it out so during testing #-- the mail is not lost # remove mail file #unlink($mailfile); }