I just thought I would add some of my code to the orgy of information on this new fangled device they call the "internet." I wasn't happy with anything out there, so I wrote my own web-based SQL interpreter in perl. It's pretty sweet, I think... It does anything within reason--and tells you when it can't. If you use it, you just need to provide it with an ODBC data source, username, and password . It should take care of the rest.
It's been pretty well tested too. Of course, it can't handle everything in the book--but most SQL commands, even complicated table joins. Here's the code, or download the script and the css at the bottom:
#!/usr/bin/perl -w
#Written by: benthehutt
#Last Updated: May 2, 2006
#Distribute freely--and don't worry about giving me credit. I'm not sure telling a code stealer to give me credit makes any sense...
#Needed for databases
use DBI;
#Connect to data source (SQL database)
my $connection = DBI->connect('dbi:ODBC:myODBCSource','username','password');
#Path to web interface folder on argon
my $path = "d:\\inetpub2\\administrative\\itservices\\its\\networkservices\\netmon\\webInterface\\";
#Print the header and banner
print "Content-type:text/html\n\n";
print <<ENDHTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="accessDatabase.css" rel="stylesheet" type="text/css" />
<title>Access the SQL Database</title>
</head>
<style type="text/css">
<!--
.style1 {
font-size: 24px;
font-weight: bold;
font-family: Arial, Helvetica, sans-serif;
}
-->
</style>
ENDHTML
#If no options, print it without floating div's
if (length($ENV{'QUERY_STRING'})==0){
print <<ENDHTML;
<div id="content">
<center>
<h3><font face = 'Arial'>Enter SQL code below.</font></h3>
<form name="access" action="accessDatabase.pl">
<p>
<textarea name="code" rows="10" cols="75">
</textarea>
</p>
<input type="submit"><input type="reset">
</form>
</center>
</div>
ENDHTML
}
else{
print <<ENDHTML;
<div id="rightnavigation">
<center>
<form name="access" action="accessDatabase.pl">
<p>
<textarea name="code" rows="23" cols="30">
</textarea>
</p>
<input type="submit"><input type="reset">
</form>
</center>
</div>
ENDHTML
my $goodSQL;
#Get the SQL sent in
my ($crap,$SQLJibberish) = split(/\=/,$ENV{'QUERY_STRING'});
my @SQLJibberish2 = split(/\+/,$SQLJibberish);
#An absolutely horrible way to do things--but I'm pressed for time
foreach my $jibb (@SQLJibberish2){
if($jibb=~/3D/){
#Equals Sign
$jibb=~s/\%3D/\=/;$jibb=~s/\%3D/\=/;
$jibb=~s/\%3D/\=/;$jibb=~s/\%3D/\=/;
$jibb=~s/\%3D/\=/;$jibb=~s/\%3D/\=/;
}
if($jibb=~/27/){
#Apostrophe
$jibb=~s/\%27/\'/;$jibb=~s/\%27/\'/;
$jibb=~s/\%27/\'/;$jibb=~s/\%27/\'/;
$jibb=~s/\%27/\'/;$jibb=~s/\%27/\'/;
}
if($jibb=~/3B/){
#Semicolon
$jibb=~s/\%3B/\;/;$jibb=~s/\%3B/\;/;
$jibb=~s/\%3B/\;/;$jibb=~s/\%3B/\;/;
$jibb=~s/\%3B/\;/;$jibb=~s/\%3B/\;/;
}
if($jibb=~/0D/){
#Enter
$jibb=~s/\%0D/ /;$jibb=~s/\%0D/ /;
$jibb=~s/\%0D/ /;$jibb=~s/\%0D/ /;
$jibb=~s/\%0D/ /;$jibb=~s/\%0D/ /;
}
if($jibb=~/0A/){
#Enter
$jibb=~s/\%0A/ /;$jibb=~s/\%0A/ /;
$jibb=~s/\%0A/ /;$jibb=~s/\%0A/ /;
$jibb=~s/\%0A/ /;$jibb=~s/\%0A/ /;
}
if($jibb=~/28/){
#(
$jibb=~s/\%28/\(/;$jibb=~s/\%28/\(/;
$jibb=~s/\%28/\(/;$jibb=~s/\%28/\(/;
$jibb=~s/\%28/\(/;$jibb=~s/\%28/\(/;
}
if($jibb=~/2C/){
#,
$jibb=~s/\%2C/\,/;$jibb=~s/\%2C/\,/;
$jibb=~s/\%2C/\,/;$jibb=~s/\%2C/\,/;
$jibb=~s/\%2C/\,/;$jibb=~s/\%2C/\,/;
}
if($jibb=~/29/){
#)
$jibb=~s/\%29/\)/;$jibb=~s/\%29/\)/;
$jibb=~s/\%29/\)/;$jibb=~s/\%29/\)/;
$jibb=~s/\%29/\)/;$jibb=~s/\%29/\)/;
}
if($jibb=~/25/){
#%
$jibb=~s/\%25/\%/;$jibb=~s/\%25/\%/;
$jibb=~s/\%25/\%/;$jibb=~s/\%25/\%/;
$jibb=~s/\%25/\%/;$jibb=~s/\%25/\%/;
}
$goodSQL .= $jibb . " ";
}
print <<ENDHTML;
<div id="content">
<br><b>Your Query: </b><br><font face='Arial'><center>$goodSQL</center></font>
<br><b>Produced the following results:</b><br><br>
<table border=1 cellpadding=5>
ENDHTML
my $returnHTML;
#Prepare and send to ODBC data source
my $preparer = $connection->prepare($goodSQL);
$preparer->execute;
#Parse the return
while (my @region = $preparer->fetchrow_array){
if (scalar(@region)==0){
print "<br>Zero results returned.";
}
else{
print "\n<tr>\n";
foreach my $element(@region){
print "<td>$element<\/td>";
}
print "\n";
}
}
print "</table>";
}
sub failed{
print <<ENDHTML;
<div id="rightnavigation"></div>
<div id="content">
<h2><i>Query failed.</i> Incorrect syntax, perhaps? Or maybe I just can't do that...</h2>
</div>
</html>
ENDHTML
exit;
}
/* CSS Document */
#banner {
border:1px solid #000;
float: left;
width: 100%;
overflow: hidden;
}
#rightnavigation {
width : 300px;
height: 440px;
overflow: auto;
float: left;
border-left:1px solid #000;
border-bottom:1px solid #000;
border-right:1px solid #000;
padding:10px;
}
#content {
width: auto;
height: 440px;
overflow: auto;
border:1px solid #000;
padding: 20px;
overflow: auto;
}
