#!/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.

Replies are listed 'Best First'.
Re: CGI frontend for mySQL
by PodMaster (Abbot) on Feb 28, 2003 at 11:29 UTC
[reply]
[d/l]
Re: CGI frontend for mySQL
by Ryszard (Priest) on Feb 28, 2003 at 14:02 UTC
    I can see how this would be useful, however, as you pointed out, its rough, and not production quality IMH(umble)O.

    I can also see a reason why not to use CGI.pm, (a very small script, and you can take everything out of %ENV). Horses for courses, whatever works for you, right?

    To productionise it, i would start with actually using CGI.pm, and a templating solution. The downside (or, "On the other hand"), this introduces complexity, which is in contradiction to the simplicity shown above.

    As i said, whatever works in your situation.. as long as you're aware of the pro's and cons.

[reply]
Re: (nrd) CGI frontend for mySQL
by newrisedesigns (Curate) on Feb 28, 2003 at 16:56 UTC

    This might be just my paranoia, but if I were you, I wouldn't let anyone but you have access to it.

    use DBI;

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

if($ENV{REMOTE_ADDR} ne '127.0.0.1'){ #your IP
       exit;
}

    [download]

    Of course, if you don't have a static IP, you can't do this.

    John J Reiser
    newrisedesigns.com

[reply]
[d/l]
      Of course, if you don't have a static IP, you can't do this.

      You can, it's just more involved. First, you have to signup for dynamic DNS service (e.g., with dyndns), then set up your system to automatically update that whenever your IP changes. Then the script has to resolve your dynamic DNS into its current IP and compare that against $ENV{REMOTE_ADDR}. This opens up in theory a window for someone who has your IP after your connection dies and is terminated and before you reconnect and update your dynamic DNS record. In practice, if you don't have a static IP that probably means you get your IP via DHCP from your ISP, so that a potential attacker would not only have to use your same ISP but also would not have any way to arrange to have your IP right after you disconnect; the most he could do (without 0wning your ISP's DHCP server at least) would be to monitor your dynamic DNS address via ping to know when you disconnect, and immediately redial just hoping to get the IP you just released. After enough tries he might get it, in theory. But that's less risk than an easily-guessed password. 

      for(unpack("C*",'GGGG?GGGG?O__\?WccW?{GCw?Wcc{?Wcc~?Wcc{?~cc'
.'W?')){$j=$_-63;++$a;for$p(0..7){$h[$p][$a]=$j%2;$j/=2}}for$
p(0..7){for$a(1..45){$_=($h[$p-1][$a])?'#':' ';print}print$/}

      [download]
[reply]
[d/l]
[select]
Re: CGI frontend for mySQL
by Anonymous Monk on Mar 01, 2003 at 00:01 UTC

    What about using strict, warnings, taint ?

    (Not to mention CGI)

    I wouldn't call this code "cool" at all.

[reply]

Back to Cool Uses for Perl