Using a prepared statement and variable bind Order By in Java with JDBC driver

Question

I'm using

1. jdbcTemplate to make JDBC connections to a mySQL DB
2. prepared statements to protect myself as much as possible from SQL injection attacks
3. in need to accept requests from the user to sort the data on any of a dozen different columns

4. the following statement

   jdbcTemplate.query("SELECT * FROM TABLE1 ORDER BY ? ?", colName, sortOrder);
   

Of course this doesn't work, because the variable bindings aren't supposed to specify column names just parameter values for expressions in the query.

So...how are people solving this issue? Just doing the sort in Java code seems like an easy solution, but since I'm getting a variable string for the column to sort on, and a variable telling me the sort order....that's an ugly number of comparator-conditions to cover. This seems like it should be a common problem with a common pattern to solve it...

Iowa

Answer 1

Placeholders ? can only be used for parameter values but not with column and sort order directions. So the standard way to do this as is pointed e.g. here is to use String#format() or something similar to append your column name and order value to your query.

Another option is to use Spring Data JPA where you can give to your method as an argument an instance of type Sort which can contain all needed info for database to sort.

Aluin

Answer 2

I would just concatenate the column name and the order to the SQL query, but only after

1. verifying that the column name and order are valid in this context.
2. sanitizing them to counter any attempt of SQL Injection attack.

I feel this is efficient compared to fetching the results to the application layer and sorting them here.

Butane

can you answer here ? The same problem: #42627574 – Lombardi

I would replace 2. with "Check that the value is in a list of allowed values". You can extract a lot of information by injecting something like ORDER BY secretValue or even expressions like ORDER BY secretValue=42 if I'm not mistaken (assuming you find an expression that the sanitizer allows). – Discomfit

Answer 3

My suggestion is the mapping of keys and columns. It's a safe solution.

At the beginning, we initiate our map in the simplest possible way. For convenience, I overloaded the get (Obiect key) method to return the default column ("fullName") in case of failure. This will protect against SqlExeption.

    static Map<String,String> sortCol;
{
    sortCol = new HashMap<String, String>(){
        {//Enter all data for mapping
            put("name","fullName");
            put("rok","year");
            put("rate","likes");
            put("count-rate","countRate");

        }
        /**
         * 
         * @param key for column name
         * @return column name otherwise default "fullName"
         */
        @Override
        public String get(Object key) {
            String col =super.get(key);
            return null==col?"fullName":col;
        }
    };
}

Here is a simple example of use.

String sqlQuery= "Select \"fullName\",year,likes,count-rate, country ..."+
 "from  blaBla..."+
 "where blaBla..."+
 "order by "+sortCol.get("keySort") "\n"; // keySort can have the value name, count-rate etc .. 

By the way, you should never reveal the real names of columns in user interfaces, such as REST or SOAP etc ... For the attacker, this is a great help.

Olag

Answer 4

If columns are pre-defined and limited you can use case-when expression:

SELECT * FROM TABLE1
ORDER BY 
   CASE ?
       WHEN 'col1' THEN col1
       ELSE col2
   END

Finicking