#!/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-06-30 21:48:03 +0000 (Wed, 30 Jun 2010) $ # SVN revision $Rev: 417 $ # Last update by $Author: kdeugau $ ### # Copyright 2005-2010 - Kris Deugau use strict; use warnings; use CGI::Carp qw(fatalsToBrowser); 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; # 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; my %webvar = parse_post(); cleanInput(\%webvar); if (defined($webvar{rpp})) { ($RESULTS_PER_PAGE) = ($webvar{rpp} =~ /(\d+)/); } if (!defined($webvar{stype})) { $webvar{stype} = ""; #shuts up the warnings. } printHeader('Searching...'); if ($webvar{stype} eq 'q') { # Quick search. if (!$webvar{input}) { # No search term. Display everything. viewBy('all', ''); } else { # Search term entered. Display matches. # We should really sanitize $webvar{input}, no? my $searchfor; # Chew up leading and trailing whitespace $webvar{input} =~ s/^\s+//; $webvar{input} =~ s/\s+$//; if ($webvar{input} =~ /^\d+$/) { # All-digits, new custID $searchfor = "cust"; } elsif ($webvar{input} =~ /^[\d\.]+(\/\d{1,3})?$/) { # IP addresses should only have numbers, digits, and maybe a slash+netmask $searchfor = "ipblock"; } else { # Anything else. $searchfor = "desc"; } viewBy($searchfor, $webvar{input}); } } elsif ($webvar{stype} eq 'c') { # Complex search. # 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 $sqlconcat; if ($webvar{which} eq 'all') { # Must match *all* specified criteria. ## use INTERSECT or EXCEPT $sqlconcat = "INTERSECT"; } elsif ($webvar{which} eq 'any') { # Match on any specified criteria ## use UNION $sqlconcat = "UNION"; } else { # We can't get here. PTHBTT! printAndExit "PTHBTT!! Your search has been rejected due to Microsoft excuse #4432: ". "Not enough mana"; } # We actually construct a monster SQL statement for all criteria. # Iff something has been entered, it will be used as a filter. # Iff something has NOT been entered, we still include it but in # such a way that it does not actually filter anything out. # Columns actually returned. Slightly better than hardcoding it # in each (sub)select my $cols = "cidr,custid,type,city,description"; # First chunk of SQL. Filter on custid, description, and notes as necessary. my $sql = "(select $cols from searchme where". " $webvar{custexclude} (custid ilike '%$webvar{custid}%'". " OR $webvar{custexclude} oldcustid 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 we're not supposed to search for all types, search for the selected types. 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 we're not supposed to search for all cities, search for the selected cities. # This could be vastly improved with proper foreign keys in the database. 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 .= "))"; } ## CIDR query options. $webvar{cidr} =~ s/\s+//; # Hates the nasty spaceseseses we does. if ($webvar{cidr} eq '') { # We has a blank CIDR. Ignore it. } elsif ($webvar{cidr} =~ /\//) { # 209.91.179/26 should show all /26 subnets in 209.91.179 my ($net,$maskbits) = split /\//, $webvar{cidr}; if ($webvar{cidr} =~ /^(\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 .= " $sqlconcat (select $cols from searchme where ". "$webvar{cidrexclude} cidr<<='$webvar{cidr}')"; } else { # Partial match; beginning of subnet and maskbits are provided # Show any blocks with the leading octet(s) and that masklength # Need some more magic for bare /nn searches: my $condition = ($net eq '' ? "masklen(cidr)=$maskbits" : "text(cidr) like '$net%' and masklen(cidr)=$maskbits"); $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ". "($condition))"; } } elsif ($webvar{cidr} =~ /^(\d{1,3}\.){3}\d{1,3}$/) { # Specific IP address match. Will show either a single netblock, # or a static pool plus an IP. $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ". "cidr >>= '$webvar{cidr}')"; } elsif ($webvar{cidr} =~ /^\d{1,3}(\.(\d{1,3}(\.(\d{1,3}\.?)?)?)?)?$/) { # Leading octets in CIDR $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ". "text(cidr) like '$webvar{cidr}%')"; } else { # This shouldn't happen, but if it does, whoever gets it deserves what they get... printAndExit("Invalid netblock query."); } # done with CIDR query options. # Find the offset for multipage results my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE; # Find out how many rows the "core" query will return. my $count = countRows($sql); if ($count == 0) { printError "No matches found. Try eliminating one of the criteria,". " or making one or more criteria more general."; } else { # Add the limit/offset clauses $sql .= " order by cidr"; $sql .= " limit $RESULTS_PER_PAGE offset $offset" if $RESULTS_PER_PAGE != 0; # And tell the user. print "
Searching...............
\n"; queryResults($sql, $webvar{page}, $count); } } elsif ($webvar{stype} eq 'n') { # Node search. my $sql = "SELECT cidr,custid,type,city,description FROM searchme". " WHERE cidr IN (SELECT block FROM noderef WHERE node_id=$webvar{node})"; # Find the offset for multipage results my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE; # Find out how many rows the "core" query will return. my $count = countRows($sql); if ($count == 0) { printError "No customers currently listed as connected through this node."; } else { # Add the limit/offset clauses $sql .= " order by cidr"; $sql .= " limit $RESULTS_PER_PAGE offset $offset" if $RESULTS_PER_PAGE != 0; # And tell the user. print "
Searching...............
\n"; queryResults($sql, $webvar{page}, $count); } } else { # how script was called. General case is to show the search criteria page. # 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; } # 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; # 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 =~ /\//) { # 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 $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]; }
$_