[Mimedefang] yet another greylist mysql implementation

[Minica, Nelson (EDS)]

The tied DBM code just didn't make sense to me so I wrote this before I
saw someone else ported the DBM code to mysql... Just thought I'd offer
it to community for comments/inspection (I'm sure it could be
improved)... use at your own risk!  The code greylists by
domain/classc/to, but will allow domain/classc to anyone once
whitelisted.


sub check_greylist(){      # return 0 is tempfail, 1 is permit
  if($Sender eq "<>" || is_trusted()) { return 1; }
        use DBI;
        $dbh = DBI->connect("DBI:mysql:spamdb","myuser",'mypass');
        $blackperiod=10*60;   #10 minutes
        $greyperiod=24*60*60; #24 hours
        $from = lc($Sender);
        $from =~ s/[<>]//g;
        ( $username, $domain ) = split(/\@/,$from);
        @classc=split(/\./,$RelayAddr);
        foreach my $mailto (@Recipients) {
                $mailto =~ s/[<>]//g;
                $sth = $dbh->prepare("SELECT greystatus,inittime FROM
greylist WHERE domain='$domain' AND ip LIKE
'$classc[0].$classc[1].$classc[2].%' AND (greystatus=1 OR
mailto='$mailto') ORDER BY greystatus DESC LIMIT 1");
                $sth->execute;
                if (($greystatus,$inittime)=$sth->fetchrow() ) {
                        $timediff = time() - $inittime;
                        if ($greystatus == 1) {
                                md_syslog('info', "GREYLIST: $domain
$classc[0].$classc[1].$classc[2] Already white");
                                $rc=1;
                                }
                        elsif ($timediff > $blackperiod && $timediff <
$greyperiod) {
                                $dbh->do("UPDATE greylist SET
greystatus=1 WHERE domain='$domain' AND ip LIKE
'$classc[0].$classc[1].$classc[2].%'");
                                md_syslog('info', "GREYLIST: $domain
$classc[0].$classc[1].$classc[2] Whitelisted now");
                                $rc=1;
                                }
                        elsif ($timediff > $blackperiod && $timediff >
$greyperiod) {
                                $dbh->do("UPDATE greylist SET
inittime='".time()."' WHERE domain='$domain' AND ip LIKE
'$classc[0].$classc[1].$classc[2].%'");
                                md_syslog('info', "GREYLIST: $domain
$classc[0].$classc[1].$classc[2] Greylisted again");
                                $rc=0;
                                }
                        else {
                                md_syslog('info', "GREYLIST: $domain
$classc[0].$classc[1].$classc[2] Black still");
                                $rc=0;
                                }
                        }
                else {
                        $dbh->do("INSERT INTO greylist
(greystatus,inittime,user,domain,ip,mailto) VALUES
(0,'".time()."','$username','$domain','$RelayAddr','$mailto')");
                        md_syslog('info', "GREYLIST: $domain
$classc[0].$classc[1].$classc[2] Greylisted now");
                        $rc=0;
                        }
                $sth->finish;
                }
        $dbh->disconnect();
        return $rc;
        }

sub is_trusted() {
  if ($RelayAddr eq "127.0.0.1" || $RelayAddr eq "123.123.123.123" ||
$RelayAddr =~ /^10\.1\.1\./) {
    return 1;
    }
  else {
    open(COMM, "<./COMMANDS") or return 0;
    while(<COMM>) {
      if (/^=auth_authen/) {
        close(COMM);
        md_syslog('info', "MDLOG: SMTP Authenticated");
        return 1;
        }
      }
    close(COMM);
    return 0;
    }
}

sub filter_begin () {
if ( !check_greylist() ) {
        return action_tempfail("Temporary Error, please retry later");
        }
etc...

CREATE TABLE greylist (
  greystatus int(11) NOT NULL default '0',
  inittime varchar(20) NOT NULL default '',
  user varchar(200) NOT NULL default '',
  domain varchar(200) NOT NULL default '',
  ip varchar(15) NOT NULL default '',
  mailto varchar(200) NOT NULL default '',
  PRIMARY KEY  (domain,ip,mailto),
  KEY status (greystatus)
) TYPE=MyISAM;