Solved

mysql script to convert IP address to integer

Posted on 2015-01-10
10
320 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 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 26

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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
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…

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