使用MySQL的通配IP禁止 [英] Wildcard IP Banning with MySQL

问题描述

我正在尝试使用MySQL在我的Web应用程序中实现IP禁止系统，我知道我可以使用.htaccess来做到这一点，但这对我来说并不整洁.

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)

在php中，我在数据库中查找客户端IP，以查看其是否被阻止:

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
}

现在..正常工作，但是我希望能够在数据库中输入通配符IP范围，例如:

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.*.*.*

该怎么做?

谢谢.

推荐答案

如果您总是一次一次检查一个IP地址，并且被禁止的范围永不相交，则应存储起点和终点要禁止使用数字格式的范围的地址.

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.

说，您想将192.168.1.0禁止为192.168.1.0/28的192.168.1.15.

您创建一个像这样的表:

You create a table like this:

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

，在其中插入范围:

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

然后检查:

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

要使查询高效，必须使用ORDER BY和LIMIT部分.

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

如前所述，它假定不相交的块和一个IP.

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

如果块相交(例如，同时禁止192.168.1.0/28和192.168.1.0/24)，则查询可能返回假阴性.

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.

如果您想一次查询多个IP(例如，用一长串IP地址更新一个表)，则此查询效率不高(MySQL不会在相关子查询中优化range)很好)

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)

在这两种情况下，您都需要将范围存储为LineString并使用空间索引进行快速搜索:

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

这篇关于使用MySQL的通配IP禁止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！