如何在SQL Server中生成加密安全号码？

I am currently using guid NEWID() but I know it is not cryptographically secure.

我目前正在使用guid NEWID（），但我知道它不具有加密安全性。

Is there any better way of generating a cryptographically secure number in SQL Server?

有没有更好的方法在SQL Server中生成加密安全号码？

2 个解决方案

#1

Interesting question :)

有趣的问题:)

I think this will work: CRYPT_GEN_RANDOM

我认为这样可行：CRYPT_GEN_RANDOM

#2

CRYPT_GEN_RANDOM is documented to return a "cryptographic random number".

记录CRYPT_GEN_RANDOM以返回“加密随机数”。

It takes a length parameter between 1 and 8000 which is the length of the number to return in bytes.

它需要1到8000之间的长度参数，这是以字节为单位返回的数字的长度。

For lengths <= 8 bytes. This can be cast to one of the SQL Server integer types straightforwardly.

长度<= 8字节。这可以直接转换为SQL Server整数类型之一。

+-----------+------------------+---------+
| Data type |      Range       | Storage |
+-----------+------------------+---------+
| bigint    | -2^63 to 2^63-1  | 8 Bytes |
| int       | -2^31 to 2^31-1  | 4 Bytes |
| smallint  | -2^15 to 2^15-1  | 2 Bytes |
| tinyint   | 0 to 255         | 1 Byte  |
+-----------+------------------+---------+

Three of them are signed integers and one unsigned. The following will each use the full range of their respective datatypes.

其中三个是有符号整数，一个是无符号整数。以下各项将使用各自数据类型的完整范围。

SELECT 
      CAST(CRYPT_GEN_RANDOM(1)  AS TINYINT),
      CAST(CRYPT_GEN_RANDOM(2)  AS SMALLINT),
      CAST(CRYPT_GEN_RANDOM(4)  AS INT),
      CAST(CRYPT_GEN_RANDOM(8)  AS BIGINT)

It is also possible to supply a shorter value than the datatype storage.

也可以提供比数据类型存储更短的值。

SELECT CAST(CRYPT_GEN_RANDOM(3)  AS INT)

In this case only positive numbers can be returned. The sign bit will always be 0 as the last byte is treated as 0x00. The range of possible numbers that can be returned by the above is between 0 and POWER(2, 24) - 1 inclusive.

在这种情况下，只能返回正数。符号位始终为0，因为最后一个字节被视为0x00。上面可以返回的可能数字范围介于0和POWER（2,24）-1之间。

Suppose the requirement is to generate some random number between 1 and 250.

假设要求是生成1到250之间的一些随机数。

One possible way of doing it would be

一种可行的方法是

SELECT  ( 1 + CAST(CRYPT_GEN_RANDOM(1)  AS TINYINT) % 250) AS X
INTO #T
FROM master..spt_values V1,  master..spt_values

However this method has a problem.

但是这种方法存在问题。

SELECT COUNT(*),X
FROM #T
GROUP BY X
ORDER BY X

The first ten rows of results are

前十行结果是

+-------+----+
| Count | X  |
+-------+----+
| 49437 |  1 |
| 49488 |  2 |
| 49659 |  3 |
| 49381 |  4 |
| 49430 |  5 |
| 49356 |  6 |
| 24914 |  7 |
| 24765 |  8 |
| 24513 |  9 |
| 24732 | 10 |
+-------+----+

Lower numbers (in this case 1 -6) are generated twice as regularly as the others because there are two possible inputs to the modulus function that can generate each of those results.

较低的数字（在这种情况下为1-6）的生成次数是其他数字的两倍，因为模数函数有两个可能的输入，可以生成每个结果。

One possible solution would be to discard all numbers >= 250

一种可能的解决方案是丢弃所有数字> = 250

UPDATE #T
SET    X = CASE
             WHEN Random >= 250 THEN NULL
             ELSE ( 1 + Random % 250 )
           END 
FROM #T
CROSS APPLY (SELECT CAST(CRYPT_GEN_RANDOM(1) AS TINYINT)) CA (Random)

This appears to work on my machine but it is probably not guaranteed that SQL Server will only evaluate the function once across both references to Random in the CASE expression. Additionally it still leaves the problem of needing second and subsequent passes to fix up the NULL rows where the random value was discarded.

这似乎可以在我的机器上运行，但可能无法保证SQL Server只会在CASE表达式中对Random的两次引用中评估该函数一次。此外，它仍然存在需要第二次和后续传递以修复丢弃随机值的NULL行的问题。

Declaring a scalar UDF can solve both those issues.

声明标量UDF可以解决这两个问题。

/*Work around as can't call CRYPT_GEN_RANDOM from a UDF directly*/
CREATE VIEW dbo.CRYPT_GEN_RANDOM1 
AS
SELECT CAST(CRYPT_GEN_RANDOM(1) AS TINYINT) AS Random

go


CREATE FUNCTION GET_CRYPT_GEN_RANDOM1()
RETURNS TINYINT
AS
BEGIN
    DECLARE @Result TINYINT

    WHILE (@Result IS NULL OR @Result >= 250)
            /*Not initialised or result to be discarded*/
        SELECT @Result = Random FROM dbo.CRYPT_GEN_RANDOM1 

    RETURN @Result

END

And then

接着

UPDATE #T
SET    X  = dbo.GET_CRYPT_GEN_RANDOM1()

Alternatively and more straight forwardly one could simply use

或者，更直接地可以简单地使用

CAST(CRYPT_GEN_RANDOM(8)  AS BIGINT) % 250

On the grounds that the range of bigint is so huge that any bias will likely be insignificant. There are 73,786,976,294,838,208 ways that 1 can be generated and 73,786,976,294,838,206 that 249 can be from the query above.

因为bigint的范围如此之大，以至于任何偏见都可能是微不足道的。可以生成1种方法有73,786,976,294,838,208种方式，而上述查询可以生成73,786,976,294,838,206种方式。

If even that small possible bias is not permitted you could discard any values NOT BETWEEN -9223372036854775750 AND 9223372036854775749 as shown earlier.

如果不允许存在这种小的可能偏差，您可以丢弃任何不在前面所示的值-9223372036854775750和9223372036854775749。

#1