CODEWITHSUNDEEP
javasortingjdbccollectionsjdbctemplate
Raevik
Raevik

Reputation: 1991

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

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

Upvotes: 25

Views: 34034

Answers (4)

Constantine
Constantine

Reputation: 151

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

Upvotes: 0

Adam111p
Adam111p

Reputation: 3727

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.

Upvotes: 2

Oleksandr Bondarenko
Oleksandr Bondarenko

Reputation: 2018

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.

Upvotes: 28

Vikdor
Vikdor

Reputation: 24134

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.

Upvotes: 2

Related Questions