Changeset 172 for branches/sql-cleanup

Timestamp:

02/24/05 17:09:30 (20 years ago)

Author:

Kris Deugau

Message:

/branches/sql-cleanup

Missed some slightly hairy SQL in the search code.
Fixed the SQL and tightened some of the surrounding logic.

File:

• branches/sql-cleanup/cgi-bin/main.cgi (modified) (5 diffs)

branches/sql-cleanup/cgi-bin/main.cgi

@@ -204 +204 @@
 
     print qq(<div class="heading">Showing all netblock and static-IP allocations</div><br>\n);
-    $sql = "select * from searchme";
+
+    # Need to assemble SQL query in this order to avoid breaking things.
+    $sql = "select cidr,custid,type,city,description from searchme";
     my $count = countRows("select count(*) from ($sql) foo");
     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
@@ -215 +217 @@
     # 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%'";
+    $sql = "select cidr,custid,type,city,description from searchme where custid ilike '%$query%'";
     my $count = countRows("select count(*) from ($sql) foo");
     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
@@ -224 +226 @@
     print qq(<div class="heading">Searching for descriptions containing '$query'</div><br>\n);
     # Query based on description (includes "name" from old DB).
-    $sql = "select * from searchme where description ilike '%$query%'";
+    $sql = "select cidr,custid,type,city,description from searchme where description ilike '%$query%'";
     my $count = countRows("select count(*) from ($sql) foo");
     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
@@ -241 +243 @@
         # /0->/9 are silly to worry about right now.  I don't think
         # we'll be getting a class A anytime soon.  <g>
-        $sql = "select * from searchme where cidr='$query'";
+        $sql = "select cidr,custid,type,city,description from searchme where cidr='$query'";
         queryResults($sql, $webvar{page}, 1);
       } else {
         print "Finding all blocks with netmask /$maskbits, leading octet(s) $net<br>\n";
         # Partial match;  beginning of subnet and maskbits are provided
-        $sql = "select * from searchme where text(cidr) like '$net%' and ".
-                "text(cidr) like '%$maskbits'";
+        $sql = "select cidr,custid,type,city,description 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";
@@ -254 +256 @@
     } elsif ($query =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
       # Specific IP address match
-      print "4-octet pattern found;  finding netblock containing IP $query<br>\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%'");
+# We do this convoluted roundabout way of finding things in order
+# to bring up matches for single IPs that are within a static block;
+# we want to show both the "container" block and the static IP itself.
+      $sth = $ip_dbh->prepare("select cidr from searchme where cidr >>= '$sfor'");
       $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);
-        }
+        queryResults("select cidr,custid,type,city,description 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<br>\n";
-      $sql = "select * from searchme where text(cidr) like '$query%'";
+      $sql = "select cidr,custid,type,city,description 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";