#!/usr/local/bin/perl
### Database Manager v1.0 ###
### Author: Ben Kittrell ###
### Created: 03/26/2001 ###
use strict;
use CGI;
use DBI;
my $q = CGI::new();
my $scriptname = $ENV{SCRIPT_NAME};
$scriptname =~ s/.*\/([^\/]*)$/$1/;
my $s = $q->param('_s');
my $database = $q->cookie('Database');
my $host = $q->cookie('Host');
my $driver = $q->cookie('Driver');
my $username = $q->cookie('Username');
my $password = $q->cookie('Password');
if ($s eq "connect") {
if ($q->param('action') eq "Connect") {
$database = $q->param('Database');
$host = $q->param('Host');
$driver = $q->param('Driver');
$username = $q->param('Username');
$password = $q->param('Password');
}
my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $password) or ErrorOut($DBI::errstr);
$dbh->disconnect;
my $cookie1 = $q->cookie(-name=>'Database', -value=>$database, -expires=>'+3M');
my $cookie2 = $q->cookie(-name=>'Host', -value=>$host, -expires=>'+3M');
my $cookie3 = $q->cookie(-name=>'Driver', -value=>$driver, -expires=>'+3M');
my $cookie4 = $q->cookie(-name=>'Username', -value=>$username, -expires=>'+3M');
my $cookie5 = $q->cookie(-name=>'Password', -value=>$password, -expires=>'+3M');
print $q->header(-cookie=>[$cookie1,$cookie2,$cookie3,$cookie4,$cookie5]);
print &header("Connected");
print "
";
print &footer;
} elsif ($s eq "tables") {
my $htmlout;
my $table = $q->param('table');
my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $password) or ErrorOut($DBI::errstr);
if ($q->param('action') eq "drop") {
$dbh->do("DROP TABLE $table");
}
my $sth = $dbh->prepare("SHOW TABLES");
$sth->execute();
while (my $results = $sth->fetchrow_hashref) {
my $key = join '', keys %$results;
my %results = %$results;
if (!$htmlout) {
$htmlout .= "$key |
";
}
$htmlout .= "$results{$key} |
edit |
drop |
";
}
$sth->finish;
print $q->header();
print &header("Show Tables");
print "";
print "Home
";
print &footer;;
$dbh->disconnect;
} elsif ($s eq "showtable") {
my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $password) or die "Connect Error: $DBI::errstr";
my $table = $q->param('table');
my $sth = $dbh->prepare("SHOW FIELDS FROM $table");
$sth->execute();
my $htmlout = "Field | Type | Default | Null | Key | Extra |
\n";
while (my $results = $sth->fetchrow_hashref) {
my %results = %$results;
$htmlout .= "$results{'Field'} | $results{'Type'} |
$results{'Default'} | $results{'Null'} |
$results{'Key'} | $results{'Extra'} |
\n";
}
$sth->finish;
print $q->header();
print &header("$table");
print "";
print "See the Values";
print "Home
";
print &footer;
$dbh->disconnect
} elsif ($s eq "showvalues") {
my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $password) or die "Connect Error: $DBI::errstr";
my $htmlout;
my $table = $q->param('table');
my $sth = $dbh->prepare("SELECT * FROM $table");
$sth->execute();
while (my $results = $sth->fetchrow_hashref) {
my %results = %$results;
if (!$htmlout) {
$htmlout = "";
foreach my $key (keys %results) {
$htmlout .= "$key | ";
}
$htmlout .= "
\n";
}
$htmlout .= "";
foreach my $key (keys %results) {
$htmlout .= "$results{$key} | ";
}
$htmlout .= "
\n";
}
$sth->finish;
print $q->header();
print &header("Values");
print "";
print "Home
";
print &footer;
$dbh->disconnect
} elsif ($s eq "logout") {
my $cookie1 = $q->cookie(-name=>'Database', -value=>'', -expires=>'now');
my $cookie2 = $q->cookie(-name=>'Host', -value=>'', -expires=>'now');
my $cookie3 = $q->cookie(-name=>'Driver', -value=>'', -expires=>'now');
my $cookie4 = $q->cookie(-name=>'Username', -value=>'', -expires=>'now');
my $cookie5 = $q->cookie(-name=>'Password', -value=>'', -expires=>'now');
print $q->header(-cookie=>[$cookie1,$cookie2,$cookie3,$cookie4,$cookie5]);
print &header("Log out");
print "Logged out";
print &footer;
} elsif ($s eq "edit") {
my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $password) or die "Connect Error: $DBI::errstr";
my $table = $q->param('table');
my $field = $q->param('field');
if ($q->param('action') eq "drop") {
$dbh->do("ALTER TABLE $table DROP $field");
}
my $adderr;
if ($q->param('action') eq "add") {
my $sqlstr = "ALTER TABLE $table ADD " . $q->param('name') . " " . $q->param('type');
$sqlstr .= " DEFAULT '" . $q->param('default') . "'" if $q->param('default');
$sqlstr .= " not null" unless ($q->param('notnull') eq "yes");
$dbh->do($sqlstr);
$adderr = $dbh->errstr;
}
if ($q->param('action') eq "modify") {
my $sqlstr = "ALTER TABLE $table CHANGE " . $q->param('field') . " " . $q->param('name') . " " . $q->param('type');
$sqlstr .= " DEFAULT '" . $q->param('default') . "'" if $q->param('default');
$sqlstr .= " not null" unless ($q->param('notnull') eq "yes");
$dbh->do($sqlstr);
$adderr = $dbh->errstr;
}
if ($q->param('action') eq "Privilegize") {
my $privs = join ", ", $q->param('privileges');
my $sqlStr = $q->param('grchoice');
$sqlStr .= " ALL PRIVILEGES" if $q->param('all');
$sqlStr .= " $privs" if $privs && !$q->param('all');
$sqlStr .= ", " if ($q->param('grant') && ($privs || $q->param('all')) && ($q->param('grchoice') eq "REVOKE"));
$sqlStr .= " GRANT OPTION" if ($q->param('grant') && $q->param('grchoice') eq "REVOKE");
$sqlStr .= " ON $table ";
$sqlStr .= "TO " . $q->param('gusername') if ($q->param('grchoice') eq "GRANT");
$sqlStr .= "FROM " . $q->param('gusername') if ($q->param('grchoice') eq "REVOKE");
$sqlStr .= " IDENTIFIED BY '" . $q->param('gpassword') . "'" if ($q->param('gpassword') && $q->param('grchoice') eq "GRANT");
$sqlStr .= " WITH GRANT OPTION" if ($q->param('grant') && $q->param('grchoice') eq "GRANT");
$dbh->do($sqlStr);
$adderr = $dbh->errstr;
}
my $sth = $dbh->prepare("SHOW FIELDS FROM $table");
$sth->execute();
my $htmlout = "Field | Type/Size | Default | Allow Null | Key | Extra | | |
\n";
while (my $results = $sth->fetchrow_hashref) {
my %results = %$results;
my $notnull = "checked" if ($results{'Null'} eq "YES");
$htmlout .= "\n";
}
$htmlout .= "";
$htmlout = "";
$htmlout .= "
Grant/Revoke Privileges:
";
$sth->finish;
print $q->header();
print &header("$table");
print $adderr;
print "$htmlout";
print "See the Values";
print "Home
";
print &footer;
$dbh->disconnect
} elsif ($s eq "createform") {
my $htmlout;
if ($q->param('_action') eq "Finished") {
my $tablename = $q->param('tablename');
my $sqlStr;
my $primary;
my @params = $q->param();
foreach my $param (@params) {
if ($q->param($param) && $param =~ s/^fieldname(\d*)$/$1/) {
$sqlStr .= ", " if $sqlStr;
$sqlStr .= $q->param("fieldname$param") . " " . $q->param("fieldtype$param");
$sqlStr .= " DEFAULT '" . $q->param("fielddefault$param") . "'" if $q->param("fielddefault$param");
$sqlStr .= " NOT NULL" unless ($q->param("allownull$param") eq "Yes");
$sqlStr .= " " . $q->param("extra$param") if $q->param("extra$param");
$primary .= ", " if $primary && $q->param("primary$param");
$primary .= $q->param("fieldname$param") if $q->param("primary$param");
}
}
$primary = ", PRIMARY KEY($primary)" if $primary;
my $queryStr = "CREATE TABLE $tablename ($sqlStr$primary)";
my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $password) or die "Connect Error: $DBI::errstr";
$dbh->do($queryStr);
my $createerr = $dbh->errstr;
$dbh->disconnect;
if ($createerr) {
$htmlout = "$createerr\n$queryStr";
} else {
$htmlout = "Table Created";
}
} else {
my $start = $q->param('_start') || 1;
$htmlout = "\n";
}
print $q->header();
print &header("Create Table");
print $htmlout;
print "Home
";
print &footer;
} elsif ($s eq "commandline") {
my $htmlout;
my $err;
if ($q->param('action') eq "Submit") {
my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $password) or die "Connect Error: $DBI::errstr";
if ($q->param('query') =~ /^SELECT/i) {
my $sth = $dbh->prepare($q->param('query'));
$sth->execute();
$err = $sth->errstr;
while (my $results = $sth->fetchrow_hashref) {
my %results = %$results;
if (!$htmlout) {
$htmlout = "";
foreach my $key (keys %results) {
$htmlout .= "$key | ";
}
$htmlout .= "
\n";
}
$htmlout .= "";
foreach my $key (keys %results) {
$htmlout .= "$results{$key} | ";
}
$htmlout .= "
\n";
}
$sth->finish;
$htmlout = "";
} else {
my $sth = $dbh->prepare($q->param('query'));
$sth->execute();
$err = $sth->errstr;
$htmlout = $sth->rows . " rows affected";
$sth->finish;
}
$dbh->disconnect;
}
print $q->header();
print &header("Command Line");
if ($err) {
print "$err
";
} elsif ($htmlout) {
print "$htmlout
";
}
print <
HTMLOUT
print "Home
";
print &footer;
} else {
print $q->header();
print &header("Login");
print <
HTMLOUT
print &footer;
}
sub header {
my $output = <$_[0]
DB Manager
HTMLOUT
return $output;
}
sub footer {
my $output = <thabenksta