Changeset 224 for trunk/DNSDB.pm

Timestamp:

01/24/12 17:59:24 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Start adding rDNS (See #26)

• Initial SQL tabledef, default records, and record types (we'll abuse the reserved range of DNS record types from 65280 to 65534 for some extra custom types)
• Start passing $webvar{revrec} around similar to $webvar{defrec}
• Adjust record list page to handle reverse records. Tested on group-default reverse records but should work fine for live zones
• Add "Reverse Zones" and "Default Reverse Records" to menu
• While we're rearranging it, add a little more paranoia on some of the SQL

File:

• trunk/DNSDB.pm (modified) (8 diffs)

trunk/DNSDB.pm

@@ -129 +129 @@
                 perpage         => 15,
         );
+
+
+##
+## utilty functions
+# _rectable()
+# Takes default+rdns flags, returns appropriate table name
+sub _rectable {
+  my $def = shift;
+  my $rev = shift;
+
+  return 'records' if $def ne 'y';
+  return 'default_records' if $rev ne 'y';
+  return 'default_rev_records';
+} # end _rectable()
+
+# _recparent()
+# Takes default+rdns flags, returns appropriate parent-id column name
+sub _recparent {
+  my $def = shift;
+  my $rev = shift;
+
+  return 'group_id' if $def eq 'y';
+  return 'rdns_id' if $rev eq 'y';
+  return 'domain_id';
+} # end _recparent()
 
 
@@ -703 +728 @@
 
 
+## DNSDB::revName()
+# Return the reverse zone name based on an rDNS ID
+# Takes a database handle and the rDNS ID
+# Returns the reverse zone name or undef on failure
+sub revName {
+  $errstr = '';
+  my $dbh = shift;
+  my $revid = shift;
+  my ($revname) = $dbh->selectrow_array("SELECT revnet FROM revzones WHERE rdns_id=?", undef, ($revid) );
+  $errstr = $DBI::errstr if !$revname;
+  return $revname if $revname;
+} # end revName()
+
+
 ## DNSDB::domainID()
 # Takes a database handle and domain name
@@ -1212 +1251 @@
 ## DNSDB::getSOA()
 # Return all suitable fields from an SOA record in separate elements of a hash
-# Takes a database handle, default/live flag, and group (default) or domain (live) ID
+# Takes a database handle, default/live flag, domain/reverse flag, and parent ID
 sub getSOA {
   $errstr = '';
   my $dbh = shift;
   my $def = shift;
+  my $rev = shift;
   my $id = shift;
   my %ret;
 
-  # (ab)use distance and weight columns to store SOA data
-
-  my $sql = "SELECT record_id,host,val,ttl,distance from";
-  if ($def eq 'def' or $def eq 'y') {
-    $sql .= " default_records WHERE group_id=? AND type=$reverse_typemap{SOA}";
-  } else {
-    # we're editing a live SOA record;  find based on domain
-    $sql .= " records WHERE domain_id=? AND type=$reverse_typemap{SOA}";
-  }
+  # (ab)use distance and weight columns to store SOA data?  can't for default_rev_records...
+  # - should really attach serial to the zone parent somewhere
+
+  my $sql = "SELECT record_id,host,val,ttl from "._rectable($def,$rev).
+        " WHERE "._recparent($def,$rev)." = ? AND type=$reverse_typemap{SOA}";
+
   my $sth = $dbh->prepare($sql);
   $sth->execute($id);
 
-  my ($recid,$host,$val,$ttl,$serial) = $sth->fetchrow_array() or return;
+  my ($recid,$host,$val,$ttl) = $sth->fetchrow_array() or return;
   my ($contact,$prins) = split /:/, $host;
   my ($refresh,$retry,$expire,$minttl) = split /:/, $val;
@@ -1238 +1275 @@
   $ret{recid}   = $recid;
   $ret{ttl}     = $ttl;
-  $ret{serial}  = $serial;
+#  $ret{serial} = $serial;      # ca't use distance for serial with default_rev_records
   $ret{prins}   = $prins;
   $ret{contact} = $contact;
@@ -1289 +1326 @@
   $errstr = '';
   my $dbh = shift;
-  my $type = shift;
+  my $def = shift;
+  my $rev = shift;
   my $id = shift;
   my $nrecs = shift || 'all';
@@ -1300 +1338 @@
   my $filter = shift || '';
 
-  $type = 'y' if $type eq 'def';
-
-  my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.distance,r.weight,r.port,r.ttl FROM ";
-  $sql .= "default_" if $type eq 'y';
-  $sql .= "records r ";
+  my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.ttl";
+  $sql .= ",r.distance,r.weight,r.port" if $rev eq 'n';
+  $sql .= " FROM "._rectable($def,$rev)." r ";
   $sql .= "INNER JOIN rectypes t ON r.type=t.val ";     # for sorting by type alphabetically
-  if ($type eq 'y') {
-    $sql .= "WHERE r.group_id=?";
-  } else {
-    $sql .= "WHERE r.domain_id=?";
-  }
+  $sql .= "WHERE "._recparent($def,$rev)." = ?";
   $sql .= " AND NOT r.type=$reverse_typemap{SOA}";
   $sql .= " AND host ~* ?" if $filter;
   # use alphaorder column for "correct" ordering of sort-by-type instead of DNS RR type number
   $sql .= " ORDER BY ".($order eq 'type' ? 't.alphaorder' : "r.$order")." $direction";
-  $sql .= " LIMIT $nrecs OFFSET ".($nstart*$nrecs) if $nstart ne 'all';
 
   my @bindvars = ($id);
   push @bindvars, $filter if $filter;
+
+  # just to be ultraparanoid about SQL injection vectors
+  if ($nstart ne 'all') {
+    $sql .= " LIMIT ? OFFSET ?";
+    push @bindvars, $nrecs;
+    push @bindvars, ($nstart*$nrecs);
+  }
   my $sth = $dbh->prepare($sql) or warn $dbh->errstr;
   $sth->execute(@bindvars) or warn "$sql: ".$sth->errstr;
@@ -1333 +1371 @@
 
 ## DNSDB::getRecCount()
-# Return count of non-SOA records in domain (or default records in a group)
-# Takes a database handle, default/live flag, group/domain ID, and optional filtering modifier
+# Return count of non-SOA records in zone (or default records in a group)
+# Takes a database handle, default/live flag, reverse/forward flag, group/domain ID,
+# and optional filtering modifier
 # Returns the count
 sub getRecCount {
   my $dbh = shift;
   my $defrec = shift;
+  my $revrec = shift;
   my $id = shift;
   my $filter = shift || '';
@@ -1348 +1388 @@
   my @bindvars = ($id);
   push @bindvars, $filter if $filter;
-  my ($count) = $dbh->selectrow_array("SELECT count(*) FROM ".
-        ($defrec eq 'y' ? 'default_' : '')."records ".
-        "WHERE ".($defrec eq 'y' ? 'group' : 'domain')."_id=? ".
-        "AND NOT type=$reverse_typemap{SOA}".
-        ($filter ? " AND host ~* ?" : ''),
-        undef, (@bindvars) );
+  my $sql = "SELECT count(*) FROM ".
+        _rectable($defrec,$revrec).
+        " WHERE "._recparent($defrec,$revrec)."=? ".
+        "AND NOT type=$reverse_typemap{SOA}".
+        ($filter ? " AND host ~* ?" : '');
+  my ($count) = $dbh->selectrow_array($sql, undef, (@bindvars) );
 
   return $count;