#!/fellow/monks.pl

Here's a quick dirty little CGI script I wipped up that I use to administer my mySQL server. Of course, you will have to know your SQL statements on this one (I mean, it's a quick and dirty ;-)

You need DBI and mysqlPP installed. 

#!/usr/bin/perl

 # # # # # # # # # # # # # # # # # #
 # Phil's mySQL/CGI front end      #
 # Version 1.0                     #
 # http://phillipmassyn.tripod.com #
 # # # # # # # # # # # # # # # # # #

use DBI;

print "Content-type: text/html\n\n";

$title = "Phil's mySQL/CGI frontend";
$version = "Version 1.0";

print "<html><head><title>$title $version</title></head>\n<h1>$title</
+h1>\n<i>$version</i><hr>";

%FORM = &unweb;

$func = $FORM{func};

$footer = "<hr></html>\n";

if($func eq "")
{
    print <<LOGIN;
<form method=post>
<table border=1>
<tr>
    <th>Server</th>
    <td><input type="text" name="server"></td>
</tr>
<tr>
    <th>Port</th>
    <td><input type="text" name="port" value="3306"></td>
</tr>
<tr>
    <th>Database</th>
    <td><input type="text" name="db"></td>
</tr>
<tr>
    <th>Username</th>
    <td><input type="text" name="user"></td>
</tr>
<tr>
    <th>Password</th>
    <td><input type="password" name="password"></td>
</tr>
<tr>
    <th>SQL</th>
    <td><textarea cols="30" rows="10" name="sql"></textarea></td>
</tr>
<tr>
    <th></th>
    <td><input type="Submit" value="Login"></td>
</tr>
<input type="hidden" value="login" name="func">

</table>
</form>
LOGIN
;
}

if($func eq "login")
{
    $sql = $FORM{sql};
    $user = $FORM{user};
    $server = $FORM{server};
    $password = $FORM{password};
    $db = $FORM{db};
    $port = $FORM{port};

        print <<ADDIT
<h3>SQL</h3>
<form method=post>
<table border=1>
<input type="hidden" name="server" value="$server">
<input type="hidden" name="port" value="$port">
<input type="hidden" name="db" value="$db">
<input type="hidden" name="user" value="$user">
<input type="hidden" name="password" value="$password">
<tr>
    <th>SQL</th>
        <td><textarea cols="30" rows="10" name="sql">$sql</textarea></
+td>
</tr>
<tr>
        <th></th>
        <td><input type="Submit" value="Execute"></td>
</tr>
<input type="hidden" value="login" name="func">

</table>
</form>
ADDIT
;    

    if($sql ne "")
    {
        $dbh = DBI->connect("DBI:mysqlPP:database=$db;host=$server;por
+t=$port", $user, $password) or &error("DB Error: <b>" . $DBI::errstr 
+. "</b>");


        $sth = $dbh->prepare($sql) || &error("DB Error: \"" . $dbh->er
+rstr . "\" while preparing SQL statement \"$sql\"","");
        $sth->execute()  || &error("DB Error: <b>" . $dbh->errstr . "<
+/b> while preparing SQL statement \"<b>$sql</b>\"","");

        print "<h3>Output</h3>\n";

        print "<table border=1>\n";
        while (@ary = $sth->fetchrow_array())
        {
            print "<tr>\n";
            foreach $field (@ary)
            {
                chomp($field);
                print "   <td>$field</td>\n";
            }
            print "</tr>\n";
        }
        print "</table>";


        $sth->finish();
        $dbh->disconnect();
    }
}


print $footer;

sub error
{
    $txt = $_[0];

    print "<center>\n";
    print "<table border=1>\n";
    print "<tr><td><center><font color=\"#FF0000\"><h3>ERROR</h3></fon
+t></center></td></tr>\n";
    print "<tr><td>ERROR: $txt</td></tr>";
    print "</table>\n";
    print "</center>\n";
    print "$footer";
    exit(0);
}
















sub unweb
{
        local $buffer;
        local @pairs;
        local $pair;
        local $name;
        local $value;

        if($ENV{QUERY_STRING} eq "")
        {
                read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
        }
        else
        {
                #$buffer = $ENV{QUERY_STRING};
        &error("Sorry -- the GET method is not supported.  It is a sec
+urity risk.");
        }
        @pairs = split(/&/,  $buffer);
        foreach $pair (@pairs)
        {
                ($name, $value) = split(/=/, $pair);
                $value =~ tr/+/ /;
                $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1
+))/eg;
                $value =~ s/~!/ ~!/g;
                $MWSFORM{$name} = $value;
        }

        return %MWSFORM;
}

[download]

Thanks!

#!/massyn.pl The early worm gets caught by the bird.

In reply to CGI frontend for mySQL by Massyn

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!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • 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, details, 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, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            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.