如何在SQL查询中使用变量？ORDER BY子句用变量报错求助

错误原因 #

你当前的代码里，ORDER BY后的变量被单引号包裹：Order by '{4}'，这会让SQL把PageOrder的值当成字符串常量，而非要排序的列名，因此触发了"A constant expression was encountered in the ORDER BY list, position 1."的错误。

修复步骤 #

1. 移除ORDER BY后的单引号，让变量直接替换为目标列名：
   修改后的代码：

   strcom = string.Format("select Id,Number,Organization,Title,PublishYear from Document where Organization='{0}' AND Number like '%{1}%' AND Title like '%{2}%' AND PublishYear like '%{3}%' Order by {4}", orgs, nums, keys2, year, PageOrder);
   

2. 添加安全校验：列名无法通过参数化查询处理，直接拼接存在SQL注入风险。需先验证PageOrder的值是否为允许的列名，不在列表内则使用默认列排序：

   // 定义允许用于排序的列集合
   var allowedColumns = new List<string> { "Id", "Number", "Organization", "Title", "PublishYear" };
   // 校验排序列合法性，不合法则用默认值Id
   var validOrderColumn = allowedColumns.Contains(PageOrder) ? PageOrder : "Id";
   // 使用校验后的列名拼接SQL
   strcom = string.Format("select Id,Number,Organization,Title,PublishYear from Document where Organization='{0}' AND Number like '%{1}%' AND Title like '%{2}%' AND PublishYear like '%{3}%' Order by {4}", orgs, nums, keys2, year, validOrderColumn);
   

3. 优化WHERE子句的安全性：WHERE条件里的参数建议改用参数化查询彻底规避注入风险：

   var baseSql = "select Id,Number,Organization,Title,PublishYear from Document where Organization=@Organization AND Number like @Number AND Title like @Title AND PublishYear like @PublishYear Order by {0}";
   // 先替换已验证合法的排序列
   var finalSql = string.Format(baseSql, validOrderColumn);
   // 创建命令并绑定参数
   using (var cmd = new SqlCommand(finalSql, connection))
   {
       cmd.Parameters.AddWithValue("@Organization", orgs);
       cmd.Parameters.AddWithValue("@Number", "%" + nums + "%");
       cmd.Parameters.AddWithValue("@Title", "%" + keys2 + "%");
       cmd.Parameters.AddWithValue("@PublishYear", "%" + year + "%");
       // 执行查询逻辑...
   }
   

内容的提问来源于stack exchange，提问作者Hadi Ranji