Re: [GENERAL] Prepared statement performance...

Toby <toby(at)paperjet(dot)com> writes:

> then the resultant SQL would be
>
> INSERT INTO Users (username) VALUES ('joe'; DROP TABLE users;');
>
> i suspect this would work.

Try it /8->

> I don't see how the above is a classic javascript hack, since there's no
> javascript. i've seen on production code places where strings taken from
> form fields are stored in cookies and session variables and subsequently
> written directly to the database, as shown above.

I didn't mean that the code you used was a javascript hack, but the
worry about unvalidated input fields is related to that. Javascript is
often used for hacking in unvalidated column values passed in and out
of databases.

> i've been to sites where this is possible and it also does not require
> intimate knowledge of the target database. a bit of messing around will
> often cause errors to be thrown and, unless the target webserver is
> configured appropriately (IIS is good for this), it is often possible to
> see the detailed error message...whihc itself can show hints of the backend
> structure.
>
> by way of example, try going to
>
> http://www.westmisnter.ac.uk/
>
> and in one of the search boxes enter the following
>
> ';select * from msdb..sysjobs;
>
> Now then, if someone spent a few minutes working on this, I'm sure it would
> be possible to drop a table or 2 or, at the very least, trash a load of
> data. the same will be possible on a postgres backend.
>
> course, what this has to do with performance I don't know.

Presumably, The original discourse was about whether you could put
these sorts of strings into PS bind variables. I don't believe you can
since bind variables are part of the syntax tree of a legal SQL
statement.

If you had:

input=";select * from somedb;"
PreparedStatement ps
= con.prepareStatement("insert into users"
+ " value ( ? );");
ps.setString(1, input);

You're not going to get anything legal.

However, I agree that SQL hacks are possible when you're combining
unvalidated strings from the frontend. This is because the query
parser has a chance to be terminated by the ";" at the start of the
input expression (when it's embedded in another query).

A select example would be:

Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from tab "
+ where id = " + input + ";");

The above insert, when done with combination, would also work:

input=";select * from somedb;"
Statement st = con.createStatement();
st.execute("insert into users value ( ? );");

However, this is different from a PS because a PS has already parsed
the string, therefore a certain amount of lexical protection is
offered.

I don't know if PostgreSQL works this way yet: I suspect that it does
because the PS facilities are being offered by the backend.

Note 1: PS's do not protect you from javascript hacks.

Note 2: one good reason to have PS's is that they offer this lexical
protection, using combination is quick, but unsafe.

Note 3: Oracle does not allow querys to be ended with ";" in JDBC
statements, this presumably stops a lot of the rot. It would be nice
if PostgreSQL JDBC had an Oracle compatibility mode for Oracle querys:
I find the biggest pain in porting apps between the two is this query
ending nonsense.

Nic