Is there way to match IP with IP+CIDR straight fro

问题:

Something like

SELECT COUNT(*) AS c FROM BANS WHERE typeid=6 AND (SELECT ipaddr,cidr FROM BANS) MATCH AGAINST 'this_ip';

So you don't first fetch all records from DB and then match them one-by one.

If c > 0 then were matched.

BANS table:

id int auto incr PK
typeid TINYINT (1=hostname, 4=ipv4, 6=ipv6)
ipaddr BINARY(128)
cidr INT
host VARCHAR(255)

DB: MySQL 5

IP and IPv type (4 or 6) is known when querying.

IP is for example ::1 in binary format

BANNED IP is for example ::1/64

回答1:

Remember that IPs are not a textual address, but a numeric ID. I have a similar situation (we're doing geo-ip lookups), and if you store all your IP addresses as integers (for example, my IP address is 192.115.22.33 so it is stored as 3228767777), then you can lookup IPs easily by using right shift operators.

The downside of all these types of lookups is that you can't benefit from indexes and you have to do a full table scan whenever you do a lookup. The above scheme can be improved by storing both the network IP address of the CIDR network (the beginning of the range) and the broadcast address (the end of the range), so for example to store 192.168.1.0/24 you can store two columns:

network     broadcast
3232235776, 3232236031 

And then you can to match it you simply do

SELECT count(*) FROM bans WHERE 3232235876 >= network AND 3232235876 <= broadcast

This would let you store CIDR networks in the database and match them against IP addresses quickly and efficiently by taking advantage of quick numeric indexes.

Note from discussion below:

MySQL 5.0 includes a ranged query optimization called "index merge intersect" which allows to speed up such queries (and avoid full table scans), as long as:

• There is a multi-column index that matches exactly the columns in the query, in order. So - for the above query example, the index would need to be (network, broadcast).
• All the data can be retrieved from the index. This is true for COUNT(*), but is not true for SELECT * ... LIMIT 1.

MySQL 5.6 includes an optimization called MRR which would also speed up full row retrieval, but that is out of scope of this answer.

回答2:

IPv4 addresses, network addresses and netmasks are all UINT32 numbers and are presented in human-readable form as "dotted-quads". The routing table code in the kernel performs a very fast bit-wise AND comparison when checking if an address is in a given network space (network/netmask). The trick here is to store the dotted-quad IP addresses, network addresses and netmasks in your tables as UINT32, and then perform the same 32-bit bit-wise AND for your matching. eg

SET @test_addr = inet_aton('1.2.3.4');
SET @network_one = inet_aton('1.2.3.0');
SET @network_two = inet_aton('4.5.6.0');
SET @network_netmask = inet_aton('255.255.255.0');

SELECT (@test_addr & @network_netmask) = @network_one AS IS_MATCHED;
+------------+
| IS_MATCHED |
+------------+
|          1 |
+------------+

SELECT (@test_addr & @network_netmask) = @network_two AS IS_NOT_MATCHED;
+----------------+
| IS_NOT_MATCHED |
+----------------+
|              0 |
+----------------+

回答3:

For IPv4, you can use:

SET @length = 4;

SELECT  INET_NTOA(ipaddr), INET_NTOA(searchaddr), INET_NTOA(mask)
FROM  (
  SELECT
        (1 << (@length * 8)) - 1 & ~((1 << (@length * 8 - cidr)) - 1) AS mask,
        CAST(CONV(SUBSTR(HEX(ipaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS ipaddr,
        CAST(CONV(SUBSTR(HEX(@myaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS searchaddr
  FROM  ip
) ipo
WHERE ipaddr & mask = searchaddr & mask

回答4:

Hmmm. You could build a table of the cidr masks, join it, and then compare the ip anded (& in MySQL) with the mask with the ban block ipaddress. Would that do what you want?

If you don't want to build a mask table, you could compute the mask as -1 << (x-cidr) with x = 64 or 32 depending.

回答5:

MySQL: Convert IP Range to CIDR

I spent several hours searching for a way to use MySQL to take a range of IP addresses and output a CIDR formatted address that covers the IP range suitable for maintaining an IP blacklist.

The Specifics of My Environment and Requirements I utilize OpenWeb Analytics to log traffic on my website and several that I manage. I developed a process to extract distinct IP addresses logged by OWA and then merge geographic data elements onto the records that are then maintained in a custom table. Armed with this table of IP-to-Location data provides an ability to report on hits from unwanted sources, or sources irrelevant to my local business -- China, Japan, Korea, Russia, etc. This has led to a growing list of IP addresses; many of which fall within the same network. To ease the maintenance requirements for my server's .htaccess file it becomes beneficial to be able to record blacklisted IP addresses in CIDR format. That then leads to the need to be able and produce the CIDR from the already logged IP addresses.

The MySQL Approach Most web hosts provide access to MySQL databases. However, few if any allow the ability to create database functions. This complicated the coding a bit.

Sample SQL available at http://blog.watsoninfotech.com/2012/12/mysql-convert-ip-range-to-cidr.html