Re: [GENERAL] Prepared statement performance... - Mailing list pgsql-jdbc
From | nferrier@tapsellferrier.co.uk |
---|---|
Subject | Re: [GENERAL] Prepared statement performance... |
Date | |
Msg-id | ulm51pcat.fsf@tapsellferrier.co.uk Whole thread Raw |
In response to | Re: [GENERAL] Prepared statement performance... (Toby <toby@paperjet.com>) |
List | pgsql-jdbc |
Toby <toby@paperjet.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
pgsql-jdbc by date: