Changeset 160


Ignore:
Timestamp:
11/02/11 18:12:35 (13 years ago)
Author:
Kris Deugau
Message:

/trunk

Use bind parameters in DNSDB::getDomRecs for filter
Make sure A records get an IPv4 address, and AAAA records get

a v6 address in DNSDB::addRec

Normalize and clean up handling for filtering and starts-with

  • common ops now done along with the rest of the global ops
  • filtering arguments now pushed into a global
  • use bind parameters in SQL (this should transfer OK to subs in DNSDB.pm later)

Add a couple new ##fixme's for scope checks
Force appending of domain or DOMAIN on record or default record

respectively, if they don't already have that at the end

Retrieve "old" info for logging record changes
Remove some stale commented fragments and ##fixme's

Location:
trunk
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • trunk/DNSDB.pm

    r157 r160  
    12121212
    12131213  my $filter = shift || '';
    1214   # keep the nasties down, since we can't ?-sub this bit.  :/
    1215   # note this is chars allowed in DNS hostnames
    1216   $filter =~ s/[^a-zA-Z0-9_.:-]//g;
    12171214
    12181215  $type = 'y' if $type eq 'def';
     
    12281225  }
    12291226  $sql .= " AND NOT r.type=$reverse_typemap{SOA}";
    1230   $sql .= " AND host ILIKE '%$filter%'" if $filter;
     1227  $sql .= " AND host ~* ?" if $filter;
    12311228  # use alphaorder column for "correct" ordering of sort-by-type instead of DNS RR type number
    12321229  $sql .= " ORDER BY ".($order eq 'type' ? 't.alphaorder' : "r.$order")." $direction";
     
    12911288
    12921289  # Validation
     1290  if ($rectype == $reverse_typemap{A}) {
     1291    return ("FAIL", "IPv4 addresses must be in the format n.n.n.n")
     1292        unless $val =~ /^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$/;
     1293  }
     1294  if ($rectype == $reverse_typemap{AAAA}) {
     1295    return ("FAIL", "IPv6 addresses must be in the format h:h:h::h")
     1296        unless $val =~ /^[a-fA-F0-9:]+$/
     1297  }
    12931298  if ($rectype == $reverse_typemap{A} or $rectype == $reverse_typemap{AAAA}) {
    12941299    my $tmpip = new NetAddr::IP $val or
  • trunk/dns.cgi

    r159 r160  
    3737
    3838my @debugbits;  # temp, to be spit out near the end of processing
    39 my $debugenv = 1;
     39my $debugenv = 0;
    4040
    4141# Let's do these templates right...
     
    8686
    8787# per-page startwith, filter, searchsubs
     88
     89##fixme:  complain-munge-and-continue with non-"[a-z0-9-.]" filter and startwith
     90$webvar{startwith} =~ s/^(0-9|[a-z]).*/$1/ if $webvar{startwith};
     91# not much call for chars not allowed in domain names
     92$webvar{filter} =~ s/[^a-zA-Z0-9_.:@-]//g if $webvar{filter};
     93
    8894$session->param($webvar{page}.'startwith', $webvar{startwith}) if defined($webvar{startwith});
    8995$session->param($webvar{page}.'filter', $webvar{filter}) if defined($webvar{filter});
     
    98104my $filter = $session->param($webvar{page}.'filter');
    99105my $searchsubs = $session->param($webvar{page}.'searchsubs');
     106
     107# ... and assemble the args
     108my @filterargs;
     109push @filterargs, "^[$startwith]" if $startwith;
     110push @filterargs, $filter if $filter;
    100111
    101112# nrgh, can't handle login here because we don't have a database handle to check the user/pass with yet
     
    121132use warnings qw(uninitialized);
    122133
    123 # default
    124 #my $perpage = 15;
    125 my $perpage = 5;
     134# pagination
     135my $perpage = 15;
    126136my $offset = ($webvar{offset} ? $webvar{offset} : 0);
    127137
     
    133143# note this is not *absolutely* fatal, since there's a default dbname/user/pass in DNSDB.pm
    134144# we'll catch a bad DB connect string a little further down.
     145##fixme:  pass params to loadConfig, and use them there, to allow one codebase to support multiple sites
    135146if (!loadConfig()) {
    136147  warn "Using default configuration;  unable to load custom settings: $DNSDB::errstr";
     
    288299        unless ($permissions{admin} || $permissions{domain_create});
    289300
     301##fixme:  scope check on $webvar{group}
    290302  my ($code,$msg) = addDomain($dbh,$webvar{domain},$webvar{group},($webvar{makeactive} eq 'on' ? 1 : 0));
    291303
     
    303315        unless ($permissions{admin} || $permissions{domain_delete});
    304316
     317##fixme: scope check on $webvar{id}
    305318  $page->param(id => $webvar{id});
    306319
     
    317330    my ($code,$msg) = delDomain($dbh, $webvar{id});
    318331    if ($code ne 'OK') {
    319 # need to find failure mode
    320332      logaction($webvar{id}, $session->param("username"), $pargroup, "Failed to delete domain $dom ($msg)");
    321333      changepage(page => "domlist", errmsg => "Error deleting domain $dom: $msg");
     
    340352    $page->param(errmsg => "You are not permitted to view or change the requested ".
    341353        ($webvar{defrec} eq 'y' ? "group's default records" : "domain's records"));
    342     $page->param(perm_err => 1);
     354    $page->param(perm_err => 1);        # this causes the template to skip the record listing output.
     355##fixme:  we could skip down to the end of the $webvar{page} eq 'reclist' block...
    343356  }
    344357 
     
    354367# ACLs
    355368    $page->param(record_create  => ($permissions{admin} || $permissions{record_create}) );
    356 #  $page->param(record_edit     => ($permissions{admin} || $permissions{record_edit}) );
     369# we don't have any general edit links on the page;  they're all embedded in the TMPL_LOOP
     370#    $page->param(record_edit   => ($permissions{admin} || $permissions{record_edit}) );
    357371    $page->param(record_delete  => ($permissions{admin} || $permissions{record_delete}) );
    358372
     
    415429  }
    416430
    417 
    418431  if ($webvar{recact} eq 'new') {
    419432
     
    433446        unless ($permissions{admin} || $permissions{record_create});
    434447
     448##fixme: this should probably go in DNSDB::addRec(), need to ponder what to do about PTR and friends
    435449    # prevent out-of-domain records from getting added by appending the domain, or DOMAIN for default records
    436450    my $pname = ($webvar{defrec} eq 'y' ? 'DOMAIN' : domainName($dbh,$webvar{parentid}));
     
    505519    $webvar{name} =~ s/\.*$/\.$pname/ if $webvar{name} !~ /$pname$/;
    506520
    507 ##fixme:  get current/previous record info so we can log "updated 'foo A 1.2.3.4' to 'foo A 2.3.4.5'"
     521    # get current/previous record info so we can log "updated 'foo A 1.2.3.4' to 'foo A 2.3.4.5'"
     522    my $oldrec = getRecLine($dbh, $webvar{defrec}, $webvar{id});
    508523
    509524    my ($code,$msg) = updateRec($dbh,$webvar{defrec},$webvar{id},
     
    513528    if ($code eq 'OK') {
    514529      if ($webvar{defrec} eq 'y') {
    515         my $restr = "Updated default record '$webvar{name} $typemap{$webvar{type}} $webvar{address}', TTL $webvar{ttl}";
     530        my $restr = "Updated default record from '$oldrec->{host} $typemap{$oldrec->{type}} $oldrec->{val}', TTL $oldrec->{ttl}\n".
     531                "to '$webvar{name} $typemap{$webvar{type}} $webvar{address}', TTL $webvar{ttl}";
    516532        logaction(0, $session->param("username"), $webvar{parentid}, $restr);
    517533        changepage(page => "reclist", id => $webvar{parentid}, defrec => $webvar{defrec}, resultmsg => $restr);
    518534      } else {
    519         my $restr = "Updated record '$webvar{name} $typemap{$webvar{type}} $webvar{address}', TTL $webvar{ttl}";
     535        my $restr = "Updated record from '$oldrec->{host} $typemap{$oldrec->{type}} $oldrec->{val}', TTL $oldrec->{ttl}\n".
     536                "to '$webvar{name} $typemap{$webvar{type}} $webvar{address}', TTL $webvar{ttl}";
    520537        logaction($webvar{parentid}, $session->param("username"), parentID($webvar{id}, 'rec', 'group'), $restr);
    521538        changepage(page => "reclist", id => $webvar{parentid}, defrec => $webvar{defrec}, resultmsg => $restr);
     
    11471164  }
    11481165
    1149 #} elsif ($webvar{page} eq 'edituser') {
    1150 
    11511166} elsif ($webvar{page} eq 'dnsq') {
    11521167
     
    14961511  $page->param(ttl      => $soa{ttl});
    14971512
    1498 #  $startwith = $session->param($webvar{page}.'startwith');
    1499 #  $filter = $session->param($webvar{page}.'filter');
    1500 
    15011513  my $foo2 = getDomRecs($dbh,$def,$id,$perpage,$webvar{offset},$sortby,$sortorder,$filter);
    15021514
     
    16501662sub listdomains {
    16511663
    1652 #  $startwith = $session->param($webvar{page}.'startwith');
    1653 #  $filter = $session->param($webvar{page}.'filter');
    16541664  $searchsubs = $session->param($webvar{page}.'searchsubs');
    16551665
     
    16591669  $page->param(domain_delete    => ($permissions{admin} || $permissions{domain_delete}) );
    16601670
    1661 ##fixme:  $logingroup or $curgroup?
    16621671  my @childgroups;
    16631672  getChildren($dbh, $curgroup, \@childgroups, 'all') if $searchsubs;
     
    16651674
    16661675  my $sql = "SELECT count(*) FROM domains WHERE group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    1667         ($startwith ? " AND domain ~* '^[$startwith]'" : '').
    1668         ($filter ? " AND domain ~* '$filter'" : '');
     1676        ($startwith ? " AND domain ~* ?" : '').
     1677        ($filter ? " AND domain ~* ?" : '');
    16691678  my $sth = $dbh->prepare($sql);
    1670   $sth->execute;
     1679  $sth->execute(@filterargs);
    16711680  my ($count) = $sth->fetchrow_array;
    16721681
     
    16871696  fill_colheads($sortby, $sortorder, \@cols, \%colheads);
    16881697
    1689 #  $page->param(sortorder => $sortorder);
    16901698  # hack! hack! pthbttt.  have to rethink the status column storage,
    16911699  # or inactive comes "before" active.  *sigh*
     
    17071715        " INNER JOIN groups ON domains.group_id=groups.group_id".
    17081716        " WHERE domains.group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    1709 ##fixme:  don't do variable subs in SQL, use placeholders and params in ->execute()
    1710         ($startwith ? " AND domain ~* '^[$startwith]'" : '').
    1711         ($filter ? " AND domain ~* '$filter'" : '').
     1717        ($startwith ? " AND domain ~* ?" : '').
     1718        ($filter ? " AND domain ~* ?" : '').
    17121719        " ORDER BY ".($sortby eq 'group' ? 'groups.group_name' : $sortby).
    17131720        " $sortorder ".($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage);
    17141721  $sth = $dbh->prepare($sql);
    1715   $sth->execute;
     1722  $sth->execute(@filterargs);
    17161723  my $rownum = 0;
    17171724  while (my @data = $sth->fetchrow_array) {
     
    17221729    $row{group} = $data[3];
    17231730    $row{bg} = ($rownum++)%2;
    1724 #    $row{mkactive} = ($data[2] eq 'inactive' ? 1 : 0);
    17251731    $row{mkactive} = !$data[2];
    17261732    $row{sid} = $sid;
     
    17291735    $row{domain_edit} = ($permissions{admin} || $permissions{domain_edit});
    17301736    $row{domain_delete} = ($permissions{admin} || $permissions{domain_delete});
    1731 ##fixme:  need to clean up status indicator/usage/inversion
    17321737    push @domlist, \%row;
    17331738  }
     
    17441749    $page->param(errmsg => "You are not permitted to view the requested group");
    17451750    $curgroup = $logingroup;
    1746 #    changepage(page => grpman, errmsg => "You are not permitted to view the requested group");
    1747 #    return;
    1748   }
    1749 # if ( grep { eq $curgroup }, @childlist ) {
    1750 #   errmsg => "You are not permitted to view this group"
    1751 #    return;
    1752 # }
     1751  }
    17531752
    17541753  my @childgroups;
     
    17571756
    17581757  my $sql = "SELECT count(*) FROM groups WHERE parent_group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    1759         ($startwith ? " AND group_name ~* '^[$startwith]'" : '').
    1760         ($filter ? " AND group_name ~* '$filter'" : '');
     1758        ($startwith ? " AND group_name ~* ?" : '').
     1759        ($filter ? " AND group_name ~* ?" : '');
    17611760  my $sth = $dbh->prepare($sql);
    1762 
    1763   $sth->execute;
     1761  $sth->execute(@filterargs);
    17641762  my ($count) = ($sth->fetchrow_array);
     1763
    17651764# fill page count and first-previous-next-last-all bits
    1766 ##fixme - hardcoded group bit
    17671765  fill_pgcount($count,"groups",'');
    17681766  fill_fpnla($count);
     
    17941792
    17951793  my @grouplist;
    1796   $sth = $dbh->prepare("SELECT g.group_id, g.group_name, g2.group_name, ".
     1794  $sql = "SELECT g.group_id, g.group_name, g2.group_name, ".
    17971795        "count(distinct(u.username)) AS nusers, count(distinct(d.domain)) AS ndomains ".
    17981796        "FROM groups g ".
     
    18011799        "LEFT OUTER JOIN domains d ON d.group_id=g.group_id ".
    18021800        "WHERE g.parent_group_id IN ($curgroup".($childlist ? ",$childlist" : '').") ".
    1803 ##fixme:  don't do variable subs in SQL, use placeholders and params in ->execute()
    1804         ($startwith ? " AND g.group_name ~* '^[$startwith]'" : '').
    1805         ($filter ? " AND g.group_name ~* '$filter'" : '').
     1801        ($startwith ? " AND g.group_name ~* ?" : '').
     1802        ($filter ? " AND g.group_name ~* ?" : '').
    18061803        " GROUP BY g.group_id, g.group_name, g2.group_name ".
    18071804        " ORDER BY $sortby $sortorder ".
    1808         ($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage));
    1809   $sth->execute;
     1805        ($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage);
     1806  $sth = $dbh->prepare($sql);
     1807  $sth->execute(@filterargs);
    18101808
    18111809  my $rownum = 0;
     
    18661864
    18671865  my $sql = "SELECT count(*) FROM users WHERE group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    1868         ($startwith ? " AND username ~* '^[$startwith]'" : '').
    1869         ($filter ? " AND username ~* '$filter'" : '');
     1866        ($startwith ? " AND username ~* ?" : '').
     1867        ($filter ? " AND username ~* ?" : '');
    18701868  my $sth = $dbh->prepare($sql);
    1871   $sth->execute;
     1869  $sth->execute(@filterargs);
    18721870  my ($count) = ($sth->fetchrow_array);
    18731871
     
    19071905        "INNER JOIN groups g ON u.group_id=g.group_id ".
    19081906        "WHERE u.group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    1909 ##fixme:  don't do variable subs in SQL, use placeholders and params in ->execute()
    1910         ($startwith ? " AND u.username ~* '^[$startwith]'" : '').
    1911         ($filter ? " AND u.username ~* '$filter'" : '').
     1907        ($startwith ? " AND u.username ~* ?" : '').
     1908        ($filter ? " AND u.username ~* ?" : '').
    19121909        " ORDER BY $sortby $sortorder ".
    19131910        ($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage);
    19141911
    19151912  $sth = $dbh->prepare($sql);
    1916   $sth->execute;
     1913  $sth->execute(@filterargs);
    19171914
    19181915  my $rownum = 0;
Note: See TracChangeset for help on using the changeset viewer.