MIMEDefang – processing the valid users list ( filter_recipient() )

I’m rebuilding our spam scanning platform at the moment and making some changes/improvements along the way. One of our issues was that we want to show customers ‘just how bad it is’ without spam scanning and so would like to illustrate how many emails are rejected because no valid user exists. Currently we filter the incoming mail on valid users by the Sendmail access list, however this does not write our logs into a neat MySQL database. MIMEDefang, which we are currently using as the hub of our AVS product can do this but we need to do it before we start running ‘expensive’, time consuming tests such as RBLs, SpamAssassin and the like. We can call MIMEDefang with the -t flag which makes the milter hook into the ‘RCPT TO’ part of the smtp handshake. From here we can see a few bits of info, but what it important to us is the sender and the recipient. The milter is called once for each recipient, so we can accept or reject as we need to on a per recipient basis. The ‘man mimedefang-filter’ gives the following example:

 sub filter_recipient {
            my ($recipient, $sender, $ip, $hostname, $first, $helo,
                   $rcpt_mailer, $rcpt_host, $rcpt_addr) = @_;
            if ($sender =~ /^?$/i) {
                 if ($recipient =~ /^?$/i) {
                      return ('CONTINUE', "ok");
                 }
                 return ('REJECT', 'Sorry; spammer@badguy.com is blacklisted.');
            }
            return ('CONTINUE', "ok");
       }

This set me thinking as to how we could use our mysql list of valid users to do this. We store our valid user lists (per domain) in a comma delimited list within a MySQL field. Here is my solution:

sub filter_recipient {

        use DBI;
        my $dbh = DBI->connect("DBI:mysql:******:******","******","******", {RaiseError => 1});
        my ($recipient, $sender, $ip, $hostname, $first, $helo, $rcpt_mailer, $rcpt_host, $rcpt_addr) = @_;

        my $domain = get_domain($recipient);
        my $clean_recip = &detag($recipient);
        my $clean_sender = &detag($sender);
        my $sender_domain = &get_domain($sender);

        #
        # CHECK THE VALID USER LIST
        #
        my $sql = "SELECT accesslist FROM avstable WHERE domain_name = \"$domain\"";
        my $sth = $dbh->prepare("$sql") or warn "preparing: ", $dbh->errstr;
        $sth->execute or warn "executing: ", $dbh_config->errstr;
        my ($access_list) = $sth->fetchrow_array();

        my @access = split(/,/,$access_list);
        if (@access){
                if (! grep /^$clean_recip$/, @access){
                        &logger ("NO VALID USER");
                        return ('REJECT', "No such user in domain: $domain");
                }
        }

        return ('CONTINUE', "ok");
}

I have a couple of little functions for extracting the domain from the email address and removing the < and > tags to make it work, but it seems to work fine. I also needed to accommodate domains with no ‘accesslist’ data at all.