使用MySQL的通配符IP禁止

Question

I'm trying to implement an IP banning system into my web app using MySQL, i know i can do it using .htaccess but that's not neat to me.

Basically my current table is:

ip_blacklist(id, ip, date)

and in php i look up the database for the client IP to see if it's blocked or not:

$sql = "SELECT ip FROM ip_blacklist WHERE ip = ? LIMIT 1"
$query = $this->db->query($sql, array($ip));
if($query->num_rows() > 0){
 // Gotcha
}

Now.. this is working fine, but i want to be able to enter wildcard IP ranges in the database like:

42.21.58.*
42.21.*.*
53.*.*.*

How to do that?

Thanks in advance.

Answer 1

If you will always be checking one IP address at a time and your banned ranges never intersect, you should store the start and end addresses of the ranges to ban in numeric format.

Say, you want to ban 192.168.1.0 to 192.168.1.15 which is 192.168.1.0/28.

You create a table like this:

CREATE TABLE ban (start_ip INT UNSIGNED NOT NULL PRIMARY KEY, end_ip INT UNSIGNED NOT NULL)

, insert the range there:

INSERT
INTO    ban
VALUES  (INET_ATON('192.168.1.0'), INET_ATON('192.168.1.0') + POWER(2, 32 - 28) - 1)

then check:

SELECT  (
        SELECT  end_ip
        FROM    ban
        WHERE   start_ip <= INET_ATON('192.168.1.14')
        ORDER BY
                start_ip DESC
        LIMIT 1
        ) >= INET_ATON('192.168.1.14')

The ORDER BY and LIMIT parts are required for the query to be efficient.

This, as was stated before, assumes non-intersecting blocks and one IP at a time.

If the blocks intersect (for instance, you ban 192.168.1.0/28 and 192.168.1.0/24 at the same time), the query may return false negatives.

If you are want to query more than one IP at a time (say, update a table with a long list of IP addresses), then this query will be inefficient (MySQL does not optimize range in correlated subqueries well)

In both these cases, you should need to store your ranges as LineString and use spatial indexes for fast searches:

CREATE TABLE ban (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, range LINESTRING NOT NULL) ENGINE=MyISAM;

CREATE SPATIAL INDEX sx_ban_range ON ban (range);

INSERT
INTO    ban (range)
VALUES  (
        LineString
                (
                Point(INET_ATON('192.168.1.0'), -1),
                Point(INET_ATON('192.168.1.0') + POWER(2, 32 - 28) - 1), 1)
                )
        );

SELECT  *
FROM    ban
WHERE   MBRContains(range, Point(INET_ATON('192.168.1.14'), 0))