Обсуждение: Semi-customized queries? Subset of SQL? Accessing the parser? Injection?

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.

Thanks!
-W

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

On Tue, Nov 4, 2008 at 10:59 AM, Steve Atkins <steve@blighty.com> wrote:
>
> 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.

This... Also, look into setting up replicant slave dbs for users to
hammer on so the main one doesn't get killed by a rogue query.

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

Can't do that.  (Or I wouldn't have asked the question.)  Need a WWW
interface, period.

Thanks for the comment, though.

On Tue, Nov 4, 2008 at 2:12 PM, Webb Sprague <webb.sprague@gmail.com> wrote:

> Can't do that.  (Or I wouldn't have asked the question.)  Need a WWW
> interface, period.

A WWW interface doesn't preclude the suggestion of simply relying on
permissions to maintain safety and providing what amounts to a query
command line; I've got that exact thing in php for one of my DBs. The
user can't make db changes, and just from paranoia I check the query
for certain bad keywords (delete, insert, into, update, drop, create,
alter, etc) before passing it on. On return, some simple php functions
create a table with the appropriate column names and such.

It's surely the simplest solution, and it definitely will work.

--
- David T. Wilson
david.t.wilson@gmail.com

On Tue, Nov 04, 2008 at 11:12:05AM -0800, Webb Sprague wrote:
> > 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.
>
> Can't do that.  (Or I wouldn't have asked the question.)  Need a WWW
> interface, period.

Why not just write a web interface that accepts SQL and renders the
results into an HTML table?  If you wanted to pretty it up a bit, you
could write an AJAX ditty to present a nice GUI query builder for those
that want it.

The fun thing, in my eyes, would be to sit down and define a new DSL
that exposes some subset of SQL that you're interested in.  Once you've
learnt about parsing and lexing, transforming the result into SQL will
be easy.  Coming up with an appropriately specific language would be a
good research project for someone, it'd be interesting to see how much
better than SQL it could be.  You should be able to get the language a
bit more regular and tidy, but it would be interesting to see what your
users thought.


  Sam

On Nov 4, 2008, at 11:12 AM, Webb Sprague wrote:

>> 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.
>
> Can't do that.  (Or I wouldn't have asked the question.)  Need a WWW
> interface, period.
>
> Thanks for the comment, though.


That wasn't mentioned in your original question at all.

(If your constraint is just "has to be via a web browser" then that's
what
anyterm is for, or even just a text field that accepts a sql query.

If you really want them to build queries via a gui web form then you
may well be able to find something pre-built, depending on your
constraints - what clients you need to support, what web framework
you're using and so on. Or do it with simple combo boxes if you
want to limit the users to crippled queries.)

I don't see anything that suggests hacking the SQL parser
is going to be a useful thing to do. If you really think that's what you
need then you might want to be a bit more specific about what
your application constraints are.

I'm guessing that roles, constraints, resource limits and possibly
a sacrificial replicated database will provide the answer to your
actual problem, but we'd need to know what that is first.

Cheers,
   Steve

> Or do it with simple combo boxes if you
> want to limit the users to crippled queries.)

I want to limit my users to *half* crippled queries -- arbitrary
column lists, where clauses, group by lists, and sort by lists.  I
want to make sure that they aren't doing any data modifications nested
inside a where clause or a column definition as a subquery.

> I don't see anything that suggests hacking the SQL parser
> is going to be a useful thing to do.

I would think that I could *use* (definitely not hack -- good god!)
the parser to ask how deep the nested subqueries are, etc.

> I'm guessing that roles, constraints, resource limits and possibly
> a sacrificial replicated database will provide the answer to your
> actual problem, but we'd need to know what that is first.

I am thinking that I may need to give them all, as in all or
nothing..., and kind of follow David Wilson's plan above.  I was
hoping someone had already done what Sam Mason suggested as being the
"fun thing", though ...

Oh -- I think query builders are a thing of the devil.

Thanks to all for putting up with my lack of good of writing.
-W

On Nov 4, 2008, at 11:46 AM, Webb Sprague wrote:

>> Or do it with simple combo boxes if you
>> want to limit the users to crippled queries.)
>
> I want to limit my users to *half* crippled queries -- arbitrary
> column lists, where clauses, group by lists, and sort by lists.  I
> want to make sure that they aren't doing any data modifications nested
> inside a where clause or a column definition as a subquery.
>
>> I don't see anything that suggests hacking the SQL parser
>> is going to be a useful thing to do.
>
> I would think that I could *use* (definitely not hack -- good god!)
> the parser to ask how deep the nested subqueries are, etc.

Have you looked at the output from "explain"? That'll give you
cost estimates, and fairly detailed data on how the query will
be executed, including nested queries, index usage and so on.

Cheers,
   Steve

On Tue, Nov 4, 2008 at 12:46 PM, Webb Sprague <webb.sprague@gmail.com> wrote:
>> Or do it with simple combo boxes if you
>> want to limit the users to crippled queries.)
>
> I want to limit my users to *half* crippled queries -- arbitrary
> column lists, where clauses, group by lists, and sort by lists.  I
> want to make sure that they aren't doing any data modifications nested
> inside a where clause or a column definition as a subquery.
>

Well, setting proper permissions will prevent them from making
changes.  So I do think the generic "throw a query at the db and turn
the result into a table" will probably work ok.  As long as you aren't
talking millions of rows.  You could detect result sets over x number
of rows and just give the user a link to download the data in a csv
file if it's over that threshold.

Am 2008-11-04 11:12:05, schrieb Webb Sprague:
> > 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.
>
> Can't do that.  (Or I wouldn't have asked the question.)  Need a WWW
> interface, period.

And where is the problem?

Thanks, Greetings and nice Day/Evening
    Michelle Konzack
    Systemadministrator
    24V Electronic Engineer
    Tamay Dogan Network
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
+49/177/9351947    50, rue de Soultz         MSN LinuxMichi
+33/6/61925193     67100 Strasbourg/France   IRC #Debian (irc.icq.com)