serverside "relay" script

[viktoras didziulis]

actually me too use server side Perl script to communicate with desktop 
application created in rev and a MySQL database on a server. First of 
all it is actually the only secure way to do this, because leaving mysql 
port open for everybody is a highly UNSECURE and unrecommended practice. 
Besides using the relay srcipt one gains additional control over the 
communication between the db & the client. In my case queries had to be 
limited to SELECT only with user passwords. Additionally it filters out 
some potentially dangerous commands like "drop". User must post two 
parameters to communicate with the database: q.pl?p=[password]&q=[sql 
query] and as a result gets back the resultset in form of tab delimited 
text. The Perl script is:
#! /usr/bin/perl -wT

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

print header;
my $sth;
my $q=param('q');
my $p=param('p');
my @line;

if ($p eq "password_goes_here")
{
unless ($q=~m/update|delete|alter |insert|truncate|drop 
|modify|create|\0|use |set |values\(|check | key /i)
{
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 we can send any sql select statement or multiple statements and get 
back the result from any handler using:
get Q("SELECT * FROM invasions")

It works, though sometimes timeouts happen...

All the best!
Viktoras