16

To get some data I'm creating an sql query :)
Of course there's some filtering and ordering parts.

To get the result I use "NamedParameterJdbcTemplate" and when I need to add something to the "where" part, I use parameter map, to prevent injection.

But it's different with "order by" part, as there is no automatic escaping (and it's a part of sql). This order part is sometimes filled with data from user (directly), sometimes put some extra sort parameters from inside code. There is one problem: sometimes this sort field contains not only column name, but a sql statement.

Now each parameter for sorting is escaped manually by replacing some characters (like ') to empty string, but some parameters we set for our code is a bit complex to pass this rule.

What is best way to prevent sql injections in sort part of query, when you use jdbc template?

APC
  • 144,005
  • 19
  • 170
  • 281
Dainius
  • 1,765
  • 1
  • 17
  • 36
  • 1
    Possible to use `PreparedStatement`? – Romain Jan 30 '12 at 13:25
  • I don't know about jdbc but it's possible to use a bind variable in an `order by` in Oracle. – Ben Jan 30 '12 at 13:33
  • PreparedStatement will escape only filtering part (where) parameters, didn't found how to escape sorting part. Maybe you have some very short example? – Dainius Jan 30 '12 at 13:34
  • can you do the order by in your application after retrieving the unsorted data ? (I know it could be nicer to have it in the query) – A.B.Cade Jan 30 '12 at 13:52
  • how about stripping off anything after ; in user input (or reject alltogether if contains ; )? And make sure this user only has select privs. Good start anyway. – tbone Jan 30 '12 at 14:01
  • Need to check that, but probably not possible, as in sort there probably are some data, that not retrieve in select (for example "order by (select value from extra_table where id=table_from_select.refId and c='some value')" ) – Dainius Jan 30 '12 at 14:05
  • tbone, something similar is already there. Problem that some fancy orders have characters that now are removed, and ';' can be inside some string value. – Dainius Jan 30 '12 at 14:07
  • @Dainius I would seriously consider NOT allowing user to pass ';' ru saying the order by can legitimately contain a ';'? Can u post an example? – tbone Jan 30 '12 at 14:12
  • It's not allowed, what I was saying that semicolon ';' can be a part of text (theoretically) e.g. "order by (select value from extra_table where id=table_from_select.refId and c='some value ; some other value')". But as risk is to high, it's allowed only some basic symbols (alphanumeric, underscore and some others). – Dainius Jan 30 '12 at 17:08

3 Answers3

7

To help guard against SQL injection on the database side, have a look at the DBMS_ASSERT built-in Oracle package: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_assert.htm

You might find the SIMPLE_SQL_NAME function will help protect against SQL Injection for your ORDER BY clause.

Hope it helps...

Ollie
  • 17,058
  • 7
  • 48
  • 59
  • That's probably better then use custom escaping function. If there will be no way to do that with some library (jdbctemplate or somthing similar), probably will do this. – Dainius Jan 30 '12 at 17:10
3

You could limit the choice of sort order. Most applications only let the users sort by a displayed column, the user could pick which column to sort through its column place.

In this case you would only accept an integer from the user (you could accept a negative number to sort by descending order). You could let the user sort by several columns by accepting a list of integers.

Integers are easy to check and this would not expose the underlying column names to the user.

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
1

One way I used though for slightly different reasons was to use Order By 3,2 (3rd column, second column)

So if you can figure out column name entered by the user position in columns of the output, indirection should kill the injection vector.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
  • problem that sometimes this sort field contains not only column name, but a sql statement – Dainius Jan 30 '12 at 13:37
  • A user entered sql statement? – Tony Hopkinson Jan 30 '12 at 15:42
  • Not that directly, some links have parameter that is passed to "order by" part. First guess to fix was create map of allowed sort fields and get that field by parameter for url, as a key, something similar (or same) what Vincent has suggested. But there's also some old code, that I not very much would like to modify (who know how long it will live), and there's some sql. And there could be some other place where this order parameter is used. – Dainius Jan 31 '12 at 07:50