How to filter a range of IP addresses in a SQL query?

Question

I am working in a web apps, I made some checking/limitation when user trying to login my application. Now i need to make a white list function to pass these checking make use of user ip address.

I'm trying to write a sql statement to get matched IP address in order to achieve white list. If the sql return data then pass the checking , if not just continue checking.

However, whitelist table in database need to be support 192.168.* or 192.* or (*. *.1.1) .So it will return data and pass if the user ip is 192.X.X.X

SELECT * FROM whitelist WHERE ip_address = $ip;

my sql statement like this.

Answer 1

I agree with @arno comment. If you have limited value to check then use regex instead of database call. It will save you time.

But if you want to call database then I remember that MySql support regex in query also

SELECT 
    * 
FROM
    whitelist 
WHERE 
    ip_address REGEXP '^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$';

Above regex is to check all valid IP address. But you can change it based on your requirement.

Answer 2

There is no natural case when you need to match something like *.*.1.1.

IP addresses are mostly matched according to subnets in their CIDR notation. Because this is how networks are organized.

Even simpler, you can convert IP addresses to a long datatype using INET_ATON() and make simple matches using > and <.

Please refer to these solutions:

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

https://dba.stackexchange.com/questions/171044/determining-if-an-ip-is-within-an-ipv4-cidr-block

Answer 3

You can use the LIKE statment:

SELECT * FROM whitelist WHERE ip_address LIKE '192.168.%';

The % character allows any character to replace it, therefore the above query will return IP adress in the range 192.168.0.0 - 192.168.255.255, provided you indeed only have IP adresses in this field.