#!/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: 2005-03-15 18:48:12 +0000 (Tue, 15 Mar 2005) $ # SVN revision $Rev: 197 $ # Last update by $Author: kdeugau $ ### # Copyright 2005 Kris Deugau use strict; use warnings; use CGI::Carp qw(fatalsToBrowser); use DBI; use CommonWeb qw(:ALL); use MyIPDB; use POSIX qw(ceil); use NetAddr::IP; # Don't need a username or syslog here. syslog left active for debugging. use Sys::Syslog; openlog "IPDBsearch","pid","local2"; # 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 = 50; my %webvar = parse_post(); cleanInput(\%webvar); if (!defined($webvar{stype})) { $webvar{stype} = ""; #shuts up the warnings. } printHeader('Searching...'); if ($webvar{stype} eq 'q') { # Quick search. print "Quick Search <zip>\n"; if (!$webvar{input}) { # No search term. Display everything. viewBy('all', ''); } else { # Search term entered. Display matches. # We should really sanitize $webvar{input}, no? # need to munge up data for $webvar{searchfor}, rather than breaking things here. my $searchfor; # Chew up leading and trailing whitespace $webvar{input} =~ s/^\s+//; $webvar{input} =~ s/\s+$//; if ($webvar{input} =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{1,3}$/) { # "Perfect" IP subnet match $searchfor = "ipblock"; } elsif ($webvar{input} =~ /^(\d{1,3}\.){3}\d{1,3}$/) { # "Perfect" IP address match (shows containing block) $searchfor = "ipblock"; } elsif ($webvar{input} =~ /^(\d{1,3}\.){2}\d{1,3}(\.\d{1,3}?)?/) { # Partial IP match $searchfor = "ipblock"; } elsif ($webvar{input} =~ /^\d+$/) { # All-digits, new custID $searchfor = "cust"; } else { # Anything else. $searchfor = "desc"; } viewBy($searchfor, $webvar{input}); } } elsif ($webvar{stype} eq 'c') { # Complex search. print "Complex Search...............\n"; print "
\n";
foreach my $key (keys %webvar) {
  print "key: $key	value: $webvar{$key}\n";
}
print "
\n"; } else { # Display search page. We have to do this here, because otherwise # we can't retrieve data from the database for the types and cities. >:( my $html; open HTML,"<../compsearch.html"; $html = join('',); close HTML; # Generate table of types my $typetable = "\n"; $sth = $ip_dbh->prepare("select type,dispname from alloctypes where listorder <500 ". "order by listorder"); $sth->execute; my $i=0; while (my @data = $sth->fetchrow_array) { $typetable .= ""; $i++; $typetable .= "\n" if ($i % 4 == 0); } if ($i %4 == 0) { $typetable =~ s/$//; } else { $typetable .= "\n"; } $typetable .= "
$data[1]
\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; } # # This is unpossible! # print "This is UnPossible! You can't get here!\n"; # Shut down and clean up. finish($ip_dbh); printFooter; # We shouldn't need to directly execute any code below here; it's all subroutines. exit 0; sub viewBy($$) { my ($category,$query) = @_; # Local variables my $sql; #print "
\n";

#print "start querysub: query '$query'\n";
# this may happen with more than one subcategory.  Unlikely, but possible.

  # Calculate start point for LIMIT clause
  my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;

# Possible cases:
# 1) Partial IP/subnet.  Treated as "first-three-octets-match" in old IPDB,
#    I should be able to handle it similarly here.
# 2a) CIDR subnet.  Treated more or less as such in old IPDB.
# 2b) CIDR netmask.  Not sure how it's treated.
# 3) Customer ID.  Not handled in old IPDB
# 4) Description.
# 5) Invalid data which might be interpretable as an IP or something, but
#    which probably shouldn't be for reasons of sanity.

  if ($category eq 'all') {

    print qq(
Showing all netblock and static-IP allocations

\n);
    $sql = "select * from searchme";
    my $count = countRows("select count(*) from ($sql) foo");
    $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 * from searchme where custid ilike '%$query%'";
    my $count = countRows("select count(*) from ($sql) foo");
    $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 * from searchme where description ilike '%$query%'";
    my $count = countRows("select count(*) from ($sql) foo");
    $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 =~ /\//) {
      # 209.91.179/26 should show all /26 subnets in 209.91.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 * 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 * from searchme where text(cidr) like '$net%' and ".
		"text(cidr) like '%$maskbits'";
	my $count = countRows("select count(*) from ($sql) foo");
	$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 * 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 * from searchme where cidr='$cidr'", $webvar{page}, 1);
	}
      }
    } elsif ($query =~ /^\d{1,3}\.\d{1,3}\.\d{1,3}\.?$/) {
      print "Finding matches where the first three octets are $query
\n";
      $sql = "select * from searchme where text(cidr) like '$query%'";
      my $count = countRows("select count(*) from ($sql) foo");
      $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


# Display certain types of search query.  Note that this can't be
# cleanly reused much of anywhere else as the data isn't neatly tabulated.
# This is tied to the search sub tightly enough I may just gut it and provide
# more appropriate tables directly as needed.
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
    # Fix up types from pools (which are single-char)
    # Fixing the database would be...  painful.  :(
##fixme LEGACY CODE
    if ($data[2] =~ /^[cdsmw]$/) {
      $data[2] .= 'i';
    }
    my @row = (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 - $upper\n";
  print "\n";

  # print the page thing..
  if ($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 first element of passed SQL query
sub countRows($) {
  my $sth = $ip_dbh->prepare($_[0]);
  $sth->execute();
  my @a = $sth->fetchrow_array();
  $sth->finish();
  return $a[0];
}


$_