SQL Redux

Derek Winkler dwinkler at ALGORITHMICS.COM
Thu Jul 31 16:03:39 IST 2003

I added a counter so it would write to DB after n emails and then remaining
when shutdown.

Seemed like a good compromise.

-----Original Message-----
From: Kearney, Rob [mailto:RKearney at AZERTY.COM]
Sent: Thursday, July 31, 2003 10:57 AM
To: MAILSCANNER at jiscmail.ac.uk
Subject: Re: SQL Redux

here is what we did for SQL logging, to bypass temp-file stuff.

Just took the SQLLogging and made SQLRTLogging, to write directly to DB, We
have not noticed any degradation in performance
Basically, we took the functions of SQLLogging and EndSQLLogging and put
them together.
(dont forget Init and End scripts also

sub SQLRTLogging {
  my($message) = @_;
  $dbh =
                      "mailscanner", "mailscanner",
                      {'PrintError' => 0})
   or MailScanner::Log::DieLog("Cannot connect to the database: %s",

  my $id = $message->{id};
  my $size = $message->{size};
  my $from = $message->{from};
  my ($from_user, $from_domain);

  # split the from address into user and domain bits.
  # This may be unnecessary for you; we use it to more easily determine
  # inbound vs outbound email in a multi-domain environment.
  # HINT: refine queries using SQL 'join' with a table containing local
  # domains.

  ($from_user, $from_domain) = split /\@/, $from;

  my @to   = @{$message->{to}};
  my $subject = $message->{subject};
  my $clientip = $message->{clientip};
  my $archives = join(',', @{$message->{archiveplaces}});
  my $isspam = $message->{isspam};
  my $ishighspam = $message->{ishigh};
  my $sascore = $message->{sascore};
  my $spamreport = $message->{spamreport};

  # Get rid of control chars and tidy-up SpamAssassin report
  $spamreport =~ s/\n/ /g;
  $spamreport =~ s/\t//g;

  # Get timestamp, and format it so it is suitable to use with MySQL
  my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
  my($timestamp) = sprintf("%d-%02d-%02d %02d:%02d:%02d",

# maillog_mail insert
    my @fields=($timestamp, $id, $size, $from_user, $from_domain,
                $subject, $clientip, $archives, $isspam, $ishighspam,
                $sascore, $spamreport);
    map { s/\'/\\'/g } @fields;

    # Insert @fields into a database table
    my($sth) = $dbh->prepare("INSERT INTO maillog_mail (time, msg_id, size,
from_user, from_domain, subject, clientip, archives, isspam, ishighspam,
sascore, spamreport) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");

[5],$fields[6],$fields[7],$fields[8],$fields[9],$fields[10],$fields[11]) or
MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);

  my($file, $text);
  while(($file, $text) = each %{$message->{allreports}}) {
    $file = "the entire message" if $file eq "";
    # Use the sanitised filename to avoid problems caused by people forcing
    # logging of attachment filenames which contain nasty SQL instructions.
    $file = $message->{file2safefile}{$file} or $file;
    $text =~ s/\n/ /;  # Make sure text report only contains 1 line
    $text =~ s/\t/ /; # and no tab characters

    my @fields = ($id, $file, $text);
    map { s/\'/\\'/g } @fields;

    my($sth) = $dbh->prepare("INSERT INTO maillog_report (msg_id, filename,
filereport) VALUES (?,?,?)");
    $sth->execute($fields[0],$fields[1],$fields[2]) or
MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);


  for (@to) {
    # again, split the recipient's email into user and domain halves first.
    # see comment above about splitting the email like this.

    my ($to_user, $to_domain);
    ($to_user, $to_domain) = split /\@/, $_;
    my @fields = ($id, $to_user, $to_domain);
    map { s/\'/\\'/g } @fields;
    my($sth) = $dbh->prepare("INSERT INTO maillog_recipient (msg_id,
to_user, to_domain) VALUES (?,?,?)");
    $sth->execute($fields[0],$fields[1],$fields[2]) or
MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);

  # Close database connection


  MailScanner::Log::InfoLog("Ending SQL Real-Time Logging");


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.mailscanner.info/pipermail/mailscanner/attachments/20030731/18dbab8d/attachment.html

More information about the MailScanner mailing list