pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew Hammond <drew@waugh.econ.queensu.ca>
To: pgsql-php@postgresql.org
Subject: beware the subselect
Date: Thu, 8 Feb 2001 00:34:19 -0500
Message-ID: <20010208003419.A13958@waugh.econ.queensu.ca> (raw)
In-Reply-To: <3A6C3957.BE9D921E@greatbridge.com>
References: <3A6B8264.5040902@bundabergcity.qld.gov.au>
	<3A6C3957.BE9D921E@greatbridge.com>

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



Message-ID: <20010208003419.A13958@waugh.econ.queensu.ca>
Permalink:  ../20010208003419.A13958@waugh.econ.queensu.ca/
Also on:    postgresql.org/message-id/20010208003419.A13958@waugh.econ.queensu.ca

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: drew@waugh.econ.queensu.ca
  Subject: Re: beware the subselect
  In-Reply-To: <20010208003419.A13958@waugh.econ.queensu.ca>
  References: <3A6B8264.5040902@bundabergcity.qld.gov.au> <3A6C3957.BE9D921E@greatbridge.com> <20010208003419.A13958@waugh.econ.queensu.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox