Solved

mysql script to convert IP address to integer

Posted on 2015-01-10
10
276 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
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 37

Assisted Solution

by:Neil Russell
Neil Russell earned 50 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 25

Assisted Solution

by:Fred Marshall
Fred Marshall earned 50 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
 
LVL 84

Assisted Solution

by:ozo
ozo earned 100 total points
ID: 40542508
perl -le 'print unpack "N",pack"C4",split/\./ for @ARGV' 1.2.3.4   64.55.66.223
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 25

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 300 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

930 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

14 Experts available now in Live!

Get 1:1 Help Now