Some Perl for entering IPs into a database

This code is proof of concept, if you want to use it in a production environment I suggest you go over it heavily. For a person fairly new to perl there is a lot going on here that you may find useful. The overall idea is to convert IPs from dotted quad decimal numbers into binary then store them in a database. Because IPs can’t be duplicated on machines or it will cause a conflict, it is in general going to be a good value to have as a primary key. Feel free to use and adapt this code as you see fit. The end result should be something like:


mysql> select * from IPs;
| ip_address                       | netmask                          | computer_name            |
| 11000000101010000000001000000101 | 11111111111111111111111100000000 | |
1 row in set (0.00 sec)


#IP2DB 0.1.0 (C) Febuary 2011 Howard A Underwood II
#Free for use and modification under the Creative Commons 1.0 License. If you want to give me a shout out try
#The purpose of this code is to convert an IP address and netmask pair into Binary to make it easily stored in the database in a processable manner. This is only for IPV4 atm and is just a proof of concept, I’d love to see your adaptations to real world applications. Feel free to give me your feedback at the above address.

#This requires DBI and DBD::MySQL. Use CPAN or your package manager of choice to get them.
use DBI;
use DBD::mysql;

#info to connect to the DB server. This assumes that your table is pre-created. If you need to create a database do the following:
#create database ips;
#CREATE TABLE IPs (ip_address BINARY(32) PRIMARY KEY, netmask BINARY(32), computer_name char(200));


#info to put into the DB. There’s the IP here, netmask and the computer name. These variables and the ones above are going to be what you need to use to adapt the script to your needs.

#Getting down to business. This first line takes the netmask and breaks it into 4 ocets.
my @netmask = split (/\./, $netmask);
#Now that we have 4 ocets, we process each one into binary. Future modifications include cleaning this code up so that it’s a loop rather than 4 instances.
$ocetnm0= unpack(“B*”, pack(“C”, $netmask[0]));
$ocetnm1= unpack(“B*”, pack(“C”, $netmask[1]));
$ocetnm2= unpack(“B*”, pack(“C”, $netmask[2]));
$ocetnm3= unpack(“B*”, pack(“C”, $netmask[3]));
#We recombine everything into 1 Binary number after this.
$totalnm= $ocetnm0.$ocetnm1.$ocetnm2.$ocetnm3;
#Just printing the post process # on the TTY for human verification
print “$totalnm\n”;

#Now we repeat the process for the IP its self. This will probably get condensed into one instance along with the above code eventually. Once again, not the most efficient way to do it but rather straight forward.
my @ip = split (/\./, $ip);
$ocet0= unpack(“B*”, pack(“C”, $ip[0]));
$ocet1= unpack(“B*”, pack(“C”, $ip[1]));
$ocet2= unpack(“B*”, pack(“C”, $ip[2]));
$ocet3= unpack(“B*”, pack(“C”, $ip[3]));
$total= $ocet0.$ocet1.$ocet2.$ocet3;
print “$total\n”;

#Basic DBI connection code. We are using the DBI script to connect to the databse
$dsn = “DBI:mysql:database=$db;host=$hostname;port=$port”;
$DBIconnect = DBI->connect($dsn, $user, $password)
#If we don’t like what we see bail out because we can’t connect.
or die “Connection denied to database $db \n;”;
#Add the entry to the table. Please note that if you use the above table it will probably not let you run this more than once for any given IP.
eval { $DBIconnect->do(“INSERT INTO IPs (ip_address,netmask,computer_name) VALUES (‘$total’,’$totalnm’,’$compname’);”) };
print “Data not added to the database: $@\n” if $@;

Leave a Reply

Your email address will not be published. Required fields are marked *