On 07/10/2010 20:57, Brian Spraker wrote:
> Is there a way to blacklist IP ranges using MySQL?
>
> I originally setup one file with all of the ranges in them such as:
>
> 41.0.0.0/8
> 58.0.0/8
>
> Each IP range on a different line in the "hosts_blacklist" file that was in
> the
> same directory as the Exim configuration file.
>
> This has worked well for a few years without any kind of problem.
>
> However, I am wanting to move this to a MySQL table. I have moved the domain
> blacklist/whitelist, sender blacklist/whitelist, and sender domains
> blacklist/whitelist to MySQL and those seem to be working OK.
>
> However, the hosts_blacklist does not work now. I'm not too sure why it
> wouldn't work; it is just a different method for storing the data.
>
> The reason I'm moving to database storage is because I am making a series of
> sites that I can use to quickly update the lists instead of logging into the
> server and updating the file. In addition, because I have two servers that
> are
> redundant, I have to update the file on each server. The MySQL database is
> setup with master-master replication so if I make the change on one server,
> it
> updates on the other at the same time - much more convenient.
>
> This is what works when using the flat file:
>
> hosts = ${if exists{CONFDIR/host_blacklist}{CONFDIR/host_blacklist}{}}
>
> Now I have changed it to this:
>
> hosts = ${lookup mysql{SELECT host FROM exim_host_blacklist }}
>
> No longer works now.
Rather than storing 41.0.0.0/8 in mysql, store two "ip numbers" covering
the start and end of the range. Notice I said "ip numbers" and not "ip
addresses" Eg, for 41.0.0.0/8:
Figure out the start and end ip addresses:
m...@snail:~$ netmask -r 41.0.0.0/8
41.0.0.0-41.255.255.255 (16777216)
m...@snail:~$
So 41.0.0.0 and 41.255.255.255
Now convert those to ip numbers:
mysql> SELECT INET_ATON('41.0.0.0');
+-----------------------+
| INET_ATON('41.0.0.0') |
+-----------------------+
| 687865856 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT INET_ATON('41.255.255.255');
+-----------------------------+
| INET_ATON('41.255.255.255') |
+-----------------------------+
| 704643071 |
+-----------------------------+
1 row in set (0.00 sec)
mysql>
So I'd have two int columns in the mysql db called start_ip and end_ip,
and then I'd do my mysql select like this:
hosts = ${lookup mysql{SELECT '${quote_mysql:$sender_host_address}' FROM
table WHERE INET_ATON('${quote_mysql:$sender_host_address}')>=start_ip
AND INET_ATON('${quote_mysql:$sender_host_address}')<=end_ip LIMIT 1}}
You can convert ip numbers back to ip addresses in MySQL using INET_NTOA:
mysql> SELECT INET_NTOA(687865856);
+----------------------+
| INET_NTOA(687865856) |
+----------------------+
| 41.0.0.0 |
+----------------------+
1 row in set (0.00 sec)
mysql>
If you're using IPv6, you're on your own ;)
--
Mike Cardwell - Perl/Java/Web developer, Linux admin, Email admin
Read my tech Blog - https://secure.grepular.com/
Follow me on Twitter - http://twitter.com/mickeyc
Hire me - http://cardwellit.com/ http://uk.linkedin.com/in/mikecardwell
--
## List details at http://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/
