Re: Prepared Statements
От | Barry Lind |
---|---|
Тема | Re: Prepared Statements |
Дата | |
Msg-id | 3F181A81.70208@xythos.com обсуждение исходный текст |
Ответ на | Re: Prepared Statements (Dmitry Tkach <dmitry@openratings.com>) |
Список | pgsql-jdbc |
Dmitry, That is a bug. Thanks for pointing it out. Anyone care to submit a patch? --Barry Dmitry Tkach wrote: > Barry Lind wrote: > >> If using a PreparedStatement the driver correctly escapes all values >> to avoid SQL injection attacks. > > > No, it doesn't :-) > For example: > > PreparedStatement s = c.prepareStatement ("select * from user where id = > ?"); > s.setObject (1, "null;drop database mydatabase", Types.INTEGER); > System.out.println (s.toString ()); > > select * from user where id=null;drop database mydb > > :-) > > Dima > > >> While this can also be done when using a regular Statement object, it >> is then the resposibility of the programmer to a) remember they need >> to escape, b) know specificially how postgresql needs things escaped, >> and c) to actually escape all user input. Invariably this will be >> forgotten some of the time and therefore I would always recommend >> using PreparedStatements when you don't have control over the values >> that are being used in the SQL statements. >> >> thanks, >> --Barry >> >> >> wsheldah@lexmark.com wrote: >> >>> I have to disagree; SQL injection can happen just from input >>> parameters, as >>> described. The only thing left out was the quotes. If you construct the >>> query as: >>> String query = "SELECT * from address_book WHERE name = '" + userInput + >>> "'"; >>> >>> Then the user needs to change his input to: "joe'; delete from >>> address_book; '" >>> >>> I don't know about the JDBC driver, but perl's DBI driver would >>> handle the >>> above IF it were a parameterized query by escaping all quotes in the >>> user's >>> input. So if instead of constructing it by hand, you had the "WHERE name >>> = ?" form and the user passed in the above, postgresql would see: >>> >>> SELECT * from address_book WHERE name = 'joe''; delete from >>> address_book; >>> '' >>> >>> (I'm assuming postgresql escapes quotes by doubling them, I don't recall >>> for sure.) >>> Hopefully the JDBC driver will do this as well. If not, then all user >>> input >>> needs to be scanned for quotes, semicolons, etc., so they can be >>> properly >>> escaped to avoid SQL injection attacks. Incidentally, such attacks >>> might be >>> a second select query instead of deleting records, so as to get info >>> on all >>> users in the database instead of just themselves for instance. In >>> that case >>> it would be much less obvious that an attack had occurred. >>> >>> Wes Sheldahl >>> >>> >>> >>> Dmitry Tkach <dmitry@openratings.com>@postgresql.org on 07/17/2003 >>> 10:47:49 >>> AM >>> >>> Sent by: pgsql-jdbc-owner@postgresql.org >>> >>> >>> To: Paul Thomas <paul@tmsl.demon.co.uk> >>> cc: "pgsql-jdbc @ postgresql . org" <pgsql-jdbc@postgresql.org> >>> Subject: Re: [JDBC] Prepared Statements >>> >>> >>> >>> >>>> This is a security hole known as SQL injection. >>> >>> >>> >>> >>> No, it isn't :-) >>> The "hole" you are referring to is letting the users type in entire >>> queries, not just input parameters. >>> As long as you have control over how your sql is constructed, you not >>> any less (nor any more) safe with plain Statements than you would be >>> with PreparedStatements. The do the same exact thing. >>> >>> Dima >>> >>> >>>> If you are using a normal Statement then your users can probably >>>> delete whole tables from the database but with a PreparedStatement you >>>> would write >>>> >>>> String query = "SELECT * from address_book WHERE name = ?" >>>> >>>> and the command actually passed over to the database would be >>>> >>>> SELECT * from address_book WHERE name = 'joe;delete from address_book' >>>> >>>> I'm sure you can see the difference. Maybe PreparedStatements will >>>> have a performance gain in some future release but at the moment they >>>> have a vital role to play in database security. >>>> >>> >>> >>> >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 1: subscribe and unsubscribe commands go to >>> majordomo@postgresql.org >>> >>> >>> >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >>> >> >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
В списке pgsql-jdbc по дате отправления:
Следующее
От: Barry LindДата:
Сообщение: Re: Back to performance issues for a moment... (RE: Prepared