source: trunk/cgi-bin/search.cgi @ 517

Last change on this file since 517 was 517, checked in by Kris Deugau, 11 years ago

/trunk

Finally merge conversion to HTML::Template from /branches/htmlform

  • Node "hack" showed conflict due to having been added to all branches in parallel
  • editDisplay.html was apparently changed enough that the merged delete caused an irrelevant conflict

Closes #3.

  • Property svn:executable set to *
  • Property svn:keywords set to Date Rev Author
File size: 17.4 KB
Line 
1#!/usr/bin/perl
2# ipdb/cgi-bin/search.cgi
3# Started splitting search functions (quick and otherwise) from
4# main IPDB interface 03/11/2005
5###
6# SVN revision info
7# $Date: 2012-10-18 20:53:10 +0000 (Thu, 18 Oct 2012) $
8# SVN revision $Rev: 517 $
9# Last update by $Author: kdeugau $
10###
11# Copyright 2005-2010 - Kris Deugau
12
13use strict;             
14use warnings;   
15use CGI::Carp qw(fatalsToBrowser);
16use CGI::Simple;
17use HTML::Template;
18use DBI;
19use POSIX qw(ceil);
20use NetAddr::IP;
21
22# don't remove!  required for GNU/FHS-ish install from tarball
23##uselib##
24
25use MyIPDB;
26
27# Don't formally need a username or syslog here.  syslog left active for debugging.
28use Sys::Syslog;
29openlog "IPDBsearch","pid","$IPDB::syslog_facility";
30
31# ... but we do *use* the username on ACLs now.
32# Collect the username from HTTP auth.  If undefined, we're in
33# a test environment, or called without a username.
34my $authuser;
35if (!defined($ENV{'REMOTE_USER'})) {
36  $authuser = '__temptest';
37} else {
38  $authuser = $ENV{'REMOTE_USER'};
39}
40
41# Global variables
42my $RESULTS_PER_PAGE = 25;
43
44# anyone got a better name?  :P
45my $thingroot = $ENV{SCRIPT_FILENAME};
46$thingroot =~ s|cgi-bin/search.cgi||;
47
48# Set up the CGI object...
49my $q = new CGI::Simple;
50# ... and get query-string params as well as POST params if necessary
51$q->parse_query_string;
52
53# Convenience;  saves changing all references to %webvar
54##fixme:  tweak for handling <select multiple='y' size=3> (list with multiple selection)
55my %webvar = $q->Vars;
56
57if (defined($webvar{rpp})) {
58  ($RESULTS_PER_PAGE) = ($webvar{rpp} =~ /(\d+)/);
59}
60
61# Why not a global DB handle?  (And a global statement handle, as well...)
62# Use the connectDB function, otherwise we end up confusing ourselves
63my $ip_dbh;
64my $sth;
65my $errstr;
66($ip_dbh,$errstr) = connectDB_My;
67if ($ip_dbh) {
68  checkDBSanity($ip_dbh);
69  initIPDBGlobals($ip_dbh);
70}
71
72# Set up some globals
73$ENV{HTML_TEMPLATE_ROOT} = $thingroot."templates";
74
75my $page;
76if (!defined($webvar{stype})) {
77  $webvar{stype} = "<NULL>";   #shuts up the warnings.
78  $page = HTML::Template->new(filename => "search/compsearch.tmpl");
79} else {
80  $page = HTML::Template->new(filename => "search/sresults.tmpl");
81}
82
83my $header = HTML::Template->new(filename => "header.tmpl");
84$header->param(version => $IPDB::VERSION);
85$header->param(addperm => $IPDBacl{$authuser} =~ /a/);
86print "Content-type: text/html\n\n", $header->output;
87
88# Handle the DB error first
89if (!$ip_dbh) {
90  $page = HTML::Template->new(filename => "dberr.tmpl");
91  $page->param(errmsg => $errstr);
92} elsif ($webvar{stype} eq 'q') {
93  # Quick search.
94
95  if (!$webvar{input}) {
96    # No search term.  Display everything.
97    viewBy('all', '');
98  } else {
99    # Search term entered.  Display matches.
100    # We should really sanitize $webvar{input}, no?
101    my $searchfor;
102    # Chew up leading and trailing whitespace
103    $webvar{input} =~ s/^\s+//;
104    $webvar{input} =~ s/\s+$//;
105    if ($webvar{input} =~ /^\d+$/) {
106      # All-digits, new custID
107      $searchfor = "cust";
108    } elsif ($webvar{input} =~ /^[\d\.]+(\/\d{1,3})?$/) {
109      # IP addresses should only have numbers, digits, and maybe a slash+netmask
110      $searchfor = "ipblock";
111    } else {
112      # Anything else.
113      $searchfor = "desc";
114    }
115    viewBy($searchfor, $webvar{input});
116  }
117
118} elsif ($webvar{stype} eq 'c') {
119  # Complex search.
120
121  # Several major cases, and a whole raft of individual cases.
122  # -> Show all types means we do not need to limit records retrieved by type
123  # -> Show all cities means we do not need to limit records retrieved by city
124  # Individual cases are for the CIDR/IP, CustID, Description, Notes, and individual type
125  # requests.
126
127  my $sqlconcat;
128  if ($webvar{which} eq 'all') {
129    # Must match *all* specified criteria.      ## use INTERSECT or EXCEPT
130    $sqlconcat = "INTERSECT";
131  } elsif ($webvar{which} eq 'any') {
132    # Match on any specified criteria           ## use UNION
133    $sqlconcat = "UNION";
134  } else {
135    # sum-buddy tryn'a game the system.  Match "all"
136    $sqlconcat = "INTERSECT";
137  }
138
139# We actually construct a monster SQL statement for all criteria.
140# Iff something has been entered, it will be used as a filter.
141# Iff something has NOT been entered, we still include it but in
142# such a way that it does not actually filter anything out.
143
144  # Columns actually returned.  Slightly better than hardcoding it
145  # in each (sub)select
146  my $cols = "cidr,custid,type,city,description";
147
148  # First chunk of SQL.  Filter on custid, description, and notes as necessary.
149  my $sql = "(select $cols from searchme where".
150        " $webvar{custexclude} (custid ilike '%$webvar{custid}%'".
151        " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ilike '%$webvar{desc}%')".
152        " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ilike '%$webvar{notes}%')";
153
154  # If we're not supposed to search for all types, search for the selected types.
155  if ($webvar{alltypes} ne 'on') {
156    $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in (";
157    foreach my $key (keys %webvar) {
158      $sql .= "'$1'," if $key =~ /type\[(..)\]/;
159    }
160    chop $sql;
161    $sql .= "))";
162  }
163
164  # If we're not supposed to search for all cities, search for the selected cities.
165  # This could be vastly improved with proper foreign keys in the database.
166  if ($webvar{allcities} ne 'on') {
167    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in (";
168    $sth = $ip_dbh->prepare("select city from cities where id=?");
169    foreach my $key (keys %webvar) {
170      if ($key =~ /city\[(\d+)\]/) {
171        $sth->execute($1);
172        my $city;
173        $sth->bind_columns(\$city);
174        $sth->fetch;
175        $city =~ s/'/''/;
176        $sql .= "'$city',";
177      }
178    }
179    chop $sql;
180    $sql .= "))";
181  }
182
183  ## CIDR query options.
184  $webvar{cidr} =~ s/\s+//;     # Hates the nasty spaceseseses we does.
185  if ($webvar{cidr} eq '') { # We has a blank CIDR.  Ignore it.
186  } elsif ($webvar{cidr} =~ /\//) {
187    # 192.168.179/26 should show all /26 subnets in 192.168.179
188    my ($net,$maskbits) = split /\//, $webvar{cidr};
189    if ($webvar{cidr} =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{2}$/) {
190      # /0->/9 are silly to worry about right now.  I don't think
191      # we'll be getting a class A anytime soon.  <g>
192      $sql .= " $sqlconcat (select $cols from searchme where ".
193        "$webvar{cidrexclude} cidr<<='$webvar{cidr}')";
194    } else {
195      # Partial match;  beginning of subnet and maskbits are provided
196      # Show any blocks with the leading octet(s) and that masklength
197      # Need some more magic for bare /nn searches:
198      my $condition = ($net eq '' ?
199        "masklen(cidr)=$maskbits" : "text(cidr) like '$net%' and masklen(cidr)=$maskbits");
200      $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
201        "($condition))";
202    }
203  } elsif ($webvar{cidr} =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
204    # Specific IP address match.  Will show either a single netblock,
205    # or a static pool plus an IP.
206    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
207        "cidr >>= '$webvar{cidr}')";
208  } elsif ($webvar{cidr} =~ /^\d{1,3}(\.(\d{1,3}(\.(\d{1,3}\.?)?)?)?)?$/) {
209    # Leading octets in CIDR
210    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
211        "text(cidr) like '$webvar{cidr}%')";
212  } else {
213    # do nothing.
214    ##fixme  we'll ignore this to clear out the references to legacy code.
215  } # done with CIDR query options.
216
217  # Find the offset for multipage results
218  my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
219
220  # Find out how many rows the "core" query will return.
221  my $count = countRows($sql);
222
223  if ($count == 0) {
224    $page->param(errmsg => "No matches found.  Try eliminating one of the criteria,".
225        " or making one or more criteria more general.");
226  } else {
227    # Add the limit/offset clauses
228    $sql .= " order by cidr";
229    $sql .= " limit $RESULTS_PER_PAGE offset $offset" if $RESULTS_PER_PAGE != 0;
230    # And tell the user.
231    print "<div class=heading>Searching...............</div>\n";
232    queryResults($sql, $webvar{page}, $count);
233  }
234
235} elsif ($webvar{stype} eq 'n') {
236  # Node search.
237
238  my $sql = "SELECT cidr,custid,type,city,description FROM searchme".
239        " WHERE cidr IN (SELECT block FROM noderef WHERE node_id=$webvar{node})";
240
241  # Find the offset for multipage results
242  my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
243
244  # Find out how many rows the "core" query will return.
245  my $count = countRows($sql);
246
247  if ($count == 0) {
248    $page->param(errmsg => "No customers currently listed as connected through this node.");
249##fixme:  still get the results table header
250  } else {
251    # Add the limit/offset clauses
252    $sql .= " order by cidr";
253    $sql .= " limit $RESULTS_PER_PAGE offset $offset" if $RESULTS_PER_PAGE != 0;
254    # And tell the user.
255    print "<div class=heading>Searching...............</div>\n";
256    queryResults($sql, $webvar{page}, $count);
257  }
258
259} else { # how script was called.  General case is to show the search criteria page.
260
261# Generate table of types
262  $sth = $ip_dbh->prepare("select type,dispname from alloctypes where listorder <500 ".
263        "order by listorder");
264  $sth->execute;
265  my $i=0;
266  my @typelist;
267  while (my ($type,$dispname) = $sth->fetchrow_array) {
268    my %row = (
269        newrow => ($i % 4 == 0),
270        type => $type,
271        dispname => $dispname,
272        endrow => ($i++ % 4 == 3)
273        );
274    push @typelist, \%row;
275  }
276  $page->param(typelist => \@typelist);
277
278# Generate table of cities
279  $sth = $ip_dbh->prepare("select id,city from cities order by city");
280  $sth->execute;
281  $i=0;
282  my @citylist;
283  while (my ($id, $city) = $sth->fetchrow_array) {
284    my %row = (
285        newrow => ($i % 4 == 0),
286        id => $id,
287        city => $city,
288        endrow => ($i++ % 4 == 3)
289        );     
290    push @citylist, \%row;
291  }
292  $page->param(citylist => \@citylist);
293
294}
295
296print $page->output;
297
298# Shut down and clean up.
299finish($ip_dbh);
300
301# We print the footer here, so we don't have to do it elsewhere.
302my $footer = HTML::Template->new(filename => "footer.tmpl");
303# include the admin tools link in the output?
304$footer->param(adminlink => ($IPDBacl{$authuser} =~ /A/));
305
306print $footer->output;
307
308# We shouldn't need to directly execute any code below here;  it's all subroutines.
309exit 0;
310
311
312# viewBy()
313# The quick search
314# Takes a category descriptor and a query string
315# Creates appropriate SQL to run the search and display the results
316# with queryResults()
317sub viewBy($$) {
318  my ($category,$query) = @_;
319
320  # Local variables
321  my $sql;
322
323  # Calculate start point for LIMIT clause
324  my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
325
326# Possible cases:
327# 1) Partial IP/subnet.  Treated as "octet-prefix".
328# 2a) CIDR subnet.  Exact match.
329# 2b) CIDR netmask.  YMMV but it should be octet-prefix-with-netmask
330#       (ie, all matches with the octet prefix *AND* that netmask)
331# 3) Customer ID.  "Match-any-segment"
332# 4) Description.  "Match-any-segment"
333# 5) Invalid data which might be interpretable as an IP or something, but
334#    which probably shouldn't be for reasons of sanity.
335
336  my $cols = "cidr,custid,type,city,description";
337
338  if ($category eq 'all') {
339
340    $sql = "select $cols from searchme";
341    my $count = countRows($sql);
342    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
343    queryResults($sql, $webvar{page}, $count);
344
345  } elsif ($category eq 'cust') {
346
347##fixme:  this and other quick-search areas;  fix up page heading title similar to first grouping above
348    print qq(<div class="heading">Searching for Customer IDs containing '$query'</div><br>\n);
349
350    # Query for a customer ID.  Note that we can't restrict to "numeric-only"
351    # as we have non-numeric custIDs in the legacy data.  :/
352    $sql = "select $cols from searchme where custid ilike '%$query%' or description like '%$query%'";
353    my $count = countRows($sql);
354    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
355    queryResults($sql, $webvar{page}, $count);
356
357  } elsif ($category eq 'desc') {
358
359    print qq(<div class="heading">Searching for descriptions containing '$query'</div><br>\n);
360    # Query based on description (includes "name" from old DB).
361    $sql = "select $cols from searchme where description ilike '%$query%'".
362        " or custid ilike '%$query%'";
363    my $count = countRows($sql);
364    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
365    queryResults($sql, $webvar{page}, $count);
366
367  } elsif ($category =~ /ipblock/) {
368
369    # Query is for a partial IP, a CIDR block in some form, or a flat IP.
370    print qq(<div class="heading">Searching for IP-based matches on '$query'</div><br>\n);
371
372    $query =~ s/\s+//g;
373    if ($query =~ /\//) {
374      # 192.168.179/26 should show all /26 subnets in 192.168.179
375      my ($net,$maskbits) = split /\//, $query;
376      if ($query =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{2}$/) {
377        # /0->/9 are silly to worry about right now.  I don't think
378        # we'll be getting a class A anytime soon.  <g>
379        $sql = "select $cols from searchme where cidr='$query'";
380        queryResults($sql, $webvar{page}, 1);
381      } else {
382        #print "Finding all blocks with netmask /$maskbits, leading octet(s) $net<br>\n";
383        # Partial match;  beginning of subnet and maskbits are provided
384        $sql = "select $cols from searchme where text(cidr) like '$net%' and ".
385                "text(cidr) like '%$maskbits'";
386        my $count = countRows($sql);
387        $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
388        queryResults($sql, $webvar{page}, $count);
389      }
390    } elsif ($query =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
391      # Specific IP address match
392      #print "4-octet pattern found;  finding netblock containing IP $query<br>\n";
393      my ($net,$ip) = ($query =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.)(\d{1,3})/);
394      my $sfor = new NetAddr::IP $query;
395      $sth = $ip_dbh->prepare("select $cols from searchme where text(cidr) like '$net%'");
396      $sth->execute;
397      while (my @data = $sth->fetchrow_array()) {
398        my $cidr = new NetAddr::IP $data[0];
399        if ($cidr->contains($sfor)) {
400          queryResults("select $cols from searchme where cidr='$cidr'", $webvar{page}, 1);
401        }
402      }
403    } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) {
404      #print "Finding matches with leading octet(s) $query<br>\n";
405      $sql = "select $cols from searchme where text(cidr) like '$query%'";
406      my $count = countRows($sql);
407      $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
408      queryResults($sql, $webvar{page}, $count);
409    } else {
410      # This shouldn't happen, but if it does, whoever gets it deserves what they get...
411      $page->param(errmsg => "Invalid query.");
412    }
413  } else {
414    # This shouldn't happen, but if it does, whoever gets it deserves what they get...
415    $page->param(errmsg => "Invalid searchfor.");
416  }
417} # viewBy
418
419
420# args are: a reference to an array with the row to be printed and the
421# class(stylesheet) to use for formatting.
422# if ommitting the class - call the sub as &printRow(\@array)
423sub printRow {
424  my ($rowRef,$class) = @_;
425
426  if (!$class) {
427    print "<tr>\n";
428  } else {
429    print "<tr class=\"$class\">\n";
430  }
431
432ELEMENT:  foreach my $element (@$rowRef) {
433    if (!defined($element)) {
434      print "<td></td>\n";
435      next ELEMENT;
436    }
437    $element =~ s|\n|</br>|g;
438    print "<td>$element</td>\n";
439  }
440  print "</tr>";
441} # printRow
442
443
444# queryResults()
445# Display search queries based on the passed SQL.
446# Takes SQL, page number (for multipage search results), and a total count.
447sub queryResults($$$) {
448  my ($sql, $pageNo, $rowCount) = @_;
449  my $offset = 0;
450  $offset = $1 if($sql =~ m/.*limit\s+(.*),.*/);
451
452  my $sth = $ip_dbh->prepare($sql);
453  $sth->execute();
454
455  $page->param(searchtitle => "Showing all netblock and static-IP allocations");
456
457  my $count = 0;
458  my @sresults;
459  while (my ($block, $custid, $type, $city, $desc) = $sth->fetchrow_array) {
460    my %row = (
461        rowclass => $count++ % 2,
462        issub => ($type =~ /^.r$/ ? 1 : 0),
463        block => $block,
464        ispool => ($type =~ /^.[pd]$/ ? 1 : 0),
465        custid => $custid,
466        disptype => $disp_alloctypes{$type},
467        city => $city,
468        desc => $desc
469        );
470    push @sresults, \%row;
471  }
472  $page->param(sresults => \@sresults);
473
474  # Have to think on this call, it's primarily to clean up unfetched rows from a select.
475  # In this context it's probably a good idea.
476  $sth->finish();
477
478  my $upper = $offset+$count;
479
480  $page->param(resfound => $rowCount);
481  $page->param(resstart => $offset+1);
482  $page->param(resstop => $upper);
483
484  # print the page thing..
485  if ($RESULTS_PER_PAGE > 0 && $rowCount > $RESULTS_PER_PAGE) {
486    $page->param(multipage => 1);
487    my $pages = ceil($rowCount/$RESULTS_PER_PAGE);
488    my @pagelist;
489    for (my $i = 1; $i <= $pages; $i++) {
490      my %row;
491      $row{pgnum} = $i;
492      if ($i == $pageNo) {
493        $row{thispage} = 1;
494      } else {
495        $row{stype} = $webvar{stype};
496        if ($webvar{stype} eq 'c') {
497          $row{extraopts} = "cidr=$webvar{cidr}&custid=$webvar{custid}&desc=$webvar{desc}&".
498                "notes=$webvar{notes}&which=$webvar{which}&alltypes=$webvar{alltypes}&".
499                "allcities=$webvar{allcities}&";
500          foreach my $key (keys %webvar) {
501            if ($key =~ /^(?:type|city)\[/ || $key =~ /exclude$/) {
502              $row{extraopts} .= "$key=$webvar{$key}&";
503            }
504          }
505        } else {
506          $row{extraopts} = "input=$webvar{input}&";
507        }
508      }
509      push @pagelist, \%row;
510    }
511    $page->param(pgnums => \@pagelist);
512  }
513
514} # queryResults
515
516
517# Prints table headings.  Accepts any number of arguments;
518# each argument is a table heading.
519sub startTable {
520  print qq(<center><table width="98%" cellspacing="0" class="center"><tr>);
521
522  foreach(@_) {
523    print qq(<td class="heading">$_</td>);
524  }
525  print "</tr>\n";
526} # startTable
527
528
529# Return count of rows to be returned in a "real" query
530# with the passed SQL statement
531sub countRows($) {
532  # Note that the "as foo" is required
533  my $sth = $ip_dbh->prepare("select count(*) from ($_[0]) as foo");
534  $sth->execute();
535  my @a = $sth->fetchrow_array();
536  $sth->finish();
537  return $a[0];
538}
Note: See TracBrowser for help on using the repository browser.