Andrew Bedno
AndrewBedno.com  •  Andrew@Bedno.com  •  773-442-2386
 History  Kudos  Samples 

SQL

SQL (click for next photo)

Web-Based Configurable Database Editor

Easy to configure and use web based end-user oriented database records editor in Perl. Include search, filter and browse features. Evolved descendants are in use on major sites.

#!/usr/bin/perl
# DBED - COncise, comprehensive, configurable user oriented SQL Database editor.
# Created by Andrew Bedno 2001.11.20 Chicago IL USA

use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

# Configuration.
$DBINIT = "DBI:mysql:database=database";
$DBLOGIN = "user";
$DBPASSWORD = "password";
# Names of editable tables.
@TABLES = ('tablename');
# Configs below are comma separated names in a string hashed on table name.
# Columns shown in browse.
$COLS_VIEW{'tablename'} = 'fieldname[,...]';
# Columns shown in edit.
$COLS_EDIT{'tablename'} = 'fieldname[,...]';
# Columns used as keys for edit/del.  All must also be in VIEW field set.
$COLS_KEYS{'tablename'} = 'fieldname[,...]';
# Set to C to confirm deletions, or D to delete without confirmation.
$CONFIRM_DELETE = 'c';
$MAX_PAGE_RECORDS = 50;
if (-e '/autoexec.bat') { $LOCALMSG = " (LOCAL)" }

# Constants.
%TYPES=(1 => "char", 2 => "numeric", 3 => "decimal", 4 => "integer",
        5 => "smallint", 6 => "float", 7 => "real", 8 => "double",
        9 => "date", 10=> "time", 11=> "timestamp", 12=> "varchar",
        -1=> "longvarchar", -2=> "binary", -3=> "varbinary", -4=> "longvarbinary",
        -5=> "bigint", -6=> "tinyint", -7=> "bit", -8=> "wchar",
        -9=> "wvarchar", -10=>"wlongvarchar");
$tds = '<font face=Arial size=2 color=black>';
$tde = '</font>';

# Collect parameters.
$param = new CGI;
$currtable = $param->param('table');
$currkey = &xd($param->param('key'));
$password = $param->param('password');
$mode = lc($param->param('mode'));
$currpage = $param->param('page');
$exiturl = &xd($param->param('exiturl'));
if ($exiturl eq '') {
  $exiturlarg = '';
  $exiturlfield = "<input type=hidden name='exiturl' value=''>";
} else {
  $exiturlarg = '&exiturl='.&xe($exiturl);
  $exiturlfield  = "<input type=hidden name='exiturl' value=".&xe($exiturl).">";
}
$currpage =~ s/[^0-9]//g;
if ($currpage eq '') {
  $currpage = 0;
  $pagearg = '';
  $pagefield = "<input type=hidden name='page' value=''>";
} else {
  $pagearg = '&page='.$currpage;
  $pagefield = "<input type=hidden name='page' value=".$currpage.">";
}
$currfilter = lc(&xd($param->param('filter')));
$filtermode = 'a';
if ($currfilter =~ / or /i) { $filtermode = 'o' }
while ($currfilter =~ / or /) { $currfilter =~ s/ or / /g }
while ($currfilter =~ / and /) { $currfilter =~ s/ and / /g }
while ($currfilter =~ /  /) { $currfilter =~ s/  / /g }
while ($currfilter =~ /^ /) { $currfilter =~ s/^ //g }
while ($currfilter =~ / $/) { $currfilter =~ s/ $//g }
$currfilter_show = $currfilter;
if ($currfilter eq '') {
  $filterarg = '';
  $filterfield = '';
  $filtermode = '';
} else {
  if ($filtermode eq 'a') {
    while ($currfilter_show =~ / /) { $currfilter_show =~ s/ /\+/g }
  }
  if ($filtermode eq 'o') {
    while ($currfilter_show =~ / /) { $currfilter_show =~ s/ /\-/g }
  }
  while ($currfilter_show =~ /\+/) { $currfilter_show =~ s/\+/ and /g }
  while ($currfilter_show =~ /\-/) { $currfilter_show =~ s/\-/ or /g }
  $filterarg = '&filter='.&xe($currfilter_show);
  $filterfield = "<input type=hidden name='filter' value=".&xe($currfilter_show).">";
}
@currfilters = split(/ /, $currfilter);
$currorderarg = &xd($param->param('order'));
$currorder = $currorderarg;
$orderdesc = '';
if ($currorder) {
  if (substr($currorder,0,1) eq '-') {
    $currorder = substr($currorder,1,length($currorder)-1);
    $orderdesc = ' DESC'
  }
  $currordersql = "order by $currorder".$orderdesc
} else {
  if ($COLS_KEYS{$currtable} ne '') {
    $currordersql = "order by ".$COLS_KEYS{$currtable}." ";
    $orderdesc = ' DESC'
  } else {
    $currordersql = ""
  }
}
if ($currorderarg eq '') {
  $orderarg = '';
  $orderfield = "<input type=hidden name='order' value=''>";
} else {
  $orderarg = '&order='.&xe($currorderarg);
  $orderfield = "<input type=hidden name='order' value=".&xe($currorderarg).">";
}

&GetPasswordCookie();
if ( ($DBPASSWORD eq '') or ($DBPASSWORD ne $password) ) { &DoLogin() }
if ($mode eq 'e') { &DoEdit() }
if ($mode eq 's') { &DoSave() }
if ($mode eq 'n') { &DoNew() }
if ($mode eq 'a') { &DoAdd() }
if ($mode eq 'c') { &DoDeleteConfirm() }
if ($mode eq 'd') { &DoDelete() }
print &HTML_Head('Browse');
DoBrowse();
exit;


##########################################
# MAJOR MODE HANDLERS

# Browse records.
sub DoBrowse {
  if (! $currtable) {
    print "<font face=Arial color=green size=3><b>Database Editor</b></font>";
    if ($exiturl) {
      print "<font size=2 color=black face=Arial>   <a href='".$exiturl."'>EXIT</a></font>";
    }
    print "<br><br>\n";
    print "<font size=3>Select a table: ";
    foreach $tablename (@TABLES) {
      print ' <a href="dbed.pl?table='.$tablename.$exiturlarg.
            '"><b>'.$tablename.'</b></a> '
    }
    print "</font>\n";
  } else {
    print "<a name='top'></a>";
    print "<table width='100%' border=0><tr>";
    print "<td valign=top align=left><font face=Arial color=green size=3><b>Database Editor</b></font>";
    if ($exiturl) {
      print "<font size=2 color=black face=Arial>   <a href='".$exiturl."'>EXIT</a></font>";
    }
    print "</td>";
    print "<form name='dbedbrowse' action='dbed.pl' method='post'>";
    print "<td align=center valign=top><font face=Arial size=2 color=black>";
    print "<a href='dbed.pl?table=$currtable' title='Show all records.'>Filter</a>: ";
    print $pagefield;
    print $orderfield;
    print $exiturlfield;
    print "<input type=hidden name='table' value='".$currtable."'>";
    print "<input type=text size=20 name='filter' value='".&xe($currfilter_show)."'>";
    print "  <input type=submit value='REFRESH'>";
    print "   <a href='#bottom' title='BOTTOM'><font size=1>BOTTOM</font></a>";
    print "</form>";
    print "</font></td>";
    print "<td align=right valign=top><font face=Arial size=2 color=black>";
    print "<a href="dbed.pl?".$exiturlarg."" title="Change table.">".uc($currtable)."</a>    ";
    print "<a href="dbed.pl?mode=n&table=$currtable$filterarg$orderarg$pagearg$exiturlarg" title='Create a new record.'>add</a> ";
    print "</font></td></tr></table>";
    $dbh = DBI->connect("$DBINIT","$DBLOGIN","$DBPASSWORD");
    if (! $dbh) { &errexit("The database is not available.") }
    $select = $COLS_VIEW{$currtable};
    if(!$select){$select="*";};
    print "<!-- select $select from $currtable $currordersql -->\n";
    $sth=$dbh->prepare("select $select from $currtable $currordersql");
    $sth->execute || &errexit($dbh->errstr);
    $fieldcnt=$sth->{NUM_OF_FIELDS};
    @fieldnames = @{$sth->{NAME}};
    $tabletop = '';
    $tabletop .= "<table border='1' width='100%'><tr>";
    $tabletop .= "<td bgcolor='#eeeeee' width='20'>$tds   $tde</td>";
    for ($fldlp=0;$fldlp<$fieldcnt;$fldlp++) {
      $tabletop .= "<td bgcolor='#eeeeee'>$tds <b><a href='dbed.pl?table=$currtable$filterarg$pagearg$exiturlarg";
      if (lc($currorder) eq lc($fieldnames[$fldlp])) {
        $tabletop .= "&order=";
        if ($orderdesc eq '') { $tabletop .= '-' }
        $tabletop .= &xe($fieldnames[$fldlp])."' title='Reverse sort order.'";
      } else {
        $tabletop .= "&order=$fieldnames[$fldlp]' title='Change sort field.'";
      }
      $tabletop .= ">".$fieldnames[$fldlp]."</a></b>$tde</td>\n";
    }
    $tabletop .= "<td bgcolor='#eeeeee' width='20'>$tds   $tde</td></tr>\n";
    $totalrecs = 0;
    $validrecs = 0;
    while (@browserec=$sth->fetchrow_array) {
      $totalrecs++;
      $currkey = "";
      $recfilter = '';
      $row_out = '';
      for ($fldlp=0;$fldlp<$fieldcnt;$fldlp++) {
        if (! $browserec[$fldlp]) { $browserec[$fldlp] = "" }
        $browserec[$fldlp] = &stripslashes($browserec[$fldlp]);
        if ($browserec[$fldlp]) { $recfilter .= ' '.lc($browserec[$fldlp]) }
        if ( ($COLS_KEYS{$currtable} eq '') or ($COLS_KEYS{$currtable} =~ /$fieldnames[$fldlp]/i) ) {
          $currkey_val = $browserec[$fldlp];
          if ($currkey ne '') { $currkey .= " and " }
          $currkey .= $fieldnames[$fldlp].'="'.$currkey_val.'"';
        }
        $row_out .= "<td valign='top'>$tds ".$browserec[$fldlp].$tde."</td>\n";
      }
      if ($recfilter ne '') { $recfilter .= ' ' }
      $validrec = 0;
      if ($currfilter eq '') {
        $validrec = 1
      } else {
        if ($filtermode eq 'o') { $validrec = 0 } else { $validrec = 1 }
        foreach $one_filter (@currfilters) {
          while ($one_filter =~ /\_/) { $one_filter =~ s/\_/ /g }
          if ($filtermode eq 'o') {
            if ($recfilter =~ /$one_filter/) {
              $validrec = 1;
              last;
            }
          } else {
            if ($recfilter !~ /$one_filter/) {
              $validrec = 0;
              last;
            }
          }
        }
      }
      if ($validrec > 0) {
        $currkey = &xe($currkey);
        $rec_row_out[$validrecs] = "<tr>\n";
        $rec_row_out[$validrecs] .= '<td valign="top">'.$tds.'<a href="dbed.pl?mode=e&key='.$currkey.
                                    '&table='.$currtable.$filterarg.$orderarg.$pagearg.$exiturlarg.'">edit</a>'.$tde."</td>\n";
        $rec_row_out[$validrecs] .= $row_out;
        $rec_row_out[$validrecs] .= '<td valign="top">'.$tds.'<a href="dbed.pl?mode='.$CONFIRM_DELETE.'&key='.$currkey.
                                    '&table='.$currtable.$filterarg.$orderarg.$pagearg.$exiturlarg.'">del</a>'.$tde."</td></tr>\n\n";
        $validrecs++;
      }
    }
    print '<table cellspacing=0 cellpadding=0 border=0><tr>';
    print '<td valign=bottom><font size=2 face=Arial color=black>';
    $start_rec = $currpage * $MAX_PAGE_RECORDS;
    $end_rec = $start_rec + ($MAX_PAGE_RECORDS - 1);
    if ($end_rec > ($validrecs - 1)) { $end_rec = $validrecs - 1 }
    if ($start_rec > $end_rec) { $start_rec = $end_rec - ($MAX_PAGE_RECORDS - 1) }
    if ($start_rec < 0) { $start_rec = 0 }
    if ($currfilter) {
      print $validrecs." out of ".$totalrecs." match filter."
    } else {
      print $totalrecs." total records."
    }
    if ($validrecs >= $MAX_PAGE_RECORDS) {
      print "    Showing records ".($start_rec+1)." through ".($end_rec+1).'.'
    }
    print "</td><td>      </td>";
    print '<form name="JUMPMENU" method=get align=top>';
    print '<td align=left valign=top><font face=Arial size=2 color=black>';
    print "Page: <select name="JUMPTO" onChange="if (document.JUMPMENU.JUMPTO.options[document.JUMPMENU.JUMPTO.selectedIndex].value) {
          window.location = 'dbed.pl?table=".$currtable.$filterarg.$orderarg.$exiturlarg."&page='+document.JUMPMENU.JUMPTO.options[document.JUMPMENU.JUMPTO.selectedIndex].value }">";
    for ($pgloop = 0; $pgloop <= ($totalrecs / $MAX_PAGE_RECORDS); $pgloop++ ) {
      print '<option value="'.$pgloop.'"';
      if ($pgloop == $currpage) { print ' selected' }
      print '>'.($pgloop+1).'</option>'
    }
    print '</select>';
    print '</td></form></tr></table>';
    if ($validrecs < 1) {
      print "<br>No records found.<br>"
    } else {
      print $tabletop;
      for ($rec_loop = $start_rec; $rec_loop <= $end_rec; $rec_loop++ ) {
        print $rec_row_out[$rec_loop];
      }
      print "</table>\n";
      print "<a name='bottom'></a><br><a href='#top' title='TOP'><font size=2>TOP</font></a><br>";
    }
    if($sth){$sth->finish;}
    $dbh->disconnect;
  }
  print &HTML_Foot();
  exit;
}

# Display form for edit record.
sub DoEdit {
  print &HTML_Head('Edit');
  if ( ($currtable) && ($currkey) ) {
    $dbh = DBI->connect("$DBINIT","$DBLOGIN","$DBPASSWORD");
    $select = $COLS_EDIT{$currtable};
    if(!$select){$select="*";};
    $sth = $dbh->prepare("SELECT ".$select." FROM ".$currtable);
    $sth->execute || &errexit($dbh->errstr);
    $fieldcnt=$sth->{NUM_OF_FIELDS};
    @fieldnames = @{$sth->{NAME}};
    @fieldtype = @{$sth->{TYPE}};
    @nullable = @{$sth->{NULLABLE}};
    $sth->finish;
    $sql = "SELECT ".$select." FROM ".$currtable." WHERE ".$currkey;
    $sth=$dbh->prepare($sql) || &errexit($dbh->errstr);
    $sth->execute || &errexit($dbh->errstr);
    @editrec=$sth->fetchrow_array;
    $currkey=&xe($currkey);
    print "<font face=Arial color=green size=3><b>Database Editor</b></font> - Edit ".$currtable." Record";
    $alt_links = '';
    foreach $alt_table (keys(%COLS_KEYS)) {
      if ($alt_table ne $currtable) {
        if ($COLS_KEYS{$alt_table} eq $COLS_KEYS{$currtable}) {
          $alt_links .= ' <a href="dbed.pl?mode=e&key='.$currkey.'&table='.$alt_table.$filterarg.$orderarg.$pagearg.$exiturlarg.
                        '" title="Switch to '.$alt_table.'">'.$alt_table.'</a>';
        }
      }
    }
    if ($alt_links) { print "<font size=1> Switch to:".$alt_links."</font>" }
    if ($exiturl) { print "<font size=1> <a href='".$exiturl."'>EXIT</a></font>" }
    print "\n";
    if ($sth->rows < 1) {
      &errexitraw("<br>No record found matching key.")
    }
    if ($sth->rows > 1) {
      &errexitraw("<br>More than one record found matching key.")
    }
    print qq(<form action="dbed.pl" method="post">
    <input type="hidden" name="mode" value="s">
    $filterfield
    $pagefield
    $orderfield
    $exiturlfield
    <input type="hidden" name="table" value="$currtable">
    <input type="hidden" name="key" value="$currkey">
    );
    print qq(
    <table border="1">
    <tr><td bgcolor="#ddddff" align=left>$tds<input type="Submit" name="ok" value="SAVE">$tde</td>
    <td bgcolor="#ddddff" align=right>$tds<a href="dbed.pl?mode=$CONFIRM_DELETE\&key=$currkey\&table=$currtable$filterarg$orderarg$pagearg$exiturlarg.
       " title="Delete this record.">DELETE</a>        <a href="dbed.pl?table=$currtable$filterarg$orderarg$pagearg$exiturlarg.
       " title="Return to browse.">CANCEL</a> $tde</td></tr>
    );
    for ($fldlp=0;$fldlp<$fieldcnt;$fldlp++) {
      if($nullable[$fldlp]){$nulla="null"}else{$nulla="not null"};
      print "<tr bgcolor='#eeeeee'><td align=right>$tds<b>";
      if (lc($fieldnames[$fldlp]) eq 'email') {
        print '<a href="mailto:'.$editrec[$fldlp].'">'.$fieldnames[$fldlp].'</a>';
      } else {
        print $fieldnames[$fldlp];
      }
      print "</b>$tde</td>\n";
      if (!$editrec[$fldlp]){$editrec[$fldlp]="";}
      $editrec[$fldlp] = &stripslashes($editrec[$fldlp]);
      if (($TYPES{$fieldtype[$fldlp]}=~/long/)||($TYPES{$fieldtype[$fldlp]}=~/bin/)&&(!$select)) {
        $editfield = '<textarea cols="70" rows="4" name="'.$fieldnames[$fldlp].'">'.$editrec[$fldlp]."</textarea>\n";
      } else {
        $editfield = '<input type="text" size="80" name="'.$fieldnames[$fldlp].'" value="'.&addquoteslashes($editrec[$fldlp]).'">'."\n";
      }
      print qq(<td align=left>$tds $editfield$tde</td></tr>\n);
    }
    print qq(
    <tr><td align=left bgcolor="#ddddff">$tds<input type="Submit" name="ok" value="SAVE">$tde</td>
    <td bgcolor="#ddddff" align=right>$tds<a href="dbed.pl?mode=$CONFIRM_DELETE\&key=$currkey\&table=$currtable$filterarg$orderarg$pagearg$exiturlarg.
       " title="Delete this record.">DELETE</a>        
       <a href="dbed.pl?table=$currtable$filterarg$orderarg$pagearg$exiturlarg" title="Return to browse."
       >CANCEL</a> $tde</td></tr>
    );
    print qq(
    </table>
    </form>
    );
    $sth->finish;
    $dbh->disconnect;
    print &HTML_Foot();
  } else {
    &errexit("Missing table or key.");
  }
  exit;
}

# Save updated record.
sub DoSave {
  print &HTML_Head('Browse');
  if ( ($currtable) && ($currkey) ) {
    $dbh = DBI->connect("$DBINIT","$DBLOGIN","$DBPASSWORD");
    $select = $COLS_EDIT{$currtable};
    if(!$select){$select="*";};
    $sth=$dbh->prepare("SELECT ".$select." FROM ".$currtable);
    $sth->execute || &errexit($dbh->errstr);
    @fieldnames = @{$sth->{NAME}};
    $sth->finish;
    $sql_save = "UPDATE ".$currtable." SET ";
    foreach $fieldname (@fieldnames) {
      if ($COLS_EDIT{$currtable} =~ /$fieldname/i) {
        $sql_save .= $fieldname."='".addslashes(&xd($param->param($fieldname)))."',";
      }
    }
    $sql_save = substr($sql_save,0,length($sql_save)-1);
    $sql_save .= " WHERE ".$currkey;
    # &errexit("Query: ".$sql_save);
    $sth=$dbh->do($sql_save) || &errexit($dbh->errstr);
    $dbh->disconnect;
    &DoBrowse();
  } else {
    &errexit("Missing table or key.");
  }
  exit;
}

# Display form for new record.
sub DoNew {
  print &HTML_Head('Add');
  if ($currtable) {
    $dbh = DBI->connect("$DBINIT","$DBLOGIN","$DBPASSWORD");
    $select = $COLS_EDIT{$currtable};
    if(!$select){$select="*";};
    $sth = $dbh->prepare("SELECT ".$select." FROM ".$currtable);
    $sth->execute || &errexit($dbh->errstr);
    $fieldcnt=$sth->{NUM_OF_FIELDS};
    @fieldnames = @{$sth->{NAME}};
    @fieldtype = @{$sth->{TYPE}};
    @nullable = @{$sth->{NULLABLE}};
    $sth->finish;
    print "<font face=Arial color=green size=3><b>Database Editor</b></font> - Add New Record<br>\n";
    print qq(<form action="dbed.pl" method="post">
    <input type="hidden" name="mode" value="a">
    $filterfield
    $pagefield
    $orderfield
    $exiturlfield
    <input type="hidden" name="table" value="$currtable">
    );
    print qq(
    <table border="1">
    <tr><td bgcolor="#ddddff" align=left>$tds<input type="Submit" name="ok" value="SAVE">$tde</td>
    <td bgcolor="#ddddff" align=right>$tds<a href="dbed.pl?table=$currtable$filterarg$orderarg$pagearg$exiturlarg.
          " title="Return to browse.">CANCEL</a> $tde</td></tr>
    );
    for ($fldlp=0;$fldlp<$fieldcnt;$fldlp++) {
      if($nullable[$fldlp]){$nulla="null"}else{$nulla="not null"};
      print qq(<tr bgcolor='#eeeeee'><td align=right>$tds<b> $fieldnames[$fldlp] </b>$tde</td>\n);
      if (($TYPES{$fieldtype[$fldlp]}=~/long/)||($TYPES{$fieldtype[$fldlp]}=~/bin/)&&(!$select)) {
        $editfield=qq(<textarea cols="70" rows="4" name="$fieldnames[$fldlp]"></textarea>\n);
      } else {
        $editfield=qq(<input type="text" size="80" name="$fieldnames[$fldlp]" value="">\n);
      }
      print qq(<td align=left>$tds $editfield$tde</td></tr>\n);
    }
    print qq(
    <tr><td align=left bgcolor="#ddddff">$tds<input type="Submit" name="ok" value="SAVE">$tde</td>
    <td align=right bgcolor="#ddddff">$tds<a href="dbed.pl?table=$currtable$filterarg$orderarg$pagearg$exiturlarg.
           " title="Return to browse.">CANCEL</a> $tde</td></tr>
    );
    print qq(
    </table>
    </form>
    );
    $sth->finish;
    $dbh->disconnect;
    print &HTML_Foot();
  } else {
    &errexit("Missing table.");
  }
  exit;
}

# Save new record.
sub DoAdd {
  print &HTML_Head('Browse');
  if ($currtable) {
    $dbh = DBI->connect("$DBINIT","$DBLOGIN","$DBPASSWORD");
    $select = $COLS_EDIT{$currtable};
    if(!$select){$select="*";};
    $sth=$dbh->prepare("SELECT ".$select." FROM ".$currtable);
    $sth->execute || &errexit($dbh->errstr);
    @fieldnames = @{$sth->{NAME}};
    @nullable = @{$sth->{NULLABLE}};
    $sth->finish;
    $sql_add = "INSERT INTO ".$currtable." SET ";
    foreach $fieldname (@fieldnames) {
      if ($COLS_EDIT{$currtable} =~ /$fieldname/i) {
        $sql_add .= $fieldname."='".addslashes(&xd($param->param($fieldname)))."',";
      }
    }
    $sql_add = substr($sql_add,0,length($sql_add)-1);
    $dbh->do($sql_add) || &errexit($dbh->errstr);
    $dbh->disconnect;
    &DoBrowse();
  } else {
    &errexit("Missing table.");
  }
}

# Confirm delete of specified record.
sub DoDeleteConfirm {
  print &HTML_Head('Delete');
  if ( ($currtable) && ($currkey) ) {
    print "<font face=Arial color=green size=3><b>Database Editor</b></font> - Confirm Delete<br>\n";
    print "<br><font size=3><b>Record key:</b> ".$currkey."<br><br></font>\n";
    $dbh = DBI->connect("$DBINIT","$DBLOGIN","$DBPASSWORD");
    print "<!-- SELECT ".$COLS_EDIT{$currtable}." FROM ".$currtable." WHERE ".$currkey." -->\n";
    $dbs = $dbh->prepare("SELECT ".$COLS_EDIT{$currtable}." FROM ".$currtable." WHERE ".$currkey) || &errexit($dbh->errstr);
    $dbs->execute;
    $db_err = $dbh->errstr; if ($db_err ne '') { &errexitraw($dbh->errstr) }
    @db_cols = $dbs->fetchrow_array;
    $db_err = $dbh->errstr; if ($db_err ne '') { &errexitraw($dbh->errstr) }
    if ($dbs->rows != 1) { &errexitraw("Key does not select a single record.") }
    $dbs->finish;
    $dbh->disconnect;
    $currkey = &xe($currkey);
    print "<a href="dbed.pl?mode=d&key=$currkey&table=$currtable$filterarg$orderarg$pagearg$exiturlarg">DELETE</a>    ";
    print "<a href="dbed.pl?table=$currtable$filterarg$orderarg$pagearg$exiturlarg" title="Return to browse.">CANCEL</a>\n";
    print &HTML_Foot();
    exit;
  } else {
    &errexit("Missing table or key.")
  }
}

# Delete specified record.
sub DoDelete {
  print &HTML_Head('Browse');
  if ($currkey && $currtable) {
    $dbh = DBI->connect("$DBINIT","$DBLOGIN","$DBPASSWORD");
    $dbs = $dbh->prepare("SELECT ".$COLS_EDIT{$currtable}." FROM ".$currtable." WHERE ".$currkey) || &errexit($dbh->errstr);
    $dbs->execute;
    $db_err = $dbh->errstr; if ($db_err ne '') { &errexitraw($dbh->errstr) }
    @db_cols = $dbs->fetchrow_array;
    $db_err = $dbh->errstr; if ($db_err ne '') { &errexitraw($dbh->errstr) }
    if ($dbs->rows != 1) { &errexitraw("Key does not select a single record.") }
    $dbs->finish;
    $dbh->do("DELETE FROM ".$currtable." WHERE ".$currkey) || &errexit($dbh->errstr);
    $dbh->disconnect;
  } else {
    &errexit("Missing table or key.")
  }
  &DoBrowse();
}

# Show login form and exit.
sub DoLogin {
  print "Content-type:text/html\n\n".'<html><head><title>dbed '.$LOCALMSG.
        ' - Login</title></head>
        <body bgcolor=white onLoad="document.loginform.password.focus()"><font face=Arial color=black size=2>'."\n";
  print "<font face=Arial color=green size=3><b>Database Editor</b></font><br>\n";
  print "<form action='dbed.pl' method='post' name='loginform'>\n";
  print "<input type=password name='password' size=20>\n";
  print $filterfield;
  print $pagefield;
  print $orderfield;
  print $exiturlfield;
  print "<input type=hidden name='mode' value='".$mode."'>\n";
  print "<input type=hidden name='table' value='".$currtable."'>\n";
  print "<input type=hidden name='key' value='".&xe($currkey)."'>\n";
  print "<input type=submit name='LOGIN' value='LOGIN'>\n";
  print "</form>\n";
  print &HTML_Foot();
  exit;
}

# Show error message and exit.
sub errexitraw {
  $errmsg = $_[0];
  print "<font size=3 color=red><br>Error:</font> <b>$errmsg</b><br><br>\n";
  print "<a href='javascript:history.back();'><<< BACK</a>";
  print "<br><br><a href='dbed.pl?table=".$currtable.$filterarg.$orderarg.$pagearg.$exiturlarg.
        "' title='Return to browse.'>CANCEL</a><br>\n";
  if ($exiturl) { print "<br><a href='".$exiturl."'>EXIT</a><br>" }
  print &HTML_Foot();
  exit;
}

sub errexit {
  $errmsg = $_[0];
  print "<font face=Arial color=green size=3><b>Database Editor</b></font><br>\n";
  &errexitraw($errmsg);
}

########################################
# SUPPORTING ROUTINES

# Convert troublesome characters to HEX for passing and storing.
sub xe {  # heX Encode
  $safe_str= $_[0];
  $safe_out = '';
  for ($safe_lp = 0; $safe_lp < length($safe_str); $safe_lp++ ) {
    $safe_char = substr($safe_str,$safe_lp,1);
    if ( ($safe_char lt ' ') or ($safe_char gt 'z') or
         ($safe_char eq '%') or ($safe_char eq '~') or
         ($safe_char eq '+') or
         ($safe_char eq "'") or ($safe_char eq '"') or
         ($safe_char eq '<') or ($safe_char eq '>') or
         ($safe_char eq '&') or ($safe_char eq '#') or
         ($safe_char eq '') or ($safe_char eq '/') or
         ($safe_char eq '?') or ($safe_char eq '@') ) {
      $safe_char = '%'.sprintf("x", ord($safe_char)) }
    $safe_out .= $safe_char;
  }
  return $safe_out;
}

# Convert trouble some characters from hex back to string.
sub xd {  # heX Decode
  $safe_str = $_[0];
  $safe_str =~ s/%(..)/pack("c",hex($1))/ge;
  $safe_str =~ s/\r//g;
  return $safe_str;
}

sub stripslashes {
  $arg_in = $_[0];
  $arg_in =~ s/'/'/g;
  $arg_in =~ s/"/"/g;
  return($arg_in);
}

sub addslashes {
  $arg_in = $_[0];
  $arg_in =~ s/'/'/g;
  $arg_in =~ s/"/"/g;
  $arg_in =~ s/'/'/g;
  $arg_in =~ s/"/"/g;
  return($arg_in);
}

sub addquoteslashes {
  $arg_in = $_[0];
  $arg_in =~ s/"/"/g;
  $arg_in =~ s/"/"/g;
  return($arg_in);
}

sub HTML_Head {
  $head_title = $_[0];
  $set_cookie = "";
  if ($password ne '') {
    $set_cookie .= "<SCRIPT LANGUAGE=JAVASCRIPT TYPE="TEXT/JAVASCRIPT">\n";
    $set_cookie .= "\n";
    $set_cookie .= "<!-- Hide from Old Browsers\n";
    $x_time = time + (12 * 60 * 60);
    @DayNames = ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday');
    @MonthNames = ('January', 'February', 'March', 'April', 'May', 'June',
                   'July', 'August', 'September', 'October', 'November', 'December');
    ($x_Sec, $x_Min, $x_Hr, $x_Da, $x_Mo, $x_Yr, $x_DOW) = localtime($x_time);
    $x_Mo++;  $x_Yr += 1900;
    $x_date = sprintf("%s, d-%s-d d:d:d CST",
                    substr($DayNames[$x_DOW],0,3),
                    $x_Da, substr($MonthNames[$x_Mo-1],0,3), $x_Yr,
                    $x_Hr, $x_Min, $x_Sec);
    $cookie_args = "; path=/; expires=".$x_date;
    $set_cookie .= "document.cookie = "dbedpassword=".$password.$cookie_args.""\n";
    $set_cookie .= " // End Hiding Script -->\n";
    $set_cookie .= "</SCRIPT>\n";
  }
  return( "Content-type:text/html\n\n"."<html><head><title>dbed ".$LOCALMSG.
          " - ".$head_title."</title>\n".$set_cookie."</head>
         <body bgcolor=white><font face=Arial color=black size=2>\n" );
}

sub HTML_Foot {
  return( "</font></body></html>\n" );
}

sub GetPasswordCookie {
  if ($password eq '') {
    @cookies = split(/;/, $ENV{'HTTP_COOKIE'});
    foreach $cookie_pair (@cookies) {
      ($cookie_name, $cookie_value) = split(/=/, $cookie_pair);
      $cookie_name =~ s/ //g;
      if ($cookie_name eq 'dbedpassword') {
        $password = $cookie_value;
      }
    }
  }
}




 DOWNLOAD         < NEWER       OLDER >