Hello
Back in February 2004 I had some ideas of blocking SPAM. Not sure if other people have had the same ideas, but it seems to work pretty good.
I originally hacked together my own SMTP server in C++, however it wasn't exactly production quality. I pointed some domains at it and ran a "test" with about 70,000 emails. It blocked about 70% of the crap before allowing DATA.
Yesterday I ran across QPSMTPD and decided that it would be much better to implement my ideas in a plugin. The best thing is that I don't need three servers to do the work, like my original server.
I am not a super expert at PERL, and QPSMTPD is new stuff for me. So please let me know if I have done something terribly wrong. After 1400 emails it appears to be working properly.
Here are my ideas
1. Compare the country of the originating IP address to the country of the "domain" in the "from address". Basically want to dump email that claims to be "from" a US company/domain but originates out of China, etc. and vice-versa.
2. Keep track of ips that send multiple "from" domains. And black-list those.
3. If the "from" address is a well-known mail service such as yahoo, hotmail, msn, aol, etc. Then the connecting IP has to be on "their" network.
Right now the black list thingy is somewhat manual, I made a simple PHP script that shows me ips that have sent messages with "from " addresses of multiple domains in the past 24 hours. Then I decide whether or not to block the ip. But this could be automated down the road I suppose.
My plugin uses MySQL / DBI and GeoIP from maxmind.com
Below is my code, etc.
Best Regards
Waitman Gobble EMK Design http://emkdesign.com/ 714 522 2528
>> SQL table structure
# --------------------------------------------------------
# # Table structure for table `blocker` #
CREATE TABLE `blocker` ( `idx` int(10) unsigned NOT NULL auto_increment, `ip` char(32) NOT NULL default '', `sequence` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`idx`) ) TYPE=MyISAM;
# --------------------------------------------------------
# # Table structure for table `checks` #
CREATE TABLE `checks` ( `idx` int(10) unsigned NOT NULL auto_increment, `mail_from` char(128) NOT NULL default '', `ip` char(32) NOT NULL default '', `reverse_dns` char(128) NOT NULL default '', `country_from` char(2) NOT NULL default '', `country_ip` char(2) NOT NULL default '', `approval` tinyint(3) unsigned NOT NULL default '0', `bulk_check` tinyint(3) unsigned NOT NULL default '0', `blist` tinyint(3) unsigned NOT NULL default '0', `sequence` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`idx`) ) TYPE=MyISAM;
>> the plugin plugins/check_country
use DBI; use Geo::IP;
sub register { my ($self, $qp) = @_; $self->register_hook("mail", "check_country"); }
sub check_country {
my ($self, $transaction, $sender) = @_;
my $host = lc $sender->host;my $client_ip = $self->qp->connection->remote_ip;
my @numbers = split(/\./, $client_ip);
my $ip_number = pack("C4", @numbers);
my ($hostname) = (gethostbyaddr($ip_number, 2))[0];
if ($hostname eq "") { $hostname="unknown";}#check to see if from large email provider (msn, aol, yahoo, hotmail)
my $allowed=1;
if ($host =~ /aol.com/)
{
if ($hostname =~ /aol.com/)
{
#
} else {
$allowed=0;
}
}
if ($host =~ /msn.com/)
{
if ($hostname =~ /msn.com/)
{
#
} else {
$allowed=0;
}
}
if ($host =~ /yahoo.com/)
{
if ($hostname =~ /yahoo.com/)
{
#
} else {
$allowed=0;
}
}
if ($host =~ /hotmail.com/)
{
if ($hostname =~ /hotmail.com/)
{
#
} else {
$allowed=0;
}
}my $gi = Geo::IP->open("/usr/local/share/GeoIP/GeoIP.dat", GEOIP_STANDARD);
my $country = $gi->country_code_by_name($client_ip);my $checkcountry = $gi->country_code_by_name($host);
if ($country eq "") { $country='UN'; }
if ($checkcountry eq "") { $checkcountry='XX'; }my $compare= ($country eq $checkcountry);
my $dbh = DBI->connect("dbi:mysql:mailer:localhost", "username", "password");
my $sth= $dbh->prepare (qq/ SELECT idx FROM blocker WHERE ip=? /); $sth->execute($client_ip); my $rv = $sth->rows;
$sth= $dbh->prepare(qq/
INSERT INTO checks (mail_from,ip,reverse_dns,country_from,country_ip,approval,bulk_check,blist,sequence) VALUES (?,?,?,?,?,?,?,?,NOW())
/);
$sth->execute($host,$client_ip,$hostname,$checkcountry,$country,$compare,$allowed,$rv);
$dbh->disconnect();
if ($compare!=0 && $allowed==1 && $rv<1)
{
return (OK) ;
} else {
return (DENY);
}
}>> NOTE add "check_country" to config/plugins after check_earlytalker and before count_unrecognized_commands
>> mailstats.php
<table border="1" cellspacing="0" cellpadding="3"> <?php
$conn=mysql_connect('localhost','username','password');
$db=mysql_select_db('mailer');if (strlen($_REQUEST['bl'])>0)
{
$sql = "INSERT INTO blocker (idx,ip,sequence) VALUES (NULL,'".$_REQUEST['bl']."',NOW())";
@mysql_query($sql,$conn);
}
$sql = "SELECT COUNT( DISTINCT ( mail_from ) ) , ip
FROM `checks`
WHERE approval =1 AND bulk_check =1 AND DATE_SUB( CURDATE( ) , INTERVAL 1 DAY ) <= sequence
GROUP BY ip
ORDER BY `COUNT( DISTINCT ( mail_from ) )` DESC";
$result = @mysql_query($sql,$conn);
while ([EMAIL PROTECTED]($result))
{
if ($row[0]>1)
{
$sql = "SELECT * FROM blocker WHERE ip='".$row[1]."'";
[EMAIL PROTECTED]($sql,$conn);
$block=0;
if (@mysql_num_rows($xresult)>0)
{
echo '<tr><td>'.$row[0].'</td><td>'.$row[1].'</td><td><strong>BLOCKED</strong></td></tr>';
$block=1;
} else {
echo '<tr><td>'.$row[0].'</td><td><a href="mailstats.php?bl='.$row[1].'">'.$row[1].'</a></td><td> </td></tr>';
}
@mysql_free_Result($xresult);
if ($block==0)
{
$sql = "SELECT DISTINCT(mail_from) FROM checks WHERE ip='".$row[1]."'";
$xresult = @mysql_query($sql,$conn) or die($sql);
while ([EMAIL PROTECTED]($xresult))
{
echo '<tr><td colspan="2"> </td><td>'.$xrow[0].'</td></tr>
';
}
@mysql_free_Result($xresult);
}
}
}
@mysql_free_result($result);
@mysql_close($conn); ?> </table>
