Index: trunk/cgi-bin/search.cgi =================================================================== --- trunk/cgi-bin/search.cgi (revision 906) +++ trunk/cgi-bin/search.cgi (revision 930) @@ -9,5 +9,5 @@ # Last update by $Author$ ### -# Copyright 2005-2010,2012,2015,2016 - Kris Deugau +# Copyright 2005-2010,2012,2015-2017,2022 - Kris Deugau use strict; @@ -101,4 +101,6 @@ # in each (sub)select my $cols = "s.cidr, s.custid, s.type, s.city, s.description, s.id, s.parent_id, s.available, a.vrf"; +# Common base select. JOIN provides the VRF which may not be noted on individual allocations +my $sqlbase = "SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id"; # Handle the DB error first @@ -125,4 +127,7 @@ # IP addresses should only have numbers, digits, and maybe a slash+netmask $searchfor = "ipblock"; + } elsif ($webvar{input} =~ /(?:^\d{6}\-|[A-Z][A-Z]\d\d\d)/) { + # Looks like part of a circuit ID + $searchfor = "circuitid"; } else { # Anything else. @@ -167,8 +172,20 @@ # First chunk of SQL. Filter on custid, description, and notes as necessary. - my $sql = qq(SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id\n); - $sql .= " WHERE $webvar{custexclude} (s.custid ~ '$webvar{custid}')\n" if $webvar{custid}; - $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}; - $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}; + # Putting newlines in the SQL so that any SQL logging is somewhat more readable + # than a gigantic long line of conditions. + my $sql = "$sqlbase\n"; + my @bindargs; + if ($webvar{custid}) { + $sql .= " WHERE $webvar{custexclude} (s.custid ~ ?)\n"; + push @bindargs, $webvar{custid}; + } + if ($webvar{desc}) { + $sql .= " $sqlconcat ($sqlbase WHERE $webvar{descexclude} s.description ~* ?)\n"; + push @bindargs, $webvar{desc}; + } + if ($webvar{notes}) { + $sql .= " $sqlconcat ($sqlbase WHERE $webvar{notesexclude} s.notes ~ ?)"; + push @bindargs, $webvar{notes}; + } # If we're not supposed to search for all types, search for the selected types. @@ -176,7 +193,7 @@ $webvar{typeexclude} = '' if !$webvar{typeexclude}; if ($webvar{alltypes} ne 'on') { - $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{typeexclude} s.type IN ("; + $sql .= " $sqlconcat ($sqlbase WHERE $webvar{typeexclude} s.type IN ("; foreach my $key (keys %webvar) { - $sql .= "'$1'," if $key =~ /type\[(..)\]/; + $sql .= "'$1'," if $key =~ /type\[(\w\w)\]/; } chop $sql; @@ -189,5 +206,5 @@ $webvar{cityexclude} = '' if !$webvar{cityexclude}; if ($webvar{allcities} ne 'on') { - $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{cityexclude} s.city IN ("; + $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cityexclude} s.city IN ("; $sth = $ip_dbh->prepare("SELECT city FROM cities WHERE id=?"); foreach my $key (keys %webvar) { @@ -214,24 +231,27 @@ # /0->/9 are silly to worry about right now. I don't think # we'll be getting a class A anytime soon. - $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE ". - "$webvar{cidrexclude} s.cidr<<='$webvar{cidr}')"; + $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cidrexclude} s.cidr <<= ?)"; + push @bindargs, $webvar{cidr}; } else { # Partial match; beginning of subnet and maskbits are provided # Show any blocks with the leading octet(s) and that masklength # Need some more magic for bare /nn searches: - my $condition = ($net eq '' ? - "masklen(s.cidr)=$maskbits" : "text(s.cidr) like '$net%' and masklen(s.cidr)=$maskbits"); - $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ". - "($condition))"; + $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cidrexclude} (masklen(s.cidr) = ?"; + push @bindargs, $maskbits; + if ($net ne '') { + $sql .= " AND text(s.cidr) LIKE ?"; + push @bindargs, $net; + } + $sql .= "))"; } } elsif ($webvar{cidr} =~ /^(\d{1,3}\.){3}\d{1,3}$/) { # Specific IP address match. Will show either a single netblock, # or a static pool plus an IP. - $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ". - "s.cidr >>= '$webvar{cidr}')"; + $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cidrexclude} s.cidr >>= ?)"; + push @bindargs, $webvar{cidr}; } elsif ($webvar{cidr} =~ /^\d{1,3}(\.(\d{1,3}(\.(\d{1,3}\.?)?)?)?)?$/) { # Leading octets in CIDR - $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ". - "text(s.cidr) like '$webvar{cidr}%')"; + $sql .= " $sqlconcat ($sqlbase WHERE $webvar{cidrexclude} text(s.cidr) LIKE ?)"; + push @bindargs, "$webvar{cidr}%"; } else { # do nothing. @@ -243,8 +263,5 @@ # Find out how many rows the "core" query will return. - my $count = countRows($sql); - -# join against yourself! only master blocks are really guaranteed to have a VRF set - especially in legacy data -#$sql .= " JOIN allocations mv ON + my $count = countRows($sql, @bindargs); if ($count == 0) { @@ -253,9 +270,9 @@ } else { # Add the limit/offset clauses - $sql .= " order by cidr"; - $sql .= " limit $RESULTS_PER_PAGE offset $offset" if $RESULTS_PER_PAGE != 0; + $sql .= " ORDER BY s.cidr"; + $sql .= " LIMIT $RESULTS_PER_PAGE OFFSET $offset" if $RESULTS_PER_PAGE != 0; # And tell the user. print "
Searching...............
\n"; - queryResults($sql, $webvar{page}, $count); + queryResults($sql, $webvar{page}, $count, @bindargs); } @@ -263,6 +280,5 @@ # Node search. - my $sql = "SELECT $cols FROM searchme". - " WHERE cidr IN (SELECT block FROM noderef WHERE node_id=$webvar{node})"; + my $sql = "$sqlbase JOIN noderef nr ON nr.block=s.cidr WHERE nr.node_id = ?"; # Find the offset for multipage results @@ -270,16 +286,18 @@ # Find out how many rows the "core" query will return. - my $count = countRows($sql); + my $count = countRows($sql, $webvar{node}); + + my $nodename = getNodeName($ip_dbh, $webvar{node}); if ($count == 0) { - $page->param(errmsg => "No customers currently listed as connected through this node."); + $page->param(errmsg => "No customers currently listed as connected through $nodename."); ##fixme: still get the results table header } else { # Add the limit/offset clauses - $sql .= " order by cidr"; - $sql .= " limit $RESULTS_PER_PAGE offset $offset" if $RESULTS_PER_PAGE != 0; + $sql .= " ORDER BY cidr"; + $sql .= " LIMIT $RESULTS_PER_PAGE OFFSET $offset" if $RESULTS_PER_PAGE != 0; # And tell the user. - print "
Searching...............
\n"; - queryResults($sql, $webvar{page}, $count); + print "
Searching for assignments terminating on $nodename...
\n"; + queryResults($sql, $webvar{page}, $count, $webvar{node}); } @@ -350,20 +368,13 @@ # Calculate start point for LIMIT clause my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE; - -# Possible cases: -# 1) Partial IP/subnet. Treated as "octet-prefix". -# 2a) CIDR subnet. Exact match. -# 2b) CIDR netmask. YMMV but it should be octet-prefix-with-netmask -# (ie, all matches with the octet prefix *AND* that netmask) -# 3) Customer ID. "Match-any-segment" -# 4) Description. "Match-any-segment" -# 5) Invalid data which might be interpretable as an IP or something, but -# which probably shouldn't be for reasons of sanity. +##fixme: squeeze ORDER BY etc out into somewhere common, or at least an +# includeable bit instead of hardcoding in each block if ($category eq 'all') { - $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id"; + # Sort of pointless, just horks up everything. + $sql = "$sqlbase"; my $count = countRows($sql); - $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset"; + $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset"; queryResults($sql, $webvar{page}, $count); @@ -375,18 +386,27 @@ # 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 $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.custid ilike '%$query%' or s.description like '%$query%'"; - my $count = countRows($sql); - $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset"; - queryResults($sql, $webvar{page}, $count); + $sql = "$sqlbase WHERE s.custid ~* ? OR s.description ~* ?"; + my $count = countRows($sql, $query, $query); + $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset"; + queryResults($sql, $webvar{page}, $count, $query, $query); } elsif ($category eq 'desc') { - print qq(
Searching for descriptions containing '$query'

\n); + print qq(
Searching for description, customer ID, or circuit ID matching '$query'

\n); # Query based on description (includes "name" from old DB). - $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.description ilike '%$query%'". - " or s.custid ilike '%$query%'"; - my $count = countRows($sql); - $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset"; - queryResults($sql, $webvar{page}, $count); + $sql = "$sqlbase WHERE s.description ~* ? OR s.custid ~* ? OR s.circuitid ~* ?"; + my $count = countRows($sql, $query, $query, $query); + $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset"; + queryResults($sql, $webvar{page}, $count, $query, $query, $query); + + } elsif ($category eq 'circuitid') { + + print qq(
Searching for allocations with circuit ID matching '$query'

\n); + # Pretty similar to description and cust searches above, but focus on circuit ID + # JOIN needed for VRF field + $sql = "$sqlbase WHERE s.circuitid ~* ? OR s.description ~* ?"; + my $count = countRows($sql, $query, $query); + $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset"; + queryResults($sql, $webvar{page}, $count, $query, $query); } elsif ($category =~ /ipblock/) { @@ -402,15 +422,15 @@ # /0->/9 are silly to worry about right now. I don't think # we'll be getting a class A anytime soon. - $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$query'"; - queryResults($sql, $webvar{page}, 1); + $sql = "$sqlbase WHERE s.cidr = ?"; + queryResults($sql, $webvar{page}, 1, $query); } else { #print "Finding all blocks with netmask /$maskbits, leading octet(s) $net
\n"; # Partial match; beginning of subnet and maskbits are provided - $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id". - " where text(s.cidr) like '$net%' and text(s.cidr) like '%$maskbits'"; - my $count = countRows($sql); - $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset"; - queryResults($sql, $webvar{page}, $count); + $sql = "$sqlbase WHERE text(s.cidr) LIKE ? AND text(s.cidr) LIKE ?"; + my $count = countRows($sql, "$net%", "%$maskbits"); + $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset"; + queryResults($sql, $webvar{page}, $count, "$net%", "%$maskbits"); } + } elsif ($query =~ /^(\d{1,3}\.){3}\d{1,3}$/) { # Specific IP address match @@ -418,27 +438,15 @@ 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 $cols from searchme s JOIN allocations a ON s.master_id=a.id where text(s.cidr) like '$net%'"); -#print "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where text(s.cidr) like '$net%'"; - -# $sth->execute; -# while (my @data = $sth->fetchrow_array()) { -# my $cidr = new NetAddr::IP $data[0]; -# if ($cidr->contains($sfor) || $cidr == $sfor) { -#print "cidr: $data[0]\n"; -#print "
select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$cidr' and s.type <> 'mm'"; - queryResults( -#"select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$cidr' and s.type <> 'mm'", -"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", - $webvar{page}, 1); -#print $page->output; -# } -# } + $sql = "$sqlbase WHERE s.cidr >>= ? AND s.type <> 'mm'"; + my $count = countRows($sql, $sfor); + $sql .= " ORDER BY masklen(s.cidr) DESC"; + queryResults($sql, $webvar{page}, $count, $sfor); + } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) { #print "Finding matches with leading octet(s) $query
\n"; - $sql = "SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id". - " WHERE text(s.cidr) LIKE '$query%'"; - my $count = countRows($sql); - $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset"; - queryResults($sql, $webvar{page}, $count); + $sql = "$sqlbase WHERE text(s.cidr) LIKE ?"; + my $count = countRows($sql, "$query%"); + $sql .= " ORDER BY s.cidr LIMIT $RESULTS_PER_PAGE OFFSET $offset"; + queryResults($sql, $webvar{page}, $count, "$query%"); } else { # This shouldn't happen, but if it does, whoever gets it deserves what they get... @@ -457,10 +465,14 @@ # Takes SQL, page number (for multipage search results), and a total count. sub queryResults { - my ($sql, $pageNo, $rowCount) = @_; + my $sql = shift; + my $pageNo = shift; + my $rowCount = shift; + my @bindargs = @_; + my $offset = 0; - $offset = $1 if($sql =~ m/.*limit\s+(.*),.*/); + $offset = $1 if($sql =~ m/.*LIMIT\s+(.*),.*/); my $sth = $ip_dbh->prepare($sql); - $sth->execute(); + $sth->execute(@bindargs); $page->param(searchtitle => "Showing all netblock and static-IP allocations"); @@ -535,9 +547,8 @@ # with the passed SQL statement sub countRows { + my $sql = shift; + # Note that the "as foo" is required - my $sth = $ip_dbh->prepare("select count(*) from ($_[0]) as foo"); - $sth->execute(); - my @a = $sth->fetchrow_array(); - $sth->finish(); + my @a = $ip_dbh->selectrow_array("SELECT count(*) FROM ($sql) AS foo", undef, @_); return $a[0]; }