beware the subselect

This is tricky.  You also have to watch out for nested sql stuff.  And
that means that to correctly parse the query string a finite state
machine (which regex's are) is not sufficient.  You need a pushdown
automata.  The temptation is to grep the input for stuff like "delete"
following a ; but that will get a false positive in situations like

SELECT * FROM table WHERE string='can you; delete this?';

However you also have to watch out for sneaky stuff like

SELECT * FROM table WHERE EXISTS (DELETE FROM table);

Basically, if you want to do it correctly you have to write a partial
SQL parser.  It sounds like overkill, but... :(

The tokens you need to look for are:  UPDATE, DELETE, ;, ', (, and )
where ; ends the current query, ' delimits string literals, and ( begins
subqueries.

in pseudo code:
ok = check_query ( tokenize (input) )

boolean check_query (input) :
    first = input.pop()
    if first in ('update', 'delete') : return false
    while token = input.pop() :
        case token :
    # ignore string literals
            "'" : until "'" == input.pop()
    # is there a subsequent query?
            ';' : return check_query (input)
    # a sub-query?
            '(' : if not check_query (input[0 .. input.index(')') )
                    return false

The tokenization function is trivial.

The short answer is that you probably want to limit what the users can
input.  The easiest way to do this is to set up a form where each field
is listed along with an operator pulldown and a text input box.  You
then look for non-empty text fields and \ escape out all the 's in the
text input.  You end up with a reasonably powerfull query tool that's a
lot less complex and thus easier to write, and easier to verify that
it's secure.

On Mon, Jan 22, 2001 at 08:44:55AM -0500, Chris Ryan wrote:
>     Probably the most direct way to look for and catch such attempts would
> be to search for the semi-colon ';' and if it exists just give an error.
>
> Chris Ryan
> chris@greatbridge.com
>
> s wrote:
> >
> > I am writing a site that
> > does select/insert SQL commands with users input.
> >
> > There is a potential hazard if some one tries to execute there
> > own commands in an input box
> > eg.    the user types into the input  box on a form  -   [ "; delete *
> > from table; ]
> >
> > I'm after a regular expression (that'd be nice) or an algorithm to
> > tell that only one query is being passed to psql at a time.
> >
> > The query string will be processed if
> > Either - one SELECT command only
> >            - one INSERT command only
> >            - one UPDATE command only
> > ELSE - dont process query
> >
> > Any input would be much appreciated.
> > thanks,
> > stef