How to select ip address in between the ip range from database using query?

Question

I have ip_table as below. Suppose my IP address is 1.22.13.15. But in my table this exact IP address is not available, This IP address is in the range between 0/24. How can I get the geoname_id for this IP address? Can someone assist me? Or there is any other way?

+---------------+------------+
| ip_adr        | geoname_id |
+---------------+------------+
| 1.22.13.0/24  |    1279233 |
| 1.22.130.0/23 |    1273294 |
| 1.22.132.0/23 |    1267995 |
| 1.22.134.0/23 |    1253405 |
| 1.22.136.0/21 |    1269750 |
+---------------+------------+

score 1 · Answer 1 · edited Feb 06 '18 at 10:51

1

You can use RegEx in SQL to identify the format of IP and check if the selected IP is in the range you give.

Example:

SELECT *
FROM ips
WHERE (ip_addr REGEXP '^[0-9]{1}\.[0-9]{2}\.[0-9]{2,3}\.0\/(24)')

edited Feb 06 '18 at 10:51

Salim Ibrohimi

1,351
3
17
35

answered Feb 06 '18 at 10:27

Andrei Todorut

4,260
2
17
28

I want to check IP address for multiple users. – Talk2Nit Feb 06 '18 at 10:32
you select first the users table left join the ip table ? – Andrei Todorut Feb 06 '18 at 10:38
the users table contains an field with ip range ? – Andrei Todorut Feb 06 '18 at 10:38
Don't have user table. Just have above table. – Talk2Nit Feb 06 '18 at 11:01

score 1 · Answer 2 · answered Feb 06 '18 at 11:00

As far as I know there is no MySQL function that can deal with the range syntax for IP adresses directly. But MySQL has a function to convert IP adresses in a bigint number: INET_ATON()

one possible solution would be to extend the table structure in such a way that you can use simple queries:

|ip_adr_range_start  |ip_adr_range_end | geoname_id|
|[bigint]            |[bigint]         | [int?]

the first row would be inserted like this:

INSERT INTO your_table VALUES (INET_ATON('1.22.13.0'), INET_ATON('1.22.13.255'), 1279233);

a query could be:

SELECT * from your_table 
WHERE ip_adr_range_start < INET_ATON('1.22.13.15') 
AND ip_adr_range_end > INET_ATON('1.22.13.15')

ideas taken from here: https://dba.stackexchange.com/questions/36451/convert-the-ip-address-range-to-two-bigint-for-faster-lookup

score 1 · Answer 3 · answered Feb 06 '18 at 12:05

try Substring_index

select left('1.22.13.0/24',length('1.22.13.0/24')- length(substring_index('1.22.13.0/24','.',-1))) part1,substring_index(substring_index('1.22.13.0/24','.',-1),'/',1) as range_start,substring_index('1.22.13.0/24','/',-1) range_end

the output will be

part1 | range_start| range_end

1.22.13.| 0 | 24

then you can compare your value accordingly

score 0 · Answer 4 · answered Feb 06 '18 at 10:43

I guess this table mean the ip in range between 1.22.13.0-1.22.13.255 will match with 1279233 geonameid. You need to define IP range by subnet provided. So, to get a geonameid with specific ip, just ask it what are its IP and subnet? How to compare the specific ip address with ip range and compare with geonameid? More info at How to check an IP address is within a range of two IPs in PHP?

Talk2Nit · Answer 5 · 2018-02-07T04:50:21.380

0

I got the output using the following query.

I split my IP address into two parts. Like 1.22.13 and 15 passed these in where condition.

SELECT ip_adr, geoname_id
FROM ip_table 
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(ip_adr, '/', 1 ), '.', 3) = 
'1.22.13' AND
SUBSTRING_INDEX(SUBSTRING_INDEX(ip_adr, '/', 1 ), '.', -1) <= '15';

edited Feb 07 '18 at 04:50

answered Feb 06 '18 at 12:50

Talk2Nit

1,115
3
22
38

score 0 · Answer 6 · edited Oct 28 '20 at 12:26

0

with ipaddressTBL as(
    select *,  INET_ATON(ip) as ipaddress from `routers` 
) select * from ipaddressTBL 
where ipaddress BETWEEN INET_ATON('32.248.135.197') AND INET_ATON('165.9.105.127')

edited Oct 28 '20 at 12:26

Mark Rotteveel

100,966
191
140
197

answered Oct 28 '20 at 11:34

Ashish Nimrot

1
2

1

Please add a description note. How this answer helps op? – Parth Raval Oct 28 '20 at 12:29

How to select ip address in between the ip range from database using query?

6 Answers6