Changeset 90 for trunk


Ignore:
Timestamp:
04/12/11 14:46:22 (14 years ago)
Author:
Kris Deugau
Message:

/trunk

Checkpoint

  • Comment-docu-tweak on addUser()
  • Disallow blank usernames on creation
  • Add docucomment on updateUser()
  • Update getRecLine() to handle long records, switch to more compact $dbh->do() instead of explicit prepare/execute/fetch
  • Update getDomRecs() to handle long records. Clean up SQL formatting.
  • Update addRec() to handle long records
  • Update updateRec() to handle long records
  • Add fixme reminder to handle VegaDNS encrypted passwords (hex-coded MD5)
  • Use getRecData() instead of local SQL for "Edit record" page
  • Log user add and update actions
  • Remember to set $permissions{admin} to 1 on update if user type is "superuser"
  • Uncomment page=deluser segment; this was not integrated with the rest of the user add/update pages
  • HTML-comment "Customer ID" (uid) on log pages; having trouble seeing a use-case
  • Nitpick tweaks on record add/edit page
  • Use HTML::Template's HTML-escaping on the record value on record add/edit page - required for eg SPF records with quotation marks
Location:
trunk
Files:
4 edited

Legend:

Unmodified
Added
Removed

  • trunk/DNSDB.pm

    r87 r90  
    690690#   last name                   - defaults to blank
    691691#   phone                       - defaults to blank (could put other data within column def)
    692 # Returns (OK,OK) on success, (FAIL,<message>) on failure
     692# Returns (OK,<uid>) on success, (FAIL,<message>) on failure
    693693sub addUser {
    694694  $errstr = '';
    … …  
    699699  my $state = shift;
    700700
    701   return ('FAIL',"Missing one or more required entries") if !defined($state);
     701  return ('FAIL', "Missing one or more required entries") if !defined($state);
     702  return ('FAIL', "Username must not be blank") if !$username;
    702703
    703704  my $type = shift || 'u';      # create limited users by default - fwiw, not sure yet how this will interact with ACLs
    … …  
    816817
    817818## DNSDB:: updateUser()
    818 #
     819# Update general data about user
    819820sub updateUser {
    820821  my $dbh = shift;
    … …  
    10201021  my $id = shift;
    10211022
    1022   my $sql = "SELECT record_id,host,type,val,distance,weight,port,ttl".
    1023         (($def eq 'def' or $def eq 'y') ? ',group_id FROM default_' : ',domain_id FROM ').
    1024         "records WHERE record_id=?";
    1025   my $sth = $dbh->prepare($sql);
    1026   $sth->execute($id);
    1027 
    1028   my ($recid,$host,$rtype,$val,$distance,$weight,$port,$ttl,$parid) = $sth->fetchrow_array();
    1029 
    1030   if ($sth->err) {
     1023  my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.distance,r.weight,r.port,r.ttl,r.longrec_id,l.recdata".
     1024        (($def eq 'def' or $def eq 'y') ? ',r.group_id FROM default_' : ',r.domain_id FROM ').
     1025        "records r LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id WHERE record_id=?";
     1026  my $ret = $dbh->selectrow_hashref($sql, undef, ($id) ) or warn $dbh->errstr;
     1027
     1028  if ($dbh->err) {
    10311029    $errstr = $DBI::errstr;
    10321030    return undef;
    10331031  }
    1034   my %ret;
    1035   $ret{recid}   = $recid;
    1036   $ret{host}    = $host;
    1037   $ret{type}    = $rtype;
    1038   $ret{val}     = $val;
    1039   $ret{distance}= $distance;
    1040   $ret{weight}  = $weight;
    1041   $ret{port}    = $port;
    1042   $ret{ttl}     = $ttl;
    1043   $ret{parid}   = $parid;
    1044 
    1045   return %ret;
     1032
     1033  $ret->{val} = $ret->{recdata} if $ret->{longrec_id};  # put the long data in the real value space
     1034  delete $ret->{longrec_id};    # remove these since they shouldn't be exposed - the caller
     1035  delete $ret->{recdata};       # should not care about "long records" vs normal ones.
     1036
     1037  return $ret;
    10461038}
    10471039
    … …  
    10651057  my $direction = shift || 'ASC';
    10661058
    1067   my $sql = "SELECT record_id,host,type,val,distance,weight,port,ttl FROM ";
    1068   if ($type eq 'def' or $type eq 'y') {
    1069     $sql .= " default_records where group_id=$id";
     1059  $type = 'y' if $type eq 'def';
     1060
     1061  my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.distance,r.weight,r.port,r.ttl,r.longrec_id,l.recdata FROM ";
     1062  $sql .= "default_" if $type eq 'y';
     1063  $sql .= "records r ";
     1064  $sql .= "LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id ";
     1065  if ($type eq 'y') {
     1066    $sql .= "WHERE r.group_id=?";
    10701067  } else {
    1071     $sql .= " records where domain_id=$id";
    1072   }
    1073   $sql .= " and not type=$reverse_typemap{SOA} order by $order $direction";
     1068    $sql .= "WHERE r.domain_id=?";
     1069  }
     1070  $sql .= " AND NOT r.type=$reverse_typemap{SOA} ORDER BY r.$order $direction";
    10741071##fixme:  need to set nstart properly (offset is not internally multiplied with limit)
    1075   $sql .= " limit $nrecs offset ".($nstart*$nrecs) if $nstart ne 'all';
    1076 
    1077   my $sth = $dbh->prepare($sql);
    1078   $sth->execute;
     1072  $sql .= " LIMIT $nrecs OFFSET ".($nstart*$nrecs) if $nstart ne 'all';
     1073
     1074  my $sth = $dbh->prepare($sql) or warn $dbh->errstr;
     1075  $sth->execute($id) or warn "$sql: ".$sth->errstr;
    10791076
    10801077  my @retbase;
    10811078  while (my $ref = $sth->fetchrow_hashref()) {
     1079    $ref->{val} = $ref->{recdata} if $ref->{longrec_id};        # put the long data in the real value space
     1080    delete $ref->{longrec_id};  # remove these since they shouldn't be exposed - the caller
     1081    delete $ref->{recdata};     # should not care about "long records" vs normal ones.
    10821082    push @retbase, $ref;
    10831083  }
    … …  
    11331133  }
    11341134
    1135   my $sql = "insert into ".($defrec eq 'y' ? 'default_' : '')."records ($fields) values ($vallen)";
    1136 ##fixme: use array for values, replace "vallist" with series of ?,?,? etc
    1137 # something is bugging me about this...
    1138 #warn "DEBUG: $sql";
    1139   my $sth = $dbh->prepare($sql);
    1140   $sth->execute(@vallist);
    1141 
    1142   return ('FAIL',$sth->errstr) if $sth->err;
     1135  # Allow transactions, and raise an exception on errors so we can catch it later.
     1136  # Use local to make sure these get "reset" properly on exiting this block
     1137  local $dbh->{AutoCommit} = 0;
     1138  local $dbh->{RaiseError} = 1;
     1139
     1140  eval {
     1141    if (length($val) > 100 ) {
     1142      # extralong records get an entry in a separate table.
     1143      $dbh->do("INSERT INTO longrecs (recdata) VALUES (?)", undef, ($val) );
     1144      my ($longid) = $dbh->selectrow_array("SELECT longrec_id FROM longrecs WHERE recdata=?", undef, ($val) );
     1145      $fields .= ",longrec_id";
     1146      $vallen .= ",?";
     1147      push @vallist, $longid;
     1148      $vallist[3] = ''; # so we don't barf when we insert the main record
     1149    }
     1150    $dbh->do("INSERT INTO ".($defrec eq 'y' ? 'default_' : '')."records ($fields) VALUES ($vallen)",
     1151        undef, @vallist);
     1152    $dbh->commit;
     1153  };
     1154  if ($@) {
     1155    my $msg = $@;
     1156    eval { $dbh->rollback; };
     1157    return ('FAIL',$msg);
     1158  }
    11431159
    11441160  return ('OK','OK');
     1161
    11451162} # end addRec()
    11461163
    … …  
    11791196  }
    11801197
    1181   my $sth = $dbh->prepare("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records ".
    1182         "SET host=?,type=?,val=?,ttl=?,distance=?,weight=?,port=? ".
    1183         "WHERE record_id=?");
    1184   $sth->execute($host,$type,$val,$ttl,$dist,$weight,$port,$id);
    1185 
    1186   return ('FAIL',$sth->errstr."<br>\n$errstr<br>\n") if $sth->err;
     1198#  my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.distance,r.weight,r.port,r.ttl,r.longrec_id,l.recdata FROM ";
     1199#  $sql .= "default_" if $type eq 'y';
     1200#  $sql .= "records r ";
     1201#  $sql .= "LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id ";
     1202
     1203  # get the long record ID, if any
     1204  my ($longid) = $dbh->selectrow_array("SELECT longrec_id FROM ".($defrec eq 'y' ? 'default_' : '')."records ".
     1205        "WHERE record_id=?", undef, ($id) );
     1206
     1207  local $dbh->{AutoCommit} = 0;
     1208  local $dbh->{RaiseError} = 1;
     1209
     1210  eval {
     1211    # there's really no tidy way to squash this down.  :/
     1212    if (length($val) > 100) {
     1213      if ($longid) {
     1214        $dbh->do("UPDATE longrecs SET recdata=? WHERE longrec_id=?", undef, ($val, $longid) );
     1215      } else {
     1216##fixme:  has to be a better way to be sure we get the right recid back once inserted...
     1217        $dbh->do("INSERT INTO longrecs (recdata) VALUES (?)", undef, ($val) );
     1218        my ($newlongid) = $dbh->selectrow_array("SELECT currval('longrecs_longrec_id_seq')");
     1219        $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records SET val=?,longrec_id=? ".
     1220                "WHERE record_id=?", undef, ('', $newlongid, $id) );
     1221      }
     1222    } else {
     1223      if ($longid) {
     1224        $dbh->do("DELETE FROM longrecs WHERE longrec_id=?", undef, ($longid) );
     1225        $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records SET val=?,longrec_id=NULL ".
     1226                "WHERE record_id=?", undef, ($val, $id) );
     1227      } else {
     1228        $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records SET val=? ".
     1229                "WHERE record_id=?", undef, ($val, $id) );
     1230      }
     1231    }
     1232
     1233    $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records ".
     1234        "SET host=?,type=?,ttl=?,distance=?,weight=?,port=? ".
     1235        "WHERE record_id=?", undef, ($host, $type, $ttl, $dist, $weight, $port, $id) );
     1236
     1237  };
     1238  if ($@) {
     1239    my $msg = $@;
     1240    $dbh->rollback;
     1241    return ('FAIL', $msg);
     1242  }
     1243#  return ('FAIL',$sth->errstr."<br>\n$errstr<br>\n") if $sth->err;
    11871244
    11881245  return ('OK','OK');

  • trunk/dns.cgi

    r88 r90  
    116116    $webvar{loginfailed} = 1 if !defined($uid);
    117117
     118##fixme:  allow imported VegaDNS passwords.  hash is hex-coded MD5.
     119#perl -e 'use Digest::MD5 qw(md5 md5_hex md5_base64); print md5_hex("test")."\n";'
     120#098f6bcd4621d373cade4e832627b4f6
     121
    118122    if ($pass =~ m|^\$1\$([A-Za-z0-9/.]+)\$|) {
    119123      $webvar{loginfailed} = 1 if $pass ne unix_md5_crypt($webvar{password},$1);
    … …  
    343347    $page->param(id             => $webvar{id});
    344348    $page->param(defrec         => $webvar{defrec});
    345 ##fixme: SQL does not belong!
    346     my $sth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl FROM ".
    347         ($webvar{defrec} eq 'y' ? 'default_' : '')."records WHERE record_id=?");
    348     $sth->execute($webvar{id});
    349     my ($host,$type,$val,$distance,$weight,$port,$ttl) = $sth->fetchrow_array;
    350     $page->param(name           => $host);
    351     $page->param(address        => $val);
    352     $page->param(distance       => $distance);
    353     $page->param(weight         => $weight);
    354     $page->param(port           => $port);
    355     $page->param(ttl            => $ttl);
    356     fill_rectypes($type);
     349    my $recdata = getRecLine($dbh, $webvar{defrec}, $webvar{id});
     350    $page->param(name           => $recdata->{host});
     351    $page->param(address        => $recdata->{val});
     352    $page->param(distance       => $recdata->{distance});
     353    $page->param(weight         => $recdata->{weight});
     354    $page->param(port           => $recdata->{port});
     355    $page->param(ttl            => $recdata->{ttl});
     356    fill_rectypes($recdata->{type});
    357357
    358358  } elsif ($webvar{recact} eq 'update') {
    … …  
    660660                ($webvar{makeactive} eq 'on' ? 1 : 0), $webvar{accttype}, $permstring,
    661661                $webvar{fname}, $webvar{lname}, $webvar{phone});
     662        logaction(0, $session->param("username"), $curgroup, "Added user $webvar{uname} (uid $msg)")
     663                if $code eq 'OK';
    662664      } else {
    663665# User update is icky.  I'd really like to do this in one atomic
    … …  
    670672##fixme - need to actually get a correct permission set to pass in here,
    671673# also a flag to revert custom permissions to inherited
     674          $newperms{admin} = 1 if $webvar{accttype} eq 'S';
    672675          ($code,$msg) = changePermissions($dbh, 'user', $webvar{uid}, \%newperms, ($permstring eq 'i'));
    673676        }
     677        logaction(0, $session->param("username"), $curgroup,
     678                "Updated uid $webvar{uid}, user $webvar{uname} ($webvar{fname} $webvar{lname})");
    674679      }
    675680    }
    … …  
    677682    if ($code eq 'OK') {
    678683
    679 ##fixme:  log doesn't log.  WTF?  O_o
    680       logaction(0, $session->param("username"), $webvar{group},
    681         ($webvar{action} eq 'add' ? 'Added' : 'Updated')." uid $webvar{uid}, user $webvar{uname} ($webvar{fname} $webvar{lname})");
    682684      if ($alterperms) {
    683685        changepage(page => "useradmin", warnmsg =>
    … …  
    810812##  $page->param(add_failed => 1);
    811813#
    812 #} elsif ($webvar{page} eq 'deluser') {
    813 #
    814 #  $page->param(id => $webvar{id});
    815 #  # first pass = confirm y/n (sorta)
    816 #  if (!defined($webvar{del})) {
    817 #    $page->param(del_getconf => 1);
    818 #    $page->param(user => userFullName($dbh,$webvar{id}));
    819 #  } elsif ($webvar{del} eq 'ok') {
    820 ###fixme: find group id user is in (for logging) *before* we delete the user
    821 #    my ($code,$msg) = delUser($dbh, $webvar{id});
    822 #    if ($code ne 'OK') {
    823 ## need to find failure mode
    824 #      $page->param(del_failed => 1);
    825 #      $page->param(errmsg => $msg);
    826 #      list_users($curgroup);
    827 #    } else {
    828 #      # success.  go back to the user list, do not pass "GO"
    829 ###log
    830 #      logaction(0, $session->param("username"), $webvar{group}, "Added domain $webvar{domain}");
    831 #      changepage(page => "useradmin");
    832 #    }
    833 #  } else {
    834 #    # cancelled.  whee!
    835 #    changepage(page => "useradmin");
    836 #  }
    837 #
     814
     815} elsif ($webvar{page} eq 'deluser') {
     816
     817  $page->param(id => $webvar{id});
     818  # first pass = confirm y/n (sorta)
     819  if (!defined($webvar{del})) {
     820    $page->param(del_getconf => 1);
     821    $page->param(user => userFullName($dbh,$webvar{id}));
     822  } elsif ($webvar{del} eq 'ok') {
     823##fixme: find group id user is in (for logging) *before* we delete the user
     824##fixme: get other user data too for log
     825    my ($code,$msg) = delUser($dbh, $webvar{id});
     826    if ($code ne 'OK') {
     827# need to find failure mode
     828      $page->param(del_failed => 1);
     829      $page->param(errmsg => $msg);
     830      list_users($curgroup);
     831    } else {
     832      # success.  go back to the user list, do not pass "GO"
     833##log
     834      logaction(0, $session->param("username"), $curgroup, "Deleted user $webvar{id}");
     835      changepage(page => "useradmin");
     836    }
     837  } else {
     838    # cancelled.  whee!
     839    changepage(page => "useradmin");
     840  }
     841
    838842#} elsif ($webvar{page} eq 'edituser') {
    839843

  • trunk/templates/log.tmpl

    r60 r90  
    1111  <tr class="darkrowheader">
    1212      <td>Name</td>
    13       <td>Customer ID</td>
    14       <td>Email</td>
     13      <!-- Not sure "Customer ID" (filled with uid) is of any use... -->
     14      <!-- td>Customer ID</td -->
     15      <td>Username/Email</td>
    1516      <td>Log Entry</td>
    1617      <td>Date / Time</td>
    … …  
    2021    <tr class="datalinelight">
    2122        <td><TMPL_VAR NAME=userfname></td>
    22         <td><TMPL_VAR NAME=userid></td>
     23        <!-- td><TMPL_VAR NAME=userid></td -->
    2324        <td><TMPL_VAR NAME=useremail></td>
    2425        <td><TMPL_VAR NAME=logentry></td>

  • trunk/templates/record.tmpl

    r87 r90  
    1919
    2020    <table border="0" cellspacing="2" cellpadding="2" width="100%">
    21 <TMPL_IF failed><tr><td class="errhead" colspan="2">Error <TMPL_VAR NAME=wastrying> record: <TMPL_VARNAME=errmsg></td></tr></TMPL_IF>
     21<TMPL_IF failed>        <tr><td class="errhead" colspan="2">Error <TMPL_VAR NAME=wastrying> record: <TMPL_VARNAME=errmsg></td></tr></TMPL_IF>
    2222        <tr class="tableheader"><td align="center" colspan="2"><TMPL_VAR NAME=todo>: <TMPL_VAR NAME=dohere></td></tr>
    2323        <tr class="datalinelight">
    … …  
    2828                <td>Type</td>
    2929                <td><select name="type">
    30         <TMPL_LOOP NAME=typelist>
     30<TMPL_LOOP NAME=typelist>
    3131                        <option value="<TMPL_VAR NAME=recval>"<TMPL_IF NAME=tselect> selected="selected"</TMPL_IF>><TMPL_VAR NAME=recname></option>
    32         </TMPL_LOOP>
     32</TMPL_LOOP>
    3333                </select></td>
    3434        </tr>
    3535        <tr class="datalinelight">
    3636                <td>Address</td>
    37                 <td><input type="text" name="address" value="<TMPL_VAR NAME=address>" /></td>
     37                <td><input type="text" name="address" value="<TMPL_VAR ESCAPE=HTML NAME=address>" /></td>
    3838        </tr>
    3939        <tr class="datalinelight">
Note: See TracChangeset for help on using the changeset viewer.