Index: branches/new-search-20050223/cgi-bin/search.cgi =================================================================== --- branches/new-search-20050223/cgi-bin/search.cgi (revision 197) +++ branches/new-search-20050223/cgi-bin/search.cgi (revision 197) @@ -0,0 +1,362 @@ +#!/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$ +# SVN revision $Rev$ +# Last update by $Author$ +### +# 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];
+}
+
Index: branches/new-search-20050223/compsearch.html
===================================================================
--- branches/new-search-20050223/compsearch.html	(revision 197)
+++ branches/new-search-20050223/compsearch.html	(revision 197)
@@ -0,0 +1,28 @@
+

+
Complex Search

+

+
$_

+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
Match on:All
+Any
+

+
IP/netblock:
CustID:
Description:
Notes:
Types:Show all types
+$$TYPELIST$$
Cities:Show all cities
+$$CITYLIST$$

+
+
Index: branches/new-search-20050223/header.inc
===================================================================
--- branches/new-search-20050223/header.inc	(revision 171)
+++ branches/new-search-20050223/header.inc	(revision 197)
@@ -40,14 +40,14 @@
 
 
-

-Quick Search:
+
+Quick Search:
 
 
-
+
 
 
-             
-Add new assignment
-
+Complex Search
+Add new assignment
+
 

 
Index: branches/new-search-20050223/help.html
===================================================================
--- branches/new-search-20050223/help.html	(revision 171)
+++ branches/new-search-20050223/help.html	(revision 197)
@@ -11,5 +11,5 @@
 
 
-
+
Index: branches/new-search-20050223/ipdb.css
===================================================================
--- branches/new-search-20050223/ipdb.css	(revision 171)
+++ branches/new-search-20050223/ipdb.css	(revision 197)
@@ -48,5 +48,5 @@
 .regular { 
 	font-family: Verdana, Arial, Helvetica, sans-serif;
-	font-size: 100%;
+	font-size: 90%;
 }
 

Searches:
Quick Searches: