1

I have a large table containing 3 Million network addresses and their location info

basic fields: 

network: eg. 74.142.124.116/30,

latitude: eg. 40475, 

longitude: eg. 37.7548

The system also captures the ipv4 address of the user accessing the service (say, 216.255.211.106).

I want to compare this ipv4 and network address and locate the ip address.. Is there a way of doing this in MySQL or node.JS?

Shri
  • 709
  • 1
  • 7
  • 23

1 Answers1

2

You can use the following method:

  • Store the network as startIP and endIP (network & broadcast) in two separate columns in MySQL
  • Find the record containing the address using a simple range query (ip >= startIP and ip <= endIP)

Implementation details:

The optimal datatype for IPv4 address in MySQL is INT UNSIGNED. You can convert your existing data by calculating network and broadcast addresses as (using your example data):

update mytable set startIP = inet_aton('74.142.124.116'), endIP = inet_aton('74.142.124.119')

Assuming your original parameter value (sourceIP) is a dotted decimal formatted IP addres as a string (i.e. '216.255.211.106') you can query the table (you need to add index on (startIP, endIP) as:

select lat, long from mytable where inet_aton(@sourceIP) >= startIP and inet_aton(@sourceIP) <= endIP

(@sourceIP is the parameter value)

Depending on the quality of your data, you might get multiple results (i.e. overlapping network ranges in your dataset)

Update

Credits to Bernd Buffen's answer here

You can use the following statement to populate the two new columns:

update mytable set
startIP = INET_ATON( SUBSTRING_INDEX(cidr, '/', 1)) 
   & 0xffffffff ^ ((0x1 << ( 32 - SUBSTRING_INDEX(cidr, '/', -1))  ) -1 ) 
, endIP = INET_ATON( SUBSTRING_INDEX(cidr, '/', 1)) 
   | ((0x100000000 >> SUBSTRING_INDEX(cidr, '/', -1) ) -1 )

Assuming the original table has a cidr column.

Tasos P.
  • 3,994
  • 2
  • 21
  • 41
  • How did you calculate the endIP value to "74.142.124.120"? I have 3 Million rows of Network addresses like this. What do I do for all the others? – Shri Mar 31 '20 at 08:28
  • 1
    I will add sample code to calculate network/broadcast address from CIDR – Tasos P. Mar 31 '20 at 08:30
  • 1
    Added sample and also corrected my (now obvious) mistake: in your example, the broadcast address is 74.142.124.119 – Tasos P. Mar 31 '20 at 08:39
  • in the statement ```select lat, long from mytable where sourceIP >= startIP and sourceIP <= endIP``` what is the datatype of the sourceIP? Is it a string? Is it supposed to be like ```select lat, long from mytable where "216.255.211.106" >= startIP and "216.255.211.106" <= endIP```? I seem to get the wrong results for any IP that I give. Which also gives me a warning ```Truncated incorrect DOUBLE value``` – Shri Mar 31 '20 at 09:24
  • I forgot to mention it: Assuming that the original parameter value is a string containing the sourceIP, you will have to convert it to an unsigned integer. This can be easily done with mysql's `inet_aton('')`. I will edit my answer accordingly. – Tasos P. Mar 31 '20 at 10:12
  • there are also two additional ```INET_NTOA```s in the population of new columns. After I removed it, and also added ```inet_aton('')``` everything was fine. Consider editing your answer. – Shri Mar 31 '20 at 10:15
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/210650/discussion-between-cascader-and-clutch-prince). – Tasos P. Mar 31 '20 at 10:17