[Raw Msg Headers][Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

RE: Own db of blocked IPs

Dear all,

Thank's for the RBL thread. I have given it a careful thought, but decided
that the solution with the separate RBL server is too troublesome and heavy
- I just want to block set of IPs! First, SQL based DNS servers do not come
in hundreds on the market ;-) Second, the SQL approach gives me easier
management control and more flexibility. Not only I can store whitelists,
but also - for example - later on I can store a response to the client, like
when he got into the database, and why. I know, this could go to the TXT
record, but somehow SELECT syntax is more convincing to me ;-) Also, more
people know how to handle SQL than DNS servers, thus more people can mantain
the database. This I find most attractive. Creating PHP web front-ends to
mysql is very easy, and I need a tool to present the CustomerCare office
current state of the DB in case of complaints, as well as the history of

BTW, as Robert smartly spotted (czesc!), we are talking about Onet.pl SMTP
servers, currently we receive few hundreds of mails per second (we have few
millions of mail accounts) and - unfortunately - about the latest worms
avaliable on the market. Today I've observed up to 10x more SMTP connections
to our servers, no thanks to the latest W32/Mydoom.A worm. The only tactics
I've managed to come up with is to recognize the first mail with virus and
block the source IP for several minutes. Detailed description follows:

Small perl program traces the smtpserver logs online, and when the virus is
spotted, the row with IP is inserted into table, together with "first_entry"
timestamp and counter set to 1. When new virus from the same IP appears, the
counter is incremented and the "latest_timestamp" timestamp is set to now().
Every few minutes cron job purges records older than XX minutes and moves
copy to the "history" table.

| Field            | Type         | Null | Key | Default             | Extra
| first_entry      | datetime     |      |     | 0000-00-00 00:00:00 |
| latest_timestamp | datetime     |      |     | 0000-00-00 00:00:00 |
| source_host      | varchar(100) |      |     |                     |
| ip               | varchar(15)  |      | PRI |                     |
| count            | int(11)      |      |     | 0                   |

The smtpserver itself, right after fork, checks whether the IP is in the

select * from blocked where id='xx.yy.zz.ww'

The patch (very lousy, I've just written it and tested lightly, more
polishing is needed) is below (against 2.99.55). The connection under MySQL
is very fast, and the lookup is also extermely quick (IP here is the primary
key and this is innodb table). Looking into smtpserver sources I've found
that writing anything to the socket before the fork is dangerous, as this
might block main smtpserver. So I resolve to wait until fork, though this
costs much more. The "disconnect" procedure itself is just plain copy of the
code responsible for disconnecting when more than N connections from the
same source IP is made to the system.

Please, comment on my idea, code and your experiences with such harsh virus
treatment (rejecting IPs for serveral minutes). Any ideas on how to build
dynamic whitelists, so that big relays do not get rejected (this would be

Also, is the 450 SMTP return code appropirate here? No message designated to
my site should be rejected by remote relay due to me saying this 450 error -
once it got the letter, it should try to deliver it to me every several
minutes, though I will refuse it from time to time. No letter should be send
back to the sender. So in the end all messages will reach me anyway, but on
my terms, and in the meantime real customers can connect (right now because
of the virus connections they often get: try later, too many connections to
the system). Wormed message will be dropped in the later stage by antyvirus
software, the real ones might just get delayed. 


*** smtpserver.c.orig   Tue Jan 27 22:52:19 2004
--- smtpserver.c        Tue Jan 27 23:37:40 2004
*** 12,17 ****
--- 12,18 ----

  #include "smtpserver.h"
+ #include <mysql/mysql.h>

  const char *VerbID = "ZMailer SMTP server %s";
  const char *Copyright = "Copyright 1990 Rayan S. Zachariassen";
*************** char **argv;
*** 1302,1307 ****
--- 1303,1363 ----
              exit(0);  /* Now exit.. */
+           //MK: check and reject the source IP in MySQL db
+           if (1) {
+             MYSQL mysql;
+             int len;
+             char msg[300];
+             char ihostaddr[20];
+             if(SS.ihostaddr[0]=='['){ //get rid of [ ] around IP
+               strcpy(ihostaddr,SS.ihostaddr+1);
+               ihostaddr[strlen(ihostaddr)-1]=0;
+             }else{ //can this happen anyway? I don't know, but just in
+               strcpy(ihostaddr,SS.ihostaddr);
+             }
+             sprintf(msg,"select * from blocked where ip='%s'",ihostaddr);
+             mysql_init(&mysql);
+               zsyslog((LOG_INFO, "failed to connect to mysql blocked-IP
+             }else{
+               // zsyslog((LOG_INFO, "check query: %s", msg));
+               if(mysql_query(&mysql, msg)){
+                 zsyslog((LOG_INFO, "Failed to execute query: \"%s\".
Error: %s\n",
+                       msg,mysql_error(&mysql)));
+               }else{
+                 MYSQL_RES *mysqlres;
+                 mysqlres = mysql_store_result(&mysql);
+                 if(mysqlres){
+                   if(mysql_num_rows(mysqlres)){ //the query returned some
+                     sprintf(msg, "450-Z tego adresu przyszlo w ostatnim
czasie zbyt duzo wirusow.\r\n450-Chwilowo nie przyjmiemy z tego adresu %s
nowych listow\r\n450-too many viruses received recently from this IP
%s\r\n", SS.ihostaddr,SS.ihostaddr);
+                     len = strlen(msg);
+                     if (write(msgfd, msg, len) != len) {
+                       sleep(2);
+                       exit(1);        /* Tough.. */
+                     }
+                     strcpy(msg, "450 Come again later\r\n");
+                     len = strlen(msg);
+                     write(msgfd, msg, len);
+                     close(0); close(1); close(2);
+ #if 1
+                     sleep(2); /* Not so fast!  We need to do this to
+                                  avoid (as much as possible) the child
+                                  to exit before the parent has called
+                                  childregister() -- not so easy to be
+                                  100% reliable (this isn't!) :-( */
+ #endif
+                     mysql_close(&mysql);
+                     exit(0);  /* Now exit.. */
+                   }
+                   mysql_free_result(mysqlres);
+                 }
+                 mysql_close(&mysql);
+               }
+             }
+           }
            smtpserver(&SS, 1);
            /* Expediated filehandle closes before
               the mandatory sleep(2) below. */ 
To unsubscribe from this list: send the line "unsubscribe zmailer" in
the body of a message to majordomo@nic.funet.fi