Changeset 930


Ignore:
Timestamp:
10/20/22 17:46:32 (2 years ago)
Author:
Kris Deugau
Message:

/trunk

Scrape a lot of the fixme-itis out of search.cgi while updating it to
properly search for ciruict IDs. Still needs a clean top-to-bottom
rewrite.

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/cgi-bin/search.cgi

    r906 r930  
    99# Last update by $Author$
    1010###
    11 # Copyright 2005-2010,2012,2015,2016 - Kris Deugau
     11# Copyright 2005-2010,2012,2015-2017,2022 - Kris Deugau <kdeugau@deepnet.cx>
    1212
    1313use strict;             
    … …  
    101101# in each (sub)select
    102102my $cols = "s.cidr, s.custid, s.type, s.city, s.description, s.id, s.parent_id, s.available, a.vrf";
     103# Common base select.  JOIN provides the VRF which may not be noted on individual allocations
     104my $sqlbase = "SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id";
    103105
    104106# Handle the DB error first
    … …  
    125127      # IP addresses should only have numbers, digits, and maybe a slash+netmask
    126128      $searchfor = "ipblock";
     129    } elsif ($webvar{input} =~ /(?:^\d{6}\-|[A-Z][A-Z]\d\d\d)/) {
     130      # Looks like part of a circuit ID
     131      $searchfor = "circuitid";
    127132    } else {
    128133      # Anything else.
    … …  
    167172
    168173  # First chunk of SQL.  Filter on custid, description, and notes as necessary.
    169   my $sql = qq(SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id\n);
    170   $sql .= " WHERE $webvar{custexclude} (s.custid ~ '$webvar{custid}')\n" if $webvar{custid};
    171   $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{descexclude} s.description ~ '$webvar{desc}')\n" if $webvar{desc};
    172   $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{notesexclude} s.notes ~ '$webvar{notes}')" if $webvar{notes};
     174  # Putting newlines in the SQL so that any SQL logging is somewhat more readable
     175  # than a gigantic long line of conditions.
     176  my $sql = "$sqlbase\n";
     177  my @bindargs;
     178  if ($webvar{custid}) {
     179    $sql .= " WHERE $webvar{custexclude} (s.custid ~ ?)\n";
     180    push @bindargs, $webvar{custid};
     181  }
     182  if ($webvar{desc}) {
     183    $sql .= " $sqlconcat ($sqlbase WHERE $webvar{descexclude} s.description ~* ?)\n";
     184    push @bindargs, $webvar{desc};
     185  }
     186  if ($webvar{notes}) {
     187    $sql .= " $sqlconcat ($sqlbase WHERE $webvar{notesexclude} s.notes ~ ?)";
     188    push @bindargs, $webvar{notes};
     189  }
    173190
    174191  # If we're not supposed to search for all types, search for the selected types.
    … …  
    176193  $webvar{typeexclude} = '' if !$webvar{typeexclude};
    177194  if ($webvar{alltypes} ne 'on') {
    178     $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{typeexclude} s.type IN (";
     195    $sql .= " $sqlconcat ($sqlbase WHERE $webvar{typeexclude} s.type IN (";
    179196    foreach my $key (keys %webvar) {
    180       $sql .= "'$1'," if $key =~ /type\[(..)\]/;
     197      $sql .= "'$1'," if $key =~ /type\[(\w\w)\]/;
    181198    }
    182199    chop $sql;
    … …  
    189206  $webvar{cityexclude} = '' if !$webvar{cityexclude};
    190207  if ($webvar{allcities} ne 'on') {
    191     $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{cityexclude} s.city IN (";
     208    $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cityexclude} s.city IN (";
    192209    $sth = $ip_dbh->prepare("SELECT city FROM cities WHERE id=?");
    193210    foreach my $key (keys %webvar) {
    … …  
    214231      # /0->/9 are silly to worry about right now.  I don't think
    215232      # we'll be getting a class A anytime soon.  <g>
    216       $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE ".
    217         "$webvar{cidrexclude} s.cidr<<='$webvar{cidr}')";
     233      $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cidrexclude} s.cidr <<= ?)";
     234      push @bindargs, $webvar{cidr};
    218235    } else {
    219236      # Partial match;  beginning of subnet and maskbits are provided
    220237      # Show any blocks with the leading octet(s) and that masklength
    221238      # Need some more magic for bare /nn searches:
    222       my $condition = ($net eq '' ?
    223         "masklen(s.cidr)=$maskbits" : "text(s.cidr) like '$net%' and masklen(s.cidr)=$maskbits");
    224       $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ".
    225         "($condition))";
     239      $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cidrexclude} (masklen(s.cidr) = ?";
     240      push @bindargs, $maskbits;
     241      if ($net ne '') {
     242        $sql .= " AND text(s.cidr) LIKE ?";
     243        push @bindargs, $net;
     244      }
     245      $sql .= "))";
    226246    }
    227247  } elsif ($webvar{cidr} =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
    228248    # Specific IP address match.  Will show either a single netblock,
    229249    # or a static pool plus an IP.
    230     $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ".
    231         "s.cidr >>= '$webvar{cidr}')";
     250    $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cidrexclude} s.cidr >>= ?)";
     251    push @bindargs, $webvar{cidr};
    232252  } elsif ($webvar{cidr} =~ /^\d{1,3}(\.(\d{1,3}(\.(\d{1,3}\.?)?)?)?)?$/) {
    233253    # Leading octets in CIDR
    234     $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ".
    235         "text(s.cidr) like '$webvar{cidr}%')";
     254    $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cidrexclude} text(s.cidr) LIKE ?)";
     255    push @bindargs, "$webvar{cidr}%";
    236256  } else {
    237257    # do nothing.
    … …  
    243263
    244264  # Find out how many rows the "core" query will return.
    245   my $count = countRows($sql);
    246 
    247 # join against yourself!  only master blocks are really guaranteed to have a VRF set - especially in legacy data
    248 #$sql .= " JOIN allocations mv ON
     265  my $count = countRows($sql, @bindargs);
    249266
    250267  if ($count == 0) {
    … …  
    253270  } else {
    254271    # Add the limit/offset clauses
    255     $sql .= " order by cidr";
    256     $sql .= " limit $RESULTS_PER_PAGE offset $offset" if $RESULTS_PER_PAGE != 0;
     272    $sql .= " ORDER BY s.cidr";
     273    $sql .= " LIMIT $RESULTS_PER_PAGE OFFSET $offset" if $RESULTS_PER_PAGE != 0;
    257274    # And tell the user.
    258275    print "<div class=heading>Searching...............</div>\n";
    259     queryResults($sql, $webvar{page}, $count);
     276    queryResults($sql, $webvar{page}, $count, @bindargs);
    260277  }
    261278
    … …  
    263280  # Node search.
    264281
    265   my $sql = "SELECT $cols FROM searchme".
    266         " WHERE cidr IN (SELECT block FROM noderef WHERE node_id=$webvar{node})";
     282  my $sql = "$sqlbase JOIN noderef nr ON nr.block=s.cidr WHERE nr.node_id = ?";
    267283
    268284  # Find the offset for multipage results
    … …  
    270286
    271287  # Find out how many rows the "core" query will return.
    272   my $count = countRows($sql);
     288  my $count = countRows($sql, $webvar{node});
     289
     290  my $nodename = getNodeName($ip_dbh, $webvar{node});
    273291
    274292  if ($count == 0) {
    275     $page->param(errmsg => "No customers currently listed as connected through this node.");
     293    $page->param(errmsg => "No customers currently listed as connected through $nodename.");
    276294##fixme:  still get the results table header
    277295  } else {
    278296    # Add the limit/offset clauses
    279     $sql .= " order by cidr";
    280     $sql .= " limit $RESULTS_PER_PAGE offset $offset" if $RESULTS_PER_PAGE != 0;
     297    $sql .= " ORDER BY cidr";
     298    $sql .= " LIMIT $RESULTS_PER_PAGE OFFSET $offset" if $RESULTS_PER_PAGE != 0;
    281299    # And tell the user.
    282     print "<div class=heading>Searching...............</div>\n";
    283     queryResults($sql, $webvar{page}, $count);
     300    print "<div class=heading>Searching for assignments terminating on $nodename...</div>\n";
     301    queryResults($sql, $webvar{page}, $count, $webvar{node});
    284302  }
    285303
    … …  
    350368  # Calculate start point for LIMIT clause
    351369  my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
    352 
    353 # Possible cases:
    354 # 1) Partial IP/subnet.  Treated as "octet-prefix".
    355 # 2a) CIDR subnet.  Exact match.
    356 # 2b) CIDR netmask.  YMMV but it should be octet-prefix-with-netmask
    357 #       (ie, all matches with the octet prefix *AND* that netmask)
    358 # 3) Customer ID.  "Match-any-segment"
    359 # 4) Description.  "Match-any-segment"
    360 # 5) Invalid data which might be interpretable as an IP or something, but
    361 #    which probably shouldn't be for reasons of sanity.
     370##fixme:  squeeze ORDER BY etc out into somewhere common, or at least an
     371# includeable bit instead of hardcoding in each block
    362372
    363373  if ($category eq 'all') {
    364374
    365     $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id";
     375    # Sort of pointless, just horks up everything.
     376    $sql = "$sqlbase";
    366377    my $count = countRows($sql);
    367     $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
     378    $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset";
    368379    queryResults($sql, $webvar{page}, $count);
    369380
    … …  
    375386    # Query for a customer ID.  Note that we can't restrict to "numeric-only"
    376387    # as we have non-numeric custIDs in the legacy data.  :/
    377     $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.custid ilike '%$query%' or s.description like '%$query%'";
    378     my $count = countRows($sql);
    379     $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
    380     queryResults($sql, $webvar{page}, $count);
     388    $sql = "$sqlbase WHERE s.custid ~* ? OR s.description ~* ?";
     389    my $count = countRows($sql, $query, $query);
     390    $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset";
     391    queryResults($sql, $webvar{page}, $count, $query, $query);
    381392
    382393  } elsif ($category eq 'desc') {
    383394
    384     print qq(<div class="heading">Searching for descriptions containing '$query'</div><br>\n);
     395    print qq(<div class="heading">Searching for description, customer ID, or circuit ID matching '$query'</div><br>\n);
    385396    # Query based on description (includes "name" from old DB).
    386     $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.description ilike '%$query%'".
    387         " or s.custid ilike '%$query%'";
    388     my $count = countRows($sql);
    389     $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
    390     queryResults($sql, $webvar{page}, $count);
     397    $sql = "$sqlbase WHERE s.description ~* ? OR s.custid ~* ? OR s.circuitid ~* ?";
     398    my $count = countRows($sql, $query, $query, $query);
     399    $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset";
     400    queryResults($sql, $webvar{page}, $count, $query, $query, $query);
     401
     402  } elsif ($category eq 'circuitid') {
     403
     404    print qq(<div class="heading">Searching for allocations with circuit ID matching '$query'</div><br>\n);
     405    # Pretty similar to description and cust searches above, but focus on circuit ID
     406    # JOIN needed for VRF field
     407    $sql = "$sqlbase WHERE s.circuitid ~* ? OR s.description ~* ?";
     408    my $count = countRows($sql, $query, $query);
     409    $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset";
     410    queryResults($sql, $webvar{page}, $count, $query, $query);
    391411
    392412  } elsif ($category =~ /ipblock/) {
    … …  
    402422        # /0->/9 are silly to worry about right now.  I don't think
    403423        # we'll be getting a class A anytime soon.  <g>
    404         $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$query'";
    405         queryResults($sql, $webvar{page}, 1);
     424        $sql = "$sqlbase WHERE s.cidr = ?";
     425        queryResults($sql, $webvar{page}, 1, $query);
    406426      } else {
    407427        #print "Finding all blocks with netmask /$maskbits, leading octet(s) $net<br>\n";
    408428        # Partial match;  beginning of subnet and maskbits are provided
    409         $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id".
    410                 " where text(s.cidr) like '$net%' and text(s.cidr) like '%$maskbits'";
    411         my $count = countRows($sql);
    412         $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
    413         queryResults($sql, $webvar{page}, $count);
     429        $sql = "$sqlbase WHERE text(s.cidr) LIKE ? AND text(s.cidr) LIKE ?";
     430        my $count = countRows($sql, "$net%", "%$maskbits");
     431        $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset";
     432        queryResults($sql, $webvar{page}, $count, "$net%", "%$maskbits");
    414433      }
     434
    415435    } elsif ($query =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
    416436      # Specific IP address match
    … …  
    418438      my ($net,$ip) = ($query =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.)(\d{1,3})/);
    419439      my $sfor = new NetAddr::IP $query;
    420 #      $sth = $ip_dbh->prepare("select $cols from searchme s JOIN allocations a ON s.master_id=a.id where text(s.cidr) like '$net%'");
    421 #print "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where text(s.cidr) like '$net%'";
    422 
    423 #      $sth->execute;
    424 #      while (my @data = $sth->fetchrow_array()) {
    425 #        my $cidr = new NetAddr::IP $data[0];
    426 #       if ($cidr->contains($sfor) || $cidr == $sfor) {
    427 #print "cidr: $data[0]\n";
    428 #print "<br>select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$cidr' and s.type <> 'mm'";
    429           queryResults(
    430 #"select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$cidr' and s.type <> 'mm'",
    431 "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr >>= '$sfor' and s.type <> 'mm' order by masklen(s.cidr) desc",
    432  $webvar{page}, 1);
    433 #print $page->output;
    434 #       }
    435 #      }
     440      $sql = "$sqlbase WHERE s.cidr >>= ? AND s.type <> 'mm'";
     441      my $count = countRows($sql, $sfor);
     442      $sql .= " ORDER BY masklen(s.cidr) DESC";
     443      queryResults($sql, $webvar{page}, $count, $sfor);
     444
    436445    } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) {
    437446      #print "Finding matches with leading octet(s) $query<br>\n";
    438       $sql = "SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id".
    439         " WHERE text(s.cidr) LIKE '$query%'";
    440       my $count = countRows($sql);
    441       $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
    442       queryResults($sql, $webvar{page}, $count);
     447      $sql = "$sqlbase WHERE text(s.cidr) LIKE ?";
     448      my $count = countRows($sql, "$query%");
     449      $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset";
     450      queryResults($sql, $webvar{page}, $count, "$query%");
    443451    } else {
    444452      # This shouldn't happen, but if it does, whoever gets it deserves what they get...
    … …  
    457465# Takes SQL, page number (for multipage search results), and a total count.
    458466sub queryResults {
    459   my ($sql, $pageNo, $rowCount) = @_;
     467  my $sql = shift;
     468  my $pageNo = shift;
     469  my $rowCount = shift;
     470  my @bindargs = @_;
     471
    460472  my $offset = 0;
    461   $offset = $1 if($sql =~ m/.*limit\s+(.*),.*/);
     473  $offset = $1 if($sql =~ m/.*LIMIT\s+(.*),.*/);
    462474
    463475  my $sth = $ip_dbh->prepare($sql);
    464   $sth->execute();
     476  $sth->execute(@bindargs);
    465477
    466478  $page->param(searchtitle => "Showing all netblock and static-IP allocations");
    … …  
    535547# with the passed SQL statement
    536548sub countRows {
     549  my $sql = shift;
     550
    537551  # Note that the "as foo" is required
    538   my $sth = $ip_dbh->prepare("select count(*) from ($_[0]) as foo");
    539   $sth->execute();
    540   my @a = $sth->fetchrow_array();
    541   $sth->finish();
     552  my @a = $ip_dbh->selectrow_array("SELECT count(*) FROM ($sql) AS foo", undef, @_);
    542553  return $a[0];
    543554}
Note: See TracChangeset for help on using the changeset viewer.