SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Password Generation Challenge		  New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

SamC
White Water Yakist

USA
3459 Posts
Posted - 12/15/2005 :  15:58:34  Show Profile  Reply with Quote
I've seen JavaScripts and C do this... how about SQL?

Generate a unique 8 character password using SQL containing upper, lower, numeric and special characters using the fewest possible statements.

edit: Maybe I meant "random" not "unique"...
Edited by - SamC on 12/15/2005 15:59:23

robvolk
Most Valuable Yak

USA
15559 Posts
Posted - 12/15/2005 :  16:00:13  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT CAST(CAST(newid() as binary(16)) as varchar(8))

What do I win?
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts
Posted - 12/15/2005 :  16:00:39  Show Profile  Reply with Quote
Completely random?
How about bonus points if the resulting passwords are extremely difficult to type?
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts
Posted - 12/15/2005 :  16:02:37  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

SELECT CAST(CAST(newid() as binary(16)) as varchar(8))

What do I win?

Well, you definitely get the bonus points...
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts
Posted - 12/15/2005 :  16:03:40  Show Profile  Reply with Quote
Sam if you want a password generator just say so. You need to call it a "challenge"
Here's one I wrote for myself at some point. I wasn't going for least statements though but it does the upper, lower, numeric, special masking:

/*
Returns a password with a similar mask as the passed in template
mask consists of length, upper case, lower case, numbers, and special characters

so these have the same mask:
zFp8=B<c3fk
mKb6}B{f7tj
*/

--Password Template
declare @template varchar(30)
set @template = 'cAd0#P*f6gc'

if len(isnull(@template,'')) = 0
	set @template = 'cAd0#P*f6gc'

declare @i int
	,@c char(1)
	,@password varchar(30)
	,@special varchar(50)

set nocount on
select	@i = 0
	,@password = ''
	,@special = '!"#$%&()*+,-./:;<=>?@{|}~'

set @i = 1
while @i <= len(@template)
begin

	set @c = 
	case
	--upper alpha [A-Z]
	when ascii(substring(@template, @i, 1)) between 65 and 90
	then	char(65 + convert(int,floor(rand()*26)))

	--lower alpha [a-z]
	when ascii(substring(@template, @i, 1)) between 97 and 122
	then	char(97 + convert(int,floor(rand()*26)))

	--number [0-9]
	when ascii(substring(@template, @i, 1)) between 48 and 57
	then	convert(char(1), convert(int,floor(rand() * 10)))
		
	--special (printable) character
	else
	substring(@special, convert(int,floor((rand()*len(@special))+1)), 1)
	
	end

	select	@i = @i+1
		,@password = @password + @c
end

select @password


Be One with the Optimizer
TG
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts
Posted - 12/15/2005 :  16:18:55  Show Profile  Reply with Quote
Here is one I just cooked up:
select	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 12/15/2005 :  16:42:49  Show Profile  Reply with Quote
Well this isn't a short one, but it generates a password from a list of characters you can change with a random password length between the min and max you define. I removed the characters like zero and O, one and l and others that are easy to confuse. You can add or remove extra copies of the letter, numbers, and special characters to weight it towards one or the other. It also generates a stored procedure call to change the password if you need it for a SQL Server password. Kind of geeked out really. 


/*	Script: Generate_Password.sql
This script generates a random password made up of
upper and lowercase letters, numbers, and special characters.
It also generates a SQL Server password change
stored procedure call.

To use this script in Query Analyzer
1. Set the minimum password length, @PASSWORD_MIN_LENGTH
2. Set the maximum password length, @PASSWORD_MAX_LENGTH
3. Set the login name, @LOGIN
4. Execute the script 
*/
set nocount on
declare @PASSWORD_MIN_LENGTH	int
declare @PASSWORD_MAX_LENGTH	int
declare @LOGIN			SYSNAME
select @PASSWORD_MIN_LENGTH = 12
select @PASSWORD_MAX_LENGTH = 20
select @LOGIN = 'mylogin'

declare @PASSWORD_DATA table (PASSWORD_CHAR VARCHAR(1) )
declare @NUM table ( DIGIT int not null primary key clustered)

insert into @NUM (DIGIT)
select DIGIT =  0 union all select DIGIT =  1 union all
select DIGIT =  2 union all select DIGIT =  3 union all
select DIGIT =  4 union all select DIGIT =  5 union all
select DIGIT =  6 union all select DIGIT =  7 union all
select DIGIT =  8 union all select DIGIT =  9
order by 1

insert into @PASSWORD_DATA (PASSWORD_CHAR)
select
	PASSWORD_CHAR = 
	substring(b.CHARACTERS,a.RAND_INT%b.MOD,1)
from
(
select
	aa.NUMBER,
	RAND_INT	= 
	abs(convert(int,convert(varbinary(100),newid())))
from
	(
	select
		NUMBER = a.DIGIT+(b.DIGIT*10)
	from
		@NUM a 	cross join @NUM b
	) aa
) a
cross join
(
select
	MOD = len(bb.CHARACTERS)-1,
	bb.CHARACTERS
from
	(
	select
		CHARACTERS =
		'ABCDEFGHJKLMNPQURSUVWXYZ'+
		'abcdefghjkmnpqursuvwxyz'+
		'ABCDEFGHJKLMNPQURSUVWXYZ'+
		'abcdefghjkmnpqursuvwxyz'+
		'ABCDEFGHJKLMNPQURSUVWXYZ'+
		'abcdefghjkmnpqursuvwxyz'+
		'ABCDEFGHJKLMNPQURSUVWXYZ'+
		'abcdefghjkmnpqursuvwxyz'+
		'23456789'+
		'23456789'+
		'23456789'+
		'23456789'+
		'23456789'+
		'23456789'+
		'23456789'+
		'23456789'+
		'23456789'+
		'23456789'+
		'@#$^*+=<>?'+
		'@#$^*+=<>?'+
		'@#$^*+=<>?'+
		'@#$^*+=<>?'
	) bb
) b
order by
	newid()

--select * from @PASSWORD_DATA

declare @password varchar(100)

select @password = ''

select @password = @password+PASSWORD_CHAR
from @PASSWORD_DATA

select @password = 
	-- Random length from MIN to MAX characters
	substring(@password,1,
	@PASSWORD_MIN_LENGTH +
	(abs(convert(int,convert(varbinary(100),newid()))))%(@PASSWORD_MAX_LENGTH -@PASSWORD_MIN_LENGTH+1)) 

print 
'

PASSWORD = '+@password+'


exec master.dbo.sp_password
	@old = NULL ,
	@new = '''+@password+''', 
	@loginame = '''+isnull(@LOGIN,'NULL')+'''

'


Script Output

PASSWORD = 9dHCR8hfDpU85dYbrEA*


exec master.dbo.sp_password
	@old = NULL ,
	@new = '9dHCR8hfDpU85dYbrEA*', 
	@loginame = 'mylogin'


CODO ERGO SUM
Edited by - Michael Valentine Jones on 12/15/2005 16:50:14
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts
Posted - 12/15/2005 :  16:50:16  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

SELECT CAST(CAST(newid() as binary(16)) as varchar(8))

What do I win?

Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts
Posted - 12/15/2005 :  16:53:13  Show Profile  Reply with Quote
quote:
Originally posted by blindman

Here is one I just cooked up:
select	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33)

Very simple! Reasonable results, though it won't guarrantee a mix of all character types.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 12/15/2005 :  16:54:17  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

SELECT CAST(CAST(newid() as binary(16)) as varchar(8))

What do I win?

You get your password set to this:
Í#òÍuD

That should be reward enough.




CODO ERGO SUM
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts
Posted - 12/15/2005 :  16:57:19  Show Profile  Reply with Quote
Michaels does the job, but it's long. Some great ideas though.
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts
Posted - 12/15/2005 :  16:59:04  Show Profile  Reply with Quote
TG - Yours seems to meet the requirements... fewer lines of code too...

I gotta take some time to study both yours and Michael's with a coupla s in hand.
Edited by - SamC on 12/15/2005 16:59:29
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 12/15/2005 :  17:17:03  Show Profile  Reply with Quote
Just for fun, I "borrowed" you idea, and "enhanced" it with code from mine, and added the REPLICATE and WHILE to make it shorter. You can still generate a password of random length whithin the range of MIN to MAX, and define a weighted list of characters to select from.


declare @chars varchar (8000)
declare @pass  varchar (100)
declare @cnt 	int
declare @len 	int
declare @min 	int
declare @max 	int
declare @pw_len int

select @min = 10, @max	= 15
select @pw_len = @min + convert(int,rand()*(@max-@min+1))

select @chars =
replicate('ABCDEFGHJKLMNPQURSUVWXYZ',8)+
replicate('abcdefghjkmnpqursuvwxyz',8)+
replicate('23456789',9)+
replicate('@#$^*+=<>?',4)

select @len = len(@chars)-1

select @cnt = 0, @pass = ''

while @cnt < @pw_len
	begin
	set @cnt = @cnt + 1
	select	@pass   = @pass + 
		substring(@chars,convert(int,rand()*@len),1)
	end
	
select pass = @pass


Output:

pass          
------------- 
r8e4FfvMZDrX<

(1 row(s) affected)



quote:
Originally posted by blindman

Here is one I just cooked up:
select	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33) + 
	char(94 * RAND() + 33)



CODO ERGO SUM
Edited by - Michael Valentine Jones on 12/15/2005 17:44:30
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 12/15/2005 :  20:44:08  Show Profile  Reply with Quote
Getting back to the original password generation challenge, this code generates the 8 character random password containing upper, lower, numeric and special characters with only four statements.

declare @ch varchar (8000), @ps  varchar (10)

select @ps = '', @ch =
replicate('ABCDEFGHJKLMNPQURSUVWXYZ',8)+replicate('23456789',9)+
replicate('abcdefghjkmnpqursuvwxyz',8)+replicate('@#$^*+=<>?',6)

while len(@ps)<8 begin set @ps=@ps+substring(@ch,convert(int,rand()*len(@ch)-1),1) end

select [Password] = @ps


quote:
Originally posted by SamC

I've seen JavaScripts and C do this... how about SQL?

Generate a unique 8 character password using SQL containing upper, lower, numeric and special characters using the fewest possible statements.

edit: Maybe I meant "random" not "unique"...


CODO ERGO SUM
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts
Posted - 12/15/2005 :  20:59:54  Show Profile  Reply with Quote
quote:
Originally posted by Michael Valentine Jones


declare @ch varchar (8000), @ps  varchar (10)

select @ps = '', @ch =
replicate('ABCDEFGHJKLMNPQURSUVWXYZ',8)+replicate('23456789',9)+
replicate('abcdefghjkmnpqursuvwxyz',8)+replicate('@#$^*+=<>?',6)

while len(@ps)<8 begin set @ps=@ps+substring(@ch,convert(int,rand()*len(@ch)-1),1) end

select [Password] = @ps
Is this a great forum or what?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 12/15/2005 :  22:23:52  Show Profile  Reply with Quote
I have to stop playing with this. This does it in one statement.

select
	[Password] =
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)
from
	(select ch =
	  replicate('ABCDEFGHJKLMNPQURSUVWXYZ',8)+
	  replicate('abcdefghjkmnpqursuvwxyz',8)+
	  replicate('23456789',9)+replicate('@#$^*+=<>?',7) ) a

Output:

Password 
-------- 
j+vPGNB4

(1 row(s) affected)


CODO ERGO SUM
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2039 Posts
Posted - 12/16/2005 :  03:16:43  Show Profile  Reply with Quote
Where's Igor when you need him, I bet he has an answer to this


steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2039 Posts
Posted - 12/16/2005 :  03:21:45  Show Profile  Reply with Quote
quote:
select
[Password] =
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-1),1)
from
(select ch =
replicate('ABCDEFGHJKLMNPQURSUVWXYZ',8)+
replicate('abcdefghjkmnpqursuvwxyz',8)+
replicate('23456789',9)+replicate('@#$^*+=<>?',7) ) a


More seriously, I think there is a minor bug in this as when I was just playing around with it one of the results I got was a NULL

I just need to work out how now!

<EDIT> It's when rand() returns 0 (or close enough that it is interpreted as 0)

so something like

substring(ch,convert(int,(rand()+1)*len(ch)-1),1)+

works I think

</EDIT>

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!
Edited by - elwoos on 12/16/2005 03:39:17
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 12/16/2005 :  04:38:19  Show Profile  Reply with Quote
quote:
More seriously, I think there is a minor bug in this as when I was just playing around with it one of the results I got was a NULL

quote:
It's when rand() returns 0 (or close enough that it is interpreted as 0)

but substring(ch, -1, 1) will not return NULL but empty string. So the effect will be the password generated is 7 chars intead of 8 (if only one of the rand() returns 0).

I tried running the codes 1 mil times and did not get NULL password at all however, i do get several 7 chars password generated.

-----------------
[KH]
Learn something new everyday
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 12/16/2005 :  07:45:11  Show Profile  Reply with Quote
Here it is using NEWID() to generate the random number. Don't feel like figuring out that RAND() problem.

I tested this code with 10 million passwords, and got 8 character passwords each time. If there is a bug in this one, it's not showing up very often.


select
[Password] =
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)
from
(select ch =
replicate('ABCDEFGHJKLMNPQURSUVWXYZ',8)+
replicate('abcdefghjkmnpqursuvwxyz',8)+
replicate('23456789',9)+replicate('@#$^*+=<>?',7) ) a


CODO ERGO SUM
Edited by - Michael Valentine Jones on 12/16/2005 07:55:22
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts
Posted - 12/16/2005 :  07:56:41  Show Profile  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

select
[Password] =
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)
from
(select ch =
replicate('ABCDEFGHJKLMNPQURSUVWXYZ',8)+
replicate('abcdefghjkmnpqursuvwxyz',8)+
replicate('23456789',9)+replicate('@#$^*+=<>?',7) ) a
What is your rationale for weighting?

For equal weighting of UPPER, lower, numeric and special, seems like the replication would be:

select
[Password] =
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)+
substring(ch,(abs(convert(int,convert(varbinary(20),newid())))%len(ch))+1,1)
from
(select ch =
replicate('ABCDEFGHJKLMNPQURSUVWXYZ',4)+
replicate('abcdefghjkmnpqursuvwxyz',4)+
replicate('23456789',12)+replicate('@#$^*+=<>?',10) ) a

This presents about 100 UPPER, 100 lower, 96 numeric and 100 special.

Right? <scooby>Harrruuuhhh?</scooby>
Edited by - SamC on 12/16/2005 08:02:38
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000