?
Solved

mysql script to convert IP address to integer

Posted on 2015-01-10
10
Medium Priority
?
332 Views
Last Modified: 2015-01-17
Hi Team,

  I have a mysql database where in the NETWORK table, the IP_ADDRESS field is varchar and holds standard IP addresses in different lengths  like 1.2.3.4  or 64.55.66.566,  or 123.456.789.123  etc.  I  need another field (i.e. IP_ADDRESS_DECIMAL) to hold their decimal values.   I have seen ip2long in php but I am not sure how to use that......an ordinary bash, perl script with similar functionality will work for me.  I need to put my script in a cron job.    Some bash/perl script like this:

 
mysql -u root -pmypass  mydatabase -e "UPDATE NETWORK SET IP_ADDRESS_DECIMAL = Somefunction(IP_ADDRESS);

Open in new window

Thanks very much.
0
Comment
Question by:rleyba828
[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
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 37

Assisted Solution

by:Neil Russell
Neil Russell earned 200 total points
ID: 40541907
Question is, are you storing THOUSANDS of addresses?  Do you ONLY ever want to store and display never anything else? Never want to know about how many on a network? How many in a country... examples...

The only point of the conversion to a long would be to save space on a very large database.  For small databases it would make no sense to have the over head of conversion everytime you needed to do anything IPADDRESS related.
0
 
LVL 26

Assisted Solution

by:Fred Marshall
Fred Marshall earned 200 total points
ID: 40542099
Perhaps some illumination on why you want to do this would be helpful.
Do you want to do the inverse conversion as well?
Do you have rules for the conversion in mind?  The specification at first glance does not appear to offer a 1:1 mapping.
For example, if one uses a straight powers-of-two summation then the result is not unique:
100.000.000.000 and 020.000.000.000 yield the same result: 2048.
This suggests the trivial but useful conversion which would simply remove the decimal points.
etc.
0
 

Author Comment

by:rleyba828
ID: 40542489
Thanks guys... bur first of all, my apologies....  I didn't use realistic IP addresses in my example above, and that might have added to the confusion...   I was just typing away to illustrate that the field would contain variable length strings.

Hi Neilsr,

   I will be only storing at most....about 1000....and we will only be doing it for our own comany's IP addresses....particularly those in our branches....  I will be doing computations, sorting, comparisons, based on the decimal equivalent of the IPs.  So it is not going to be expensive for a fairly small database.

Hi fmarshall,
   I will only be converting one way....   IP   --> Decimal.  

I am looking for a bash or perl script  that does this   -->  http://www.ipaddressguide.com/ip

1.2.3.4  --> 16909060.
 64.55.66.223   --> 1077363423.

Thanks very much.
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 84

Assisted Solution

by:ozo
ozo earned 400 total points
ID: 40542508
perl -le 'print unpack "N",pack"C4",split/\./ for @ARGV' 1.2.3.4   64.55.66.223
0
 
LVL 26

Expert Comment

by:Fred Marshall
ID: 40542591
Well, I can't write this out in perl but I can write it out in pseudo code of sorts:

Get the dotted IP address:
Split it into 4 parts.
Multiply the leftmost part by 16,777,216 or 256^3 or 2^(8*3)=2^24
Multiply the 2nd part by 65,536 or 256^2 or 2^(8*2)=2^16
Multiply the 3rd part by 256 or 256^1 or 2^(8*1)=2^8
Multiply the 4th part by 256^0 orr 2^(8*0) or 2^0.
Add the 4 results.

Now, if someone can explain how ozo's perl statement does this then I'll be pleased to learn.

This indeed is a 1:1 mapping but it's not the only 1:1 mapping.
For example, one could have just as readily used 257^n as the base.
So then, 1.2.3.4 would convert to 17,107,466
and 64.55.66.223 would convert to 1,090,023,832.
The mapping wouldn't be contiguous any longer but that wasn't a specification until the examples were given OR if what the website does were taken as the only possibility.
0
 

Author Comment

by:rleyba828
ID: 40542631
Hi Ozo,

Thanks very much.  Brilliant one liner.   This is what I need and the output matches  what is computed in  --> http://www.ipaddressguide.com/ip

* Not being a PERL expert, I just need your help converting that statement into a function so i can do this:

mysql -u root -pmypass  mydatabase -e "UPDATE NETWORK SET IP_ADDRESS_DECIMAL = Somefunction(IP_ADDRESS);

Open in new window


Where IP_ADDRESS_DECIMAL and IP_ADDRESS are fields in my mysql table called NETWORK.

Hi fmarshall,

   The first half or your reply above was I think the formula being used to compute decimals equivalents, but for the purpose of my objectives, it should be good enough.

Thanks and regards.
0
 
LVL 29

Accepted Solution

by:
fibo earned 1200 total points
ID: 40551016
- depending on your final objective, keeping the IP addresses as usual IP might in fact be the best solution: on every computer you can know its IP address as an IP, so what would really be the use of the conversion?

- you did not mention how these addresses are collected. if you real need a complete decimal value, collection time is probably the best time to make the conversion

- the mysql function to convert IP to decimal exists as a standard: INET_ATON()
 cf http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_inet-aton
0
 

Author Comment

by:rleyba828
ID: 40554988
Thanks Fibo,

   That function was exactly what I was looking for.   My SQL statement, which works now is something like this:

 mysql -uroot -ppassword  mydatabase  -e "UPDATE IP_ADDRESS_TABLE SET IP_DECIMAL=INET_ATON(IP_ADDRESS);"       

Open in new window

0
 
LVL 29

Expert Comment

by:fibo
ID: 40555355
B-) Glad we could help. Thx for the grade and points.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 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