Simple Email Logger Using cPanel's Pipe Forwarder

Mar 31, 2009

I needed a way to easily log all email being sent from my websites so I devised this solution where I just have to bcc the emails to a single email address and they get placed in a database.

First, add a forwarder in cPanel. Enter a super secret address to forward. I recommend a random string of 20-30 chars. Just make it something someone can't guess, otherwise your database could be filled with spam. Then select Pipe to a Program and enter the location of where you will put your script. I recommend placing it outside of the web root (public_html in my case). See the screenshot for information on how to enter it. The path to my file is:

/home/my_username/cron/email.php

Now here is the php script I use. Enter your database information at the top, and save it to where you indicated when creating your forwarder.

#!/usr/bin/php -q
<?php
// Config
$dbuser = '';
$dbpass = '';
$dbname = '';
$dbhost = 'localhost';
$notify= ''; // an email address required in case of errors

// read from stdin
$fd = fopen("php://stdin", "r");
$email = "";
while (!feof($fd)) {
    $email .= fread($fd, 1024);
}
fclose($fd);
// handle email
$lines = explode("\n", $email);

// empty vars
$from = "";
$subject = "";
$headers = "";
$message = "";
$splittingheaders = true;
for ($i=0; $i < count($lines); $i++) {
    if ($splittingheaders) {
        // this is a header
        $headers .= $lines[$i]."\n";

        // look out for special headers
        if (preg_match("/^Subject: (.*)/", $lines[$i], $matches)) {
            $subject = $matches[1];
        }
        if (preg_match("/^From: (.*)/", $lines[$i], $matches)) {
            $from = $matches[1];
        }
        if (preg_match("/^To: (.*)/", $lines[$i], $matches)) {
            $to = $matches[1];
        }
    } else {
        // not a header, but message
        $message .= $lines[$i]."\n";
    }

    if (trim($lines[$i])=="") {
        // empty line, header section has ended
        $splittingheaders = false;
    }
}

if ($conn = @mysql_connect($dbhost,$dbuser,$dbpass)) {
  if(!@mysql_select_db($dbname,$conn))
    mail($email,'Email Logger Error',"There was an error selecting the email logger database.\n\n".mysql_error());
  $from    = mysql_real_escape_string($from);
  $to    = mysql_real_escape_string($to);
  $subject = mysql_real_escape_string($subject);
  $headers = mysql_real_escape_string($headers);
  $message = mysql_real_escape_string($message);
  $email   = mysql_real_escape_string($email);
  $result = @mysql_query("INSERT INTO email_log (`to`,`from`,`subject`,`headers`,`message`,`source`) VALUES('$to','$from','$subject','$headers','$message','$email')");
  if (mysql_affected_rows() == 0)
    mail($notify,'Email Logger Error',"There was an error inserting into the email logger database.\n\n".mysql_error());
} else {
  mail($notify,'Email Logger Error',"There was an error connecting the email logger database.\n\n".mysql_error());
}
?>

Here is the mysql script to create the table you'll need.

CREATE TABLE IF NOT EXISTS `email_log` (
`id` int(11) NOT NULL auto_increment,
`to` varchar(100) NOT NULL,
`subject` varchar(200) NOT NULL,
`from` varchar(100) NOT NULL,
`headers` text NOT NULL,
`message` text NOT NULL,
`source` text NOT NULL,
`logged_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

And that's it. I use phpMyEdit behind basic HTTP Authenication to view the emails, but you could use phpMyAdmin or any other php table editor.

Comments (12)

Apr 13, 2009 at 04:08 PM
Dux Marketing:

Hi, I'll come across your website and found it more interesting in Web Development. I learn more, thanks for the information you share, i'll come back often. Regards, Dux Marketing

Reply
May 13, 2009 at 04:28 PM
William:

How would you change this to where when someone sends you an email it gets piped to the database?

Reply
Sep 01, 2009 at 09:07 PM
JSteele:

This is great! Works like a charm! My only question, is there a way to handle an attachment in the same way? The script pulls out who the email is from, to, subject and of course the message. What about an attachment? Can it be saved to the server and referenced in the db? Once again thanks for this code, it has helped greatly!

Reply
1. Sep 02, 2009 at 07:39 AM
  devtrench:
  
  Well, if you save the entire body of the email to the database, you are saving the attachment. Attachments use the MIME standard, so to extract them you have to parse out that data yourself or use a class to do it. Read about the MIME standard, and this MIME parser class might help: http://email.about.com/cs/standards/a/mime.htm http://phpclasses.ranchoweb.com/browse/package/3169.html
  
  Reply
  1. Sep 02, 2009 at 08:49 AM
    JSteele:
    
    Is this something you'd be interested in doing for $$? I've been reading on how to do this but can't seem to get it to work. As I stated before I like the code you have here and how it saves multiple pieces of data from the email to the DB. It works well for what I need (except sometimes it fails to grab who the message is from, I think the email might be in a different format??) but I need the script to also save the attachment to the server and reference that file in the DB. Let me know what you think, thanks!
    
    Reply
    1. Oct 03, 2010 at 05:59 AM
      CharlesLampert:
      
      A great class for parsing the MIME parts is Pear's Mail_mimeDecode http://pear.php.net/package/Mail_mimeDecode/redirected It will put all the email parts into a PHP object. Also, there are a couple of free and paid services that will help you parse out the MIME Email parts. I would recommend checking out: http://www.mailhooks.com http://smtp2web.com And http://mailnuggets.com will parse a forwarded email with attachment into a JSON string (if you need the attachment).
      
      Reply
May 07, 2010 at 08:34 AM
Jancel Garcia:

not work for me, i have change any line in the script ?

Reply
Jun 05, 2010 at 12:35 PM
Karl Mulder:

Hi, Your script looks functional, but you start out with a variable $email which you say should be and email address for errors. But then you read the input stream into that same variable.

Reply
1. Jun 05, 2010 at 01:27 PM
  devtrench:
  
  Hey, thanks for letting me know! I'll get that fixed, since that is how it's supposed to work. I wonder why I've never gotten an error notice from my script?? :) [Note: this bug is fixed in the script above now]
  
  Reply
Jun 11, 2010 at 10:54 AM
Michael:

Thats because email is already defined on line 12. So your defining it twice now.

Reply
Dec 18, 2011 at 07:36 PM - Unapproved
Kyle:

Just ran across this - it seems to be EXACTLY what I'm looking for, and a simple solution. However, I can't find the "pipe to program" option - it looks like you're using Hostmonster?

Any light on the subject would be awesome! Thanks,

Kyle
Dec 29, 2011 at 11:59 AM - Unapproved
Saku:

Hi,

Really nice script, definitely d/l:d it for good use.
THanks again,
Saku

Simple Email Logger Using cPanel's Pipe Forwarder

Comments (12)

Make a Comment: