Perl SQL Interpreter

benthehutt · May 02, 2006, 01:48:15 AM

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:

Code perl Select

#!/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;
}

Code css Select

/* 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;
}

benthehutt · July 17, 2006, 10:20:18 AM

An updated version is attached. Works better, faster, with more commands.

Perl SQL Interpreter

benthehutt

May 02, 2006, 01:48:15 AM

benthehutt

July 17, 2006, 10:20:18 AM #1