#!/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-17 23:02:08 +0000 (Thu, 17 Mar 2005) $ # SVN revision $Rev: 201 $ # 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\.]+(\/\d{2})?$/) { # IP addresses should only have numbers, digits, and maybe a slash+netmask $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"; # Several major cases, and a whole raft of individual cases. # -> Show all types means we do not need to limit records retrieved by type # -> Show all cities means we do not need to limit records retrieved by city # Individual cases are for the CIDR/IP, CustID, Description, Notes, and individual type # requests. my $sql = "SELECT cidr,custid,type,city,description FROM searchme"; my $sqlconcat; if ($webvar{which} eq 'all') { # Must match *all* specified criteria. ## use INTERSECT or EXCEPT $sqlconcat = "INTERSECT"; # $sqlconcat = "and"; } elsif ($webvar{which} eq 'any') { # Match on any specified criteria ## use UNION $sqlconcat = "UNION"; # $sqlconcat="or"; } else { # We can't get here. PTHBTT! printAndExit "PTHBTT!! Your search has been rejected due to Microsoft excuse #4432: Not enough mana"; } # Cases: # -> No search terms. Display everything. # -> One or more terms (cidr/custid/desc/notes) # -> Type/city restrictions my $cols = "cidr,custid,type,city,description"; $sql = "(select $cols from searchme where $webvar{custexclude} custid ilike '%$webvar{custid}%')". " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ilike '%$webvar{desc}%')". " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ilike '%$webvar{notes}%')"; if ($webvar{alltypes} ne 'on') { $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in ("; foreach my $key (keys %webvar) { $sql .= "'$1'," if $key =~ /type\[(..)\]/; } chop $sql; $sql .= "))"; } if ($webvar{allcities} ne 'on') { $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in ("; $sth = $ip_dbh->prepare("select city from cities where id=?"); foreach my $key (keys %webvar) { if ($key =~ /city\[(\d+)\]/) { $sth->execute($1); my $city; $sth->bind_columns(\$city); $sth->fetch; $city =~ s/'/''/; $sql .= "'$city',"; } } chop $sql; $sql .= "))"; } # gotta find a way to search cleanly... #if (!(!$webvar{cidr} && !$webvar{custid} && !$webvar{desc} && !$webvar{notes} && # $webvar{alltypes} && $webvar{allcities})) { # $sql .= " INTERSECT "; #} # #$sql .= # ($webvar{custid} eq '' ? '' : "SELECT cidr,custid,type,city,description FROM searchme WHERE custid LIKE '%$webvar{custid}%'"). # ($webvar{desc} eq '' ? '' : " $sqlconcat SELECT cidr,custid,type,city,description FROM searchme WHERE description like '%$webvar{desc}%'"). # ($webvar{notes} eq '' ? '' : " $sqlconcat SELECT cidr,custid,type,city,description FROM searchme WHERE notes like '%$webvar{notes}%'"). #""; print $sql."\n"; $sth = $ip_dbh->prepare($sql); $sth->execute; print "\n"; while (my @data = $sth->fetchrow_array) { print "\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 = "
$data[0]$data[1]$data[2]$data[3]$data[4]
\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]; }
$_