How to avoid injection for sorting query by runtime value

Viewed 756

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>
1 Answers
Related