从.NET安全地获取下一个SQL Server序列值

Question

我正在尝试在SQL Server中使用SEQUENCE对象，并通过指定序列名称使用C#获取下一个值。范围很简单，因为有一个存储过程可以用于它们，您可以传递序列名称；

    public static T Reserve<T>(string name, int count, SqlConnection sqlConn)
    {
        using (var sqlCmd = new SqlCommand("sp_sequence_get_range", sqlConn))
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            var firstValueParam = new SqlParameter("@range_first_value", SqlDbType.Variant) { Direction = ParameterDirection.Output };

            sqlCmd.Parameters.AddWithValue("@sequence_name", name);
            sqlCmd.Parameters.AddWithValue("@range_size", count);
            sqlCmd.Parameters.Add(firstValueParam);

            sqlCmd.ExecuteNonQuery();

            return (T)firstValueParam.Value;
        }
    }

但是单个值怎么办？在我看来，我可以使用计数为“1”来调用上述函数，或者我可以动态构建SQL。例如：

var sqlCmdStr = string.Format("SELECT NEXT VALUE FOR {0}", name);

我知道这通常是一种不好的做法（即SQL注入）。有什么建议吗？

- Barguast

Answer 1

我知道这通常是不好的做法（即SQL注入）。

并非所有动态SQL都是邪恶的。您是否容易受到SQL注入攻击取决于插入SQL文本的值来自何处。如果它来自代码严格控制的位置（例如选择一组字符串常量的switch语句），则SQL注入不是问题。

或者，您可以为每个序列单独查询（假设您没有很多序列）。

- Branko Dimitrijevic

---

不仅要考虑插入文本的来源，还要考虑其类型。如果插入的文本来自int，则不应存在注入风险。 - Trisped

Answer 2

我的建议是结合@Gserg的答案和您当前的解决方案。编写一个存储过程，接受一个VARCHAR参数@Name。在存储过程中构建SQL字符串，使用@GSerg建议的QUOTENAME。使用EXEC或sp_executesql运行脚本。

类似这样（手写）：

CREATE PROCEDURE [GetNext]
    @Name VARCHAR(50)
AS
BEGIN
    DECLARE @sql VARCHAR(200);
    SET @Name = QUOTENAME(@Name, '[');
    SET @sql = 'SELECT NEXT VALUE FOR ' + @Name;

    EXEC (@sql);
END

- Paul Fleming

---

这对我来说看起来最明智、最灵活。我不想在代码中维护可用序列的列表。我将使用1个范围大小的sp_sequence_get_range测试它，以查看性能是否有任何差异（尽管这不是问题！）。谢谢。 - Barguast

将 EXEC 语句中的 @Sql 部分用括号括起来。因为 EXEC 用于存储过程，而 EXEC()（一个函数）用于动态字符串执行。最终应该以 EXEC (@sql); 结束。 - Onur Omer

Answer 3

以下是保罗另一个解决方案的版本，它将从 SQL 序列返回格式化的字母数字键。

CREATE PROCEDURE [sp_GetNextKey]
@Name NVARCHAR(50),
@FormatText NVARCHAR(50)
AS

--DECLARE @Name NVARCHAR(50)='CustomerKeySequence'
--DECLARE @FormatText NVARCHAR(50) = 'CUS0000#'

DECLARE @sql NVARCHAR(200) = 'SELECT FORMAT((NEXT VALUE FOR ' + QUOTENAME(@Name, '"') + '),'+QUOTENAME(@FormatText, '''')+')';
EXEC (@sql)

/*
RETURNS i.e CUS00184
*/

- Onur Omer

Answer 4

当我需要做类似的事情时，我会这样做：

string sanitized_name;
using (var sqlCmd = new SqlCommand("select quotename(@unsafe_name, '[');", sqlConn))
{
    sqlCmd.Parameters.AddWithValue("@unsafe_name", name);

    sanitized_name = (string)sqlCmd.ExecuteScalar();
}

using (var sqlCmd = new SqlCommand(string.Format("select next value for {0};", sanitized_name), sqlConn))
{
    ...
}

或者创建一个执行相同操作的服务器端过程。

- GSerg

---

真的值得为了引用字段而进行额外的SQL调用吗？最好在CLR代码中模仿quotename。 - Paul Fleming

@flem 你会拿你的年薪打赌第一次就做对吗？ - GSerg

这就是测试的作用。从来没有人期望你第一次就做对了。 - Paul Fleming

@Flem 如果我是你的雇主，我会非常看重你“第一次就把事情做对”的能力，对于非原型代码，我会要求你至少接近90%的正确率。同时，我也要求你重新审视“测试的目的是什么”。 - Casper Leon Nielsen

@CasperLeonNielsen。我同意你关于设定目标以更频繁地“做对”的说法，但我不认为这是一个期望。我期望我的团队“做对”。如果需要10倍的时间来完成最好的工作，并在合理的时间内完成，我并不介意。当我说“这就是测试的用途”时，我指的是TDD。单元测试可以快速确定“quotename”方法的质量。你认为微软的SQL Server引擎开发人员会“第一次就做对”，然后不进行测试吗？ - Paul Fleming

@Flem，考虑到你细致入微的回答，我想邀请你加入我的团队;) - Casper Leon Nielsen