October 19, 2020, 11:48:33 AM

Author Topic: Perl SQL Interpreter  (Read 7362 times)

0 Members and 1 Guest are viewing this topic.

Offline benthehutt

  • Global Moderator
  • Seasoned Poster
  • *****
  • Posts: 368
  • Country:
  • "Drugs? You'd better not be!" - my mom
    • Echelon9
Perl SQL Interpreter
« on: 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
  1. #!/usr/bin/perl -w
  2.  
  3. #Written by:   benthehutt
  4. #Last Updated:   May 2, 2006
  5. #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...
  6.  
  7. #Needed for databases
  8. use DBI;
  9.  
  10. #Connect to data source (SQL database)
  11. my $connection = DBI->connect('dbi:ODBC:myODBCSource','username','password');
  12.  
  13. #Path to web interface folder on argon
  14. my $path = "d:\\inetpub2\\administrative\\itservices\\its\\networkservices\\netmon\\webInterface\\";
  15.  
  16. #Print the header and banner
  17. print "Content-type:text/html\n\n";
  18.  
  19. print <<ENDHTML;
  20.  
  21. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  22. <html xmlns="http://www.w3.org/1999/xhtml">
  23. <head>
  24. <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
  25. <link href="accessDatabase.css" rel="stylesheet" type="text/css" />
  26. <title>Access the SQL Database</title>
  27. </head>
  28.  
  29. <style type="text/css">
  30. <!--
  31. .style1 {
  32.    font-size: 24px;
  33.    font-weight: bold;
  34.    font-family: Arial, Helvetica, sans-serif;
  35. }
  36. -->
  37. </style>
  38.  
  39. ENDHTML
  40.  
  41. #If no options, print it without floating div's
  42. if (length($ENV{'QUERY_STRING'})==0){
  43.  
  44. print <<ENDHTML;
  45.  
  46. <div id="content">
  47. <center>
  48. <h3><font face = 'Arial'>Enter SQL code below.</font></h3>
  49. <form name="access" action="accessDatabase.pl">
  50. <p>
  51. <textarea name="code" rows="10" cols="75">
  52. </textarea>
  53. </p>
  54. <input type="submit"><input type="reset">
  55. </form>
  56. </center>
  57. </div>
  58.  
  59. ENDHTML
  60.  
  61. }
  62. else{
  63.  
  64. print <<ENDHTML;
  65.  
  66. <div id="rightnavigation">
  67. <center>
  68. <form name="access" action="accessDatabase.pl">
  69. <p>
  70. <textarea name="code" rows="23" cols="30">
  71. </textarea>
  72. </p>
  73. <input type="submit"><input type="reset">
  74. </form>
  75. </center>
  76. </div>
  77.  
  78. ENDHTML
  79.  
  80. my $goodSQL;
  81.  
  82. #Get the SQL sent in
  83. my ($crap,$SQLJibberish) = split(/\=/,$ENV{'QUERY_STRING'});
  84.  
  85. my @SQLJibberish2 = split(/\+/,$SQLJibberish);
  86. #An absolutely horrible way to do things--but I'm pressed for time
  87. foreach my $jibb (@SQLJibberish2){
  88.    if($jibb=~/3D/){
  89.       #Equals Sign
  90.       $jibb=~s/\%3D/\=/;$jibb=~s/\%3D/\=/;
  91.       $jibb=~s/\%3D/\=/;$jibb=~s/\%3D/\=/;
  92.       $jibb=~s/\%3D/\=/;$jibb=~s/\%3D/\=/;
  93.    }
  94.    if($jibb=~/27/){
  95.       #Apostrophe
  96.       $jibb=~s/\%27/\'/;$jibb=~s/\%27/\'/;
  97.      $jibb=~s/\%27/\'/;$jibb=~s/\%27/\'/;
  98.      $jibb=~s/\%27/\'/;$jibb=~s/\%27/\'/;
  99.   }
  100.   if($jibb=~/3B/){
  101.      #Semicolon
  102.      $jibb=~s/\%3B/\;/;$jibb=~s/\%3B/\;/;
  103.      $jibb=~s/\%3B/\;/;$jibb=~s/\%3B/\;/;
  104.      $jibb=~s/\%3B/\;/;$jibb=~s/\%3B/\;/;
  105.   }
  106.   if($jibb=~/0D/){
  107.      #Enter
  108.      $jibb=~s/\%0D/ /;$jibb=~s/\%0D/ /;
  109.      $jibb=~s/\%0D/ /;$jibb=~s/\%0D/ /;
  110.      $jibb=~s/\%0D/ /;$jibb=~s/\%0D/ /;
  111.   }
  112.   if($jibb=~/0A/){
  113.      #Enter
  114.      $jibb=~s/\%0A/ /;$jibb=~s/\%0A/ /;
  115.      $jibb=~s/\%0A/ /;$jibb=~s/\%0A/ /;
  116.      $jibb=~s/\%0A/ /;$jibb=~s/\%0A/ /;
  117.   }
  118.   if($jibb=~/28/){
  119.      #(
  120.      $jibb=~s/\%28/\(/;$jibb=~s/\%28/\(/;
  121.      $jibb=~s/\%28/\(/;$jibb=~s/\%28/\(/;
  122.      $jibb=~s/\%28/\(/;$jibb=~s/\%28/\(/;
  123.   }
  124.   if($jibb=~/2C/){
  125.      #,
  126.      $jibb=~s/\%2C/\,/;$jibb=~s/\%2C/\,/;
  127.      $jibb=~s/\%2C/\,/;$jibb=~s/\%2C/\,/;
  128.      $jibb=~s/\%2C/\,/;$jibb=~s/\%2C/\,/;
  129.   }
  130.   if($jibb=~/29/){
  131.      #)
  132.      $jibb=~s/\%29/\)/;$jibb=~s/\%29/\)/;
  133.      $jibb=~s/\%29/\)/;$jibb=~s/\%29/\)/;
  134.      $jibb=~s/\%29/\)/;$jibb=~s/\%29/\)/;
  135.   }
  136.   if($jibb=~/25/){
  137.      #%
  138.      $jibb=~s/\%25/\%/;$jibb=~s/\%25/\%/;
  139.      $jibb=~s/\%25/\%/;$jibb=~s/\%25/\%/;
  140.      $jibb=~s/\%25/\%/;$jibb=~s/\%25/\%/;
  141.   }
  142.   $goodSQL .= $jibb . " ";
  143. }
  144.  
  145. print <<ENDHTML;
  146.  
  147. <div id="content">
  148. <br><b>Your Query:  </b><br><font face='Arial'><center>$goodSQL</center></font>
  149. <br><b>Produced the following results:</b><br><br>
  150. <table border=1 cellpadding=5>
  151. ENDHTML
  152.  
  153. my $returnHTML;
  154.  
  155. #Prepare and send to ODBC data source
  156. my $preparer = $connection->prepare($goodSQL);
  157. $preparer->execute;
  158.  
  159. #Parse the return
  160. while (my @region = $preparer->fetchrow_array){
  161.   if (scalar(@region)==0){
  162.      print "<br>Zero results returned.";
  163.   }
  164.   else{
  165.      print "\n<tr>\n";
  166.      foreach my $element(@region){
  167.         print "<td>$element<\/td>";
  168.      }
  169.      print "\n";
  170.   }
  171. }
  172.  
  173. print "</table>";
  174.  
  175. }
  176.  
  177. sub failed{
  178. print <<ENDHTML;
  179. <div id="rightnavigation"></div>
  180. <div id="content">
  181. <h2><i>Query failed.</i> Incorrect syntax, perhaps? Or maybe I just can't do that...</h2>
  182.  
  183. </div>
  184. </html>
  185. ENDHTML
  186. }
  187.  
  188.  

Code: CSS
  1. /* CSS Document */
  2.  
  3.  
  4. #banner {
  5.    border:1px solid #000;
  6.    float: left;
  7.    width: 100%;
  8.    overflow: hidden;
  9. }
  10. #rightnavigation {
  11.    width : 300px;
  12.    height: 440px;
  13.    overflow: auto;
  14.    float: left;
  15.    border-left:1px solid #000;
  16.    border-bottom:1px solid #000;
  17.    border-right:1px solid #000;
  18.    padding:10px;
  19. }
  20. #content {
  21.    width: auto;
  22.    height: 440px;
  23.    overflow: auto;
  24.    border:1px solid #000;
  25.    padding: 20px;
  26.    overflow: auto;
  27. }
  28.  
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.

Offline benthehutt

  • Global Moderator
  • Seasoned Poster
  • *****
  • Posts: 368
  • Country:
  • "Drugs? You'd better not be!" - my mom
    • Echelon9
Re:Perl SQL Interpreter
« Reply #1 on: July 17, 2006, 10:20:18 AM »
An updated version is attached.  Works better, faster, with more commands.
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.