=head1 NAME
RepeatMessageDB - provides eval test using info about received messages.
=head1 SYNOPSIS
loadplugin Mail::SpamAssassin::Plugin::RepeatMessageDB /usr/local/etc/mail/spamassassin/plugins/RepeatMessageDB.pm
repeatmessagedb_sql_dsn DBI:mysql:dbname:localhost
repeatmessagedb_sql_username user
repeatmessagedb_sql_password pass
describe RMDB_REPEAT Message repeated
header RMDB_REPEAT eval:repeatmessagedb_check(2)
score RMDB_REPEAT 0.1
describe RMDB_REPEAT_MORE Message repeated more times
header RMDB_REPEAT_MORE eval:repeatmessagedb_check(5)
score RMDB_REPEAT_MORE 0.5
describe RMDB_REPEAT_SPAM Spam message repeated
header RMDB_REPEAT_SPAM eval:repeatmessagedb_spam()
score RMDB_REPEAT_SPAM 1.0
describe RMDB_HAMMER_SPAM Spam message repeated directly
header RMDB_HAMMER_SPAM eval:repeatmessagedb_spam(3)
score RMDB_HAMMER_SPAM 1.0
=head1 DESCRIPTION
This module provides eval tests that checks a database containins entries
for messages that has been received.
=head1 REQUIREMENT
This plugin uses the database used by the MIMEDefang filter at
http://whatever.frukt.org.
=head1 CONFIGURATION
=head2 Eval tests
=over
=item repeatmessagedb_check(limit)
True if a message with the same envelope from, envelope to and message-id has
been seen more than limit times before.
NOTE: All messages will normally be seen at least once before this check is
done, and if using greylisting, they may well be seen twice or more before
ever getting to SpamAssassin. Limit defaults to 2.
=item repeatmessagedb_spam(window)
True if a message with the same envelope from, envelope to and message-id has
been rejected as spam within the window last minutes (or ever if window is
unspecified or less than 1)
=back
=head2 Options
=over
=item repeatmessagedb_sql_dsn
Wich database driver and database to use.
=item repeatmessagedb_sql_username
User name for the database connection.
=item repeatmessagedb_sql_password
Password for the database connection.
=back
=cut
package Mail::SpamAssassin::Plugin::RepeatMessageDB;
# $Id: RepeatMessageDB.pm,v 1.5 2009/06/26 11:52:16 jonas Exp $
use strict;
use base 'Mail::SpamAssassin::Plugin';
use DBI;
sub dbg {
my $msg = shift;
Mail::SpamAssassin::Plugin::dbg(sprintf("repeatmessagedb: $msg",@_));
}
sub new {
my ($class,$mailsa) = @_;
$class = ref($class) || $class;
my $self = $class->SUPER::new($mailsa);
bless($self,$class);
$self->{sqldb} = undef;
$self->{main}->{conf}->{repeatmessagedb_sql_dsn} = 'DBI:mysql:mdf:localhost';
$self->{main}->{conf}->{repeatmessagedb_sql_username} = 'sa';
$self->{main}->{conf}->{repeatmessagedb_sql_password} = 'pwd';
$self->register_eval_rule('repeatmessagedb_check');
$self->register_eval_rule('repeatmessagedb_spam');
#dbg('registered');
return $self;
}
sub parse_config {
my ($self,$pars) = @_;
return 0 if ($pars->{user_config});
return 0 unless ($pars->{key} =~ /^repeatmessagedb_(sql_dsn|sql_username|sql_password)$/);
my $key = $1;
my $val = $pars->{value};
$val = '' if ($key =~ /(username|password)/);
$val = " = $val" if ($val);
dbg('config %s%s',$key,$val);
$self->{main}->{conf}->{$pars->{key}} = $pars->{value};
$self->inhibit_further_callbacks();
return 1;
}
sub _sql_connect {
my ($self) = @_;
return 1 if ($self->{sqldb});
#dbg('sql connect');
$self->{sqldb} = DBI->connect_cached(
$self->{main}->{conf}->{repeatmessagedb_sql_dsn},
$self->{main}->{conf}->{repeatmessagedb_sql_username},
$self->{main}->{conf}->{repeatmessagedb_sql_password},
{RaiseError=>0}
);
return 1 if ($self->{sqldb});
dbg('sql connect failed');
return 0;
}
sub _sql_disconnect {
my ($self) = @_;
if ($self->{sqldb}) {
#dbg('sql disconnect');
$self->{sqldb}->disconnect();
}
$self->{sqldb} = undef;
}
sub _sql_select_stuff {
my ($self,$pms,$cmd,$fast,@params) = @_;
unless ($pms->{repeatmessagedb}) {
$pms->{repeatmessagedb} = {};
$pms->{repeatmessagedb}->{efrom} = lc($pms->get('EnvelopeFrom:addr'));
$pms->{repeatmessagedb}->{efrom} =~ s/^<(.*?)>$/$1/ if (defined($pms->{repeatmessagedb}{efrom}));
$pms->{repeatmessagedb}->{msgid} = $pms->get('Message-Id:addr');
$pms->{repeatmessagedb}->{msgid} =~ s/^<(.*?)>$/$1/ if (defined($pms->{repeatmessagedb}{efrom}));
my %rcpts = ();
foreach my $rcpth (('Apparently-To','Delivered-To')) {
my $rcptv = $pms->get($rcpth);
next unless ($rcptv);
$rcptv =~ s/[\r\n]+//gs;
foreach my $rcpt (split(/\s*[;,]\s*/s,$rcptv)) {
$rcpt =~ s/^<(.*?)>$/$1/;
next if ($rcpt =~ /^\s*$/);
$rcpts{lc($rcpt)} = 1;
}
}
$pms->{repeatmessagedb}->{rcpts} = [keys %rcpts] if (%rcpts);
dbg('efrom: %s',$pms->{repeatmessagedb}->{efrom}) if (defined($pms->{repeatmessagedb}->{efrom}));
dbg('msgid: %s',$pms->{repeatmessagedb}->{msgid}) if (defined($pms->{repeatmessagedb}->{msgid}));
dbg('rcpts: %s',join(',',@{$pms->{repeatmessagedb}->{rcpts}})) if (defined($pms->{repeatmessagedb}->{rcpts}));
}
return 0 unless (defined($pms->{repeatmessagedb}->{efrom}) && defined($pms->{repeatmessagedb}->{msgid}) && defined($pms->{repeatmessagedb}->{rcpts}));
my $qid = join(';',$cmd,$fast?1:0,join(',',@params));
return $pms->{repeatmessagedb}->{$qid} if (defined($pms->{repeatmessagedb}->{$qid}));
$pms->{repeatmessagedb}->{$qid} = 0;
return $pms->{repeatmessagedb}->{$qid} unless ($self->_sql_connect);
my $st = $self->{sqldb}->prepare($cmd);
if ($st) {
foreach my $rcpt (@{$pms->{repeatmessagedb}->{rcpts}}) {
dbg('"%s"; %s',$cmd,join(', ',($pms->{repeatmessagedb}->{msgid},$pms->{repeatmessagedb}->{efrom},$rcpt,@params)));
$st->execute($pms->{repeatmessagedb}->{msgid},$pms->{repeatmessagedb}->{efrom},$rcpt,@params);
my @res = $st->fetchrow_array;
$st->finish;
next unless (@res && $res[0]);
$pms->{repeatmessagedb}->{$qid} = $res[0] if ($res[0] > $pms->{repeatmessagedb}->{$qid});
last if ($fast);
}
} else {
dbg('sql prepare failed');
}
$self->_sql_disconnect();
dbg('%s: %u',$qid,$pms->{repeatmessagedb}->{$qid});
return $pms->{repeatmessagedb}->{$qid};
}
sub repeatmessagedb_check {
my ($self,$pms,$limit) = @_;
$limit = defined($limit) ? eval($limit) : 2;
my $repc = $self->_sql_select_stuff($pms,'SELECT msg_count FROM messages WHERE msg_id=? AND msg_sender=? and msg_recipient=?');
return ($repc > $limit) ? 1 : 0;
}
sub repeatmessagedb_spam {
my ($self,$pms,$window) = @_;
$window = defined($window) ? eval($window) : 0;
$window = 0 unless ($window && $window>0);
my $repc = $window ?
$self->_sql_select_stuff($pms,'SELECT msg_spam FROM messages WHERE msg_id=? AND msg_sender=? and msg_recipient=? and msg_spam>?',1,time()-($window*60)) :
$self->_sql_select_stuff($pms,'SELECT msg_spam FROM messages WHERE msg_id=? AND msg_sender=? and msg_recipient=?',1);
return $repc ? 1 : 0;
}
1;
(2008-02-01)