Solved

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

Posted on 2013-10-22
18
599 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +2
18 Comments
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39593005
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39593293
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
ID: 39593597
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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39593729
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 31

Expert Comment

by:Marco Gasi
ID: 39593900
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
ID: 39594000
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
ID: 39594079
@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
ID: 39594096
@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
 
LVL 29

Expert Comment

by:fibo
ID: 39594875
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
ID: 39594902
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
ID: 39595258
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
ID: 39596535
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
ID: 39596749
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
ID: 39599083
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
ID: 39599176
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
ID: 39600466
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 110

Expert Comment

by:Ray Paseur
ID: 39603766
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Simple Linear Regression

689 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