I'm trying to write a REST interface using Java and Spring Boot that will read a database. As part of that interface, the client will be able to specify which field to order and in which direction. (Eventually I want to extend this to allow the client to request filtered data, but let's keep this simple). Because of the well known issues with SQL injection attacks, I don't want to hand roll the SQL myself; instead I'd prefer to use the JDK and/or Spring to build up the query for me.
For now, a couple samples of the SQL for my query will look like:
SELECT * FROM myTable ORDER BY col1 ASC
SELECT * FROM myTable ORDER BY col2 DESC
I had hoped to do something like:
/**
* @param direction either {@literal ASC} or {@literal DESC}
*/
static <T> List<T> myQuery(JdbcTemplate jdbc, RowMapper<T> mapper, String orderBy, String direction) {
PreparedStatementCreator psc = connection -> {
PreparedStatement statement = connection
.prepareStatement("SELECT * FROM myTable ORDER BY ? ?");
statement.setString(1, orderBy);
statement.setString(2, direction);
return statement;
};
return jdbc.query(psc, mapper);
}
However, this never returns any data. If I hard coded the PreparedStatement to be one of the sample queries it works as expected (but the client can't control the ordering).
I believe the problem that the PreparedStatement is generating SQL like:
SELECT * FROM myTable ORDER BY 'col1' 'ASC';
Those extra quotes around the column name and direction confuse the database.
Lots of REST interfaces offer this feature and they all want to avoid SQL injection attacks, so this must be a solved problem. So my question is: what's the right way of parametrising the query?
