Re: Semi-customized queries? Subset of SQL? Accessing the parser? Injection?

On Nov 4, 2008, at 9:21 AM, Webb Sprague wrote:

> Hi all,
>
> I am writing an application that allows users to analyze demographic
> and economic data, and I would like the users to be able to pick
> columns, transform columns with functions (economists take the
> logarithm of everything), and write customized WHERE and GROUP-BY
> clauses. This is kind of like passing through a query to the DB in a
> library catalog.
>
> Has anybody found a good way to do this, especially inside the
> database from a plpgsql function (select * from custom_query('table1',
> 'col1 > 100')) ? I don't want to just concatenate a user supplied
> WHERE clause, at least without somehow checking the resulting
> statement for (1) only one statement, (2) no data modification
> clauses, and (3) only one "level" in the tree.
>
>
> It seems like if I could interact with an SQL parser through a script,
> I could accomplish this relatively easily. Perhaps SPI can help me
> (give me hints!), though I don't really want to write any C. Perhaps
> I am wrong about the possibility of this at all.
>
> I realize that roles and permissions can help protect the system, but
> I still feel nervous.
>
> Has anybody done a similar thing, or tried? The problem is that if we
> try to parameterize everything, then we don't really allow the kind of
> data exploration that we are shooting for and these guys / gals are
> smart enough to deal with a little syntax.

If they're that smart, they're smart enough to deal with SQL, and
likely to be frustrated by a like-sql-but-not command language or
a GUI query designer.

Instead, create a user that only has enough access to read data (and
maybe create temporary tables) and use that user to give them
a sql commandline.

It'll be drastically less development effort for you, and the end result
is less likely to frustrate your users.

When I've done this I've also provided some useful plpgsql and sql
functions for users to use, to wrap commonly needed transformations,
and some views to hide parts of the data model they didn't need
to know about.

Cheers,
Steve