Re: Prepared Statements

On Thu, 2003-07-17 at 17:27, Dmitry Tkach wrote:
> >
> >
> >In my understanding the prepared statement will properly escape any
> >parameter so it can be trusted that the resulting query will not contain
> >something you wouldn't expect. Example (< and > are delimiters, ignore
> >them):
> >
> >query: <SELECT * from address_book WHERE name = ?>
> >
> >input: <joe';delete from address_book where 'true>
> >
> >result if you just replace the <?> with <'$input'>:
> >SELECT * from address_book WHERE name = 'joe';delete from address_book
> >where 'true'
> >-> results in 2 statements executed
> >
> Nope. You missed a quote :-)

No, I didn't. Take a closer look. The user inputs a string which
contains single quotes matching the quotes placed by the program around
the user supplied input. I never said that this method is a good one, on
the contrary, if the program would use the prepared statement instead,
it could safely use the user supplied text as parameter, because the
JDBC driver will safely escape SQL injection attempts. It will also make
sure there will be no syntax error.
The conclusion is that if you must include in your queries strings
coming from the user (and most of the applications do, just think about
a form where you supply your name - that's a string which will go in to
the DB), then use prepared statements, and never build the query
yourself.

So plain queries are not the same as prepared, because you have to
construct them, and believe me that it's easy to make mistakes here...
you can rely on prepared queries being safe, but implementing your own
escaping mechanisms will just give you a false sense of security, and
possibly cause you lots of trouble.

I hope it's clear enough now what I meant.

Cheers,
Csaba.

>
> The resulting query would be:
> SELECT * from address_book WHERE name = 'joe'';delete from address_book
> where 'true';
>
> This will be a syntax error - not "2 statements executed"... not even
> one statement :-)
>
> But that's not the point anyway.
> The app that accepts user input the way you describe and just puts
> quotes around it is of little use anyway ...
> To be useful, it would have to take care about escaping the special
> characters on its own - not even to prevent "injection attacs", but just
> to be functional in the way that doesn't generate unexpected syntax
> errors (or just totally wrong data being entered)  just because the
> user's input happens to contain a character that has a special meaning
> to the parser.
>
> Dima
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>