Cryptographic quality random text string generation in SQL Server
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
-- ============================================= | |
-- Description: Generate a 20-character cryptographically secure random string of uppercase letters and digits. | |
--- The entropy of such a string is about 100 bits: plenty. | |
-- Reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e4b5a6d1-1068-41e1-abd7-b3ec51d3f1c3/create-function-error?forum=transactsql | |
-- ============================================= | |
CREATE VIEW RandomSeqView AS | |
SELECT seq = CRYPT_GEN_RANDOM(22); | |
GO | |
CREATE FUNCTION RandomString () | |
RETURNS CHAR(20) | |
AS | |
BEGIN | |
DECLARE @result CHAR(20); | |
DECLARE @randomSeq BINARY(22); | |
SELECT @randomSeq = seq FROM RandomSeqView; | |
SELECT @result = | |
UPPER(LEFT(REPLACE(REPLACE(REPLACE(CAST(N'' AS XML).value( | |
'xs:base64Binary(xs:hexBinary(sql:column("bin")))' | |
, 'VARCHAR(MAX)' | |
),'=',''),'+',''),'/','')+'ABCDEFGHIJKLMNOPQRST',20)) | |
FROM ( | |
SELECT @randomSeq AS bin | |
) AS bin_sql_server_temp; | |
RETURN @result; | |
END | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment