Re: [exim] Drop messages at RCPT time based on IP/Subnet

On 16/08/14 12:00, Alex wrote:
> Is there an iplsearch equivalent that can do MySQL table lookups? I am
> really trying to shift away from flat files where possible and have
> everything in one central database.

Yes and no. It is possible to do similar lookups in mysql, but not in
N.N.N.N/N format. In this thinking, the database stores the start and
end IP address of ranges in number format.

With a recent version of MySQL*, the INET6_ATON() function will handle
and store appropriately both IPv6 and IPv4 addresses in a VARBINARY(16).
If you're IPv4 only, use INET_ATON() and INT UNSIGNED (32 bit integer
for me, check platform first).

SQL defined in a macro somewhere:
# No need for ${quote_mysql:$var} here.
SQL_NAUGHTYIPS=SELECT 'yes' FROM naughtyips WHERE \
start <= INET6_ATON('$sender_host_address') AND \
end >= INET6_ATON('$sender_host_address') LIMIT 1

The condition of that previously used ACL is then replaced with:
condition = ${lookup mysql{SQL_NAUGHTYIPS}{$value}{no}}

An eg simplified database table used here - double check the indexing as
I haven't tested this much.
CREATE TABLE naughtyips (
id INT UNSIGNED AUTO_INCREMENT NOT NULL,
start VARBINARY(16) NOT NULL,
end VARBINARY(16) NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX start_end (start, end) USING BTREE
) engine = InnoDB;

* I've moved to using a very recent version primarily because these
functions work as I need them to only in the latest. I don't know
exactly which version they started working exactly like this, but I'm
pretty sure it was the 5.6.x branch. I'm on 5.6.19 at present, but it
looks like 5.6.20 has come out since.