How do I store (and find) a string with a wildcard in MySQL?

Question

I need to store an IP address with a wildcard in it, like 10.0.%.1. Then, I want anyone with an IP address in the allowed range (10.0.0-255.1) to be found from my query.

Could you guys please help me? I've tried storing 10.0.%.1 but can't find it with a LIKE query (I don't even know if that's the way to do it - probably not).

Thanks in advance.

Answer 1

Convert IP addresses to 32 bit integers and make a column for the address and the mask. You can use INET_NTOA() and INET_ATON() functions. Your 10.0.%.1 range can be expressed as network 10.0.0.1 with a mask of 255.255.0.255. To see if a given IP address matches this network, you apply the mask to the IP address, and compare it to the network address. If they match, the address is in the network. For example, if you want to test 10.0.4.1, you apply the mask to it using bitwise AND:

10.0.4.1 & 255.255.0.255 = 10.0.0.1

This matches your network address, so this IP is in the network.

You can store this network in your table like this (ip and mask are integers):

CREATE TABLE networks (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, ip INT, mask INT);

INSERT INTO networks (ip, mask)
VALUES (INET_ATON('10.0.0.1'), INET_ATON('255.255.0.255'));

You can find if a given IP (10.0.23.1) matches like this:

SELECT * FROM networks
WHERE ip & mask = INET_ATON('10.0.23.1') & mask

This will select the IPs as integers though, if you want them human readable:

SELECT id, INET_NTOA(ip) AS ipstring, INET_NTOA(mask) AS maskstring
FROM networks
WHERE ip & mask = INET_ATON('10.0.23.1') & mask

This allows you to test if a single given IP address matches a network. If you want to test a range of IPs, you'd have to apply the mask to each one in the range and test each one as a above. You can do it in a single query, it'll just have to list every IP address you're testing.