cutting out the middleperl

On 22 Mar 2007 14:58:15 -0700, Kev <kevinjamesfield@gmail.com> wrote:

Hi everyone,

I'm still in the design phase of a project. I was just wondering if
anyone has any thoughts or experience on the idea of cutting the P out
of the LAMP (or in my case, WAMP for now) stack. What I mean is
having
everything encapsulated into sql (or plpgsql or plperl where needed)
functions stored in the pgsql server, and have Apache communicate with
pgsql via a tiny C program that pretty much just checks whether the
incoming function is on the allowed list and has the proper data
types,
then passes it straight in. Any errors are logged as potential
security
breaches.

I'm really new to mod_perl too, so another question would be if this
would be much faster than a simple perl script that did the same
thing.

I ask this because I realize I need to carefully check data coming
into
pgsql functions as well as at the client end. Why maintain a bunch of
scripts with names similar to the functions they're calling and all
performing similar checks anyway?

I was kinda salivating at the thought of how fast things would be if
you
cut out the A as well, by using a Flash applet to give socket access
to
JavaScript. But then I guess you have to make your pgsql server
itself
publicly accessible on some port. Is that just asking for trouble?

I appreciate any comments or thoughts anyone might have on this.

IMO, I think 'thin middleware' approach is a great way to design
applications...so you are right on the money. The web server. IMO,
should be mostly concerned about rendering html. I don't think
eliminating the middleware is really practical. While you could use a
thick-client javascript framework like GWT and write your queries in
javascript (getting data back via json), I don't think it's really
possible to secure this properly without killing the 'ease of
implementation' factor.

Then again, it's no worse then your typical old school visual basic or
delphi in-house application so common in the 90's. I really miss the
simplicity of Delphi.

merlin

On 27/03/07, Merlin Moncure <mmoncure@gmail.com> wrote:

On 22 Mar 2007 14:58:15 -0700, Kev <kevinjamesfield@gmail.com> wrote:

Hi everyone,

I'm still in the design phase of a project. I was just wondering if
anyone has any thoughts or experience on the idea of cutting the P out
of the LAMP (or in my case, WAMP for now) stack. What I mean is
having
everything encapsulated into sql (or plpgsql or plperl where needed)
functions stored in the pgsql server, and have Apache communicate with
pgsql via a tiny C program that pretty much just checks whether the
incoming function is on the allowed list and has the proper data
types,
then passes it straight in. Any errors are logged as potential
security
breaches.

I'm really new to mod_perl too, so another question would be if this
would be much faster than a simple perl script that did the same
thing.

I ask this because I realize I need to carefully check data coming
into
pgsql functions as well as at the client end. Why maintain a bunch of
scripts with names similar to the functions they're calling and all
performing similar checks anyway?

I was kinda salivating at the thought of how fast things would be if
you
cut out the A as well, by using a Flash applet to give socket access
to
JavaScript. But then I guess you have to make your pgsql server
itself
publicly accessible on some port. Is that just asking for trouble?

I appreciate any comments or thoughts anyone might have on this.

IMO, I think 'thin middleware' approach is a great way to design
applications...so you are right on the money. The web server. IMO,
should be mostly concerned about rendering html. I don't think
eliminating the middleware is really practical. While you could use a
thick-client javascript framework like GWT and write your queries in
javascript (getting data back via json), I don't think it's really
possible to secure this properly without killing the 'ease of
implementation' factor.

Then again, it's no worse then your typical old school visual basic or
delphi in-house application so common in the 90's. I really miss the
simplicity of Delphi.

Strangely the in-house application is often still the better way to
go. The web can make everything 3 times more complicated than it needs
to be. Toolkits like GWT help this but you still need to write
"middleware" even when you can trust the trust the end user. Hence
most places still use in-house applications except the VB or Delphi
gets replaced with Ruby or Python. Here we use C++ and Qt but thats
another story.
The web should still be used for mass market apps and heavy
communication apps and not standard desktop answers. (Unless you
particularly like writing everything twice)
The secret is to use the right tool for the right job, and not try and
find the does everything spanner that fits all nuts and also undoes
screws too. Its never going to work in every case. Unfortunately some
people like this idea.

Peter.

Kev, we have a GPL'd product targeting Postgres that has significant
overlap with what you want, though in other areas we went in another
direction. The site is www.andromeda-project.org, and I've put some
comments below:

Kev wrote:

Hi everyone,

I'm still in the design phase of a project. I was just wondering if
anyone has any thoughts or experience on the idea of cutting the P out
of the LAMP (or in my case, WAMP for now) stack. What I mean is
having
everything encapsulated into sql (or plpgsql or plperl where needed)
functions stored in the pgsql server, and have Apache communicate with
pgsql via a tiny C program that pretty much just checks whether the
incoming function is on the allowed list and has the proper data
types,
then passes it straight in. Any errors are logged as potential
security
breaches.

Andromeda's goal is to implement all biz rules: constraints, automations
and security, in the server.

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

In terms of experience, I sat down to write the first code 33 months
ago, and it began to pay my bills about six months later. All of the
commercial bragging stuff is on the company website: http://www.secdat.com.

I'm really new to mod_perl too, so another question would be if this
would be much faster than a simple perl script that did the same
thing.

Can't say there. My personal preference is for PHP because I can't
understand Perl five minutes after I've written it.

I ask this because I realize I need to carefully check data coming
into
pgsql functions as well as at the client end. Why maintain a bunch of
scripts with names similar to the functions they're calling and all
performing similar checks anyway?

Well actually we tackled that problem by decided to *preserve* direct
table access through SQL as the standard API, which I realize is not the
standard, but for the life of me I can't understand why, since it is
such an amazingly simpler way to get what everyone says they are after.

Here's what I mean. We write out a database spec in a plaintext file
that includes security, constraints, and automations. A "builder"
program then generates the DDL, encodes the biz logic in triggers, and
assigns table sel/ins/upd/del permissions to the tables.

No messy API to remember or manage. Just specify the tables and
columns, who can do what, and what the formulas are, and its all automatic.

A huge benefit to this is the basic ability to manipulate user's
databases through direct SQL.

It's also IMHO the only way to ensure that you can accomplish the task
of having the web server be a proxy. Its easy to convert HTTP into
simple SQL insert/update etc., much harder to make it try to learn an API.

On 3/27/07, Peter Childs <peterachilds@gmail.com> wrote:

Strangely the in-house application is often still the better way to
go. The web can make everything 3 times more complicated than it needs
to be. Toolkits like GWT help this but you still need to write
"middleware" even when you can trust the trust the end user. Hence
most places still use in-house applications except the VB or Delphi
gets replaced with Ruby or Python. Here we use C++ and Qt but thats
another story.

I agree with everything you said except the point about the GWT.
Using a framework like this you can have your query in the javascript,
and pass it through directly the database and pass the data back using
extremely simple (think 10 line) php or perl rpc that renders query
result back in json to the browser. In fact, you can write, compile,
and debug the app in java which is great advantage of gwt (imo). Of
course, this is not an appropriate way of writing an application over
untrusted network but otoh, is....very RAD.

What you get is the limitation of working through the browser but you
can kiss goodbye to deployment headaches that plague classic thick
client apps because the runtime is 100% contained in the browser
rendering engine and some mighty .js files. With a little bit of
clever programming you can get proper binding if you prefer that type
development.

merlin

I agree with everything you said except the point about the GWT.
Using a framework like this you can have your query in the javascript,
and pass it through directly the database and pass the data back using
extremely simple (think 10 line) php or perl rpc that renders query
result back in json to the browser. In fact, you can write, compile,
and debug the app in java which is great advantage of gwt (imo). Of
course, this is not an appropriate way of writing an application over
untrusted network but otoh, is....very RAD.

"Untrusted" is the key point here... in most of the real world cases you
will be far away from such trust that you would run SQL coming from the
end users browser...

What you get is the limitation of working through the browser but you
can kiss goodbye to deployment headaches that plague classic thick
client apps because the runtime is 100% contained in the browser
rendering engine and some mighty .js files.

And this draws the next problem, in the moment your .js is too "mighty",
the users will come screaming after you once their browser starts to
regularly crash, drive the client box out of memory, bog it down to a
halt, etc.

There's no way you can replace all the functionality of a middleware
layer, but it's certainly true some cleverness can be placed at the
client side. Google mail is a very good example, I love that kind of
interface...

Cheers,
Csaba.

"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.

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

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

On 3/27/07, Randal L. Schwartz <merlyn@stonehenge.com> 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.

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

looking at his project, it looks like you create tables and forms
using simple rule based system. very elegant imo, although I would
greatly prefer to to have the rules be in tables them selves, so I can
manipulate with sql, or self hosting dialogs. very interesting
project i must sasy, it feels like alphora but with web spin on it.

merlin

On 3/27/07, Csaba Nagy <nagy@ecircle-ag.com> wrote:

I agree with everything you said except the point about the GWT.
Using a framework like this you can have your query in the javascript,
and pass it through directly the database and pass the data back using
extremely simple (think 10 line) php or perl rpc that renders query
result back in json to the browser. In fact, you can write, compile,
and debug the app in java which is great advantage of gwt (imo). Of
course, this is not an appropriate way of writing an application over
untrusted network but otoh, is....very RAD.

"Untrusted" is the key point here... in most of the real world cases you
will be far away from such trust that you would run SQL coming from the
end users browser...

well, untrusted meaning to general public. you can ssl encrypt the
session and do authentication in the middleware (10 line php becomes
50 lines). The real danger is that someone reverse engineer your .js
app and execute arbitrary sql which is quite dangerous to any databse,
even after basic armoring. However, in-house application development
is quite common, maybe the most common type of development.

What you get is the limitation of working through the browser but you
can kiss goodbye to deployment headaches that plague classic thick
client apps because the runtime is 100% contained in the browser
rendering engine and some mighty .js files.

And this draws the next problem, in the moment your .js is too "mighty",
the users will come screaming after you once their browser starts to
regularly crash, drive the client box out of memory, bog it down to a
halt, etc.

maybe...google and others have pretty much nailed the leaky browser
problem on modern browsers imo. I think you may find this is much
more reasonable than you might expect...

my point is that with thick server you can do very rapid development
eliminating the middleware completely and doing all work on
client/server. and, reversing .js is only slightly more difficult
than reversing vb6 for example, which is arguably most popular, albeit
reviled quick'n'dirty application platform of all time. If most of
the real work is done on the server, though, it's not so bad.

merln

Kev wrote:

Hi everyone,

I'm still in the design phase of a project. I was just wondering if
anyone has any thoughts or experience on the idea of cutting the P out
of the LAMP (or in my case, WAMP for now) stack. What I mean is
having
everything encapsulated into sql (or plpgsql or plperl where needed)
functions stored in the pgsql server, and have Apache communicate with
pgsql via a tiny C program that pretty much just checks whether the
incoming function is on the allowed list and has the proper data
types,
then passes it straight in. Any errors are logged as potential
security
breaches.

Sounds something like mod_libpq:
http://asmith.id.au/mod_libpq.html

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.

Merlin Moncure wrote:

On 3/27/07, Randal L. Schwartz <merlyn@stonehenge.com> 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.

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

looking at his project, it looks like you create tables and forms
using simple rule based system. very elegant imo, although I would
greatly prefer to to have the rules be in tables them selves, so I can
manipulate with sql, or self hosting dialogs. very interesting
project i must sasy, it feels like alphora but with web spin on it.

Actually we do put the rules in the tables and you can execute SQL
directly, something that I so much take for granted now that I sometimes
have to remind myself that most of the world cannot do this!

If the website is not giving that impression, I'll have to correct that,
ouch! Can you tell me what gave you the impression we were just about
web forms?

Thanks for the comments, "elegant", now that's something I'll have to
forward to Mom :)

On 22 Mar 2007 14:58:15 -0700, Kev <kevinjamesfield@gmail.com> wrote:

Hi everyone,

I'm still in the design phase of a project. I was just wondering if
anyone has any thoughts or experience on the idea of cutting the P out
of the LAMP (or in my case, WAMP for now) stack. What I mean is
having
everything encapsulated into sql (or plpgsql or plperl where needed)
functions stored in the pgsql server, and have Apache communicate with
pgsql via a tiny C program that pretty much just checks whether the
incoming function is on the allowed list and has the proper data
types,
then passes it straight in. Any errors are logged as potential
security
breaches.

I'm really new to mod_perl too, so another question would be if this
would be much faster than a simple perl script that did the same
thing.

I ask this because I realize I need to carefully check data coming
into
pgsql functions as well as at the client end. Why maintain a bunch of
scripts with names similar to the functions they're calling and all
performing similar checks anyway?

I was kinda salivating at the thought of how fast things would be if
you
cut out the A as well, by using a Flash applet to give socket access
to
JavaScript. But then I guess you have to make your pgsql server
itself
publicly accessible on some port. Is that just asking for trouble?

I appreciate any comments or thoughts anyone might have on this.

IMO, I think 'thin middleware' approach is a great way to design
applications...so you are right on the money. The web server. IMO,
should be mostly concerned about rendering html. I don't think
eliminating the middleware is really practical. While you could use a
thick-client javascript framework like GWT and write your queries in
javascript (getting data back via json), I don't think it's really
possible to secure this properly without killing the 'ease of
implementation' factor.

Then again, it's no worse then your typical old school visual basic or
delphi in-house application so common in the 90's. I really miss the
simplicity of Delphi.

merlin

Hi Merlin,

Thanks for your reply, these are helpful comments. Just wondering about the security factor, though--is there something specific that would be impossible to lock down? I would think (but I'm no expert to be sure!) that a whitelist-only filter, I mean, if there weren't any buffer overflow vulnerabilities or anything like that, would be tough to sneak malicious SQL functions or other code through. Or did you mean in some other way?

I don't think I would pass straight SQL queries anyway, if it makes a difference, but rather just the function name and the parameters separately, so that there's always an SQL function involved, but so that that's not obvious from the javascript end. All that's obvious is the thin-perlness of it: my perl script could be calling another script by the name we pass it, or a perl subroutine, or something else.

Heh...Delphi was fun, except when I wanted to do some fancier things with it, even in high school...although our low-budget high school didn't exactly have the latest major version.

Thanks,
Kev

Hi Kenneth,

This is wonderful news. I will definitely be checking into it as soon as I have a moment. Thanks!

Kev

On Mar 27, 2007, at 7:34 AM, Aidan Van Dyk wrote:

Kev wrote:

Hi everyone,

I'm still in the design phase of a project. I was just wondering if
anyone has any thoughts or experience on the idea of cutting the P
out
of the LAMP (or in my case, WAMP for now) stack. What I mean is
having
everything encapsulated into sql (or plpgsql or plperl where needed)
functions stored in the pgsql server, and have Apache communicate
with
pgsql via a tiny C program that pretty much just checks whether the
incoming function is on the allowed list and has the proper data
types,
then passes it straight in. Any errors are logged as potential
security
breaches.

Sounds something like mod_libpq:
http://asmith.id.au/mod_libpq.html

Or SQL-on-rails
http://www.sqlonrails.org/

Cheers,
Steve

On 3/27/07, Steve Atkins <steve@blighty.com> wrote:

Or SQL-on-rails
http://www.sqlonrails.org/

LOL!

merlin

Or SQL-on-rails
http://www.sqlonrails.org/

LOL!

merlin

Heh heh..."insufficiently AJAX-y"

On Mar 27, 10:34 am, a...@highrise.ca (Aidan Van Dyk) wrote:

Sounds something like mod_libpq:
http://asmith.id.au/mod_libpq.html

Thanks, I appreciate the link...also looks promising.

Kev

On 3/27/07, Aidan Van Dyk <aidan@highrise.ca> wrote:

Kev wrote:

Hi everyone,

I'm still in the design phase of a project. I was just wondering if
anyone has any thoughts or experience on the idea of cutting the P out
of the LAMP (or in my case, WAMP for now) stack. What I mean is
having
everything encapsulated into sql (or plpgsql or plperl where needed)
functions stored in the pgsql server, and have Apache communicate with
pgsql via a tiny C program that pretty much just checks whether the
incoming function is on the allowed list and has the proper data
types,
then passes it straight in. Any errors are logged as potential
security
breaches.

Sounds something like mod_libpq:
http://asmith.id.au/mod_libpq.html

brilliant. highest possible marks! i mean, wow! :-)

merlin