prevent sql injection in oracle "order by" part

Question

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?

Answer 1

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