Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
#!/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'}&nbsp;</td><td>$results{ +'Type'}&nbsp;</td> <td>$results{'Default'}&nbsp;</td><td>$results{'Null'}&nbsp;</ +td> <td>$results{'Key'}&nbsp;</td><td>$results{'Extra'}&nbsp;</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}&nbsp;</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>&nbsp;</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>&nbsp;</td> <td><input type=text name=default value=\"$results{'Default'}\ +" size=10>&nbsp;</td> <td><input type=checkbox name=notnull value=yes $notnull>&nbsp +;</td> <td>$results{'Key'}&nbsp;</td><td>$results{'Extra'}&nbsp;</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>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</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}&nbsp;</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; }

In reply to DB Mangager by thabenksta

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others examining the Monastery: (9)
    As of 2014-11-27 00:19 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      My preferred Perl binaries come from:














      Results (177 votes), past polls