src/org/ibex/mail/Graylist.java

   1 package org.ibex.mail;
   2 // todo: periodically flush out old graylist entries
   3
   4 import java.sql.*;
   5 import java.net.*;
   6 import java.io.*;
   7 import java.util.*;
   8 import java.sql.Timestamp;
   9
  10 public class Graylist extends SqliteDB {
  11
  12     public Graylist(String filename) throws SQLException {
  13         super(filename);
  14         SqliteTable whitelist = getTable("whitelist", "(ip unique)");
  15         SqliteTable graylist  = getTable("graylist",  "(ip,fromaddr,toaddr,date,PRIMARY KEY(ip,fromaddr,toaddr))");
  16         graylist.reap("date");
  17     }
  18
  19     public synchronized void addWhitelist(InetAddress ip) {
  20         try {
  21             PreparedStatement add    = conn.prepareStatement("insert or replace into 'whitelist' values(?)");
  22             add.setString(1, ip.getHostAddress());
  23             add.executeUpdate();
  24         } catch (SQLException e) { throw new RuntimeException(e); }
  25     }
  26
  27     public synchronized boolean isWhitelisted(InetAddress ip) {
  28         try {
  29             PreparedStatement check = conn.prepareStatement("select * from 'whitelist' where ip=?");
  30             check.setString(1, ip.getHostAddress());
  31             ResultSet rs = null;
  32             boolean ret;
  33             try {
  34                 rs = check.executeQuery();
  35                 ret = !rs.isAfterLast();
  36             } finally { close(rs); }
  37             return ret;
  38         } catch (SQLException e) { throw new RuntimeException(e); }
  39     }
  40
  41     public synchronized long getGrayListTimestamp(InetAddress ip, String from, String to) {
  42         try {
  43             PreparedStatement check =
  44                 conn.prepareStatement("select date from graylist where ip=? and fromaddr=? and toaddr=?");
  45             check.setString(1, graylistAddress(ip));
  46             check.setString(2, from);
  47             check.setString(3, to);
  48             ResultSet rs = null;
  49             try {
  50                 rs = check.executeQuery();
  51                 if (rs.isAfterLast()) return 0;
  52                 return rs.getTimestamp(1).getTime();
  53             } finally { close(rs); }
  54         } catch (SQLException e) { throw new RuntimeException(e); }
  55     }
  56
  57     public synchronized void setGrayListTimestamp(InetAddress ip, String from, String to, long date) {
  58         try {
  59             PreparedStatement check =
  60                 conn.prepareStatement("insert or replace into graylist (ip,fromaddr,toaddr,date) values(?,?,?,?)");
  61             check.setString(1, graylistAddress(ip));
  62             check.setString(2, from);
  63             check.setString(3, to);
  64             check.setTimestamp(4, new Timestamp(date));
  65             check.executeUpdate();
  66         } catch (SQLException e) { throw new RuntimeException(e); }
  67     }
  68
  69     //
  70     // this is mostly to deal with comcast's pathetic retry policy and
  71     // rotating pool of outbound servers
  72     // see: http://lists.puremagic.com/pipermail/greylist-users/2006-November/001255.html
  73     //
  74     private static String graylistAddress(InetAddress ipa) {
  75         byte[] ip = ipa.getAddress();
  76         return
  77             (ip[0] & 0xff)+"."+
  78             (ip[1] & 0xff)+"."+
  79             (ip[2] & 0xff)+"."+
  80             ".0";
  81     }
  82
  83 }
  84