Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL check if an IP-address is in range?

Tags:

mysql

I have a table that contains two columns ipStart and ipEnd

These two fields contain a range of ip address. For example:

`ipStart` = 193.235.18.0

and

ipEnd = 193.235.21.255

I want to be able to select the row with an ip that is within the range, for example:

193.235.19.255

Is this possible?

like image 771
Alosyius Avatar asked Jun 12 '14 08:06

Alosyius


People also ask

How do I compare IP addresses?

The easiest way to determine the range is to convert the IP into a decimal format and then do the ordinary comparing statements necessary. You can do this by assigning a weight for each part of the IP. Each part has a maximum value of 255. Thus, the best weight you can choose is 256.

How do I find the IP address of a MySQL database?

The SQL query SHOW VARIABLES WHERE Variable_name = 'hostname' will show you the hostname of the MySQL server which you can easily resolve to its IP address. Will give you the port number. You can find details about this in MySQL's manual: https://dev.mysql.com/doc/refman/8.0/en/show-variables.html.

What is data type for IP address in MySQL?

MySQL. While MySQL doesn't have a data type for IP addresses, it's still easy to compare IPs in MySQL. Using inet_aton you can convert your IP addresses to integers before you compare.


2 Answers

Try this

SELECT *
FROM TABLE_NAME
WHERE (INET_ATON("193.235.19.255") BETWEEN INET_ATON(ipStart) AND INET_ATON(ipEnd));
like image 98
Sadikhasan Avatar answered Oct 28 '22 13:10

Sadikhasan


To explain converting an ip address to a number which a few answers have relied on (and which I agree with).

The ip address needs to be treated as one 32 bit number rather than 4 8 bit numbers

For example the ip address

193.235.18.0

converted to binary is:-

11000001.11101011.00010010.00000000

Which you translate into (ie, take the dots out):-

11000001111010110001001000000000

Working that out you get:-

1 * 2147483648 = 2147483648 
1 * 1073741824 = 1073741824 
0 * 536870912 = 0
0 * 268435456 = 0
0 * 134217728 = 0
0 * 67108864 = 0
0 * 33554432 = 0
1 * 16777216 = 16777216 
1 * 8388608 = 8388608 
1 * 4194304 = 4194304 
1 * 2097152 = 2097152 
0 * 1048576 = 0
1 * 524288 = 524288 
0 * 262144 = 0
1 * 131072 = 131072 
1 * 65536 = 65536 
0 * 32768 = 0
0 * 16384 = 0
0 * 8192 = 0
1 * 4096 = 4096 
0 * 2048 = 0
0 * 1024 = 0
1 * 512 = 512 
0 * 256 = 0
0 * 128 = 0
0 * 64 = 0
0 * 32 = 0
0 * 16 = 0
0 * 8 = 0
0 * 4 = 0
0 * 2 = 0
0 * 1 = 0

Adding those together you get 3253408256

You an short cut that a bit by treating the original IP address as a base 256 number. So you have 0 units, 18 of 256s, 235 of 65536 (ie, 256 * 256) and 193 of (ie, 256 * 256 * 256)

0 * 1 + 18 * 256 + 235 * 256 * 256 + 193 * 256 * 256 * 256

INET_ATON function that is mentioned does this for you.

like image 22
Kickstart Avatar answered Oct 28 '22 11:10

Kickstart