Wildcard IP Banning with MySQL

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

Quick'n'Dirty, but cannot use proper indexes:

SELECT ip FROM ip_blacklist WHERE ? LIKE REPLACE(ip,'*','%') LIMIT 1

Answer 2

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))

Answer 3

This is trickier if you want to ban subnets

Notes:

• A "wildcard" mapping should use 0 (eg 42.21.58.0) which defines that subnet
• the .0 may not be the subnet because of CIDR (could .128, .192 etc)

So:

• Store IP as a 4 byte binary or unsigned int
• Store the subnet mask as a binary (or uint) 4 for subnet blacklisting
• Look at INET_NTOA and INET_ATON for translating IP addresses

Then the WHERE clause becomes

WHERE ip = @ip   --whole IP
      OR
      (ip & mask = @ip) --subnet

If you make the mask 0xffffffff for exact IP addresses then you can always do ip & mask = @ip, with ip & mask as a computed column

Also, you have IPv6 to think of too

Answer 4

My suggestion might make some cringe, but you seem to be going for nontraditional in this project so here it goes: Parse each IP from the database into a regex that can be compared against the user's IP. Example:

<?php
//Fetch IP's and begin to contruct regex
$regex = array();
while($arr = mysql_fetch_array($result)) {
    $regex[] = '('.$arr['ip'].')';
}

$regex = implode('|', $regex); //Regex now becomes (1.1.1.1)|(2.2.2.2)|etc.
$regex = str_replace('.', '\.', $regex); //Escape dots for regex
$regex = str_replace('*', '((25[0-5])|(2[0-4]\d)|(1\d\d)|(\d\d?))', $regex); //Deal with wildcards

$httpVars = array( 'HTTP_CLIENT_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_X_FORWARDED', 'HTTP_FORWARDED_FOR', 'HTTP_FORWARDED', 'REMOTE_ADDR' );
foreach( $httpVars as $httpVar ) {  //No hiding behind proxies
    if( isset( $IP = $_SERVER[$httpVar] ) ) {
        break;
    }
}

if(preg_match('/^'.$regex.'$/', $IP) != 0) {
    die(header('HTTP/1.1 403 Forbidden'));  //Magical regex says user should be banned
}
?>

And of course you can do much more with this. You could cache the regex to save querying the database on every request or even expand your wild card options to includes IP ranges.