Exim DNSBL Caching with MySQL

v0.0.1	-	Initial version - Evren Yurtesen <yurtesen@ispro.net.tr>

You should first create the sql table:

CREATE TABLE dnsbllist (
  firstdate datetime default NULL,
  lastdate datetime default NULL,
  ip varchar(15) NOT NULL default '',
  timeslisted smallint(2) UNSIGNED NOT NULL default '0',
  timesconnected smallint(2) UNSIGNED NOT NULL default '0',
  blacklisted tinyint(1) UNSIGNED NOT NULL default '0',
  UNIQUE KEY ip (ip)
) TYPE=MyISAM COMMENT='DNSBL List';

and create a user for exim:

GRANT SELECT,UPDATE, INSERT ON database.dnsbllist TO 'eximuser'@'localhost' IDENTIFIED BY 'eximpass';

Then put the following lines to exim's conf file, somewhere at the beginning.
These are the SQL statements which will be used:

hide mysql_servers = localhost/database/eximuser/eximpass

# Seconds before we expire the entries in dnsbl DB table
DNSBLLIST_EXPIRE_TIME = 2*24*60*60
# Inserts the host IP as whitelisted into DB
DNSBLLIST_WHITELIST_INSERT = INSERT IGNORE INTO dnsbllist \              
        (blacklisted,firstdate,lastdate,ip) \
        VALUES('0',now(), now(),'${quote_mysql:$sender_host_address}');  
# If the host IP is not blacklisted on re-check. Updates the 'lastdate' and set the host not blacklisted
DNSBLLIST_UPDATE_WHITELIST = UPDATE dnsbllist SET \
         lastdate=now(),blacklisted=0 WHERE ip='${quote_mysql:$sender_host_address}'
# Updates the 'timeslisted' field on re-listings of blacklisted host IPs    
DNSBLLIST_TIMESLISTED_UPDATE = UPDATE dnsbllist SET \
        lastdate=now(),blacklisted=1,timeslisted=timeslisted+1 \            
        WHERE ip='${quote_mysql:$sender_host_address}'
# Updates the 'timesconnected' field on connection attempts from blacklisted host IPs
DNSBLLIST_TIMESCONNECTED_UPDATE = UPDATE dnsbllist SET \
        timesconnected=timesconnected+1 \
        WHERE ip='${quote_mysql:$sender_host_address}'
# Queries if the host IP is listed in DB
DNSBLLIST_QUERY  = SELECT blacklisted,ip,from_unixtime(DNSBLLIST_EXPIRE_TIME+unix_timestamp(lastdate)) \
        AS expires, IF(DNSBLLIST_EXPIRE_TIME+unix_timestamp(lastdate)<unix_timestamp(now()),'1','0') \
        AS expired FROM dnsbllist where ip='${quote_mysql:$sender_host_address}'

Next we need to add the ACL statements, we would like to deny blacklisted hosts at
connect time so we should add the following to acl_check_connect, it doesnt exist in
default exim conf file. we should define it first in main configuration section:

acl_smtp_connect	= acl_check_connect

Then in ACL section of the exim conf file, we should add the following.
Note: accept statement should be after all your conditions etc. at the end
of the acl_check_connect
This will stop offenders at connect time:

acl_check_connect:

  # Sets acl_c0 with ip,blacklist info, we will use the result in many ACLs.
  warn set acl_c0 = ${lookup mysql{DNSBLLIST_QUERY}{$value}{fail}}            
  
  # Insert the IP to database as whitelisted if it is not in there yet      
  condition       = ${if eq {${extract{ip}{$acl_c0}}} \
                  {$sender_host_address} {yes} {${lookup mysql{DNSBLLIST_WHITELIST_INSERT} }}}
  
  # If host IP is listed in DB and not expired AND if it is blacklisted, then deny.
  drop  condition = ${if and { {eq{${extract{ip}{$acl_c0}}}{$sender_host_address}} \
                    {eq{${extract{blacklisted}{$acl_c0}}}{1}} {eq{${extract{expired}{$acl_c0}}}{0}} } {yes}{no} }
  # After denying the host, updates the number of times a blacklisted host tried to connect
        condition = ${lookup mysql{DNSBLLIST_TIMESCONNECTED_UPDATE}{yes}{no}}
        message   = Your host's IP address is blacklisted.\n \
                    ${extract{ip}{$acl_c0}} will be retested\n \
                    after ${extract{expires}{$acl_c0}} \n \
                    current date is $tod_log
        log_message = DNSBL Offender

  # finally accept all the rest                       
  accept  

Now we set DNSBL checking at acl_check_rcpt ACL:

  # check DNSBL, and compose a message if needed.
  drop    message       = Your message has been rejected on $tod_full.\n \
                        Permanent failure: $local_part@$domain did NOT receive your message.\n \
                        The reason is: $sender_fullhost is listed on a dns block list.\n \
                        $dnslist_domain returned this information:\n \
                        $dnslist_text\n \           
                        Visit the link above for more information.
        # If we previously checked this IP then stop executing ACL and allow access if it is whitelisted and
        # expiration time didn't pass
        !condition      = ${if and { {eq{${extract{ip}{$acl_c0}}}{$sender_host_address}} \
                          {eq{${extract{blacklisted}{$acl_c0}}}{0}} {eq{${extract{expired}{$acl_c0}}}{0}} } {yes}{no} }
        # Updates the host IP in DB and sets it as whitelisted and it updates lastdate
        # we will change it to blacklisted if it is found in dnsbl            
        condition       = ${lookup mysql{DNSBLLIST_UPDATE_WHITELIST}{1}}
        dnslists        = sbl-xbl.spamhaus.org : bl.spamcop.net : dnsbl.sorbs.net
        # If the host IP is listed in DNSBL, update the timeslisted and datelast values.
        condition       = ${lookup mysql{DNSBLLIST_TIMESLISTED_UPDATE}{1}}
        log_message     = DNSBL $dnslist_domain

Of course you can change the 'dnslists'etc..

Notes: One should purge the old entries in the database periodically. It would be best to use lastdate field
for age calculation when deleting the entries.

Please report any mistakes which may be in this document to:
Evren Yurtesen
yurtesen@ispro.net.tr