IP wildcard ban using MySQL

Question

I am trying to implement an IP blocking system in my web application using MySQL, I know that I can do it with .htaccess , but this does not match me.

Basically my current table:

 ip_blacklist(id, ip, date) 

and in php I look in the database for the IP client to see if it is blocked:

 $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 works fine, but I want to be able to enter IP wildcards in the database, for example:

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

How to do it?

Thanks in advance.

Answer 1

If you will always check one IP address at a time and your forbidden ranges never overlap, you must save the starting and ending addresses of the ranges to be denied in numerical format.

Let's say you want to ban 192.168.1.0 to 192.168.1.15 , which is 192.168.1.0/28 .

You create the table as follows:

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

enter range:

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

Successful query execution requires the ORDER BY and LIMIT parts.

This, as stated earlier, involves disjoint blocks and one IP at a time.

If the blocks intersect (for example, you forbid 192.168.1.0/28 and 192.168.1.0/24 at the same time), the request may return false negatives.

If you 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 a)

In both cases, you need to save your ranges as a LineString and use spatial indexes for a quick search:

 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 2

This is harder if you want to disable subnets.

Notes:

• Wildcard mapping should use 0 (for example, 42.21.58.0), which defines this subnet
• .0 may not be subnet due to CIDR (maybe .128, .192, etc.)

So:

• Save IP as 4-byte binary or unsigned int
• Save the subnet mask as binary (or uint) 4 for the subnet blacklist
• See 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 create a 0xffffffff mask for exact IP addresses, you can always do ip & mask = @ip , with ip & mask as the calculated column

Also, you have IPv6 to think too

Answer 3

Quick'n'Dirty, but cannot use the correct indexes:

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

Answer 4

My suggestion may make some cringe, but you seem to be going for unconventional purposes in this project, so here it is: Parse each IP from the database into a regular expression that can be compared with the user's IP address. Example:

 <?php //Fetch IP 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 that. You can cache the regex to save a database query for each query, or even extend the parameters of your wild card by including IP ranges.

Answer 5

convert wildcards from 42.21.*.* to 42.21.0.0 and vice versa when writing or reading records from the database. For efficiency (low memory and disk size, performance), save it as an integer, use INET_NTOA and INET_ATON for conversion.

when searching for abcd IP address:

 SELECT ip FROM ip_blacklist WHERE ip=INET_ATON('abcd') or ip=INET_ATON('abc0') or ip=INET_ATON('ab0.0') or ip=INET_ATON('a.0.0.0') 

Well, the last coincidence is probably stupid.

Remember to add indexes.