Mailwatch 0.2 sql prob
Peter C. Ndikuwera
pndiku at DSMAGIC.COM
Mon Sep 1 08:37:06 IST 2003
Hi Chris.
Try this CustomConfig.pm. I got it by combining Mailwatch's version with
the default Mailscanner one. It's more efficient than my last hack in
that it only connects to the dB once per batch of messages rather than
once per message.
It uses a temporary file, so doesn't have the default mailwatch problem
of losing the dB connection.
If you (and anyone else using mailwatch) like it I'll send a diff to the
mailwatch maintainer.
Peter
On Fri, 2003-08-29 at 15:22, Chris Campbell wrote:
> Hey Peter....
> Would you mind dumping your table schema for me? I think I messed stuff
> up... Your "hack" seems to work a little..... but
>
> Aug 29 08:21:32 nycexmx1 MailScanner[6895]: Cannot insert row: called with
> 20 bind variables when 2 are needed
>
>
> .....................................
> Christopher S. Campbell
> UNIX Admin
> First Albany Corp
> 518.447.8544
> chris.campbell at fac.com
>
>
>
>
>
> "Peter C.
> Ndikuwera" To: MAILSCANNER at JISCMAIL.AC.UK
> <pndiku at DSMAGIC.CO cc:
> M> Subject: Re: Mailwatch 0.2 sql prob
> Sent by:
> MailScanner
> mailing list
> <MAILSCANNER at JISCM
> AIL.AC.UK>
>
>
> 08/29/03 03:16 AM
> Please respond to
> MailScanner
> mailing list
>
>
>
>
>
>
> Hi Daniel,
>
> I have a fix which is really a hack. I'm sure it's the wrong way to do
> it but it works for me. I've attached the relevant part of my
> CustomConfig.pm.
>
> Peter
> On Wed, 2003-08-27 at 17:14, Daniel Bird wrote:
> > Hi,
> > I noticed in the archives the same problem I'm having, but no solution:
> > I keep seeing this in the maillog:
> >
> > Cannot insert row: MySQL server has gone away
> >
> > I was wondering if anyone has had this problem and managed to find a fix?
> >
> > Regards
> > --
> > ____________________________________
> >
> > Daniel Bird
> > Network & Systems Manager
> > St. George's Hospital Medical School
> > Tooting
> > London SW17 0RE
> >
> > P: +44 20 8725 2897
> > F: +44 20 8725 3583
> > E: dan at sghms.ac.uk
> > ____________________________________
> >
> > Hex dump: Where witches put used curses...
> > "#define QUESTION ((bb) || !(bb)) - Shakespeare."
> (See attached file: SQLLogging.pm)
>
-------------- next part --------------
#
# MailScanner - SMTP E-Mail Virus Scanner
# Copyright (C) 2002 Julian Field
#
# $Id: CustomConfig.pm,v 1.3.2.10 2003/08/11 20:35:40 jkf Exp $
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
# The author, Julian Field, can be contacted by email at
# Jules at JulianField.net
# or by paper mail at
# Julian Field
# Dept of Electronics & Computer Science
# University of Southampton
# Southampton
# SO17 1BJ
# United Kingdom
#
package MailScanner::CustomConfig;
use strict 'vars';
use strict 'refs';
no strict 'subs'; # Allow bare words for parameter %'s
use vars qw($VERSION);
### The package version, both in 1.23 style *and* usable by MakeMaker:
$VERSION = substr q$Revision: 1.3.2.10 $, 10;
#
# These are the custom functions that you can write to produce a value
# for any configuration keyword that you want to do clever things such
# as retrieve values from a database.
#
# Your function may be passed a "message" object, and must return
# a legal value for the configuration parameter. No checking will be
# done on the result, for extra speed. If you want to find out what
# there is in a "message" object, look at Message.pm as they are all
# listed there.
#
# You must handle the case when no "message" object is passed to your
# function. In this case it should return a sensible default value.
#
# Return value: You must return the internal form of the result values.
# For example, if you are producing a yes or no value,
# you return 1 or 0. To find all the internal values
# look in ConfigDefs.pl.
#
# For each function "FooValue" that you write, there needs to be a
# function "InitFooValue" which will be called when the configuration
# file is read. In the InitFooValue function, you will need to set up
# any global state such as create database connections, read more
# configuration files and so on.
#
##
## This is a trivial example function to get you started.
## You could use it in the main MailScanner configuration file like
## this:
## VirusScanning = &ScanningValue
##
#sub InitScanningValue {
# # No initialisation needs doing here at all.
# MailScanner::Log::InfoLog("Initialising ScanningValue");
#}
#
#sub EndScanningValue {
# # No shutdown code needed here at all.
# # This function could log total stats, close databases, etc.
# MailScanner::Log::InfoLog("Ending ScanningValue");
#}
#
## This will return 1 for all messages except those generated by this
## computer.
#sub ScanningValue {
# my($message) = @_;
#
# return 1 unless $message; # Default if no message passed in
#
# return 0 if $message->{subject} =~ /jules/i;
# return 1;
#
# #my($IPAddress);
# #$IPAddress = $message->{clientip};
# #return 0 if $IPAddress eq '127.0.0.1';
# #return 1;
#}
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#
# This set of functions provides per-domain simple spam whitelists and
# blacklists. Each of the 2 directories set below contains 1 file for
# each domain, with the domain name being the filename. The file contains
# a list of entries, 1 per line, each one either being a full address:
# user at domain.com
# or an entire domain:
# domain.com
# The addresses contained in the file for a domain make up the entire
# spam whitelist or blacklist for that domain.
#
# For example, say you had /etc/MailScanner/spam.bydomain/whitelist/jules.fm
# which included the lines
# soton.ac.uk
# ecs.soton.ac.uk
# jules at julianfield.net
# 123.234.45.56
# Then all mail from anything at soton.ac.uk, anything at ecs.soton.ac.uk or
# jules at julianfield.net would be whitelisted if it was heading to any
# address @jules.fm. Also all mail from IP address 123.234.45.56 would be
# whitelisted if it was heading to any address @jules.fm.
# The same thing works for the blacklist directory.
#
# Overall white and blacklists should be put in a file in each directory
# called 'default'.
#
# To enable these functions, set the following in your MailScanner.conf file:
# Is Definitely Not Spam = &ByDomainSpamWhitelist
# Is Definitely Spam = &ByDomainSpamBlacklist
#
# Set these to be the location of your whitelist files and blacklist files
my $WhitelistDir = '/etc/MailScanner/spam.bydomain/whitelist';
my $BlacklistDir = '/etc/MailScanner/spam.bydomain/blacklist';
use DirHandle;
use FileHandle;
my(%Whitelist, %Blacklist);
#
# Initialise by-domain spam whitelist and blacklist
#
sub InitByDomainSpamWhitelist {
MailScanner::Log::InfoLog("Starting up by-domain spam whitelist, " .
"reading from %s", $WhitelistDir);
my $domains = CreateByDomainList($WhitelistDir, \%Whitelist);
MailScanner::Log::InfoLog("Read whitelist for %d domains", $domains);
}
sub InitByDomainSpamBlacklist {
MailScanner::Log::InfoLog("Starting up by-domain spam blacklist, " .
"reading from %s", $BlacklistDir);
my $domains = CreateByDomainList($BlacklistDir, \%Blacklist);
MailScanner::Log::InfoLog("Read blacklist for %d domains", $domains);
}
#
# Lookup a message in the by-domain whitelist and blacklist
#
sub ByDomainSpamWhitelist {
my($message) = @_;
return LookupByDomainList($message, \%Whitelist);
}
sub ByDomainSpamBlacklist {
my($message) = @_;
return LookupByDomainList($message, \%Blacklist);
}
#
# Close down the by-domain whitelist and blacklist
#
sub EndByDomainSpamWhitelist {
MailScanner::Log::InfoLog("Closing down by-domain spam whitelist");
}
sub EndByDomainSpamBlacklist {
MailScanner::Log::InfoLog("Closing down by-domain spam blacklist");
}
#
# Setup the per-domain spam white or black list.
# Note this doesn't do anything much in the way of syntax-checking the
# files, so they better be right! If there are duff lines in the files,
# they just won't produce any matches, they can't actually cause any harm.
#
sub CreateByDomainList {
my($dirname, $BlackWhite) = @_;
my($dir, $filename, $fh, $domains);
$dir = new DirHandle;
$dir->open($dirname) or return 0;
$domains = 0; # Count the number of domains we have read
while ($filename = $dir->read()) {
next if $filename =~ /^\./;
next unless -f "$dirname/$filename";
$fh = new FileHandle;
$fh->open("$dirname/$filename") or next;
$filename = lc($filename); # Going to store the name in lower case
while(<$fh>) {
chomp;
#print STDERR "Line is \"$_\"\n";
s/#.*$//; # Strip comments
s/\S*:\S*//g; # Strip any words with ":" in them
s/^\s+//g; # Strip leading whitespace
s/^(\S+)\s.*$/$1/; # Use only the 1st word
s/^\*\@//; # Strip any leading "*@" they might have put in
#print STDERR "Line is \"$_\"\n";
next if /^$/; # Strip blank lines
$BlackWhite->{$filename}{lc($_)} = 1; # Store the whitelist entry
}
$fh->close();
$domains++;
}
$dir->close();
return $domains;
}
#
# Based on the address it is going to, choose the right spam white/blacklist.
# Return 1 if the "from" address is white/blacklisted, 0 if not.
#
sub LookupByDomainList {
my($message, $BlackWhite) = @_;
return 0 unless $message; # Sanity check the input
# Find the "from" address and the first "to" address
my($from, $fromdomain, @todomain, $todomain, @to, $to, $ip);
$from = $message->{from};
$fromdomain = $message->{fromdomain};
@todomain = @{$message->{todomain}};
$todomain = $todomain[0];
@to = @{$message->{to}};
$to = $to[0];
$ip = $message->{clientip};
# It is in the list if either the exact address is listed,
# or the domain is listed
return 1 if $BlackWhite->{$to}{$from};
return 1 if $BlackWhite->{$to}{$fromdomain};
return 1 if $BlackWhite->{$to}{$ip};
return 1 if $BlackWhite->{$todomain}{$from};
return 1 if $BlackWhite->{$todomain}{$fromdomain};
return 1 if $BlackWhite->{'default'}{$from};
return 1 if $BlackWhite->{'default'}{$fromdomain};
return 1 if $BlackWhite->{'default'}{$ip};
# It is not in the list
return 0;
}
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
###########################################################################
#
# Handy little feature to let you use the same MailScanner.conf file on
# lots of different hosts, where the only difference is the hostname.
# Just uncomment the "use Sys::Hostname" line and then set
# Hostname = &Hostname
# in your MailScanner.conf to use this.
#
# Many thanks to Tony Finch for this.
#
###########################################################################
use Sys::Hostname;
my $hostname;
sub InitHostname {
$hostname = hostname;
}
sub Hostname {
return $hostname;
}
sub EndHostname {
# nothing to do
}
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
###########################################################################
#
# This is a setup to do logging to an SQL database.
# For speed, the per-message logs are written to a tab-separated file
# during execution.
# When the child process dies of old age (or is politely killed), the
# log file is read and inserted into a database table.
#
# If you want to use this code, you must:
# 1. uncomment the "use DBI;" line just below this comment.
# 2. Read the README.sql-logging file in the docs directory
# of the distribution.
#
###########################################################################
use IO::File;
use DBI;
my($logfile1);
my($hostname) = hostname; # Don't forget to uncomment Use Sys::Hostname above
# Modify this as necessary for your configuration
my($db_name) = "mailscanner";
my($db_host) = "localhost";
my($db_user) = "mailscanner";
my($db_pass) = "mailscanner";
# Initialise. All we need to do is create the temporary log files. These
# are created using tmpfile() to avoid security problems caused by any
# other process on the system being able to read (or even write!) to
# the log files. The files created are not accessible to any other processes
# at all, as they don't have an entry in a directory.
sub InitSQLLogging {
MailScanner::Log::InfoLog("Initialising SQL Logging temp file");
$logfile1 = IO::File->new_tmpfile or die "IO::File->new_tmpfile: $!";
#$logfile->autoflush(1);
}
# Shutdown. Write all the log entries to the SQL database, then close
# the temporary log files. Closing them will also delete them as they were
# created with tmpfile().
sub EndSQLLogging {
my(@fields);
MailScanner::Log::InfoLog("Ending SQL Logging temp output " .
"and flushing to database");
# Create database connection
my($dbh) = DBI->connect("DBI:mysql:database=$db_name;host=$db_host",
$db_user, $db_pass,
{PrintError => 0}) or
MailScanner::Log::DieLog("Cannot connect to the database: %s",
$DBI::errstr);
# Rewind to start of logfile
$logfile1->flush();
seek($logfile1, 0, 0)
or MailScanner::Log::DieLog("EndSQLLogging seek: %s", $!);
while(<$logfile1>) {
chomp;
@fields = split(/\t/);
print join(",", @fields);
# Work through each field protecting any special characters such as single quote
# The line below replaces ' with \'
# @fields = map { s/\'/\\'/g } @fields;
# ADD: Peter C. Ndikuwera. The above line doesn't seem to work...
# Set any empty strings to NULL so the SQL insert works correctly
@fields = map { ($_ eq '')?'NULL':"$_" } @fields;
# Insert @fields into a database table
my($sth) = $dbh->prepare("INSERT INTO maillog VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$sth->execute($fields[0],$fields[1],$fields[2],$fields[3],$fields[4],$fields[5],$fields[6],$fields[7],$fields[8],$fields[9],$fields[10],$fields[11],$fields[12],$fields[13],$fields[14],$fields[15],$fields[16],$fields[17],$fields[18],$fields[19]) or MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);
}
# Close database connection
$dbh->disconnect();
# Close and delete the temporary files (deletion is done automatically)
$logfile1->close();
MailScanner::Log::InfoLog("Database flush completed");
}
# Write all the log information for 1 message to the temporary file.
sub SQLLogging {
my($message) = @_;
# Get rid of control chars and tidy-up SpamAssassin report
my $spamreport = $message->{spamreport};
$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",
$year+1900,$mon+1,$mday,$hour,$min,$sec);
# Also print 1 line for each report about this message. These lines
# contain all the info above, + the attachment filename and text of
# each report.
my($file, $text, @report_array);
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
push (@report_array, $text);
}
# Sanitize reports
my $reports = join(",", at report_array);
# Print 1 line for each message.
print $logfile1 join("\t", $timestamp,
$message->{id},
$message->{size},
$message->{from},
join(',', @{$message->{to}}),
$message->{subject},
$message->{clientip},
join(',', @{$message->{archiveplaces}}),
$message->{isspam},
$message->{ishigh},
$message->{issaspam},
$message->{isrblspam},
$message->{spamwhitelisted},
$message->{sascore},
$spamreport,
$message->{virusinfected},
$message->{nameinfected},
$message->{otherinfected},
$reports,
$hostname) . "\n";
}
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#**************************************************************************
#
# This Custom Function provides a facility whereby some internal-only
# accounts can only send mail to other "internal" domain names, and cannot
# send mail to any other addresses apart from those domains.
#
# To use it, specify
# Non Spam Actions = &InternalActions
# Spam Actions = &InternalActions
# High Scoring Spam Actions = &InternalActions
# in your MailScanner.conf file, having added this code to
# /usr/lib/MailScanner/MailScanner/CustomConfig.pm
#
# It uses a configuration file whose path is
my($InternalAccountList) = '/etc/MailScanner/internal.accounts.conf';
# to read lines that look like one of these
# domain yourdomain.com
# account local-only1
# These 2 lines in the file would define that a local email account
# "local-only1" could not send mail to any address except addresses
# @yourdomain.com.
# There can be many domains and many accounts specified, one per line.
#
# Mail from the internal-only accounts to external domains will have the
my($InternalFailAction) = 'delete';
# action applied to it. This can be any of the legal "spam actions" as
# defined in the MailScanner.conf file.
#
use FileHandle;
my(%InternalDomains, %InternalAccounts);
sub InitInternalActions {
MailScanner::Log::InfoLog("Initialising Internal account list");
my $listfile = new FileHandle;
unless($listfile->open("<$InternalAccountList")) {
MailScanner::Log::WarnLog("Could not read list of internal accounts " .
"from %s", $InternalAccountList);
return;
}
my($keyword, $value);
my $line = 0;
my $domains = 0;
my $accounts = 0;
while(<$listfile>) {
$line++;
chomp;
s/^#.*$//;
s/^\s*//g;
s/\s*$//g;
next if /^$/;
$keyword = undef;
$value = undef;
/^([^\s]+)\s*([^\s]+)$/;
($keyword, $value) = (lc($1), lc($2));
$value =~ s/\@.*$//; # Delete the @ and everything after it
if ($keyword =~ /domain/i) {
#print STDERR "Storing domain $value\n";
$InternalDomains{$value} = 1;
$domains++;
} elsif ($keyword =~ /account|user/i) {
#print STDERR "Storing account $value\n";
$InternalAccounts{$value} = 1;
$accounts++;
} else {
MailScanner::Log::WarnLog("Syntax error in %s at line %d",
$InternalAccountList, $line);
}
}
$listfile->close();
MailScanner::Log::InfoLog("Internal Account List read %d domains and %d " .
"accounts", $domains, $accounts);
}
sub EndInternalActions {
# No shutdown code needed here at all.
MailScanner::Log::InfoLog("Shutting down internal accounts list");
}
# This will return 1 for all messages except those generated by this
# computer.
# This will return "deliver" for all internal mail as requested,
# and $InternalFailAction for everything else.
sub InternalActions {
my($message) = @_;
return 'deliver' unless $message; # Default if no message passed in
return 'deliver' unless $message->{from}; # Default if duff message
my($fromac, $fromdomain, $todomain);
$fromac = lc($message->{from});
$fromdomain = $fromac;
$fromac =~ s/\@.*$//; # Leave everything before @
$fromdomain =~ s/^.*\@//; # Leave everything after @
# Is it coming from inside?
#print STDERR "Testing $fromdomain\n";
#print STDERR "Answer is " . $InternalDomains{$fromdomain} . "\n";
return 'deliver' unless $InternalDomains{$fromdomain};
#print STDERR "$fromdomain passed internaldomains test\n";
# and is it coming from an internal-only address?
return 'deliver' unless $InternalAccounts{$fromac};
#print STDERR "$fromac passed internalaccounts test\n";
# Fail if it is being delivered to *any* external addresses
foreach $todomain (@{$message->{todomain}}) {
$todomain = lc($todomain);
#print STDERR "Testing $todomain\n";
unless ($InternalDomains{$todomain}) {
MailScanner::Log::WarnLog("Internal-only account %s attempted to " .
"send mail to external address \@%s", $fromac, $todomain);
return $InternalFailAction;
}
}
# Passed that, so it must be only going to internal addresses
return 'deliver';
}
1;
More information about the MailScanner
mailing list