Re: cutting out the middleperl

Randal L. Schwartz wrote:
>>>>>> "Kenneth" == Kenneth Downs <ken(at)secdat(dot)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.