Remote database access: How?

[viktoras d.]

Hi Teresa,

a hile ago I have produced exactly such application to one of my 
clients. Client downloads it, the app communicated with mysql database 
on a remote server. I used a relay script written in Perl (so it should 
work everywhere, because Perl is omnipresent nowadays). It will pass 
nearly all sql except for alter, truncate, drop, modify, create. User 
must post two parameters - pasword and sql query to communicate with the 
database. Unfortunately both are submitted as clear text
: q.pl?p=[password]&q=[sql query]
and gets back the resultset in form of tab delimited text.

Here is the Perl script's (q.pl) source:
#! /usr/bin/perl -wT
#above string should point to perl installation directory on your server

use strict;
use CGI qw(:standard);
use DBI;

print header;
my $sth;
my $q=param('q'); #reads query data
my $p=param('p'); #reads password
my @line;

if ($p eq "password_to_check_goes_here") #if submitted password is 
correct then...
{
unless ($q=~m/alter |truncate|drop |modify|create/i) #unless dangerous 
strings apear in query string then...
{
my $dbh = 
DBI->connect("DBI:mysql:database=db_name:host=localhost","db_user","db_password") 
or die "$DBI::errstr\n";
   $sth = $dbh->prepare($q);
   $sth->execute();
     while (my @line = $sth->fetchrow_array)
       {
       if (@line) {print join("    ", at line)."\n";}
       }
  $sth->finish();
$sth = $dbh->disconnect();
}
else
{
print "\err.#1000: Access denied"; #Wrong password
}
}
else
{
print "\err.#1001:\"$p\" Access denied"; #Potentially dangerous commands 
in query
}
exit 0;

and the clientside Revolution function (stack's script) is as simple as:

function Q vQ
local myServer
local myData

put "http://dommain.net/cgi-bin/q.pl" into myServer
      put  "p=authentication password&" & "q=" & URLencode(vQ) into myData
   post myData to URL myServer
 wait 1 sec    
if it is not empty then
     return it
   else
     return "No matches found or timeout"
     exit to top
   end if
end Q

Now it can send almost any sql statement or multiple statements and get 
back the result from any handler using:
get Q("SELECT * FROM invasions")

It works but sometimes timeouts may happen :-)...

HTH
Viktoras