Last active
February 25, 2016 21:01
-
-
Save OllieJones/eeb1e4936527c4685dfa to your computer and use it in GitHub Desktop.
Cryptographic quality random text string generation in SQL Server
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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