#!/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, -ex
+pires=>'+3M');
my $cookie2 = $q->cookie(-name=>'Host', -value=>$host, -expires=>'
++3M');
my $cookie3 = $q->cookie(-name=>'Driver', -value=>$driver, -expire
+s=>'+3M');
my $cookie4 = $q->cookie(-name=>'Username', -value=>$username, -ex
+pires=>'+3M');
my $cookie5 = $q->cookie(-name=>'Password', -value=>$password, -ex
+pires=>'+3M');
print $q->header(-cookie=>[$cookie1,$cookie2,$cookie3,$cookie4,$co
+okie5]);
print &header("Connected");
print "<ul><li><a href=\"$scriptname?_s=tables\">Show Tables</a></
+li>\n
<li><a href=\"$scriptname?_s=createform\">Create Tables</a></li>\n
<li><a href=\"$scriptname?_s=commandline\">Command Line</a></li>\n
<li><a href=\"$scriptname?_s=logout\">Log Out</a></li></ul>";
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 .= "<tr><th colspan=3>$key</th></tr>";
}
$htmlout .= "<tr><td><a href=\"$scriptname?_s=showtable&table=
+$results{$key}\">$results{$key}</a></td>
<td><a href=\"$scriptname?_s=edit&table=$results{$key}\">edit<
+/a></td>
<td><a href=\"$scriptname?_s=tables&table=$results{$key}&actio
+n=drop\">drop</a></td></tr>";
}
$sth->finish;
print $q->header();
print &header("Show Tables");
print "<table border=1>$htmlout</table>";
print "<p><a href=\"$scriptname?_s=connect\">Home</a></p>";
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 = "<tr><th>Field</th><th>Type</th><th>Default</th><th>
+Null</th><th>Key</th><th>Extra</th></tr>\n";
while (my $results = $sth->fetchrow_hashref) {
my %results = %$results;
$htmlout .= "<tr><td>$results{'Field'} </td><td>$results{
+'Type'} </td>
<td>$results{'Default'} </td><td>$results{'Null'} </
+td>
<td>$results{'Key'} </td><td>$results{'Extra'} </td>
+</tr>\n";
}
$sth->finish;
print $q->header();
print &header("$table");
print "<table border=1>$htmlout</table>";
print "<a href=\"$scriptname?_s=showvalues&table=$table\">See the
+Values</a>";
print "<p><a href=\"$scriptname?_s=connect\">Home</a></p>";
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 = "<tr>";
foreach my $key (keys %results) {
$htmlout .= "<th>$key</th>";
}
$htmlout .= "</tr>\n";
}
$htmlout .= "<tr>";
foreach my $key (keys %results) {
$htmlout .= "<td>$results{$key} </td>";
}
$htmlout .= "</tr>\n";
}
$sth->finish;
print $q->header();
print &header("Values");
print "<table border=1>$htmlout</table>";
print "<p><a href=\"$scriptname?_s=connect\">Home</a></p>";
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=>'n
+ow');
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,$co
+okie5]);
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->pa
+ram('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->pa
+ram('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('grcho
+ice') eq "GRANT");
$sqlStr .= "FROM " . $q->param('gusername') if ($q->param('grc
+hoice') eq "REVOKE");
$sqlStr .= " IDENTIFIED BY '" . $q->param('gpassword') . "'" i
+f ($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 = "<tr><th>Field</th><th>Type/Size</th><th>Default</th
+><th>Allow Null</th><th>Key</th><th>Extra</th><th> </th><th>&nbs
+p;</th></tr>\n";
while (my $results = $sth->fetchrow_hashref) {
my %results = %$results;
my $notnull = "checked" if ($results{'Null'} eq "YES");
$htmlout .= "<form action=\"$scriptname\" method=\"POST\"><tr>
+<td><input type=text name=name value=\"$results{'Field'}\" size=15>&n
+bsp;</td>
<td><input type=text name=type value=\"$results{'Type'}\" size
+=15> </td>
<td><input type=text name=default value=\"$results{'Default'}\
+" size=10> </td>
<td><input type=checkbox name=notnull value=yes $notnull> 
+;</td>
<td>$results{'Key'} </td><td>$results{'Extra'} </td>
<td><a href=\"$scriptname?_s=edit&table=$table&field=$results{
+'Field'}&action=drop\">drop</a></td>
<td><input type=submit name=action value=modify><input type=hi
+dden name=table value=$table>
<input type=hidden name=field value=\"$results{'Field'}\"><inp
+ut type=hidden name=\"_s\" value=edit></td></tr></form>\n";
}
$htmlout .= "<form action=\"$scriptname\" method=\"POST\"><tr><td>
+<input type=text name=name size=15></td><td><input type=text name=typ
+e size=15></td>
<td><input type=text name=default size=10></td><td><input type=che
+ckbox name=notnull value=yes></td>
<td> </td><td> </td><td> </td>
<td><input type=submit name=action value=add><input type=hidden na
+me=table value=$table><input type=hidden name=\"_s\" value=edit></td>
+</tr></form>";
$htmlout = "<table border=1>$htmlout</table>";
$htmlout .= "<br><b>Grant/Revoke Privileges:</b><br><form action=\
+"$scriptname\" method=\"POST\"><table border=1>
<tr><td colspan=3 align=center><select name=\"grchoice\"><option v
+alue=\"GRANT\">GRANT</option><option value=\"REVOKE\">REVOKE</option>
+</select></td></tr>\n
<tr><td colspan=2>ALL</td><td><input type=\"checkbox\" name=\"all\
+" value=\"Yes\"></td></tr><tr>\n
<tr><td colspan=2>SELECT</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"SELECT\"></td></tr>\n
<tr><td colspan=2>INSERT</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"INSERT\"></td></tr>\n
<tr><td colspan=2>UPDATE</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"UPDATE\"></td></tr>\n
<tr><td colspan=2>DELETE</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"DELETE\"></td></tr>\n
<tr><td colspan=2>CREATE</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"CREATE\"></td></tr>\n
<tr><td colspan=2>DROP</td><td><input type=\"checkbox\" name=\"pri
+vileges\" value=\"DROP\"></td></tr>\n
<tr><td colspan=2>ALTER</td><td><input type=\"checkbox\" name=\"pr
+ivileges\" value=\"ALTER\"></td></tr>\n
<tr><td colspan=2>INDEX</td><td><input type=\"checkbox\" name=\"pr
+ivileges\" value=\"INDEX\"></td></tr>\n
<tr><td colspan=2>GRANT</td><td><input type=\"checkbox\" name=\"gr
+ant\" value=\"Yes\"></td></tr>\n
<tr><td>User\@Host:</td><td colspan=2><input type=\"text\" name=\"
+gusername\" size=10></td></tr>\n
<tr><td>Password:</td><td colspan=2><input type=\"password\" name=
+\"gpassword\" size=10></td></tr>\n
<tr><td colspan=3 align=center><input type=\"submit\" name=\"actio
+n\" value=\"Privilegize\"></td></tr>\n
</table><input type=hidden name=\"_s\" value=edit><input type=hidd
+en name=\"table\" value=\"$table\"></form>";
$sth->finish;
print $q->header();
print &header("$table");
print $adderr;
print "$htmlout";
print "<a href=\"$scriptname?_s=showvalues&table=$table\">See the
+Values</a>";
print "<p><a href=\"$scriptname?_s=connect\">Home</a></p>";
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->pa
+ram("fieldtype$param");
$sqlStr .= " DEFAULT '" . $q->param("fielddefault$para
+m") . "'" if $q->param("fielddefault$param");
$sqlStr .= " NOT NULL" unless ($q->param("allownull$pa
+ram") eq "Yes");
$sqlStr .= " " . $q->param("extra$param") if $q->param
+("extra$param");
$primary .= ", " if $primary && $q->param("primary$par
+am");
$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", $usernam
+e, $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 = "<form action=\"$scriptname\" method=\"POST\">\n";
my $end = $start + 5;
my @params = $q->param();
if ($q->param('_action') eq "Add More Fields") {
foreach my $param (@params) {
if ($param !~ /^_/ && $q->param($param)) {
$htmlout .= "<input type=\"hidden\" name=\"$param\
+" value=\"" . $q->param($param) . "\">\n";
}
}
} else {
$htmlout .= "Table Name:<input type=\"text\" name=\"tablen
+ame\">";
}
$htmlout .= "<table border=1><tr><th>Field</th><th>Type/Size</
+th><th>Default</th><th>Allow Null</th><th>Key</th><th>Extra</th></tr>
+\n";
for (my $i=$start; $i<$end; $i++) {
$htmlout .= "<tr><td><input type=\"text\" name=\"fieldname
+$i\"></td>
<td><input type=\"text\" name=\"fieldtype$i\"></td>
<td><input type=\"text\" name=\"fielddefault$i\"></td>
<td><input type=\"checkbox\" name=\"allownull$i\" value=\"
+Yes\"></td>
<td><input type=\"checkbox\" name=\"primary$i\" value=\"Ye
+s\"></td>
<td><input type=\"text\" name=\"extra$i\"></td></tr>\n";
}
$htmlout .= "</table><input type=\"hidden\" name=\"_start\" va
+lue=\"$end\">
<input type=\"hidden\" name=\"_s\" value=\"createform\">
<input type=\"submit\" name=\"_action\" value=\"Add More Field
+s\">
<input type=\"submit\" name=\"_action\" value=\"Finished\"></f
+orm>\n";
}
print $q->header();
print &header("Create Table");
print $htmlout;
print "<p><a href=\"dbman.mpl?_s=connect\">Home</a></p>";
print &footer;
} elsif ($s eq "commandline") {
my $htmlout;
my $err;
if ($q->param('action') eq "Submit") {
my $dbh = DBI->connect("DBI:$driver:$database:$host", $usernam
+e, $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 = "<tr>";
foreach my $key (keys %results) {
$htmlout .= "<th>$key</th>";
}
$htmlout .= "</tr>\n";
}
$htmlout .= "<tr>";
foreach my $key (keys %results) {
$htmlout .= "<td>$results{$key} </td>";
}
$htmlout .= "</tr>\n";
}
$sth->finish;
$htmlout = "<table border=1>$htmlout</table>";
} 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<br>";
} elsif ($htmlout) {
print "$htmlout<br>";
}
print <<HTMLOUT;
<form action="$scriptname" method="POST">
<input type="hidden" name="_s" value="commandline">
<input type="text" name="query" size="40">
<input type="submit" name="action" value="Submit">
</form>
HTMLOUT
print "<p><a href=\"dbman.mpl?_s=connect\">Home</a></p>";
print &footer;
} else {
print $q->header();
print &header("Login");
print <<HTMLOUT;
<form action="$scriptname" method="POST">
<input type="hidden" name="_s" value="connect">
<table>
<tr><td align="right">Database:</td>
<td><input type="text" name="Database" value="$database"><br></t
+d></tr>
<tr><td align="right">Host:</td>
<td><input type="text" name="Host" value="$host"><br></td></tr>
<tr><td align="right">Driver:</td>
<td><input type="text" name="Driver" value="$driver"><br></td></
+tr>
<tr><td align="right">Username:</td>
<td><input type="text" name="Username" value="$username"><br></t
+d></tr>
<tr><td align="right">Password:</td>
<td><input type="password" name="Password" value="$password"><br
+></td></tr>
<tr><td colspan="2" align="center"><input type="submit" name="ac
+tion" value="Connect">
<input type="reset" value="Reset"></td></tr>
</table>
</form>
HTMLOUT
print &footer;
}
sub header {
my $output = <<HTMLOUT;
<html><head><title>$_[0]</title></head>
<body>
<h1>DB Manager</h1>
HTMLOUT
return $output;
}
sub footer {
my $output = <<HTMLOUT;
<p align="right"><a href="http://benk.hypermart.net/kaos">thabenks
+ta</a></p>
</body></html>
HTMLOUT
return $output;
}
sub ErrorOut {
print $q->header();
print "<h1>Error:</h1><p>$_[0]</p>";
exit;
}
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.