#!/usr/bin/perl # ipdb/cgi-bin/search.cgi # Started splitting search functions (quick and otherwise) from # main IPDB interface 03/11/2005 ### # SVN revision info # $Date: 2010-07-27 21:06:18 +0000 (Tue, 27 Jul 2010) $ # SVN revision $Rev: 449 $ # Last update by $Author: kdeugau $ ### # Copyright 2005-2010 - Kris Deugau use strict; use warnings; use CGI::Carp qw(fatalsToBrowser); use CGI::Simple; use HTML::Template; use DBI; use CommonWeb qw(:ALL); use POSIX qw(ceil); use NetAddr::IP; # don't remove! required for GNU/FHS-ish install from tarball ##uselib## use MyIPDB; # Don't formally need a username or syslog here. syslog left active for debugging. use Sys::Syslog; openlog "IPDBsearch","pid","$IPDB::syslog_facility"; # ... but we do *use* the username on ACLs now. # Collect the username from HTTP auth. If undefined, we're in # a test environment, or called without a username. my $authuser; if (!defined($ENV{'REMOTE_USER'})) { $authuser = '__temptest'; } else { $authuser = $ENV{'REMOTE_USER'}; } # Why not a global DB handle? (And a global statement handle, as well...) # Use the connectDB function, otherwise we end up confusing ourselves my $ip_dbh; my $sth; my $errstr; ($ip_dbh,$errstr) = connectDB_My; if (!$ip_dbh) { printAndExit("Failed to connect to database: $errstr\n"); } checkDBSanity($ip_dbh); initIPDBGlobals($ip_dbh); # Global variables my $RESULTS_PER_PAGE = 25; ##fixme - need to autofill this somehow $ENV{HTML_TEMPLATE_ROOT} = '/home/kdeugau/dev/ipdb/trunk/templates'; # Set up the CGI object... my $q = new CGI::Simple; # ... and get query-string params as well as POST params if necessary $q->parse_query_string; # Convenience; saves changing all references to %webvar ##fixme: tweak for handling $data[1]"; $i++; $typetable .= "\n" if ($i % 4 == 0); } if ($i %4 == 0) { $typetable =~ s/$//; } else { $typetable .= "\n"; } $typetable .= "\n"; # Generate table of cities my $citytable = "\n"; $sth = $ip_dbh->prepare("select id,city from cities order by city"); $sth->execute; my $i=0; while (my @data = $sth->fetchrow_array) { $citytable .= ""; $i++; $citytable .= "\n" if ($i % 5 == 0); } if ($i %5 == 0) { $citytable =~ s/$//; } else { $citytable .= "\n"; } $citytable .= "
$data[1]
\n"; $html =~ s/\$\$TYPELIST\$\$/$typetable/; $html =~ s/\$\$CITYLIST\$\$/$citytable/; print $html; } # Shut down and clean up. finish($ip_dbh); # We print the footer here, so we don't have to do it elsewhere. my $footer = HTML::Template->new(filename => "footer.tmpl"); # include the admin tools link in the output? $footer->param(adminlink => ($IPDBacl{$authuser} =~ /A/)); print $footer->output; # We shouldn't need to directly execute any code below here; it's all subroutines. exit 0; # viewBy() # The quick search # Takes a category descriptor and a query string # Creates appropriate SQL to run the search and display the results # with queryResults() sub viewBy($$) { my ($category,$query) = @_; # Local variables my $sql; # Calculate start point for LIMIT clause my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE; # Possible cases: # 1) Partial IP/subnet. Treated as "octet-prefix". # 2a) CIDR subnet. Exact match. # 2b) CIDR netmask. YMMV but it should be octet-prefix-with-netmask # (ie, all matches with the octet prefix *AND* that netmask) # 3) Customer ID. "Match-any-segment" # 4) Description. "Match-any-segment" # 5) Invalid data which might be interpretable as an IP or something, but # which probably shouldn't be for reasons of sanity. my $cols = "cidr,custid,type,city,description"; if ($category eq 'all') { print qq(
Showing all netblock and static-IP allocations

\n); $sql = "select $cols from searchme"; my $count = countRows($sql); $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; queryResults($sql, $webvar{page}, $count); } elsif ($category eq 'cust') { print qq(
Searching for Customer IDs containing '$query'

\n); # Query for a customer ID. Note that we can't restrict to "numeric-only" # as we have non-numeric custIDs in the legacy data. :/ $sql = "select $cols from searchme where custid ilike '%$query%' or oldcustid ilike '%$query%' or description like '%$query%'"; my $count = countRows($sql); $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; queryResults($sql, $webvar{page}, $count); } elsif ($category eq 'desc') { print qq(
Searching for descriptions containing '$query'

\n); # Query based on description (includes "name" from old DB). $sql = "select $cols from searchme where description ilike '%$query%'". " or custid ilike '%$query%'"; my $count = countRows($sql); $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; queryResults($sql, $webvar{page}, $count); } elsif ($category =~ /ipblock/) { # Query is for a partial IP, a CIDR block in some form, or a flat IP. print qq(
Searching for IP-based matches on '$query'

\n); $query =~ s/\s+//g; if ($query =~ /\//) { # 192.168.179/26 should show all /26 subnets in 192.168.179 my ($net,$maskbits) = split /\//, $query; if ($query =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{2}$/) { # /0->/9 are silly to worry about right now. I don't think # we'll be getting a class A anytime soon. $sql = "select $cols from searchme where cidr='$query'"; queryResults($sql, $webvar{page}, 1); } else { #print "Finding all blocks with netmask /$maskbits, leading octet(s) $net
\n"; # Partial match; beginning of subnet and maskbits are provided $sql = "select $cols from searchme where text(cidr) like '$net%' and ". "text(cidr) like '%$maskbits'"; my $count = countRows($sql); $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; queryResults($sql, $webvar{page}, $count); } } elsif ($query =~ /^(\d{1,3}\.){3}\d{1,3}$/) { # Specific IP address match #print "4-octet pattern found; finding netblock containing IP $query
\n"; my ($net,$ip) = ($query =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.)(\d{1,3})/); my $sfor = new NetAddr::IP $query; $sth = $ip_dbh->prepare("select $cols from searchme where text(cidr) like '$net%'"); $sth->execute; while (my @data = $sth->fetchrow_array()) { my $cidr = new NetAddr::IP $data[0]; if ($cidr->contains($sfor)) { queryResults("select $cols from searchme where cidr='$cidr'", $webvar{page}, 1); } } } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) { #print "Finding matches with leading octet(s) $query
\n"; $sql = "select $cols from searchme where text(cidr) like '$query%'"; my $count = countRows($sql); $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; queryResults($sql, $webvar{page}, $count); } else { # This shouldn't happen, but if it does, whoever gets it deserves what they get... printError("Invalid query."); } } else { # This shouldn't happen, but if it does, whoever gets it deserves what they get... printError("Invalid searchfor."); } } # viewBy # args are: a reference to an array with the row to be printed and the # class(stylesheet) to use for formatting. # if ommitting the class - call the sub as &printRow(\@array) sub printRow { my ($rowRef,$class) = @_; if (!$class) { print "\n"; } else { print "\n"; } ELEMENT: foreach my $element (@$rowRef) { if (!defined($element)) { print "\n"; next ELEMENT; } $element =~ s|\n|
|g; print "$element\n"; } print ""; } # printRow # queryResults() # Display search queries based on the passed SQL. # Takes SQL, page number (for multipage search results), and a total count. sub queryResults($$$) { my ($sql, $pageNo, $rowCount) = @_; my $offset = 0; $offset = $1 if($sql =~ m/.*limit\s+(.*),.*/); my $sth = $ip_dbh->prepare($sql); $sth->execute(); startTable('Allocation','CustID','Type','City','Description/Name'); my $count = 0; while (my @data = $sth->fetchrow_array) { # cidr,custid,type,city,description,notes # Another bit of HairyPerl(TM) to prefix subblocks with "Sub" my @row = (($data[2] =~ /^.r$/ ? 'Sub ' : ''). qq($data[0]), $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]); # Allow listing of pool if desired/required. if ($data[2] =~ /^.[pd]$/) { $row[0] .= '   List IPs"; } printRow(\@row, 'color1', 1) if ($count%2==0); printRow(\@row, 'color2', 1) if ($count%2!=0); $count++; } # Have to think on this call, it's primarily to clean up unfetched rows from a select. # In this context it's probably a good idea. $sth->finish(); my $upper = $offset+$count; print "Records found: $rowCount
Displaying: ".($offset+1)." - $upper\n"; print "\n"; # print the page thing.. if ($RESULTS_PER_PAGE > 0 && $rowCount > $RESULTS_PER_PAGE) { my $pages = ceil($rowCount/$RESULTS_PER_PAGE); print qq(
Page: ); for (my $i = 1; $i <= $pages; $i++) { if ($i == $pageNo) { print "$i \n"; } else { print qq($i \n); } } print "
"; } } # queryResults # Prints table headings. Accepts any number of arguments; # each argument is a table heading. sub startTable { print qq(
); foreach(@_) { print qq(); } print "\n"; } # startTable # Return count of rows to be returned in a "real" query # with the passed SQL statement sub countRows($) { # Note that the "as foo" is required my $sth = $ip_dbh->prepare("select count(*) from ($_[0]) as foo"); $sth->execute(); my @a = $sth->fetchrow_array(); $sth->finish(); return $a[0]; }
$_