Changeset 361 for branches/stable

Timestamp:

05/09/07 11:07:22 (17 years ago)

Author:

Kris Deugau

Message:

/branches/stable

Tweak searchme view to include circuit ID field
Make sure we don't spit out data we shouldn't from searches;

make all "select ... from searchme" SQL explicitly request
the columns we want to show.

Location:

branches/stable/cgi-bin

Files:

• ipdb.psql (modified) (1 diff)
• search.cgi (modified) (5 diffs)

branches/stable/cgi-bin/ipdb.psql

@@ -111 +111 @@
 GRANT ALL on "allocations" to "ipdb";
 
-CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes FROM poolips;
+CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes, allocations.oldcustid, allocations.circuitid FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes, poolips.oldcustid, poolips.circuitid FROM poolips;
 
 REVOKE ALL on "searchme" from PUBLIC;

branches/stable/cgi-bin/search.cgi

@@ -279 +279 @@
 #    which probably shouldn't be for reasons of sanity.
 
+  my $cols = "cidr,custid,type,city,description";
+
   if ($category eq 'all') {
 
     print qq(<div class="heading">Showing all netblock and static-IP allocations</div><br>\n);
-    $sql = "select * from searchme";
+    $sql = "select $cols from searchme";
     my $count = countRows($sql);
     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
@@ -293 +295 @@
     # 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%' or oldcustid ilike '%$query%'";
+    $sql = "select $cols from searchme where custid ilike '%$query%' or oldcustid ilike '%$query%'";
     my $count = countRows($sql);
     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
@@ -302 +304 @@
     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 $cols from searchme where description ilike '%$query%'".
         " or custid ilike '%$query%'";
     my $count = countRows($sql);
@@ -320 +322 @@
         # /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 $cols 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 ".
+        $sql = "select $cols from searchme where text(cidr) like '$net%' and ".
                 "text(cidr) like '%$maskbits'";
         my $count = countRows($sql);
@@ -336 +338 @@
       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 = $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 * from searchme where cidr='$cidr'", $webvar{page}, 1);
+          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<br>\n";
-      $sql = "select * from searchme where text(cidr) like '$query%'";
+      $sql = "select $cols from searchme where text(cidr) like '$query%'";
       my $count = countRows($sql);
       $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";