Does anyone know of a way to generate a RNGCryptoServiceProvider-like random number in T-SQL without having to register any .NET Assemblies?
有没有人知道如何在T-SQL中生成类似RNGCryptoServiceProvider的随机数而无需注册任何.NET程序集?
2 个解决方案
#1
2
SELECT CHECKSUM(NEWID()) gives you a 32 signed integer based on GUIDs. You can use this as base: it's pretty much the best SQL way
SELECT CHECKSUM(NEWID())为您提供基于GUID的32个有符号整数。您可以使用它作为基础:它几乎是最好的SQL方式
You can use it to seed RAND (because RAND is the same for all rows in a single select.
您可以使用它来播种RAND(因为RAND对于单个选择中的所有行都是相同的。
SELECT RAND(CHECKSUM(NEWID()))
A signed 64 bit integer:
带符号的64位整数:
SELECT CAST(CHECKSUM(NEWID()) as bigint) * CAST(CHECKSUM(NEWID()) as bigint)
With some playing around you could cast and/or concatenate to get binary("multiples of 4") etc to build up a byte array... I've not used the RAND thingy though to emulate directly
有些玩游戏你可以转换和/或连接以获得二进制(“4的倍数”)等来构建一个字节数组...我没有使用RAND东西虽然直接模拟
#2
1
If you have to generate cryptographycally secure random numbers (like salts for hashing) then you should use the CLR functions. Checksum, guids and the like are not secure for crypto operations.
如果必须生成加密密码安全随机数(如散列盐),则应使用CLR函数。校验和,guid等对于加密操作是不安全的。
The easiest solution is to use a CLR scalar function that invokes RNGCryptoServiceProvider.
最简单的解决方案是使用调用RNGCryptoServiceProvider的CLR标量函数。
Note that many of the SQL Server crpto functions like EncryptByKey are already salting the input using cryptographically secure generated salts (except for EncryptByKey on RC4 algorithm, that is broken in SQL).
请注意,许多SQL Server crpto函数(如EncryptByKey)已经使用加密安全生成的盐来对输入进行盐化(RC4算法上的EncryptByKey除外,这在SQL中已被破坏)。
#1
2
SELECT CHECKSUM(NEWID()) gives you a 32 signed integer based on GUIDs. You can use this as base: it's pretty much the best SQL way
SELECT CHECKSUM(NEWID())为您提供基于GUID的32个有符号整数。您可以使用它作为基础:它几乎是最好的SQL方式
You can use it to seed RAND (because RAND is the same for all rows in a single select.
您可以使用它来播种RAND(因为RAND对于单个选择中的所有行都是相同的。
SELECT RAND(CHECKSUM(NEWID()))
A signed 64 bit integer:
带符号的64位整数:
SELECT CAST(CHECKSUM(NEWID()) as bigint) * CAST(CHECKSUM(NEWID()) as bigint)
With some playing around you could cast and/or concatenate to get binary("multiples of 4") etc to build up a byte array... I've not used the RAND thingy though to emulate directly
有些玩游戏你可以转换和/或连接以获得二进制(“4的倍数”)等来构建一个字节数组...我没有使用RAND东西虽然直接模拟
#2
1
If you have to generate cryptographycally secure random numbers (like salts for hashing) then you should use the CLR functions. Checksum, guids and the like are not secure for crypto operations.
如果必须生成加密密码安全随机数(如散列盐),则应使用CLR函数。校验和,guid等对于加密操作是不安全的。
The easiest solution is to use a CLR scalar function that invokes RNGCryptoServiceProvider.
最简单的解决方案是使用调用RNGCryptoServiceProvider的CLR标量函数。
Note that many of the SQL Server crpto functions like EncryptByKey are already salting the input using cryptographically secure generated salts (except for EncryptByKey on RC4 algorithm, that is broken in SQL).
请注意,许多SQL Server crpto函数(如EncryptByKey)已经使用加密安全生成的盐来对输入进行盐化(RC4算法上的EncryptByKey除外,这在SQL中已被破坏)。