Re: cutting out the middleperl - Mailing list pgsql-general

Randal L. Schwartz wrote:

"Kenneth" == Kenneth Downs <ken@secdat.com> writes:           

Kenneth> This in effect makes the web server a proxy to the database, which
Kenneth> sounds like what you are after.  The "P" portion for us is PHP, not
Kenneth> Perl, and it is small though non-zero.  It has only two jobs really.
Kenneth> In the one direction it converts HTTP requests into SQL, and in the
Kenneth> other it converts SQL results into HTML.

How do you control trust?  I presume you're not accepting raw SQL queries (or
even snippets) over the wire, so you have to have enough server-side mapping
code to map domain objects into database objects and domain verbs into
queries, and then authenticate and authorize that this verb is permitted by
the incoming user. That can't be just a trivial amount of code.  That's
usually a serious pile of code.
 

In a proxy or quasi-proxy situation the simplest scenario is direct table access, all other scenarios are more complicated and reduce to table access in the end.  So because the problem must be considered in terms of table access we ask what is required to pull that off, and the answer is:

a) the database is implementing security
b) users are using real accounts instead of connecting as a superuser and having the client do the security

When this is the case, there are only two implementation issues.  The first is how to manage trust (or authentication), and the second is the mundane issue of how to encode the queries.

Just a couple of weeks ago we discussed the trust issue, it comes down to the known design tradeoffs off HTTPS, sessions, dongles, user habits and so forth.  'nuf said on that.

As for the mundane question of how to encode the queries, the KISS principle says they will come over looking like HTML FORM actions (post or get).  So you'll have a list of input values with some hidden variables that control the action.

You need precious little code to translate these into SQL if you have a description of the database, we use the old-fashioned term "data dictionary" for this.  Our data dictionary lists the column names, types and sizes for each table (among other things).  Since all simple SQL commands are lists of column names and values, the SQL generation is child's play.  Our typical code might look like this:

if(gp('gp_mode')=='ins') {  // gp() retrieves a get/post variable
   $rowvalues=aFromGP("txt_");  // convert group of post vars into an associative array
   $table=gp('gp_table');   // fetch the table name from the stream
   SQLX_insert($table,$rowvalues);  // this routine generates an insert statement
}

The server will throw an error for constraint violations or security violations, the web layer doesn't concern itself with these things except to report them.

The only thing the web layer need do is handle the escaping of quotes to prevent SQL injection, but again, this is only to prevent the user from shooting himself in the foot, anything he injects we'd be happy to execute for him, since it all runs at his security level!

The shocking conclusion from points a) and b) at the top of this reply is this:  there is absolutely no difference, from a security perspective, between these this HTTP request:

index.php?gp_table=example&gp_mode=ins&txt_colname=value&txt_colname=value

and this one:

index.php?gp_sql=insert+into+example+(column1,column2)+values+(value1,value2)

Amazing!  The simple fact is the user is either authorized to execute the query or he isn't.  If you connect to the database using his credentials then let him inject all the SQL he wants, if that's his idea of fun.

And please don't tell me you do all of that client-side. :)
 

Well, since you said please, and since we don't do it, I won't say it.