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(at)ecircle-ag(dot)com>@postgresql.org on 07/18/2003 10:46:33 AM

Sent by: pgsql-jdbc-owner(at)postgresql(dot)org

To: Fernando Nasser <fnasser(at)redhat(dot)com>
cc: Dmitry Tkach <dmitry(at)openratings(dot)com>, Barry Lind
<blind(at)xythos(dot)com>, wsheldah(at)lexmark(dot)com, "pgsql-jdbc @ postgresql
" ". org" <pgsql-jdbc(at)postgresql(dot)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(at)redhat(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly