source: branches/stable/cgi-bin/search.cgi@ 604

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

/branches/stable

File off the last few rough edges (make sure webpath is used everywhere

appropriate)

Update copyright dates
Bump the version for release

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