Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there way to match IP with IP+CIDR straight from SELECT query?

Tags:

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

like image 570
raspi Avatar asked Feb 27 '09 17:02

raspi


People also ask

How do I find the CIDR of an IP address?

The CIDR number is typically preceded by a slash “/” and follows the IP address. For example, an IP address of 131.10. 55.70 with a subnet mask of 255.0. 0.0 (which has 8 network bits) would be represented as 131.10.

Does IP match CIDR?

An IP address is part of a CIDR block and is said to match the CIDR prefix if the initial n bits of the address and the CIDR prefix are the same. An IPv4 address is 32 bits so an n-bit CIDR prefix leaves 32 − n bits unmatched, meaning that 232−n IPv4 addresses match a given n-bit CIDR prefix.

What is the fastest way to calculate CIDR?

The formula to calculate the number of assignable IP address to CIDR networks is similar to classful networking. Subtract the number of network bits from 32. Raise 2 to that power and subtract 2 for the network and broadcast addresses. For example, a /24 network has 232-24 - 2 addresses available for host assignment.


2 Answers

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.

like image 80
Guss Avatar answered Nov 22 '22 09:11

Guss


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 
like image 24
Quassnoi Avatar answered Nov 22 '22 10:11

Quassnoi