Solved

check if ip address is present between the given range of ip addresses in the database

Posted on 2013-10-22
18
585 Views
Last Modified: 2013-11-13
Hi,
Here is my problem.
I have a db table that has following columns:

ip_from | ip_to | country

and the values present in these columns are:
1.72.0.0 | 1.79.255.255 | japan

now i have some value in a variable say $ip_to_look that contains some value of ip address, now how will i check that the ip in variable $ip_to_look is present between the range of values present in the table in columns ip_from and ip_to.
To do this what would be my database structure.

Please help me to solve this problem, i will be very thankful.
Thank you.
0
Comment
Question by:hung3r4more
  • 7
  • 5
  • 3
  • +2
18 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
check if this works for you.
select * 
  from (
		select replace(substring(substring_index(ip_from, ',', 1), length(substring_index(ip_from, ',', 1 - 1)) + 1), ',', '') ip_from_1,
			   replace(substring(substring_index(ip_from, ',', 2), length(substring_index(ip_from, ',', 2 - 1)) + 1), ',', '') ip_from_2,
			   replace(substring(substring_index(ip_from, ',', 3), length(substring_index(ip_from, ',', 3 - 1)) + 1), ',', '') ip_from_3,
			   replace(substring(substring_index(ip_from, ',', 4), length(substring_index(ip_from, ',', 4 - 1)) + 1), ',', '') ip_from_4,
			   
			   replace(substring(substring_index(ip_to, ',', 1), length(substring_index(ip_to, ',', 1 - 1)) + 1), ',', '') ip_to_1,
			   replace(substring(substring_index(ip_to, ',', 2), length(substring_index(ip_to, ',', 2 - 1)) + 1), ',', '') ip_to_2,
			   replace(substring(substring_index(ip_to, ',', 3), length(substring_index(ip_to, ',', 3 - 1)) + 1), ',', '') ip_to_3,
			   replace(substring(substring_index(ip_to, ',', 4), length(substring_index(ip_to, ',', 4 - 1)) + 1), ',', '') ip_to_4,
			   
			   replace(substring(substring_index($ip_to_look, ',', 1), length(substring_index($ip_to_look, ',', 1 - 1)) + 1), ',', '') ip_to_look_1,
			   replace(substring(substring_index($ip_to_look, ',', 2), length(substring_index($ip_to_look, ',', 2 - 1)) + 1), ',', '') ip_to_look_2,
			   replace(substring(substring_index($ip_to_look, ',', 3), length(substring_index($ip_to_look, ',', 3 - 1)) + 1), ',', '') ip_to_look_3,
			   replace(substring(substring_index($ip_to_look, ',', 4), length(substring_index($ip_to_look, ',', 4 - 1)) + 1), ',', '') ip_to_look_4
			   
		  from your_table) t1
 where (ip_to_look_1 between ip_from_1 and ip_to_1)
   and (ip_to_look_2 between ip_from_2 and ip_to_2)
   and (ip_to_look_3 between ip_from_3 and ip_to_3)
   and (ip_to_look_4 between ip_from_4 and ip_to_4)

Open in new window

or you can simply remove the "." from the ip address and compare if that works.
select *
  from (select replace(ip_from,'.','') as ip_from,replace(ip_to,'.','') as ip_to,replace($ip_to_look,'.','') as ip_to_look   
          from your_table) t1
 where ip_to_look between ip_from and ip_to

Open in new window

0
 
LVL 29

Expert Comment

by:fibo
Comment Utility
Just a side comment, since the solutions offered cover a complete spectrum.

The dot between the digits is NOT a problem as long as you are using ranges where upper and lower IP addresses have the dot in the same position: 1.234xxx will not be part of 12.34xx to 12.35yy (let's forget that .3zz cannot be in an IP address!)
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Rather than deal with the dots in the IP address, you may find these functions helpful.
http://php.net/manual/en/function.ip2long.php <-- Interesting comments
http://php.net/manual/en/function.sprintf.php <-- See the %u format

Be careful about doing arithmetic with the "integer" values of IP addresses, since the IPv4 address scheme can generate negative numbers on 32-bit machines.

I have not tried to handle the IPv6 yet.

Best to all, over and out, ~Ray
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
ip2long is exactly what is used in solution I linked above:

$range_start = ip2long("68.61.156.0");
$range_end   = ip2long("68.61.181.255");
$ip          = ip2long($_SERVER['REMOTE_ADDR']);
if ($ip >= $range_start && $ip <= $range_end) {
...
}

Open in new window

0
 
LVL 29

Expert Comment

by:fibo
Comment Utility
Again, what is the interest of dealing with dots if all range limits are such that both their values have the dot in the same position?
0
 

Author Comment

by:hung3r4more
Comment Utility
@Ray_Paseur:
You have provided a really great solution and i have used it.
I have a question:
I have run the query 45 mins ago and it is still going, is it because of the much amount of data present in the .csv file ?
Would it take this long if i have to export the database and install it on some other server ?
0
 

Author Comment

by:hung3r4more
Comment Utility
@Ray
Only 38,000 rows has been inserted into database so far in 45 min and it is still going, and in the .csv file there are are 34,00000 rows,
is there some company that provide API so that instead of inserting data into my database i can get the service from them?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 29

Expert Comment

by:fibo
Comment Utility
You mean 34M rows?
That's certainly a lot.
This was not clear in your question!

Now may be you should address this volume itself. It implies that you have very detailed IP ranges, probably detailed by clock owner etc.
Since there are 200 countries in the world, that would mean that you have on average 170K IP ranges per country? Do you really need THAT level of detail?
0
 
LVL 29

Expert Comment

by:fibo
Comment Utility
Looking back at the question and the answers

- your table just need 3 data columns (ip_start, varchar 1; ip_end, varchar 15; country, varchar 100) and probably as an id the record number (autocincrement primary)
- importing should be easy (but probably not with that volume!)

Once the import is over (but NOT before!!!!!!!!!!!), remember putting in place an index on each of the columns, so that you search will as fast as possible

(just to be sure: you are not currently importing with the index active, right?)
0
 

Author Comment

by:hung3r4more
Comment Utility
i am not importing with the index active. I have ip location to the city so there is a lot of rows in the file.
0
 
LVL 29

Expert Comment

by:fibo
Comment Utility
your initial question specifically mentions country... Beware: city ip resolution is fuzzy at best.

Use of countries is usually for serving country-based service, eg language or some specific pages.

Use of cities is usually for location based services, like local info and services (and advertisment). You will probably find in THAT case that your ip reference data is not good enough. If your site targets mobiles, you should consider the location service they integrate, and ask them for permission to get it.

Just exactly what do you want to achieve?
0
 

Author Comment

by:hung3r4more
Comment Utility
I am developing an application that helps me to know about the visitors who visits on my website. I am creating an chat application that will let me to chat with the visitors in real time.
so i need to know that from which specific region the visitor is .
0
 
LVL 29

Expert Comment

by:fibo
Comment Utility
You might consider reconsidering. Knowing which country they are from will be enough in most situations.
And city / IP are not really trustable enough to build business upon: in countries like mine (France) some ISPs are frequently moving IPs between region. And most IP databases place me in the center of France because they do not know for sure which city I'm living in.

(I presume that by now your database has been fully loaded...)
0
 

Author Comment

by:hung3r4more
Comment Utility
Well i dont need it to be precise , it is just my customer's requirement . I just need to get it working somehow regardless the accuracy.
I stopped loading the database because it would have took more than 24 hours to execute the 3700000 quires.
Other thing is that when i try to know that which browser the visitor is using by $_SERVER['HTTP_USER_AGENT'] , instead of giving me one browser's name it gives me a string that includes the name of all browsers , it gives me something like this:
 [HTTP_USER_AGENT] => Mozilla/5.0 (X11; Linux i686) AppleWebKit/535.19 (KHTML, like Gecko) Ubuntu/12.04 Chromium/18.0.1025.151 Chrome/18.0.1025.151 Safari/535.19
it includes the name of Mozilla, chrome, safari while in actual i am using the chrome browser.
So these are the two problems i am facing right now.
0
 
LVL 29

Expert Comment

by:fibo
Comment Utility
The user agent problemis another complete issue and probably needs another question.
Here again, I fear that you search after a perfect identification, which in most situations is not possible
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Something has got to be wrong in the logic or the data.  Let's do the arithmetic...

3,700,000 queries.  24 hours = 86,400 seconds, suggests a query rate of 43 per second.

When I ran this using the Maxmind data, I found that I could insert 170,000 rows in about 15 seconds, or about 11,300 per second.  

Your loading process should have finished in less than 6 minutes.  Please go back to the article and try it that way, then post back here if you're still having troubles.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3437-IP-Address-to-Country-in-PHP.html
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now