Thread: query checking
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
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
You can try using the ereg() or eregi() functions and check for what the user
inputed. For example:
if (eregi("delete", $query)
{
echo " you can not delete from table";
}
elseif ( (eregi("select", $query) || (eregi("update", $query) ||
(eregi("insert", $query) )
{
execute $query;.....
}
Hope this helps.
Tim.
s <stefang@bundabergcity.qld.gov.au> on 01/21/2001 07:44:20 PM
To: pgsql-php@postgresql.org
cc: (bcc: Timothy Maguire/Data-Technologies/Harte-Hanks)
Subject: query checking
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
You might also try giving the client user different rights to the database. Only allow select, insert, and update but disallowing any deletes. That way you won't need to build it into your PHP code. Brent --- s <stefang@bundabergcity.qld.gov.au> 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 > ===== "The instructions said install windows 98 or better, so I installed Linux" http://www.matzelle.net __________________________________________________ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices. http://auctions.yahoo.com/
Even safer if yuo don't let users type in actual SQL at all. Have a set of check boxes so they can pick which type of query they want.(update, delete, select, etc.) Have them fill in fields and have them fill in tables. Then, in your php code, construct the sql code yourself, while checking the fields for ilelgal characters like ";" Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Brent R. Matzelle" <bmatzelle@yahoo.com> To: <pgsql-php@postgresql.org> Sent: Tuesday, January 23, 2001 3:37 PM Subject: [PHP] Re: query checking > You might also try giving the client user different rights to > the database. Only allow select, insert, and update but > disallowing any deletes. That way you won't need to build it > into your PHP code. > > Brent > > --- s <stefang@bundabergcity.qld.gov.au> 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 > > > > > ===== > "The instructions said install windows 98 or better, so I installed Linux" > > http://www.matzelle.net > > __________________________________________________ > Do You Yahoo!? > Yahoo! Auctions - Buy the things you want at great prices. > http://auctions.yahoo.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