T-SQL: How to Generate Random Passwords
Problem Definition
This article shows how to write an SQL Server Scalar Function which generates a random password. To avoid copy/paste each code block, all code blocks that used in this article is available for download from TechNet Gallery.
Introduction
This article is an outcome from a request by one of our C Sharp developers, so this comes from a real world requirements, and this is one reason to publish this article and also because practical T-SQL solutions are interesting when they show the language strength and also are tricky enough.
Although this could be achieved using C Sharp, this problem was part of a solution mainly inside SQL Server, so if this part could have been done in SQL Server, it was more maintainable and decreased the round trip.
He thought about using CLR programming in SQL Server, but he was not familiar with it. Therefore, he thought about using T-SQL and tried using function RAND(), But he encountered bigger problem; how using function RAND() within SQL scalar function. He tried again, and put the code within a stored procedure, then try to call it from within a scalar function. Again, he faced with the error that was similar to former error; he could not also call a stored procedure within a SQL scalar function.
Solution
George Polya in his wonderful classic book “How to solve it”, mentioned that the first step to solve a problem is “UNDERSTANDING THE PROBLEM”. Let’s start with this great advice:
Our problem contains two parts:
- How to generate a random 8-character password
- How to call it within a scalar function
Now, we have two smaller problems to solve. This allows us to have deep focus on each of which. Let’s start with first problem.
How to generate a random 8-character password
Well, if we want to do this job using T-SQL, first we have to see it from the right point of view, because our T-SQL tools differ from C Sharp tools. In T-SQL, tools designed to work with a Set of objects or elements. Respectfully to T-SQL nature, if we have a Table which contains some characters, we can produce a set of random elements using a single query.
For example, following code creates a table and inserts some characters into it.
Code Block 001
if object_id( 'CharacterSet', 'U' ) is not null
drop table CharacterSet ;
go
create table CharacterSet
( Value char(1) collate SQL_Latin1_General_CP1_CS_AS
not null
primary key ) ;
go
insert CharacterSet
values ( 'a' ) ,
( 'b' ) ,
( 'c' ) ,
( 'd' ) ,
( 'e' ) ,
( 'f' ) ,
( 'g' ) ,
( 'A' ) ,
( 'B' ) ,
( 'C' ) ,
( '1' ) ,
( '2' ) ,
( '3' ) ;
go
select *
from CharacterSet as cs ;
In the above code, we had to explicitly set column collation by specifying collation “SQL_Latin1_General_CP1_CS_AS”.This collation is case Sensitive and allows us to insert both “a” and “A” , for example, in a column which is primary key.
Now, we can change output result of select statement and sort it with random order using function NEWID() in ORDER BY clause.
Code Block 002
select cs.Value
from CharacterSet as cs
order by NEWID() ;
We have a random output order, so by adding TOP 8 in select clause, we achieve a set with eight random elements.
Code Block 003
select top 8
cs.Value
from CharacterSet as cs
order by NEWID() ;
We have to concatenate all these output characters. There are various ways to do this job. We can use one of them, for example, we can use XML solution. We can produce an XML output that contains all output characters by adding FOR XML PATH at the end of the query.
Code Block 004
select top 8
cs.Value
from CharacterSet as cs
order by NEWID()
for xml path ;
We can get rid of metadata and extra XML characters. Achieving this using PATH(‘’) instead of PATH, and also using [text()] as an alias in SELECT clause for column “Value”.
Code Block 005
select top 8
cs.Value as [text()]
from CharacterSet as cs
order by NEWID()
for xml path('') ;
It seems that we solved the first problem. It’s right, but there is an idea to get rid of the table “CharacterSet”. So, we can continue to optimize our first solution.
One solution is using a CTE instead of this table. Following code illustrates it.
Code Block 006
; with CharacterSet as
( select 'a' as Value
union all
select 'b'
union all
select 'c'
union all
select 'd'
union all
select 'e'
union all
select 'f'
union all
select 'g'
union all
select 'A'
union all
select 'B'
union all
select 'C'
union all
select '1'
union all
select '2'
union all
select '3' )
select top 8
cs.Value as [text()]
from CharacterSet as cs
order by NEWID()
for xml path('') ;
There are two points in this code that may need to explain:
- Using semicolon before WITH is necessary
- Using UNION ALL is very important. If we use UNION and the default database collation is case insensitive, the CTE output ignore one of both Case Sensitive Characters, one of “a” or “A”, for example.
This is a good solution, but it can be long. If we could make it shorter than the above query, it would be a better solution, of course.
SQL Server 2008 introduced “Table Value Constructor” which can help use. We can use it to specify multiple values in FROM clause within a SELECT statement. So we can use it to specify a derived table in FROM clause. Follow code shows it.
Code Block 007
select top 8
cs.Value as [text()]
from
( Values ('a'),
('b'),
('c'),
('d'),
('e'),
('f'),
('g'),
('A'),
('B'),
('C'),
('1'),
('2'),
('3') ) as cs(Value)
order by NEWID()
for xml path('') ;
Using Table Value Constructor is really good improvement, and makes our code shorter and more readable. But it has one limitation which is the maximum number of rows that can be constructed using the table value constructor is 1000. This restriction may not be a troublemaker with our problem as we usually have less than 1000 characters for generating a password. But some languages like Chinese has thousands characters. So, one work around to this problem is using UNION All separate Table Value Constructors to cover all of them. By the way, in such situations, it’s better to design a Table that contains every characters, then use it.
Although this is a really good solution, but it’s worth to think about how it can be better. For instance, if we have the ability to change one string into a table, like what we did when converting a Set of elements to string, so we will have better solution. The idea over this try is using system view “master..spt_Values” which is an elegant practical object in such problems. This make it possible to achieve a solution which in it, knowing number of characters is not mandatory. Follow code illustrate it.
Code Block 008
declare @String varchar(2048)
SET @String = 'abcdefg' + --lower letters
'ABC' + --upper letters
'123'; --number characters
select top 8
substring(@String, 1 + Number, 1) as [text()]
from master..spt_values
where number < datalength(@String)
and type = 'P'
order by newid()
for xml path('')
Now, we can see what is happening in above code. First we declared a string variable that contained all characters. Then we used the system view “master..spt_values” which can provide a column of numbers between 0 and 2047. But we limit it by DATALENGTH(@string) which is the maximum number of characters we have. What we did in above code is converting string to columns and then converting columns to string as shown in next data flow diagram:
How to call it within a scalar function
Well, we want to convert our solution into a SQL scalar function. Let’s start do it by writing next code.
Code Block 009
create function fnPassCreator()
returns char(8)
as
begin
declare @pass char(8)
declare @String varchar(2048)
SET @String = 'abcdefg' + --lower letters
'ABC' + --upper letters
'123'; --number characters
select @pass = (select top 8
substring(@String, 1 + Number, 1) as [text()]
from master..spt_values
where number < datalength(@String)
and type = 'P'
order by newid()
for xml path('') )
return @pass
end
But after executing above code, we cannot create the function, because we cannot use operator “newid()” within a function as shown in next figure:
This is the last barrier to achieve final solution. We know that we cannot use newid within function, but we can use it within a View. If we can call that View within our Function, we achieve our target. Because Views cannot be parameterized, we can put newid() in a View, then call it in ORDER BY clause within our Function. Let’s try it using follow code:
Code Block 010
create view vwGetNewID
as
select newid() as new_id
By selecting from this View, in each time, we have one random GUID. So, we can use it in ORDER BY clause and as shown in next code, we can create this Function.
Code Block 011
create function dbo.fnPassCreator()
returns char(8)
as
begin
declare @pass char(8)
declare @String varchar(2048)
SET @String = 'abcdefg' + --lower letters
'ABC' + --upper letters
'123'; --number characters
select @pass = (select top 8
substring(@String, 1 + Number, 1) as [text()]
from master..spt_values
where number < datalength(@String)
and type = 'P'
order by
(select new_id from vwGetNewID) --instead of using newid()
for xml path('') )
return @pass
end
go
--------------------------
--test case:
select dbo.fnPassCreator() as Pa$$word
--------------------------
XML TIP
Using FOR XML to concatenate values in one column is a great solution, but we have to replace XML special character equivalents in our solution. Next chart shows these characters and their equivalents.
Special XML characters |
Equivalent |
< |
< |
> |
> |
& |
& |
‘’ |
"e; |
‘ |
' |
Follow code shows the next version. Although, it is not necessary to use these special characters.
Code Block 012
create function dbo.fnPassCreator()
returns char(8)
as
begin
declare @pass char(8)
declare @String varchar(2048)
SET @String = 'abcdefg' + --lower letters
'ABC' + --upper letters
'123'+ --number characters
')[:|!@$&<'; --sign characters
select @pass = replace
(replace
(replace
(replace
(replace(
(select top 8
substring(@String, 1 + Number, 1) as [text()]
from master..spt_values
where number < datalength(@String)
and type = 'P'
order by
(select new_id from vwGetNewID) --instead of using newid()
for xml path('') )
, '<', '<'
)
,'>','>'
)
,'&','&'
)
,'"',''''''
)
,'&apos',''''
)
return @pass
end
go
--------------------------
--test case:
select dbo.fnPassCreator() as Pa$$word
--------------------------
English-Specific Solution
If we want to use English alphabetic letters, signs and numbers, we can still improve our solution. T-SQL is more powerful language than many people imagine about it. It has function CHAR() which can convert an ASCII code to its related character. For example follow code shows character “B”.
Code Block 013
select char(66)
So, we can select a column of all English alphabetic letters (upper and lower), signs and numbers (ASCII codes between 33 and 126). Then using this Set to generate a random password, like next code:
Code Block 014
select top 8
char(number) as [text()]
from master..spt_values
where type = 'P'
and number between 33 and 126
order by newid()
for xml path('')
Now, we need to create a View that contains above code. It seems that follow code do it for us:
Code Block 015
create view dbo.vwGetRandomPass
as
select top 8
char(number) as [text()]
from master..spt_values
where type = 'P'
and number between 33 and 126
order by newid()
for xml path('')
But it cannot. There is a problem with our code, as above figure shows. We have to specify column name when creating a View. Therefore, we add an alias to specify column name. It will be more readable, if we first add this alias then add its definition, instead of using keyword “AS”.
Code Block 016
create view dbo.vwGetRandomPass
as
select Pass =
(
select top 8
char(number) as [text()]
from master..spt_values
where type = 'P'
and number between 33 and 126
order by newid()
for xml path('')
) ;
go
Then, we write Function code like next code. The main point is that we still have to create a View for using newid(). In this code we have to replace equivalents of XML special characters to correct them, because we used them.
Code Block 017
create function dbo.fnPassCreatorEnglish()
returns char(8)
as
begin
declare @pass char(8)
select @pass = replace
(replace
(replace
(replace
(replace(
(select Pass
from dbo.vwGetRandomPass )
, '<', '<'
)
,'>','>'
)
,'&','&'
)
,'"',''''''
)
,'&apos',''''
)
return @pass
end
go
---------------------------------
--test case:
select dbo.fnPassCreatorEnglish()
---------------------------------
Puzzle Section (Weighted Solution)
In this section, we try to find a weighted way in generating a random password. Suppose we have bellow condition chart:
Character Type |
Weight |
Number |
2 |
Upper letter |
3 |
Lower letter |
2 |
Sign |
1 |
Based on this chart, the random password must have two number characters, for example. To solve this problem, we choose using English-specific solution, to avoid complexity. To achieve the target, we have to know more about ASCII codes. Next chart shows various ASCII code intervals based on the given character types in above chart.
Character Type |
From |
To |
Number |
48 |
57 |
Upper letter |
65 |
90 |
Lower letter |
97 |
122 |
Sign |
33 |
47 |
58 |
64 |
|
91 |
96 |
|
123 |
126 |
It seems we can get the result using next code:
Code Block 018
select top 2 CHAR(number) as chr
from master..spt_values
where number between 48 and 57 -- number characters
union all
select top 3 CHAR(number) as chr
from master..spt_values
where number between 65 and 90 -- Upper letters
union all
select top 2 CHAR(number) as chr
from master..spt_values
where number between 97 and 122 -- Lower letters
union all
select top 1 CHAR(number) as chr
from master..spt_values
where number between 33 and 47 -- sign characters
or number between 58 and 64
or number between 91 and 96
or number between 123 and 126
order by newid()
As illustrated in above figure, it’s not possible to use newid() in ORDER BY clause within SELECT queries that contains UNION ALL. So, we change our code and rewrite it using CTEs.
Code Block 019
alter view dbo.vwGetRandomPass
as
with s1 as
(
select top 2 CHAR(number) as chr
from master..spt_values
where number between 48 and 57 -- number characters
order by newid()
)
, s2 as
(
select top 3 CHAR(number) as chr
from master..spt_values
where number between 65 and 90 -- Upper letters
order by newid()
)
, s3 as
(
select top 2 CHAR(number) as chr
from master..spt_values
where number between 97 and 122 -- Lower letters
order by newid()
)
, s4 as
(
select top 1 CHAR(number) as chr
from master..spt_values
where number between 33 and 47 -- sign characters
or number between 58 and 64
or number between 91 and 96
or number between 123 and 126
order by newid()
)
, final as
(
select chr
from s1
union all
select chr
from s2
union all
select chr
from s3
union all
select chr
from s4
)
select pass =
(
select chr as [text()]
from final
order by newid()
for xml path('')
)
We wrote five CTEs in above code which in first four Ctes we used newid() separately. Then we moved UNION ALL to the last CTE with name “final”. Eventually, we use newid() again in last select statement to make sure the order of character types will be random. Now, we can use the scalar function we wrote in former section to produce a weighted random password.
Conclusion
Despite writing this function seems to be easy, its implementation using T-SQL needs creative methods and Set-Based thinking. But we can still be more productive with this problem. For example, we can add input argument(s) to the function, and when input argument is shorter than 8 characters, fix its length using interesting T-SQL Functions like DUPLICATE(). For me, it is still a good sample to know more about T-SQL capabilities. Hope you enjoyed it.
See Also
- T-SQL: How to Generate Random Passwords Script published in TechNet Gallery
- T-SQL: Random String
- Transact-SQL Portal