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.