Index: branches/stable/cgi-bin/CustIDCK.pm =================================================================== --- branches/stable/cgi-bin/CustIDCK.pm (revision 593) +++ branches/stable/cgi-bin/CustIDCK.pm (revision 594) @@ -34,8 +34,12 @@ my $custid = shift; - return 1 if $custid =~ /^STAFF$/; - return 1 if $custid =~ /^5554242$/; # just in case some later change might block this - return 1 if $custid =~ /^\d{7}$/; - return 1 if $custid =~ /^\d{10}$/; + # hardcoded "OK" custids. + return 1 if $custid =~ /^STAFF(?:-\d\d?)?$/; + return 1 if $custid =~ /^5554242(?:-\d\d?)?$/; # just in case some later change might block this + return 1 if $custid =~ /^\d{7}(?:-\d\d?)?$/; + return 1 if $custid =~ /^\d{10}(?:-\d\d?)?$/; + + # Force uppercase for now... + $custid =~ tr/a-z/A-Z/; # some example code for a database check @@ -66,17 +70,13 @@ # We should have a valid DB connection by now. - my $sth = $dbh->prepare("SELECT custid FROM custid WHERE custid = '$custid'"); - $sth->execute; + + my $hr = $dbh->selectrow_hashref("SELECT custid FROM custid WHERE custid = ?", undef, ($custid) ); + my $status = 0; if ($dbh->err) { $CustIDCK::Error = 1; $CustIDCK::ErrMsg = $dbh->errstr(); - $sth->finish; - $dbh->disconnect; - return 0; + } else { + $status = 1 if ( $hr->{custid} ); } - my $hr = $sth->fetchrow_hashref(); - my $status = 0; - $status = 1 if ( $hr->{custid} ); - $sth->finish; $dbh->disconnect; return $status; Index: branches/stable/cgi-bin/IPDB.pm =================================================================== --- branches/stable/cgi-bin/IPDB.pm (revision 593) +++ branches/stable/cgi-bin/IPDB.pm (revision 594) @@ -23,8 +23,14 @@ @ISA = qw(Exporter); @EXPORT_OK = qw( - %disp_alloctypes %list_alloctypes %def_custids @citylist @poplist @masterblocks - %allocated %free %routed %bigfree %IPDBacl %aclmsg - &initIPDBGlobals &connectDB &finish &checkDBSanity &allocateBlock &addMaster - &deleteBlock &getBlockData &mailNotify + %disp_alloctypes %list_alloctypes %def_custids @citylist @poplist + %IPDBacl %aclmsg + &initIPDBGlobals &connectDB &finish &checkDBSanity + &addMaster &touchMaster + &listSummary &listMaster &listRBlock &listFree &listPool + &getMasterList &getTypeList &getPoolSelect &findAllocateFrom + &ipParent &subParent &blockParent &getRoutedCity + &allocateBlock &updateBlock &deleteBlock &getBlockData + &getNodeList &getNodeName &getNodeInfo + &mailNotify ); @@ -32,7 +38,13 @@ %EXPORT_TAGS = ( ALL => [qw( %disp_alloctypes %list_alloctypes %def_custids @citylist @poplist - @masterblocks %allocated %free %routed %bigfree %IPDBacl %aclmsg - &initIPDBGlobals &connectDB &finish &checkDBSanity &allocateBlock - &addMaster &deleteBlock &getBlockData &mailNotify + %IPDBacl %aclmsg + &initIPDBGlobals &connectDB &finish &checkDBSanity + &addMaster &touchMaster + &listSummary &listMaster &listRBlock &listFree &listPool + &getMasterList &getTypeList &getPoolSelect &findAllocateFrom + &ipParent &subParent &blockParent &getRoutedCity + &allocateBlock &updateBlock &deleteBlock &getBlockData + &getNodeList &getNodeName &getNodeInfo + &mailNotify )] ); @@ -46,9 +58,4 @@ our @citylist; our @poplist; -our @masterblocks; -our %allocated; -our %free; -our %routed; -our %bigfree; our %IPDBacl; @@ -113,17 +120,4 @@ } - # Master block list - $sth = $dbh->prepare("select cidr from masterblocks order by cidr"); - $sth->execute; - return (undef,$sth->errstr) if $sth->err; - for (my $i=0; my @data = $sth->fetchrow_array(); $i++) { - $masterblocks[$i] = new NetAddr::IP $data[0]; - $allocated{"$masterblocks[$i]"} = 0; - $free{"$masterblocks[$i]"} = 0; - $bigfree{"$masterblocks[$i]"} = 128; # Larger number means smaller block. - # Set to 128 to prepare for IPv6 - $routed{"$masterblocks[$i]"} = 0; - } - # Load ACL data. Specific username checks are done at a different level. $sth = $dbh->prepare("select username,acl from users"); @@ -232,33 +226,28 @@ # Wrap all the SQL in a transaction eval { - my $sth = $dbh->prepare("select count(*) from masterblocks where cidr <<= '$cidr'"); - $sth->execute; - my @data = $sth->fetchrow_array; - - if ($data[0] eq 0) { + my ($mexist) = $dbh->selectrow_array("SELECT cidr FROM masterblocks WHERE cidr <<= ?", undef, ($cidr) ); + + if (!$mexist) { # First case - master is brand-spanking-new. ##fixme: rwhois should be globally-flagable somewhere, much like a number of other things ## maybe a db table called "config"? - $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')"); - $sth->execute; + $dbh->do("INSERT INTO masterblocks (cidr,rwhois) VALUES (?,?)", undef, ($cidr,'y') ); # Unrouted blocks aren't associated with a city (yet). We don't rely on this # elsewhere though; legacy data may have traps and pitfalls in it to break this. # Thus the "routed" flag. - - $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)". - " values ('$cidr',".$cidr->masklen.",'','n')"); - $sth->execute; + $dbh->do("INSERT INTO freeblocks (cidr,maskbits,city,routed) VALUES (?,?,?,?)", undef, + ($cidr, $cidr->masklen, '', 'n') ); # If we get here, everything is happy. Commit changes. $dbh->commit; - } # new master does not contain existing master(s) + } # done new master does not contain existing master(s) else { # collect the master(s) we're going to absorb, and snag the longest netmask while we're at it. my $smallmask = $cidr->masklen; - $sth = $dbh->prepare("select cidr as mask from masterblocks where cidr <<= '$cidr'"); - $sth->execute; + my $sth = $dbh->prepare("SELECT cidr FROM masterblocks WHERE cidr <<= ?"); + $sth->execute($cidr); my @cmasters; while (my @data = $sth->fetchrow_array) { @@ -279,7 +268,6 @@ # collect the unrouted free blocks within the new master - $sth = $dbh->prepare("select cidr from freeblocks where ". - "maskbits>=$smallmask and cidr <<= '$cidr' and routed='n'"); - $sth->execute; + $sth = $dbh->prepare("SELECT cidr FROM freeblocks WHERE maskbits <= ? AND cidr <<= ? AND routed = 'n'"); + $sth->execute($smallmask, $cidr); while (my @data = $sth->fetchrow_array) { my $freeblock = new NetAddr::IP $data[0]; @@ -293,16 +281,14 @@ # freeblocks - $sth = $dbh->prepare("delete from freeblocks where cidr <<= ?"); - my $sth2 = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed) values (?,?,'','n')"); + $sth = $dbh->prepare("DELETE FROM freeblocks WHERE cidr <<= ?"); + my $sth2 = $dbh->prepare("INSERT INTO freeblocks (cidr,maskbits,city,routed) VALUES (?,?,'','n')"); foreach my $newblock (@blocklist) { - $sth->execute("$newblock"); - $sth2->execute("$newblock", $newblock->masklen); + $sth->execute($newblock); + $sth2->execute($newblock, $newblock->masklen); } # master - $sth = $dbh->prepare("delete from masterblocks where cidr <<= '$cidr'"); - $sth->execute; - $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')"); - $sth->execute; + $dbh->do("DELETE FROM masterblocks WHERE cidr <<= ?", undef, ($cidr) ); + $dbh->do("INSERT INTO masterblocks (cidr,rwhois) VALUES (?,?)", undef, ($cidr, 'y') ); # *whew* If we got here, we likely suceeded. @@ -319,4 +305,355 @@ } } # end addMaster + + +## IPDB::touchMaster() +# Update last-changed timestamp on a master block. +sub touchMaster { + my $dbh = shift; + my $master = shift; + + local $dbh->{AutoCommit} = 0; + local $dbh->{RaiseError} = 1; + + eval { + $dbh->do("UPDATE masterblocks SET mtime=now() WHERE cidr = ?", undef, ($master)); + $dbh->commit; + }; + + if ($@) { + my $msg = $@; + eval { $dbh->rollback; }; + return ('FAIL',$msg); + } + return ('OK','OK'); +} # end touchMaster() + + +## IPDB::listSummary() +# Get summary list of all master blocks +# Returns an arrayref to a list of hashrefs containing the master block, routed count, +# allocated count, free count, and largest free block masklength +sub listSummary { + my $dbh = shift; + + my $mlist = $dbh->selectall_arrayref("SELECT cidr AS master FROM masterblocks ORDER BY cidr", { Slice => {} }); + + foreach (@{$mlist}) { + my ($rcnt) = $dbh->selectrow_array("SELECT count(*) FROM routed WHERE cidr <<= ?", undef, ($$_{master})); + $$_{routed} = $rcnt; + my ($acnt) = $dbh->selectrow_array("SELECT count(*) FROM allocations WHERE cidr <<= ?", undef, ($$_{master})); + $$_{allocated} = $acnt; + my ($fcnt) = $dbh->selectrow_array("SELECT count(*) FROM freeblocks WHERE cidr <<= ?". + " AND (routed='y' OR routed='n')", undef, ($$_{master})); + $$_{free} = $fcnt; + my ($bigfree) = $dbh->selectrow_array("SELECT maskbits FROM freeblocks WHERE cidr <<= ?". + " AND (routed='y' OR routed='n') ORDER BY maskbits LIMIT 1", undef, ($$_{master})); +##fixme: should find a way to do this without having to HTMLize the <> + $bigfree = "/$bigfree" if $bigfree; + $bigfree = '' if !$bigfree; + $$_{bigfree} = $bigfree; + } + return $mlist; +} # end listSummary() + + +## IPDB::listMaster() +# Get list of routed blocks in the requested master +# Returns an arrayref to a list of hashrefs containing the routed block, POP/city the block is routed to, +# allocated count, free count, and largest free block masklength +sub listMaster { + my $dbh = shift; + my $master = shift; + + my $rlist = $dbh->selectall_arrayref("SELECT cidr AS block,city FROM routed WHERE cidr <<= ? ORDER BY cidr", + { Slice => {} }, ($master) ); + + foreach (@{$rlist}) { + my ($acnt) = $dbh->selectrow_array("SELECT count(*) FROM allocations WHERE cidr <<= ?", undef, ($$_{block})); + $$_{nsubs} = $acnt; + my ($fcnt) = $dbh->selectrow_array("SELECT count(*) FROM freeblocks WHERE cidr <<= ?". + " AND (routed='y' OR routed='n')", undef, ($$_{block})); + $$_{nfree} = $fcnt; + my ($bigfree) = $dbh->selectrow_array("SELECT maskbits FROM freeblocks WHERE cidr <<= ?". + " AND (routed='y' OR routed='n') ORDER BY maskbits LIMIT 1", undef, ($$_{block})); +##fixme: should find a way to do this without having to HTMLize the <> + $bigfree = "/$bigfree" if $bigfree; + $bigfree = '' if !$bigfree; + $$_{lfree} = $bigfree; + } + return $rlist; +} # end listMaster() + + +## IPDB::listRBlock() +# Gets a list of free blocks in the requested parent/master in both CIDR and range notation +# Takes a parent/master and an optional flag to look at routed or unrouted blocks, depending +# on whether the master is a direct master or a routed block +# Returns an arrayref to a list of hashrefs containing the CIDR and range-notation blocks +sub listRBlock { + my $dbh = shift; + my $routed = shift; + + # Snag the allocations for this block + my $sth = $dbh->prepare("SELECT cidr,city,type,custid,swip,description". + " FROM allocations WHERE cidr <<= ? ORDER BY cidr"); + $sth->execute($routed); + + # hack hack hack + # set up to flag swip=y records if they don't actually have supporting data in the customers table + my $custsth = $dbh->prepare("SELECT count(*) FROM customers WHERE custid = ?"); + + my @blocklist; + while (my ($cidr,$city,$type,$custid,$swip,$desc) = $sth->fetchrow_array()) { + $custsth->execute($custid); + my ($ncust) = $custsth->fetchrow_array(); + my %row = ( + block => $cidr, + city => $city, + type => $disp_alloctypes{$type}, + custid => $custid, + swip => ($swip eq 'y' ? 'Yes' : 'No'), + partswip => ($swip eq 'y' && $ncust == 0 ? 1 : 0), + desc => $desc + ); + $row{subblock} = ($type =~ /^.r$/); # hmf. wonder why these won't work in the hash declaration... + $row{listpool} = ($type =~ /^.[pd]$/); + push (@blocklist, \%row); + } + return \@blocklist; +} # end listRBlock() + + +## IPDB::listFree() +# Gets a list of free blocks in the requested parent/master in both CIDR and range notation +# Takes a parent/master and an optional "routed or unrouted" flag that defaults to unrouted. +# Returns an arrayref to a list of hashrefs containing the CIDR and range-notation blocks +# Returns some extra flags in the hashrefs for routed blocks, since those can have several subtypes +sub listFree { + my $dbh = shift; + my $master = shift; + my $routed = shift || 'n'; + + # do it this way so we can waste a little less time iterating + my $sth = $dbh->prepare("SELECT cidr,routed FROM freeblocks WHERE cidr <<= ? AND ". + ($routed eq 'n' ? '' : 'NOT')." routed = 'n' ORDER BY cidr"); + $sth->execute($master); + my @flist; + while (my ($cidr,$rtype) = $sth->fetchrow_array()) { + $cidr = new NetAddr::IP $cidr; + my %row = ( + fblock => "$cidr", + frange => $cidr->range, + ); + if ($routed eq 'y') { + $row{subblock} = ($rtype ne 'y' && $rtype ne 'n'); + $row{fbtype} = $rtype; + } + push @flist, \%row; + } + return \@flist; +} # end listFree() + + +## IPDB::listPool() +# +sub listPool { + my $dbh = shift; + my $pool = shift; + + my $sth = $dbh->prepare("SELECT ip,custid,available,description,type". + " FROM poolips WHERE pool = ? ORDER BY ip"); + $sth->execute($pool); + my @poolips; + while (my ($ip,$custid,$available,$desc,$type) = $sth->fetchrow_array) { + my %row = ( + ip => $ip, + custid => $custid, + available => $available, + desc => $desc, + delme => $available eq 'n' + ); + push @poolips, \%row; + } + return \@poolips; +} # end listPool() + + +## IPDB::getMasterList() +# Get a list of master blocks, optionally including last-modified timestamps +# Takes an optional flag to indicate whether to include timestamps; +# 'm' includes ctime, all others (suggest 'c') do not. +# Returns an arrayref to a list of hashrefs +sub getMasterList { + my $dbh = shift; + my $stampme = shift || 'm'; # optional but should be set by caller for clarity + + my $mlist = $dbh->selectall_arrayref("SELECT cidr AS master".($stampme eq 'm' ? ',mtime' : ''). + " FROM masterblocks ORDER BY cidr", { Slice => {} }); + return $mlist; +} # end getMasterList() + + +## IPDB::getTypeList() +# Get an alloctype/description pair list suitable for dropdowns +# Takes a flag to determine which general groups of types are returned +# Returns an reference to an array of hashrefs +sub getTypeList { + my $dbh = shift; + my $tgroup = shift || 'a'; # technically optional, like this, but should + # really be specified in the call for clarity + my $tlist; + if ($tgroup eq 'p') { + # grouping 'p' - primary allocation types. These include static IP pools (_d and _p), + # dynamic-allocation ranges (_e), containers (_c), and the "miscellaneous" cn, in, and en types. + $tlist = $dbh->selectall_arrayref("SELECT type,listname FROM alloctypes WHERE listorder < 500 ". + "AND type NOT LIKE '_i' AND type NOT LIKE '_r' ORDER BY listorder", { Slice => {} }); + } elsif ($tgroup eq 'c') { + # grouping 'c' - contained types. These include all static IPs and all _r types. + $tlist = $dbh->selectall_arrayref("SELECT type,listname FROM alloctypes WHERE listorder <= 500 ". + " AND (type LIKE '_i' OR type LIKE '_r') ORDER BY listorder", { Slice => {} }); + } else { + # grouping 'a' - all standard allocation types. This includes everything + # but mm (present only as a formality). Make this the default. + $tlist = $dbh->selectall_arrayref("SELECT type,listname FROM alloctypes WHERE listorder <= 500 ". + " ORDER BY listorder", { Slice => {} }); + } + return $tlist; +} + + +## IPDB::getPoolSelect() +# Get a list of pools matching the passed city and type that have 1 or more free IPs +# Returns an arrayref to a list of hashrefs +sub getPoolSelect { + my $dbh = shift; + my $iptype = shift; + my $pcity = shift; + + my ($ptype) = ($iptype =~ /^(.)i$/); + return if !$ptype; + $ptype .= '_'; + + my $plist = $dbh->selectall_arrayref( + "SELECT (SELECT city FROM allocations WHERE cidr=poolips.pool) AS poolcit, ". + "poolips.pool AS poolblock, COUNT(*) AS poolfree FROM poolips,allocations ". + "WHERE poolips.available='y' AND poolips.pool=allocations.cidr ". + "AND allocations.city = ? AND poolips.type LIKE ? ". + "GROUP BY pool", { Slice => {} }, ($pcity, $ptype) ); + return $plist; +} # end getPoolSelect() + + +## IPDB::findAllocateFrom() +# Find free block to add a new allocation from. (CIDR block version of pool select above, more or less) +# Takes +# - mask length +# - allocation type +# - POP city "parent" +# - optional master-block restriction +# - optional flag to allow automatic pick-from-private-network-ranges +# Returns a string with the first CIDR block matching the criteria, if any +sub findAllocateFrom { + my $dbh = shift; + my $maskbits = shift; + my $type = shift; + my $city = shift; + my $pop = shift; + my %optargs = @_; + + my $failmsg = "No suitable free block found\n"; + +## Set up the SQL to find out what freeblock we can (probably) use for an allocation. +## Very large systems will require development of a reserve system (possibly an extension +## of the reserve-for-expansion concept in https://secure.deepnet.cx/trac/ipdb/ticket/24?) +## Also populate a value list for the DBI call. + + my @vallist = ($maskbits, ($type eq 'rm' ? 'n' : ($type =~ /^(.)r$/ ? "$1" : 'y')) ); + my $sql = "SELECT cidr FROM freeblocks WHERE maskbits <= ? AND routed = ?"; + + # for PPP(oE) and container types, the POP city is the one attached to the pool. + # individual allocations get listed with the customer city site. + ##fixme: chain cities to align roughly with a full layer-2 node graph + $city = $pop if $type !~ /^.[pc]$/; + if ($type ne 'rm' && $city) { + $sql .= " AND city = ?"; + push @vallist, $city; + } + # Allow specifying an arbitrary full block, instead of a master + if ($optargs{gimme}) { + $sql .= " AND cidr >>= ?"; + push @vallist, $optargs{gimme}; + } + # if a specific master was requested, allow the requestor to self->shoot(foot) + if ($optargs{master} && $optargs{master} ne '-') { + $sql .= " AND cidr <<= ?" if $optargs{master} ne '-'; + push @vallist, $optargs{master}; + } else { + # if a specific master was NOT requested, filter out the RFC 1918 private networks + if (!$optargs{allowpriv}) { + $sql .= " AND NOT (cidr <<= '192.168.0.0/16' OR cidr <<= '10.0.0.0/8' OR cidr <<= '172.16.0.0/12')"; + } + } + # Sorting and limiting, since we don't (currently) care to provide a selection of + # blocks to carve up. This preserves something resembling optimal usage of the IP + # space by forcing contiguous allocations and free blocks as much as possible. + $sql .= " ORDER BY maskbits DESC,cidr LIMIT 1"; + + my ($fbfound) = $dbh->selectrow_array($sql, undef, @vallist); + return $fbfound; +} # end findAllocateFrom() + + +## IPDB::ipParent() +# Get an IP's parent pool's details +# Takes a database handle and IP +# Returns a hashref to the parent pool block, if any +sub ipParent { + my $dbh = shift; + my $block = shift; + + my $pinfo = $dbh->selectrow_hashref("SELECT cidr,custid,type,city,description FROM allocations". + " WHERE cidr >>= ?", undef, ($block) ); + return $pinfo; +} # end ipParent() + + +## IPDB::subParent() +# Get a block's parent's details +# Takes a database handle and CIDR block +# Returns a hashref to the parent container block, if any +sub subParent { + my $dbh = shift; + my $block = shift; + + my $pinfo = $dbh->selectrow_hashref("SELECT cidr,custid,type,city,description FROM allocations". + " WHERE cidr >>= ?", undef, ($block) ); + return $pinfo; +} # end subParent() + + +## IPDB::blockParent() +# Get a block's parent's details +# Takes a database handle and CIDR block +# Returns a hashref to the parent container block, if any +sub blockParent { + my $dbh = shift; + my $block = shift; + + my $pinfo = $dbh->selectrow_hashref("SELECT cidr,city FROM routed". + " WHERE cidr >>= ?", undef, ($block) ); + return $pinfo; +} # end blockParent() + + +## IPDB::getRoutedCity() +# Get the city for a routed block. +sub getRoutedCity { + my $dbh = shift; + my $block = shift; + + my ($rcity) = $dbh->selectrow_array("SELECT city FROM routed WHERE cidr = ?", undef, ($block) ); + return $rcity; +} # end getRoutedCity() @@ -362,18 +699,19 @@ # and available='y' order by ip limit 1); -# If no specific IP was requested, pick the next available one from the pool. - if (!$cidr) { - $sth = $dbh->prepare("select ip from poolips where pool='$alloc_from'". - " and available='y' order by ip"); - $sth->execute; - - my @data = $sth->fetchrow_array; - $cidr = $data[0]; # $cidr is already declared when we get here! - } - - $sth = $dbh->prepare("update poolips set custid=?,city=?,". - "available='n',description=?,notes=?,circuitid=?,privdata=?". - " where ip=?"); - $sth->execute($custid, $city, $desc, $notes, $circid, $privdata, "$cidr"); + if ($cidr) { + my ($isavail) = $dbh->selectrow_array("SELECT available FROM poolips WHERE ip=?", undef, ($cidr) ); + if ($isavail eq 'n') { + die "IP already allocated. Deallocate and reallocate, or update the entry\n"; + } + if (!$isavail) { + die "IP is not in an IP pool.\n"; + } + } else { + ($cidr) = $dbh->selectrow_array("SELECT ip FROM poolips WHERE pool=? AND available='y' ORDER BY ip", + undef, ($alloc_from) ); + } + $dbh->do("UPDATE poolips SET custid=?,city=?,available='n',description=?,notes=?,circuitid=?,privdata=? ". + "WHERE ip=?", undef, ($custid, $city, $desc, $notes, $circid, $privdata, $cidr) ); + # node hack if ($nodeid && $nodeid ne '') { @@ -382,8 +720,9 @@ } # end node hack + $dbh->commit; }; if ($@) { - $msg .= ": '".$sth->errstr."'"; + $msg .= ": $@"; eval { $dbh->rollback; }; return ('FAIL',$msg); @@ -634,4 +973,76 @@ +## IPDB::updateBlock() +# Update an allocation +# Takes all allocation fields in a hash +sub updateBlock { + my $dbh = shift; + my %args = @_; + + return ('FAIL', 'Missing block to update') if !$args{block}; + + # do it all in a transaction + local $dbh->{AutoCommit} = 0; + local $dbh->{RaiseError} = 1; + + my @fieldlist; + my @vallist; + foreach ('custid', 'city', 'description', 'notes', 'circuitid', 'privdata') { + if ($args{$_}) { + push @fieldlist, $_; + push @vallist, $args{$_}; + } + } + + my $updtable = 'allocations'; + my $keyfield = 'cidr'; + if ($args{type} =~ /^(.)i$/) { + $updtable = 'poolips'; + $keyfield = 'ip'; + } else { +## fixme: there's got to be a better way... + if ($args{swip}) { + if ($args{swip} eq 'on' || $args{swip} eq '1' || $args{swip} eq 'y') { + $args{swip} = 'y'; + } else { + $args{swip} = 'n'; + } + } + foreach ('type', 'swip') { + if ($args{$_}) { + push @fieldlist, $_; + push @vallist, $args{$_}; + } + } + } + + return ('FAIL', 'No fields to update') if !@fieldlist; + + push @vallist, $args{block}; + my $sql = "UPDATE $updtable SET "; + $sql .= join " = ?, ", @fieldlist; + $sql .= " = ? WHERE $keyfield = ?"; + + eval { + # do the update + $dbh->do($sql, undef, @vallist); + + if ($args{node}) { + # done with delete/insert so we don't have to worry about funkyness updating a node ref that isn't there + $dbh->do("DELETE FROM noderef WHERE block = ?", undef, ($args{block}) ); + $dbh->do("INSERT INTO noderef (block,node_id) VALUES (?,?)", undef, ($args{block}, $args{node}) ); + } + + $dbh->commit; + }; + if ($@) { + my $msg = $@; + $dbh->rollback; + return ('FAIL', $msg); + } + return 0; +} # end updateBlock() + + ## IPDB::deleteBlock() # Removes an allocation from the database, including deleting IPs @@ -843,14 +1254,65 @@ ## IPDB::getBlockData() -# Return custid, type, city, and description for a block +# Get CIDR or IP, custid, type, city, circuit ID, description, notes, modification time, private/restricted +# data, for a CIDR block or pool IP +# Also returns SWIP status flag for CIDR blocks +# Takes the block/IP to look up +# Returns an arrayref to a list of hashrefs sub getBlockData { my $dbh = shift; my $block = shift; - my $sth = $dbh->prepare("select cidr,custid,type,city,description from searchme". - " where cidr='$block'"); - $sth->execute(); - return $sth->fetchrow_array(); + my $cidr = new NetAddr::IP $block; + + my $keycol = 'cidr'; + my $blocktable = 'allocations'; + my $poolip = 0; + + # Pool IP and IPv6 check all in one! Still needs to be tightened + # up a little for the as-yet-unhandled case of IPv6 IP pools + if ($cidr->bits == 32 && $cidr->masklen == 32) { + $poolip = 1; + $keycol = 'ip'; + $blocktable = 'poolips'; + } + my $binfo = $dbh->selectrow_hashref("SELECT $keycol AS block, custid, type, city, circuitid, description,". + " notes, modifystamp AS lastmod, privdata".($poolip ? '' : ', swip')." FROM $blocktable". + " WHERE $keycol = ?", undef, ($block) ); + return $binfo; } # end getBlockData() + + +## IPDB::getNodeList() +# Gets a list of node ID+name pairs as an arrayref to a list of hashrefs +sub getNodeList { + my $dbh = shift; + + my $ret = $dbh->selectall_arrayref("SELECT node_id, node_name FROM nodes ORDER BY node_type,node_id", + { Slice => {} }); + return $ret; +} # end getNodeList() + + +## IPDB::getNodeName() +# Get node name from the ID +sub getNodeName { + my $dbh = shift; + my $nid = shift; + + my ($nname) = $dbh->selectrow_array("SELECT node_name FROM nodes WHERE node_id = ?", undef, ($nid) ); + return $nname; +} # end getNodeName() + + +## IPDB::getNodeInfo() +# Get node name and ID associated with a block +sub getNodeInfo { + my $dbh = shift; + my $block = shift; + + my ($nid, $nname) = $dbh->selectrow_array("SELECT nodes.node_id,node_name FROM nodes INNER JOIN noderef". + " ON nodes.node_id=noderef.node_id WHERE noderef.block = ?", undef, ($block) ); + return ($nid, $nname); +} # end getNodeInfo() Index: branches/stable/cgi-bin/admin.cgi =================================================================== --- branches/stable/cgi-bin/admin.cgi (revision 593) +++ branches/stable/cgi-bin/admin.cgi (revision 594) @@ -69,4 +69,8 @@ } +if(!defined($webvar{action})) { + $webvar{action} = "main"; #shuts up the warnings. +} + # handle DB error output if ($webvar{action} eq 'dberr') { @@ -88,8 +92,4 @@ my $header = HTML::Template->new(filename => "admin/header.tmpl"); -if(!defined($webvar{action})) { - $webvar{action} = "main"; #shuts up the warnings. -} - my $page; if (-e "$ENV{HTML_TEMPLATE_ROOT}/admin/$webvar{action}.tmpl") { @@ -103,31 +103,10 @@ $header->param(mainpage => 1); - $sth = $ip_dbh->prepare("select type,listname from alloctypes where listorder < 900 order by listorder"); - $sth->execute; - - my @typelist; - my $count = 0; - while (my ($type,$listname) = $sth->fetchrow_array) { - my %row = ( - selected => $count++, - type => $type, - dispname => $listname - ); - push @typelist, \%row; - } - $page->param(typelist => \@typelist); - - my @masterlist; - $sth = $ip_dbh->prepare("select cidr,mtime from masterblocks order by cidr"); - $sth->execute; - while (my ($cidr,$mtime) = $sth->fetchrow_array) { - my %row = ( - master => $cidr, - masterdate => $mtime - ); - push @masterlist, \%row; - } - $page->param(masterlist => \@masterlist); - + my $tlist = getTypeList($ip_dbh, 'a'); + $tlist->[0]->{sel} = 1; + $page->param(typelist => $tlist); + + my $mlist = getMasterList($ip_dbh, 'm'); + $page->param(masterlist => $mlist); } @@ -136,29 +115,23 @@ elsif ($webvar{action} eq 'alloc') { - if ($webvar{cidr} !~ /^\s*(\d{1,3}\.){3}\d{1,3}(\/\d{2})?\s*$/) { + my $cidr = new NetAddr::IP $webvar{cidr}; + if (!$cidr || "$cidr" =~ /^0/) { $page->param(errmsg => "Can't allocate something that's not a netblock/ip"); goto ERRJUMP; } - $sth = $ip_dbh->prepare("select def_custid from alloctypes where type='$webvar{alloctype}'"); - $sth->execute; - my @data = $sth->fetchrow_array; - my $custid = $data[0]; + my $custid = $def_custids{$webvar{alloctype}}; if ($custid eq '') { - if ($webvar{custid} !~ /^(?:\d{10}|\d{7}|STAFF)(?:-\d\d?)?$/) { - # Force uppercase for now... - $webvar{custid} =~ tr/a-z/A-Z/; - # Crosscheck with billing. - my $status = CustIDCK->custid_exist($webvar{custid}); - if ($CustIDCK::Error) { - $page->param(errmsg => "Error verifying customer ID: ".$CustIDCK::ErrMsg); - goto ERRJUMP; - } - if (!$status) { - $page->param(errmsg => "Customer ID not valid. Make sure the Customer ID ". - "is correct.
\nUse STAFF for staff static IPs, and $IPDB::defcustid for any other ". - "non-customer assignments."); - goto ERRJUMP; - } + # Crosscheck with billing. + my $status = CustIDCK->custid_exist($webvar{custid}); + if ($CustIDCK::Error) { + $page->param(errmsg => "Error verifying customer ID: ".$CustIDCK::ErrMsg); + goto ERRJUMP; + } + if (!$status) { + $page->param(errmsg => "Customer ID not valid. Make sure the Customer ID ". + "is correct.
\nUse STAFF for staff static IPs, and $IPDB::defcustid for any other ". + "non-customer assignments."); + goto ERRJUMP; } # Type that doesn't have a default custid @@ -166,30 +139,31 @@ } - my $cidr = new NetAddr::IP $webvar{cidr}; - my @data; + my $maskbits = $cidr->masklen; + my $fbtmp = findAllocateFrom($ip_dbh, $maskbits, $webvar{alloctype}, '','', + (gimme => "$cidr", allowpriv => 1)); + if ($webvar{alloctype} eq 'rm') { - $sth = $ip_dbh->prepare("select cidr from freeblocks where cidr >>='$cidr' and routed='n'"); - $sth->execute; - @data = $sth->fetchrow_array; -# User deserves errors if user can't be bothered to find the free block first. - if (!$data[0]) { + if (!$fbtmp) { $page->param(errmsg => "Can't allocate from outside a free block!!"); goto ERRJUMP; } } elsif ($webvar{alloctype} =~ /^(.)i$/) { - $sth = $ip_dbh->prepare("select cidr from allocations where cidr >>='$cidr' and (type like '_d' or type like '_p')"); - $sth->execute; - @data = $sth->fetchrow_array; -# User deserves errors if user can't be bothered to find the pool and a free IP first. - if (!$data[0]) { + my $iptype = $1; + my $ptmp = ipParent($ip_dbh, "$cidr"); + if ($ptmp->{type} =~ /^(.)[dp]$/) { + my $newiptype = "$1i"; + $fbtmp = $ptmp->{cidr}; + if ($ptmp->{type} !~ /^$iptype./) { + $page->param(warnmsg => "Warning: Allocating IP as '".$disp_alloctypes{$newiptype}."' instead of '". + $disp_alloctypes{$webvar{alloctype}}."' to match pool $fbtmp\n"); + $webvar{alloctype} = $newiptype; + } + } + if (!$fbtmp) { $page->param(errmsg => "Can't allocate static IP from outside a pool!!"); goto ERRJUMP; } } else { - $sth = $ip_dbh->prepare("select cidr from freeblocks where cidr >>='$cidr' and not (routed='n')"); - $sth->execute; - @data = $sth->fetchrow_array; -# User deserves errors if user can't be bothered to find the free block first. - if (!$data[0]) { + if (!$fbtmp) { $page->param(errmsg => "Can't allocate from outside a routed block!!"); goto ERRJUMP; @@ -197,6 +171,5 @@ } - my $alloc_from = new NetAddr::IP $data[0]; - $sth->finish; + my $alloc_from = new NetAddr::IP $fbtmp; my @cities; @@ -226,36 +199,22 @@ $page->param(locerr => "Invalid customer location! Go back and select customer's location."); goto ERRJUMP; - } else { - if ($webvar{alloctype} =~ /^.i$/) { - $sth = $ip_dbh->prepare("update poolips set available='n', custid='$webvar{custid}', ". - "city='$webvar{city}', description='$webvar{desc}', notes='$webvar{notes}' ". - "where ip='$webvar{cidr}'"); - $sth->execute; - if ($sth->err) { - $page->param(errmsg => $sth->errstr); - syslog "err", "($authuser) Allocation of '$webvar{cidr}' to '$webvar{custid}' as ". - "'$webvar{alloctype}' failed: '".$sth->errstr."'"; - } else { - syslog "notice", "$authuser allocated '$webvar{cidr}' to '$webvar{custid}' as ". - "'$webvar{alloctype}'"; - mailNotify($ip_dbh, "a$webvar{alloctype}", "ADDED: $disp_alloctypes{$webvar{alloctype}} allocation", - "$disp_alloctypes{$webvar{alloctype}} $webvar{cidr} allocated to customer $webvar{custid}\n". - "Description: $webvar{desc}\n\nAllocated by: $authuser\n"); - } - } else { - my ($retcode,$msg) = allocateBlock($ip_dbh, $webvar{cidr}, $webvar{alloc_from}, + } + + my ($retcode,$msg) = allocateBlock($ip_dbh, $webvar{cidr}, $webvar{alloc_from}, $webvar{custid}, $webvar{alloctype}, $webvar{city}, $webvar{desc}, $webvar{notes}, $webvar{circid}); - if ($retcode eq 'OK') { - syslog "notice", "$authuser allocated '$webvar{cidr}' to '$webvar{custid}' as ". - "'$webvar{alloctype}'"; - } else { - $page->param(errmsg => $msg); - syslog "err", "($authuser) Allocation of '$webvar{cidr}' to '$webvar{custid}' as ". - "'$webvar{alloctype}' failed: '$msg'"; - } - } # static IP vs netblock - - } # done city check + if ($retcode eq 'OK') { + syslog "notice", "$authuser allocated '$webvar{cidr}' to '$webvar{custid}' as ". + "'$webvar{alloctype}'"; + if ($webvar{alloctype} =~ /^.i$/) { + mailNotify($ip_dbh, "a$webvar{alloctype}", "ADDED: $disp_alloctypes{$webvar{alloctype}} allocation", + "$disp_alloctypes{$webvar{alloctype}} $webvar{cidr} allocated to customer $webvar{custid}\n". + "Description: $webvar{desc}\n\nAllocated by: $authuser\n"); + } + } else { + $page->param(errmsg => $msg); + syslog "err", "($authuser) Allocation of '$webvar{cidr}' to '$webvar{custid}' as ". + "'$webvar{alloctype}' failed: '$msg'"; + } } elsif ($webvar{action} eq 'alloctweak') { @@ -270,10 +229,6 @@ } elsif ($webvar{action} eq 'touch') { - $page->param(master => $webvar{whichmaster}); - $sth = $ip_dbh->prepare("update masterblocks set mtime=now() where cidr='$webvar{whichmaster}'"); - $sth->execute; - if ($sth->err) { - $page->param(errmsg => $sth->errstr); - } + my ($code,$msg) = touchMaster($ip_dbh, $webvar{whichmaster}); + $page->param(errmsg => $msg) if $code eq 'FAIL'; } elsif ($webvar{action} eq 'listcust') { @@ -612,5 +567,5 @@ # List all IPs in a pool, and allow arbitrary admin changes to each # Allow changes to ALL fields -sub showPool($) { +sub showPool { my $pool = new NetAddr::IP $_[0]; @@ -627,6 +582,7 @@ $page->param(typelist => \@typelist); - $sth = $ip_dbh->prepare("select ip,custid,city,type,available,description,notes from poolips where pool='$pool' order by ip"); - $sth->execute; + $sth = $ip_dbh->prepare("SELECT ip,custid,city,type,available,description,notes from poolips". + " WHERE pool=? ORDER BY ip"); + $sth->execute($pool); my @iplist; while (my ($ip,$custid,$city,$type,$avail,$desc,$notes) = $sth->fetchrow_array) { Index: branches/stable/cgi-bin/main.cgi =================================================================== --- branches/stable/cgi-bin/main.cgi (revision 593) +++ branches/stable/cgi-bin/main.cgi (revision 594) @@ -60,5 +60,4 @@ # Use the connectDB function, otherwise we end up confusing ourselves my $ip_dbh; -my $sth; my $errstr; ($ip_dbh,$errstr) = connectDB_My; @@ -90,5 +89,5 @@ my $page; if (-e "$ENV{HTML_TEMPLATE_ROOT}/$webvar{action}.tmpl") { - $page = HTML::Template->new(filename => "$webvar{action}.tmpl"); + $page = HTML::Template->new(filename => "$webvar{action}.tmpl", loop_context_vars => 1, global_vars => 1); } else { $page = HTML::Template->new(filename => "dunno.tmpl"); @@ -129,5 +128,5 @@ } elsif($webvar{action} eq 'listpool') { - listPool(); + showPool(); } @@ -155,12 +154,6 @@ } elsif ($webvar{action} eq 'nodesearch') { - $sth = $ip_dbh->prepare("SELECT node_id, node_name FROM nodes ORDER BY node_type,node_id"); - $sth->execute() or $page->param(errmsg => $sth->errstr); - my @nodelist; - while (my ($nid,$nname) = $sth->fetchrow_array()) { - my %row = (nodeid => $nid, nodename => $nname); - push @nodelist, \%row; - } - $page->param(nodelist => \@nodelist); + my $nodelist = getNodeList($ip_dbh); + $page->param(nodelist => $nodelist); } @@ -201,5 +194,4 @@ } - # Clean up IPDB globals, DB handle, etc. finish($ip_dbh); @@ -224,61 +216,8 @@ # Initial display: Show master blocks with total allocated subnets, total free subnets sub showSummary { - my %allocated; - my %free; - my %routed; - my %bigfree; - - # Count the allocations. - $sth = $ip_dbh->prepare("select count(*) from allocations where cidr <<= ?"); - foreach my $master (@masterblocks) { - $sth->execute("$master"); - $sth->bind_columns(\$allocated{"$master"}); - $sth->fetch(); - } - - # Count routed blocks - $sth = $ip_dbh->prepare("select count(*) from routed where cidr <<= ?"); - foreach my $master (@masterblocks) { - $sth->execute("$master"); - $sth->bind_columns(\$routed{"$master"}); - $sth->fetch(); - } - - # Count the free blocks. - $sth = $ip_dbh->prepare("select count(*) from freeblocks where cidr <<= ? and ". - "(routed='y' or routed='n')"); - foreach my $master (@masterblocks) { - $sth->execute("$master"); - $sth->bind_columns(\$free{"$master"}); - $sth->fetch(); - } - - # Find the largest free block in each master - $sth = $ip_dbh->prepare("select maskbits from freeblocks where cidr <<= ? and ". - "(routed='y' or routed='n') order by maskbits limit 1"); - foreach my $master (@masterblocks) { - $sth->execute("$master"); - $sth->bind_columns(\$bigfree{"$master"}); - $sth->fetch(); - } - - # Assemble the data to stuff into the template. - my @masterlist; - my $rowclass=0; - foreach my $master (@masterblocks) { - my %row = ( - rowclass => $rowclass++ % 2, - master => "$master", - routed => $routed{"$master"}, - allocated => $allocated{"$master"}, - free => $free{"$master"}, - bigfree => ( ($bigfree{"$master"} eq '') ? ("<NONE>") : ("/".$bigfree{"$master"}) ) - ); - push (@masterlist, \%row); - } - $page->param(masterlist => \@masterlist); + my $masterlist = listSummary($ip_dbh); + $page->param(masterlist => $masterlist); $page->param(addmaster => ($IPDBacl{$authuser} =~ /a/) ); - } # showSummary @@ -292,94 +231,11 @@ $page->param(master => $webvar{block}); - - my %allocated; - my %free; - my %cities; - my %bigfree; - - my $master = new NetAddr::IP $webvar{block}; - my @localmasters; - - # Fetch only the blocks relevant to this master - $sth = $ip_dbh->prepare("select cidr,city from routed where cidr <<= '$master' order by cidr"); - $sth->execute(); - - my $i=0; - while (my @data = $sth->fetchrow_array()) { - my $cidr = new NetAddr::IP $data[0]; - $localmasters[$i++] = $cidr; - $free{"$cidr"} = 0; - $allocated{"$cidr"} = 0; - $bigfree{"$cidr"} = 128; - # Retain the routing destination - $cities{"$cidr"} = $data[1]; - } - - # Check if there were actually any blocks routed from this master - if ($i > 0) { - - # Count the allocations - $sth = $ip_dbh->prepare("select count(*) from allocations where cidr <<= ?"); - foreach my $master (@localmasters) { - $sth->execute("$master"); - $sth->bind_columns(\$allocated{"$master"}); - $sth->fetch(); - } - - # Count the free blocks. - $sth = $ip_dbh->prepare("select count(*) from freeblocks where cidr <<= ? and ". - "(routed='y' or routed='n')"); - foreach my $master (@localmasters) { - $sth->execute("$master"); - $sth->bind_columns(\$free{"$master"}); - $sth->fetch(); - } - - # Get the size of the largest free block - $sth = $ip_dbh->prepare("select maskbits from freeblocks where cidr <<= ? and ". - "(routed='y' or routed='n') order by maskbits limit 1"); - foreach my $master (@localmasters) { - $sth->execute("$master"); - $sth->bind_columns(\$bigfree{"$master"}); - $sth->fetch(); - } - - my @routed; - my $rowclass = 0; - foreach my $master (@localmasters) { - my %row = ( - rowclass => $rowclass++ % 2, - block => "$master", - city => $cities{"$master"}, - nsubs => $allocated{"$master"}, - nfree => $free{"$master"}, - lfree => ( ($bigfree{"$master"} eq 128) ? ("<NONE>") : ("/".$bigfree{"$master"}) ) - ); - push @routed, \%row; - } - $page->param(routedlist => \@routed); - - } # end check for existence of routed blocks in master - $page->param(delmaster => ($IPDBacl{$authuser} =~ /d/)); - # Snag the free blocks. - my $count = 0; - $sth = $ip_dbh->prepare("select cidr from freeblocks where cidr <<='$master' and ". - "routed='n' order by cidr"); - $sth->execute(); - my @unrouted; - my $rowclass = 0; - while (my @data = $sth->fetchrow_array()) { - my $cidr = new NetAddr::IP $data[0]; - my %row = ( - rowclass => $rowclass++ % 2, - fblock => "$cidr", - frange => $cidr->range - ); - push @unrouted, \%row; - } - $page->param(unrouted => \@unrouted); - + my $rlist = listMaster($ip_dbh, $webvar{block}); + $page->param(routedlist => $rlist); + + my $flist = listFree($ip_dbh, $webvar{block}); + $page->param(unrouted => $flist); } # showMaster @@ -394,71 +250,20 @@ sub showRBlock { - my $master = new NetAddr::IP $webvar{block}; - - $sth = $ip_dbh->prepare("select city from routed where cidr='$master'"); - $sth->execute; - my ($rcity) = $sth->fetchrow_array; - - $page->param(master => "$master"); + $page->param(master => $webvar{block}); + $page->param(delrouted => $IPDBacl{$authuser} =~ /d/); + + my $rcity = getRoutedCity($ip_dbh, $webvar{block}); $page->param(rcity => $rcity); - # Snag the allocations for this block - $sth = $ip_dbh->prepare("select cidr,city,type,custid,swip,description". - " from allocations where cidr <<= '$master' order by cidr"); - $sth->execute(); - - # hack hack hack - # set up to flag swip=y records if they don't actually have supporting data in the customers table - my $custsth = $ip_dbh->prepare("select count(*) from customers where custid=?"); - - my $rowclass = 0; - my @blocklist; - while (my ($cidr,$city,$type,$custid,$swip,$desc) = $sth->fetchrow_array()) { - $custsth->execute($custid); - my ($ncust) = $custsth->fetchrow_array(); - - my %row = ( - rowclass => $rowclass++ % 2, - block => $cidr, - city => $city, - type => $disp_alloctypes{$type}, - custid => $custid, - swip => ($swip eq 'y' ? ($ncust == 0 ? 'Yes*' : 'Yes') : 'No'), - desc => $desc - ); - $row{subblock} = ($type =~ /^.r$/); # hmf. wonder why these won't work in the hash declaration... - $row{listpool} = ($type =~ /^.[pd]$/); - push (@blocklist, \%row); - } - $page->param(blocklist => \@blocklist); - - $page->param(delrouted => $IPDBacl{$authuser} =~ /d/); - - # Snag the free blocks. We don't really *need* to be pedantic about avoiding - # unrouted free blocks, but it's better to let the database do the work if we can. - $rowclass = 0; - my @unassigned; - $sth = $ip_dbh->prepare("select cidr,routed from freeblocks where cidr <<= '$master'". - " order by cidr"); - $sth->execute(); - while (my ($cidr_db,$routed) = $sth->fetchrow_array()) { - my $cidr = new NetAddr::IP $cidr_db; - - my %row = ( - rowclass => $rowclass++ % 2, - subblock => ($routed ne 'y' && $routed ne 'n'), - fblock => $cidr_db, - fbtype => $routed, - frange => $cidr->range, - ); - push @unassigned, \%row; - } - $page->param(unassigned => \@unassigned); - + my $blist = listRBlock($ip_dbh, $webvar{block}); + $page->param(blocklist => $blist); + + my $flist = listFree($ip_dbh, $webvar{block}, 'y'); + $page->param(unassigned => $flist); } # showRBlock # List the IPs used in a pool -sub listPool { +sub showPool { my $cidr = new NetAddr::IP $webvar{pool}; @@ -473,36 +278,21 @@ # Snag pool info for heading - $sth = $ip_dbh->prepare("select type,city from allocations where cidr=?"); - $sth->execute($webvar{pool}); - my ($pooltype, $poolcity) = $sth->fetchrow_array; - - $page->param(disptype => $disp_alloctypes{$pooltype}); - $page->param(city => $poolcity); + my $poolinfo = getBlockData($ip_dbh, $webvar{pool}); + + $page->param(disptype => $disp_alloctypes{$poolinfo->{type}}); + $page->param(city => $poolinfo->{city}); # Only display net/gw/bcast if it's a "real" netblock and not a PPP(oE) lunacy - $page->param(realblock => $pooltype =~ /^.d$/); + $page->param(realblock => $poolinfo->{type} =~ /^.d$/); # probably have to add an "edit IP allocation" link here somewhere. - $sth = $ip_dbh->prepare("select ip,custid,available,description,type". - " from poolips where pool='$webvar{pool}' order by ip"); - $sth->execute; - my @poolips; - my $rowclass = 0; - while (my ($ip,$custid,$available,$desc,$type) = $sth->fetchrow_array) { - my %row = ( - rowclass => $rowclass++ % 2, - ip => $ip, - custid => $custid, - available => $available, - desc => $desc, - maydel => $IPDBacl{$authuser} =~ /d/, - delme => $available eq 'n' - ); - push @poolips, \%row; - } - $page->param(poolips => \@poolips); - -} # end listPool + my $plist = listPool($ip_dbh, $webvar{pool}); + # technically slightly more efficient to check the ACL in an if () once outside the foreach + foreach (@{$plist}) { + $$_{maydel} = $IPDBacl{$authuser} =~ /d/; + } + $page->param(poolips => $plist); +} # end showPool @@ -535,33 +325,19 @@ if ($webvar{fbtype} ne 'y') { # Snag the type of the container block from the database. - $sth = $ip_dbh->prepare("select type from allocations where cidr >>='$block'"); - $sth->execute; - my @data = $sth->fetchrow_array; - $data[0] =~ s/c$/r/; # Munge the type into the correct form - $page->param(fbdisptype => $list_alloctypes{$data[0]}); - $page->param(type => $data[0]); +## hmm. need a flag for parent class/type, sort of? + my $pblock = subParent($ip_dbh, $webvar{block}); + my $ptype = $pblock->{type}; + $ptype =~ s/c$/r/; + $page->param(fbdisptype => $list_alloctypes{$ptype}); + $page->param(type => $ptype); } else { - $sth = $ip_dbh->prepare("select type,listname from alloctypes where listorder < 500 ". - "and type not like '_i' order by listorder"); - $sth->execute; - my @typelist; - my $selflag = 0; - while (my @data = $sth->fetchrow_array) { - my %row = (tval => $data[0], - type => $data[1], - sel => ($selflag == 0 ? ' selected' : '') - ); - push (@typelist, \%row); - $selflag++; - } - $page->param(typelist => \@typelist); + # get "primary" alloctypes, since these are all that can correctly be assigned if we're in this branch + my $tlist = getTypeList($ip_dbh, 'p'); + $tlist->[0]->{sel} = 1; + $page->param(typelist => $tlist); } } else { - my @masterlist; - foreach my $master (@masterblocks) { - my %row = (master => "$master"); - push (@masterlist, \%row); - } - $page->param(masterlist => \@masterlist); + my $mlist = getMasterList($ip_dbh, 'c'); + $page->param(masterlist => $mlist); my @pops; @@ -572,19 +348,8 @@ $page->param(pops => \@pops); - # could arguably include routing (500) in the list, but ATM it doesn't - # make sense, and in any case that shouldn't be structurally possible here. - $sth = $ip_dbh->prepare("select type,listname from alloctypes where listorder <= 500 order by listorder"); - $sth->execute; - my @typelist; - my $selflag = 0; - while (my @data = $sth->fetchrow_array) { - my %row = (tval => $data[0], - type => $data[1], - sel => ($selflag == 0 ? ' selected' : '') - ); - push (@typelist, \%row); - $selflag++; - } - $page->param(typelist => \@typelist); + # get all standard alloctypes + my $tlist = getTypeList($ip_dbh, 'a'); + $tlist->[0]->{sel} = 1; + $page->param(typelist => $tlist); } @@ -597,12 +362,6 @@ ## node hack - $sth = $ip_dbh->prepare("SELECT node_id, node_name FROM nodes ORDER BY node_type,node_id"); - $sth->execute() or print "DEBUG: failed retrieval from nodes: ".$sth->errstr,"
\n"; - my @nodes; - while (my ($nid,$nname) = $sth->fetchrow_array()) { - my %row = (nid => $nid, nname => $nname); - push (@nodes, \%row); - } - $page->param(nodelist => \@nodes); + my $nlist = getNodeList($ip_dbh); + $page->param(nodelist => $nlist); ## end node hack @@ -632,24 +391,7 @@ if ($webvar{alloctype} =~ /^.i$/ && $webvar{fbassign} ne 'y') { - my ($base,undef) = split //, $webvar{alloctype}; # split into individual chars - -# Ewww. But it works. - $sth = $ip_dbh->prepare("SELECT (SELECT city FROM allocations WHERE cidr=poolips.pool), ". - "poolips.pool, COUNT(*) FROM poolips,allocations WHERE poolips.available='y' AND ". - "poolips.pool=allocations.cidr AND allocations.city='$webvar{pop}' AND poolips.type LIKE '".$base."_' ". - "GROUP BY pool"); - $sth->execute; - my $optionlist; - - my @poollist; - while (my ($poolcit,$poolblock,$poolfree) = $sth->fetchrow_array) { - # city,pool cidr,free IP count - if ($poolfree > 0) { - my %row = (poolcit => $poolcit, poolblock => $poolblock, poolfree => $poolfree); - push (@poollist, \%row); - } - } + my $plist = getPoolSelect($ip_dbh, $webvar{alloctype}, $webvar{pop}); $page->param(staticip => 1); - $page->param(poollist => \@poollist); + $page->param(poollist => $plist) if $plist; $cidr = "Single static IP"; ##fixme: need to handle "no available pools" @@ -657,5 +399,5 @@ } else { # end show pool options - if ($webvar{fbassign} eq 'y') { + if ($webvar{fbassign} && $webvar{fbassign} eq 'y') { $alloc_from = new NetAddr::IP $webvar{allocfrom}; ## possibly messy behaviour: force the _from and block to be the network addr? @@ -669,38 +411,14 @@ return; } - my $sql; - my $city; - my $failmsg; - my $extracond = ''; - if ($webvar{allocfrom} eq '-') { - $extracond = ($webvar{allowpriv} eq 'on' ? '' : - " and not (cidr <<= '192.168.0.0/16'". - " or cidr <<= '10.0.0.0/8'". - " or cidr <<= '172.16.0.0/12')"); - } - my $sortorder; + +##fixme ick, ew, bleh. gotta handle the failure message generation better. push it into findAllocateFrom()? + my $failmsg = "No suitable free block found.
\n"; if ($webvar{alloctype} eq 'rm') { - if ($webvar{allocfrom} ne '-') { - $sql = "select * from freeblocks where maskbits<=$webvar{maskbits} and routed='n'". - " and cidr <<= '$webvar{allocfrom}'"; - $sortorder = "maskbits desc"; - } else { - $sql = "select * from freeblocks where maskbits<=$webvar{maskbits} and routed='n'"; - $sortorder = "maskbits desc"; - } - $failmsg = "No suitable free block found.
\nWe do not have a free". - " routeable block of that size.
\nYou will have to either route". - " a set of smaller netblocks or a single smaller netblock."; + $failmsg .= "We do not have a free routeable block of that size.
\n". + "You will have to either route a set of smaller netblocks or a single smaller netblock."; } else { -##fixme -# This section needs serious Pondering. - # Pools of most types get assigned to the POP they're "routed from" - # This includes WAN blocks and other netblock "containers" - # This does NOT include cable pools. if ($webvar{alloctype} =~ /^.[pc]$/) { - $city = $webvar{city}; - $failmsg = "No suitable free block found.
\nYou will have to route another". - " superblock from one of the
\nmaster blocks or chose a smaller". - " block size for the pool."; + $failmsg .= "You will have to route another superblock from one of the
\n". + "master blocks or chose a smaller block size for the pool."; } else { if (!$webvar{pop}) { @@ -708,29 +426,16 @@ return; } - $city = $webvar{pop}; - $failmsg = "No suitable free block found.
\nYou will have to route another". - " superblock to $webvar{pop}
\nfrom one of the master blocks or". - " chose a smaller blocksize."; - } - if (defined $webvar{allocfrom} && $webvar{allocfrom} ne '-') { - $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}". - " and cidr <<= '$webvar{allocfrom}' and routed='". - (($webvar{alloctype} =~ /^(.)r$/) ? "$1" : 'y')."'"; - $sortorder = "maskbits desc,cidr"; - } else { - $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}". - " and routed='".(($webvar{alloctype} =~ /^(.)r$/) ? "$1" : 'y')."'"; - $sortorder = "maskbits desc,cidr"; + $failmsg .= "You will have to route another superblock to $webvar{pop}
\n". + "from one of the master blocks or chose a smaller blocksize."; } } - $sql = $sql.$extracond." order by ".$sortorder; - $sth = $ip_dbh->prepare($sql); - $sth->execute; - my @data = $sth->fetchrow_array(); - if ($data[0] eq "") { + + $cidr = findAllocateFrom($ip_dbh, $webvar{maskbits}, $webvar{alloctype}, $webvar{city}, $webvar{pop}, + (master => $webvar{allocfrom}, allowpriv => $webvar{allowpriv}) ); + if (!$cidr) { $page->param(err => $failmsg); return; } - $cidr = new NetAddr::IP $data[0]; + $cidr = new NetAddr::IP $cidr; # this chunk now specific to "guided" allocation; freeblock-select can now slice-n-dice on its own. @@ -754,7 +459,5 @@ ## node hack if ($webvar{node} && $webvar{node} ne '-') { - $sth = $ip_dbh->prepare("SELECT node_name FROM nodes WHERE node_id=?"); - $sth->execute($webvar{node}); - my ($nodename) = $sth->fetchrow_array(); + my $nodename = getNodeName($ip_dbh, $webvar{node}); $page->param(nodename => $nodename); $page->param(nodeid => $webvar{node}); @@ -875,19 +578,15 @@ return; } - if ($webvar{custid} !~ /^(?:\d{10}|\d{7}|STAFF)(?:-\d\d?)?$/) { - # Force uppercase for now... - $webvar{custid} =~ tr/a-z/A-Z/; - # Crosscheck with billing. - my $status = CustIDCK->custid_exist($webvar{custid}); - if ($CustIDCK::Error) { - $page->param(err => "Error verifying customer ID: ".$CustIDCK::ErrMsg); - return; - } - if (!$status) { - $page->param(err => "Customer ID not valid. Make sure the Customer ID ". - "is correct.
\nUse STAFF for staff static IPs, and $IPDB::defcustid for any other ". - "non-customer assignments."); - return; - } + # Crosscheck with billing. + my $status = CustIDCK->custid_exist($webvar{custid}); + if ($CustIDCK::Error) { + $page->param(err => "Error verifying customer ID: ".$CustIDCK::ErrMsg); + return; + } + if (!$status) { + $page->param(err => "Customer ID not valid. Make sure the Customer ID ". + "is correct.
\nUse STAFF for staff static IPs, and $IPDB::defcustid for any other ". + "non-customer assignments."); + return; } # print "\n"; @@ -913,5 +612,5 @@ ##fixme: hook to force-set POP or city on certain alloctypes # if ($webvar{alloctype =~ /foo,bar,bz/ { $webvar{pop} = 'blah'; } - if ($webvar{pop} =~ /^-$/) { + if ($webvar{pop} && $webvar{pop} =~ /^-$/) { $flag = 'to route the block from/through'; } @@ -935,67 +634,42 @@ sub edit { - my $sql; - - # Two cases: block is a netblock, or block is a static IP from a pool - # because I'm lazy, we'll try to make the SELECT's bring out identical)ish) data -##fixme: allow "SWIP" (publication to rWHOIS) of static IP data - if ($webvar{block} =~ /\/32$/) { - $sql = "select ip,custid,type,city,circuitid,description,notes,modifystamp,privdata from poolips where ip='$webvar{block}'"; - } else { - $sql = "select cidr,custid,type,city,circuitid,description,notes,modifystamp,privdata,swip from allocations where cidr='$webvar{block}'" - } - - # gotta snag block info from db - $sth = $ip_dbh->prepare($sql); - $sth->execute; - my @data = $sth->fetchrow_array; - - # Clean up extra whitespace on alloc type - $data[2] =~ s/\s//; - - # We can't let the city be changed here; this block is a part of - # a larger routed allocation and therefore by definition can't be moved. - # block and city are static. -##fixme -# Needs thinking. Have to allow changes to city to correct errors, no? -# Also have areas where a routed block at a POP serves "many" cities/towns/named crossroads - -# @data: cidr,custid,type,city,circuitid,description,notes,modifystamp,privdata,swip + # snag block info from db + my $blockinfo = getBlockData($ip_dbh, $webvar{block}); + + # Clean up extra whitespace on alloc type. Mainly a legacy-data cleanup. + $blockinfo->{type} =~ s/\s//; $page->param(block => $webvar{block}); - $page->param(custid => $data[1]); - $page->param(city => $data[3]); - $page->param(circid => $data[4]); - $page->param(desc => $data[5]); - $page->param(notes => $data[6]); + $page->param(custid => $blockinfo->{custid}); + $page->param(city => $blockinfo->{city}); + $page->param(circid => $blockinfo->{circuitid}); + $page->param(desc => $blockinfo->{description}); + $page->param(notes => $blockinfo->{notes}); ##fixme The check here should be built from the database # Need to expand to support pool types too - if ($data[2] =~ /^.[ne]$/ && $IPDBacl{$authuser} =~ /c/) { + if ($blockinfo->{type} =~ /^.[ne]$/ && $IPDBacl{$authuser} =~ /c/) { $page->param(changetype => 1); $page->param(alloctype => [ - { selme => ($data[2] eq 'me'), type => "me", disptype => "Dialup netblock" }, - { selme => ($data[2] eq 'de'), type => "de", disptype => "Dynamic DSL netblock" }, - { selme => ($data[2] eq 'ce'), type => "ce", disptype => "Dynamic cable netblock" }, - { selme => ($data[2] eq 'we'), type => "we", disptype => "Dynamic wireless netblock" }, - { selme => ($data[2] eq 'cn'), type => "cn", disptype => "Customer netblock" }, - { selme => ($data[2] eq 'en'), type => "en", disptype => "End-use netblock" }, - { selme => ($data[2] eq 'in'), type => "in", disptype => "Internal netblock" }, + { selme => ($blockinfo->{type} eq 'me'), type => "me", disptype => "Dialup netblock" }, + { selme => ($blockinfo->{type} eq 'de'), type => "de", disptype => "Dynamic DSL netblock" }, + { selme => ($blockinfo->{type} eq 'ce'), type => "ce", disptype => "Dynamic cable netblock" }, + { selme => ($blockinfo->{type} eq 'we'), type => "we", disptype => "Dynamic wireless netblock" }, + { selme => ($blockinfo->{type} eq 'cn'), type => "cn", disptype => "Customer netblock" }, + { selme => ($blockinfo->{type} eq 'en'), type => "en", disptype => "End-use netblock" }, + { selme => ($blockinfo->{type} eq 'in'), type => "in", disptype => "Internal netblock" }, ] ); } else { - $page->param(disptype => $disp_alloctypes{$data[2]}); - $page->param(type => $data[2]); + $page->param(disptype => $disp_alloctypes{$blockinfo->{type}}); + $page->param(type => $blockinfo->{type}); } ## node hack - $sth = $ip_dbh->prepare("SELECT nodes.node_id,node_name FROM nodes INNER JOIN noderef". - " ON nodes.node_id=noderef.node_id WHERE noderef.block='$webvar{block}'"); - $sth->execute; - my ($nodeid,$nodename) = $sth->fetchrow_array(); + my ($nodeid,$nodename) = getNodeInfo($ip_dbh, $webvar{block}); $page->param(havenodeid => $nodeid); - if ($data[2] eq 'fr' || $data[2] eq 'bi') { + if ($blockinfo->{type} eq 'fr' || $blockinfo->{type} eq 'bi') { $page->param(typesupportsnodes => 1); $page->param(nodename => $nodename); @@ -1006,30 +680,23 @@ # (currently) have a nodeid set in the first place. if ($IPDBacl{$authuser} =~ /c/) { - $sth = $ip_dbh->prepare("SELECT node_id, node_name FROM nodes ORDER BY node_type,node_id"); - $sth->execute; - my @nodelist; - while (my ($nid,$nname) = $sth->fetchrow_array()) { - my %row = ( - selme => ($nodeid == $nid), - nodeid => $nid, - nodename => $nname, - ); - push (@nodelist, \%row); + my $nlist = getNodeList($ip_dbh); + foreach (@{$nlist}) { + $$_{selme} = ($$_{node_id} == $nodeid); } - $page->param(nodelist => \@nodelist); + $page->param(nodelist => $nlist); } } ## end node hack - my ($lastmod,undef) = split /\s+/, $data[7]; + my ($lastmod,undef) = split /\s+/, $blockinfo->{lastmod}; $page->param(lastmod => $lastmod); # not happy with the upside-down logic, but... - $page->param(swipable => $data[2] !~ /.i/); - $page->param(swip => $data[10] ne 'n'); + $page->param(swipable => $blockinfo->{type} !~ /.i/); + $page->param(swip => $blockinfo->{swip} ne 'n') if $blockinfo->{swip}; # Check to see if we can display sensitive data $page->param(nocling => $IPDBacl{$authuser} =~ /s/); - $page->param(privdata => $data[8]); + $page->param(privdata => $blockinfo->{privdata}); # ACL trickery - these two template booleans control the presence of all form/input tags @@ -1048,47 +715,27 @@ } - # Check to see if we can update restricted data - my $privdata = ''; - if ($IPDBacl{$authuser} =~ /s/) { - $privdata = ",privdata='$webvar{privdata}'"; - } - # Make sure incoming data is in correct format - custID among other things. return if !validateInput; - # SQL transaction wrapper - eval { - # Relatively simple SQL transaction here. - my $sql; - if (my $pooltype = ($webvar{alloctype} =~ /^(.)i$/) ) { - $sql = "UPDATE poolips SET custid='$webvar{custid}',". - "city=?,description=?,notes=?,". - "circuitid='$webvar{circid}',". - "$privdata where ip='$webvar{block}'"; - } else { - $sql = "UPDATE allocations SET custid='$webvar{custid}',". - "city=?,description=?,notes=?,". - "circuitid='$webvar{circid}'$privdata,". - "type='$webvar{alloctype}',". - "swip='".($webvar{swip} eq 'on' ? 'y' : 'n')."' ". - "where cidr='$webvar{block}'"; - } - # Log the details of the change. - syslog "debug", $sql; - $sth = $ip_dbh->prepare($sql); - $sth->execute($webvar{city}, $webvar{desc}, $webvar{notes}); -## node hack - if ($webvar{node}) { - # done with delete/insert so we don't have to worry about funkyness updating a node ref that isn't there - $ip_dbh->do("DELETE FROM noderef WHERE block='$webvar{block}'"); - $sth = $ip_dbh->prepare("INSERT INTO noderef (block,node_id) VALUES (?,?)"); - $sth->execute($webvar{block},$webvar{node}); - } -## end node hack - $ip_dbh->commit; - }; - if ($@) { - my $msg = $@; - eval { $ip_dbh->rollback; }; + $webvar{swip} = 'n' if !$webvar{swip}; + + my %updargs = ( + custid => $webvar{custid}, + city => $webvar{city}, + description => $webvar{desc}, + notes => $webvar{notes}, + circuitid => $webvar{circid}, + block => $webvar{block}, + type => $webvar{alloctype}, + swip => $webvar{swip}, + ); + + # Semioptional values + $updargs{privdata} = $webvar{privdata} if $IPDBacl{$authuser} =~ /s/; + $updargs{node} = $webvar{node} if $webvar{node}; + + my ($code,$msg) = updateBlock($ip_dbh, %updargs); + + if ($code eq 'FAIL') { syslog "err", "$authuser could not update block/IP '$webvar{block}': '$msg'"; $page->param(err => "Could not update block/IP $webvar{block}: $msg"); @@ -1098,4 +745,5 @@ # If we get here, the operation succeeded. syslog "notice", "$authuser updated $webvar{block}"; +##fixme: log details of the change? old way is in the .debug stream anyway. ##fixme: need to wedge something in to allow "update:field" notifications ## hmm. how to tell what changed? O_o @@ -1105,7 +753,5 @@ ## node hack if ($webvar{node} && $webvar{node} ne '-') { - $sth = $ip_dbh->prepare("SELECT node_name FROM nodes WHERE node_id=?"); - $sth->execute($webvar{node}); - my ($nodename) = $sth->fetchrow_array(); + my $nodename = getNodeName($ip_dbh, $webvar{node}); $page->param(nodename => $nodename); } @@ -1113,19 +759,19 @@ # Link back to browse-routed or list-pool page on "Update complete" page. - my $cblock; # to contain the CIDR of the container block we're retrieving. - my $sql; + my $cblock; if (my $pooltype = ($webvar{alloctype} =~ /^(.)i$/) ) { $page->param(backpool => 1); - $sql = "select pool from poolips where ip='$webvar{block}'"; + $cblock = ipParent($ip_dbh, $webvar{block}); } else { - $sql = "select cidr from routed where cidr >>= '$webvar{block}'"; - } - # I define there to be no errors on this operation... so we don't need to check for them. - $sth = $ip_dbh->prepare($sql); - $sth->execute; - $sth->bind_columns(\$cblock); - $sth->fetch(); - $sth->finish; - $page->param(backblock => $cblock); + $cblock = blockParent($ip_dbh, $webvar{block}); + } + $page->param(backblock => $cblock->{cidr}); + + # Do some HTML fiddling here instead of using ESCAPE=HTML in the template, + # because otherwise we can't convert \n to
. *sigh* + $webvar{notes} = $q->escapeHTML($webvar{notes}); # escape first... + $webvar{notes} =~ s/\n/
\n/; # ... then convert newlines + $webvar{privdata} = ($webvar{privdata} ? $q->escapeHTML($webvar{privdata}) : " "); + $webvar{privdata} =~ s/\n/
\n/; $page->param(cidr => $webvar{block}); @@ -1134,8 +780,7 @@ $page->param(custid => $webvar{custid}); $page->param(swip => $webvar{swip} eq 'on' ? 'Yes' : 'No'); - $page->param(circid => $q->escapeHTML($webvar{circid})); - $page->param(desc => $q->escapeHTML($webvar{desc})); - $page->param(notes => $q->escapeHTML($webvar{notes})); - $webvar{privdata} = ($webvar{privdata} ? $q->escapeHTML($webvar{privdata}) : " "); + $page->param(circid => $webvar{circid}); + $page->param(desc => $webvar{desc}); + $page->param(notes => $webvar{notes}); $page->param(privdata => $webvar{privdata}) if $IPDBacl{$authuser} =~ /s/; @@ -1157,69 +802,49 @@ } - my ($cidr, $custid, $type, $city, $circid, $desc, $notes, $alloctype, $privdata); + my $blockdata; if ($webvar{alloctype} eq 'rm') { - $sth = $ip_dbh->prepare("select cidr,city from routed where cidr='$webvar{block}'"); - $sth->execute(); - -# This feels... extreme. - croak $sth->errstr() if($sth->errstr()); - - $sth->bind_columns(\$cidr,\$city); - $sth->execute(); - $sth->fetch || croak $sth->errstr(); - $custid = "N/A"; - $alloctype = $webvar{alloctype}; - $circid = "N/A"; - $desc = "N/A"; - $notes = "N/A"; - $privdata = "N/A"; + + $blockdata->{block} = $webvar{block}; + $blockdata->{city} = getRoutedCity($ip_dbh, $webvar{block}); + $blockdata->{custid} = "N/A"; + $blockdata->{type} = $webvar{alloctype}; + $blockdata->{circuitid} = "N/A"; + $blockdata->{description} = "N/A"; + $blockdata->{notes} = "N/A"; + $blockdata->{privdata} = "N/A"; } elsif ($webvar{alloctype} eq 'mm') { - $cidr = $webvar{block}; - $city = "N/A"; - $custid = "N/A"; - $alloctype = $webvar{alloctype}; - $circid = "N/A"; - $desc = "N/A"; - $notes = "N/A"; - $privdata = "N/A"; - - } elsif ($webvar{alloctype} =~ /^.i$/) { # done with alloctype=[rm]m - - # Unassigning a static IP - my $sth = $ip_dbh->prepare("select ip,custid,city,type,notes,circuitid,privdata". - " from poolips where ip='$webvar{block}'"); - $sth->execute(); -# croak $sth->errstr() if($sth->errstr()); - - $sth->bind_columns(\$cidr, \$custid, \$city, \$alloctype, \$notes, \$circid, - \$privdata); - $sth->fetch() || croak $sth->errstr; - - } else { # done with alloctype=~ /^.i$/ - - my $sth = $ip_dbh->prepare("select cidr,custid,type,city,circuitid,description,notes,privdata". - " from allocations where cidr='$webvar{block}'"); - $sth->execute(); -# croak $sth->errstr() if($sth->errstr()); - - $sth->bind_columns(\$cidr, \$custid, \$alloctype, \$city, \$circid, \$desc, - \$notes, \$privdata); - $sth->fetch() || carp $sth->errstr; + $blockdata->{block} = $webvar{block}; + $blockdata->{city} = "N/A"; + $blockdata->{custid} = "N/A"; + $blockdata->{type} = $webvar{alloctype}; + $blockdata->{circuitid} = "N/A"; + $blockdata->{description} = "N/A"; + $blockdata->{notes} = "N/A"; + $blockdata->{privdata} = "N/A"; + + } else { + + $blockdata = getBlockData($ip_dbh, $webvar{block}) + } # end cases for different alloctypes - $page->param(block => $cidr); - $page->param(disptype => $disp_alloctypes{$alloctype}); - $page->param(type => $alloctype); - $page->param(city => $city); - $page->param(custid => $custid); - $page->param(circid => $circid); - $page->param(desc => $desc); - $page->param(notes => $notes); - $privdata = ' ' if $privdata eq ''; - $page->param(privdata => $privdata) if $IPDBacl{$authuser} =~ /s/; - $page->param(delpool => $alloctype =~ /^.[pd]$/); + $page->param(block => $blockdata->{block}); + $page->param(disptype => $disp_alloctypes{$blockdata->{type}}); + $page->param(type => $blockdata->{type}); + $page->param(city => $blockdata->{city}); + $page->param(custid => $blockdata->{custid}); + $page->param(circid => $blockdata->{circuitid}); + $page->param(desc => $blockdata->{description}); + $blockdata->{notes} = $q->escapeHTML($blockdata->{notes}); + $blockdata->{notes} =~ s/\n/
\n/; + $page->param(notes => $blockdata->{notes}); + $blockdata->{privdata} = $q->escapeHTML($blockdata->{privdata}); + $blockdata->{privdata} = ' ' if !$blockdata->{privdata}; + $blockdata->{privdata} =~ s/\n/
\n/; + $page->param(privdata => $blockdata->{privdata}) if $IPDBacl{$authuser} =~ /s/; + $page->param(delpool => $blockdata->{type} =~ /^.[pd]$/); } # end remove() @@ -1237,5 +862,5 @@ # need to retrieve block data before deleting so we can notify on that - my ($cidr,$custid,$type,$city,$description) = getBlockData($ip_dbh, $webvar{block}); + my $blockinfo = getBlockData($ip_dbh, $webvar{block}); my ($code,$msg) = deleteBlock($ip_dbh, $webvar{block}, $webvar{alloctype}); @@ -1243,9 +868,10 @@ $page->param(block => $webvar{block}); if ($code eq 'OK') { - syslog "notice", "$authuser deallocated '$webvar{alloctype}'-type netblock $webvar{block}". - " $custid, $city, desc='$description'"; + syslog "notice", "$authuser deallocated '$webvar{alloctype}'-type netblock $webvar{block} ". + $blockinfo->{custid}.", ".$blockinfo->{city}.", desc='".$blockinfo->{description}."'"; mailNotify($ip_dbh, 'da', "REMOVED: $disp_alloctypes{$webvar{alloctype}} $webvar{block}", "$disp_alloctypes{$webvar{alloctype}} $webvar{block} deallocated by $authuser\n". - "CustID: $custid\nCity: $city\nDescription: $description\n"); + "CustID: ".$blockinfo->{custid}."\nCity: ".$blockinfo->{city}. + "\nDescription: ".$blockinfo->{description}."\n"); } else { $page->param(failmsg => $msg); Index: branches/stable/cgi-bin/search.cgi =================================================================== --- branches/stable/cgi-bin/search.cgi (revision 593) +++ branches/stable/cgi-bin/search.cgi (revision 594) @@ -146,11 +146,21 @@ my $cols = "cidr,custid,type,city,description"; + # hack fix for undefined variables + $webvar{custid} = '' if !$webvar{custid}; + $webvar{desc} = '' if !$webvar{desc}; + $webvar{notes} = '' if !$webvar{notes}; + $webvar{custexclude} = '' if !$webvar{custexclude}; + $webvar{descexclude} = '' if !$webvar{descexclude}; + $webvar{notesexclude} = '' if !$webvar{notesexclude}; + # First chunk of SQL. Filter on custid, description, and notes as necessary. - my $sql = "(select $cols from searchme where". - " $webvar{custexclude} (custid ilike '%$webvar{custid}%'". - " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ilike '%$webvar{desc}%')". - " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ilike '%$webvar{notes}%')"; + my $sql = qq(SELECT $cols FROM searchme\n); + $sql .= " WHERE $webvar{custexclude} (custid ~ '$webvar{custid}')\n"; + $sql .= " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ~ '$webvar{desc}')\n"; + $sql .= " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ~ '$webvar{notes}')"; # If we're not supposed to search for all types, search for the selected types. + $webvar{alltypes} = '' if !$webvar{alltypes}; + $webvar{typeexclude} = '' if !$webvar{typeexclude}; if ($webvar{alltypes} ne 'on') { $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in ("; @@ -164,4 +174,6 @@ # If we're not supposed to search for all cities, search for the selected cities. # This could be vastly improved with proper foreign keys in the database. + $webvar{allcities} = '' if !$webvar{allcities}; + $webvar{cityexclude} = '' if !$webvar{cityexclude}; if ($webvar{allcities} ne 'on') { $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in ("; @@ -315,5 +327,5 @@ # Creates appropriate SQL to run the search and display the results # with queryResults() -sub viewBy($$) { +sub viewBy { my ($category,$query) = @_; @@ -445,5 +457,5 @@ # Display search queries based on the passed SQL. # Takes SQL, page number (for multipage search results), and a total count. -sub queryResults($$$) { +sub queryResults { my ($sql, $pageNo, $rowCount) = @_; my $offset = 0; @@ -529,5 +541,5 @@ # Return count of rows to be returned in a "real" query # with the passed SQL statement -sub countRows($) { +sub countRows { # Note that the "as foo" is required my $sth = $ip_dbh->prepare("select count(*) from ($_[0]) as foo");