防止SQL注入的ORDER BY子句 [英] Prevent SQL Injection in ORDER BY clause

问题描述

在我们的数据库访问层，我们有一些动态查询创建。例如，我们拥有了构建秩序的一部分BY 子句下面的方法：

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);
}

现在的问题是， 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?

推荐答案

如果您的有无的，以应对在字符串，然后白名单是你最好的选择。首先， sortDirection 应该是很微不足道的白名单：不区分大小写的比较ASC / 递减，你应该设置。对于其他人，我更倾向于将到白名单为称为列，也许在预期键入通过为数据和验证。但在一个绝对的紧要关头，你可以与正则表达式限制（说）强制它们都是严格字母数字（在包括AZ，az，0-9范围 - 也许强调如果需要的话） - 再添加 [] ，即

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.

这篇关于防止SQL注入的ORDER BY子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！