Re: Prepared Statements - Mailing list pgsql-jdbc

This looks like a bug to me.
Still, once these kind of bugs are identified and fixed, it's much safer
to rely on the prepared statement's security than on your own escaping.
Besides, if you would set the parameter as string (as you would normally
do in an application) and not as an integer, the escaping would be
correct. And numeric parameters normally get checked for valid
formatting in the application, so this would not slip through.

Cheers,
Csasba.


On Fri, 2003-07-18 at 16:18, 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
>