Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 The Yak Corral
 Password Generation Challenge

Author  Topic 

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-15 : 15:58:34
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"...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-12-15 : 16:00:13
SELECT CAST(CAST(newid() as binary(16)) as varchar(8))

What do I win?
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-15 : 16:00:39
Completely random?
How about bonus points if the resulting passwords are extremely difficult to type?
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-15 : 16:02:37
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-15 : 16:03:40
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
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-15 : 16:18:55
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)

7020 Posts

Posted - 2005-12-15 : 16:42:49
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
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-15 : 16:50:16
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

3467 Posts

Posted - 2005-12-15 : 16:53:13
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)

7020 Posts

Posted - 2005-12-15 : 16:54:17
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

3467 Posts

Posted - 2005-12-15 : 16:57:19
Michaels does the job, but it's long. Some great ideas though.
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-15 : 16:59:04
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-15 : 17:17:03
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-15 : 20:44:08
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

3467 Posts

Posted - 2005-12-15 : 20:59:54
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)

7020 Posts

Posted - 2005-12-15 : 22:23:52
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
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-12-16 : 03:16:43
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
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-12-16 : 03:21:45
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!
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-16 : 04:38:19
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)

7020 Posts

Posted - 2005-12-16 : 07:45:11
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
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-16 : 07:56:41
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>
Go to Top of Page
    Next Page

- Advertisement -