Changeset 174 for branches


Ignore:
Timestamp:
02/24/05 17:14:06 (20 years ago)
Author:
Kris Deugau
Message:

/branches/stable

Backported SQL cleanup from /trunk r173

File:
1 edited

Legend:

Unmodified
Added
Removed
  • branches/stable/cgi-bin/main.cgi

    r168 r174  
    205205
    206206    print qq(<div class="heading">Showing all netblock and static-IP allocations</div><br>\n);
    207     $sql = "select * from searchme";
     207
     208    # Need to assemble SQL query in this order to avoid breaking things.
     209    $sql = "select cidr,custid,type,city,description from searchme";
    208210    my $count = countRows("select count(*) from ($sql) foo");
    209211    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     
    216218    # Query for a customer ID.  Note that we can't restrict to "numeric-only"
    217219    # as we have non-numeric custIDs in the legacy data.  :/
    218     $sql = "select * from searchme where custid ilike '%$query%'";
     220    $sql = "select cidr,custid,type,city,description from searchme where custid ilike '%$query%'";
    219221    my $count = countRows("select count(*) from ($sql) foo");
    220222    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     
    225227    print qq(<div class="heading">Searching for descriptions containing '$query'</div><br>\n);
    226228    # Query based on description (includes "name" from old DB).
    227     $sql = "select * from searchme where description ilike '%$query%'";
     229    $sql = "select cidr,custid,type,city,description from searchme where description ilike '%$query%'";
    228230    my $count = countRows("select count(*) from ($sql) foo");
    229231    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     
    242244        # /0->/9 are silly to worry about right now.  I don't think
    243245        # we'll be getting a class A anytime soon.  <g>
    244         $sql = "select * from searchme where cidr='$query'";
     246        $sql = "select cidr,custid,type,city,description from searchme where cidr='$query'";
    245247        queryResults($sql, $webvar{page}, 1);
    246248      } else {
    247249        print "Finding all blocks with netmask /$maskbits, leading octet(s) $net<br>\n";
    248250        # Partial match;  beginning of subnet and maskbits are provided
    249         $sql = "select * from searchme where text(cidr) like '$net%' and ".
    250                 "text(cidr) like '%$maskbits'";
     251        $sql = "select cidr,custid,type,city,description from searchme where ".
     252                "text(cidr) like '$net%' and text(cidr) like '%$maskbits'";
    251253        my $count = countRows("select count(*) from ($sql) foo");
    252254        $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     
    255257    } elsif ($query =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
    256258      # Specific IP address match
    257       print "4-octet pattern found;  finding netblock containing IP $query<br>\n";
    258       my ($net,$ip) = ($query =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.)(\d{1,3})/);
    259259      my $sfor = new NetAddr::IP $query;
    260       $sth = $ip_dbh->prepare("select * from searchme where text(cidr) like '$net%'");
     260# We do this convoluted roundabout way of finding things in order
     261# to bring up matches for single IPs that are within a static block;
     262# we want to show both the "container" block and the static IP itself.
     263      $sth = $ip_dbh->prepare("select cidr from searchme where cidr >>= '$sfor'");
    261264      $sth->execute;
    262265      while (my @data = $sth->fetchrow_array()) {
    263266        my $cidr = new NetAddr::IP $data[0];
    264         if ($cidr->contains($sfor)) {
    265           queryResults("select * from searchme where cidr='$cidr'", $webvar{page}, 1);
    266         }
     267        queryResults("select cidr,custid,type,city,description from searchme where ".
     268                "cidr='$cidr'", $webvar{page}, 1);
    267269      }
    268270    } elsif ($query =~ /^\d{1,3}\.\d{1,3}\.\d{1,3}\.?$/) {
    269271      print "Finding matches where the first three octets are $query<br>\n";
    270       $sql = "select * from searchme where text(cidr) like '$query%'";
     272      $sql = "select cidr,custid,type,city,description from searchme where ".
     273                "text(cidr) like '$query%'";
    271274      my $count = countRows("select count(*) from ($sql) foo");
    272275      $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
Note: See TracChangeset for help on using the changeset viewer.