Re: Prepared Statements
От | Csaba Nagy |
---|---|
Тема | Re: Prepared Statements |
Дата | |
Msg-id | 1058456607.24801.226.camel@coppola.ecircle.de обсуждение исходный текст |
Ответ на | Re: Prepared Statements (Dmitry Tkach <dmitry@openratings.com>) |
Список | pgsql-jdbc |
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 >
В списке pgsql-jdbc по дате отправления: