Prevent SQL Injection in ORDER BY clause

Question

In our DB access layer we have some dynamic query creation. For instance, we have the following method for building a part of an ORDER BY clause:

protected string BuildSortString(string sortColumn, string sortDirection, string defaultColumn)
{
    if (String.IsNullOrEmpty(sortColumn))
    {
        return defaultColumn;
    }

    return String.Format("{0} {1}", sortColumn, sortDirection);
}

The problem is, sortColumn and sortDirection both come from outside as strings, so of course something should be done to prevent possible injection attacks. Does anybody have any idea how this can be done?

Answer 1

If you have to deal in strings, then white-listing is your best bet. Firstly, sortDirection should be pretty trivial to white-list: a case-insensitive compare to "asc" / "desc" and you should be set. For the others, my preference would be to white-list to known columns, perhaps by passing in the expected Type for the data and validating. But at an absolute pinch, you could restrict with regex to (say) enforce they are all strictly alpha-numeric (in the a-z, A-Z, 0-9 range - maybe underscore if needed) - and then add [], i.e.

return string.Format("[{0}] {1}", sortColumn, sortDirection);

But: strict white-list of known columns would be much better, as would an enum for the direction.

Answer 2

Another solution if you can change your method to accept int instead of string parameters.

protected string BuildSortString(int sortColumn, int sortDirection, string defaultColumn)
{
    if (String.IsNullOrEmpty(sortColumn))
    {
        return defaultColumn;
    }
//sortdirection 0-> "ASC" else "DESC"
//sorColumn 1 for your firstcolumn, 2 for your second column etc.
    return String.Format("{0} {1}", sortColumn, sortDirection==0? " ASC " : " DESC ");
}

Good luck.