serverside "relay" script

[Josh Mellicker]

Cool, I like the filters...

I forgot to mention the other benefit to a "relay" script which is  
that a database-enabled Rev app could be shipped without MySQL  
drivers, so (if I understand MySQL licensing correctly) this means  
you don't owe MySQL $$$.


On Oct 2, 2007, at 11:25 PM, viktoras didziulis wrote:

> 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
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution