Simple Email Logger Using cPanel's Pipe Forwarder

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.