How to avoid injection for sorting query by runtime value

Question

This is a common problem when the query allows you to sort a column by passed-in arguments. Below is what I am trying with ColdFusion ORM. I know that doing this will add a security loop-hole for SQL injection. Since ORDER BY cannot put on parameter we have to append it in the query itself. I have already escaped some vulnerable characters but still I cannot say that is safe (from SQL injection). ESAPI provides the function encodeForSQL() but this doesn't work SQL Server (it works with MYSQL though).

Another approach I normally use is that instead of passing a column name in the arguments, I pass some numeric value and use switch-case to match the proper column name... but this is an increase in maintenance.

Is there any good method for escaping sorting parameters in SQL (or HQL) when we cannot use named parameters?

<cfscript>
    var gridstruct = {};
    var isPaging = 0;
    var hql = "FROM tbl6 order by #arguments.sortcolumn#";      
    x = entitytoquery(ORMexecuteQuery(hql,false));
</cfscript>

Answer 1

I'd probably just validate the value of the incoming sortcolumn argument against a known list of values that are appropriate. SQLI aside, you'd not want to be sorting on just any column in the underlying schema, surely?

eg:

if (!isValid("regex", arguments.sortcolumn, "list|of|valid|values|here")){
    throw(type="IllegalArgumentException");
}