Re: Prepared Statements

If it only skips the escaping for numeric types, the obvious workaround
would be first put the user's entry into an int variable:

int userId = getUserId();
PreparedStatement s = c.prepareStatement ("select * from user where id
= ?");
s.setObject(1, userId, Types.INTEGER);

That way you use java's built-in type checking to avoid sending non-numeric
data to the backend any time you're specifying a numeric type that will
skip the escaping.

Can someone confirm that it at least does do the escaping for
string/varchar inputs?

Wes Sheldahl



Csaba Nagy <nagy@ecircle-ag.com>@postgresql.org on 07/18/2003 10:46:33 AM

Sent by:    pgsql-jdbc-owner@postgresql.org


To:    Fernando Nasser <fnasser@redhat.com>
cc:    Dmitry Tkach <dmitry@openratings.com>, Barry Lind
       <blind@xythos.com>, wsheldah@lexmark.com, "pgsql-jdbc @ postgresql
       " ". org" <pgsql-jdbc@postgresql.org>
Subject:    Re: [JDBC] Prepared Statements


I have checked, the query is indeed sent like that to the backend, I've
just checked.
It is a bug.
Presumably for number types the parameter set is passed as it is,
without any escaping.

Cheers,
Csaba.


On Fri, 2003-07-18 at 16:38, Fernando Nasser wrote:
> 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
> >
> > :-)
> >
>
> I don't believe this is actually being sent to the backend, maybe it is
> just a toString() bug.
>
> The backend should get:
>
> select * from user where id='null;drop database mydb'
>
> (If it does not it is a bug.)
>
>
> P.S.: The example case would only succeed if the DBA is an idiot.
> You program should not be accessing the database (for this queries at
> least) as an user who can drop databases unless it is a privileged
> program for privileged users (who could do the damage using plain psql
> anyway).  Perhaps the injection of a 'DELETE FROM mytable' would be a
> more realistic example.
>
>
> --
> Fernando Nasser
> Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario   M4P 2C9
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>



---------------------------(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