Re: Prepared Statements

Fernando Nasser wrote:

>>
>
> I don't believe this is actually being sent to the backend, maybe it
> is just a toString() bug.

You better do believe it. I tried it, and it works. :-)

>
> The backend should get:
>
> select * from user where id='null;drop database mydb'
>
> (If it does not it is a bug.)

Nah... That's what it would get if you did setString()... setObject ()
doesn't work that way.
I tend to agree, it's a bug - if the type is INTEGER, it should be
checking if the object, passed in is really numeric.

The thing is that, at least, in the current state of the driver, this is
a *really nice* bug, that gives you the only way to use certain
functionality....
For example:

PreparedStatement stmt = c.prepareStatement ("select * from mytable
where data in ?");
stmt.setObject (1, "(1,2,3,4,5)", Types.INTEGER);

... if the "bug" was fixed, there would be no other way to do this kind
of thing :-(

>
>
>
> P.S.: The example case would only succeed if the DBA is an idiot.

No objection here :-)
But, in my opinion, the same comment applies to all the earlier examples
(without PreparedStatements) just as well - the point is, if you are an
idiot, you will trash your database one way or another, with or without
using PS, and if you are not, then you won't :-)

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

Come on!... Replace 'drop databse' with just 'do whatever you want' :-)
I just put it in to make it look scarier :-) That was a joke ...
It's just an illustration of the nice 'injection attac' using
PreparedStatements, that everybody else around seems to believe is
impossible.

It isn't. If the person writing the code is an idiot, PreparedStatements
won't help him (nothing will), and if he isn't they won't help him
either (because he wouldn't need that kind of help).
I would like the performance benefit of PS (if there was any)... But
security? No way...
If you accept any kind of user input and send it to the database without
bothering to check what the hell is there, you will be doomed, and no
PreparedStatement in the world will save you :-)

Dima