Comment On T-SQL "Random" Password "Generation"

At least it's better than setting all of the passwords to the user's name, which I've seen.

Hey now, this is much better than most systems that have a default password of 'password'.

A random guess... 3.4.2.5=120 unique passwords? You don't want to be running this on software with a huge user base :)

I think you could have stopped after the word "this". [;)]

150 passwords =  3 * 5 * 2 * 5

Why not just do an md5 hash of the current time?

:

Why not just do an md5 hash of the current time?

I'm sorry, I mean the current time as in seconds since the epoch.  

You have to love a system where mistyping your password by one letter gives you a high percentage chance of entering in the a correct password for someone else.

Interesting that the number of characters in the password is variable. Most random password generators, you would think, would employ a fixed number of characters. But that would imply thinking, of course.

select left(newid(),8)

I don't even understand how this could generate a different password every time. You select the same (A.a+B.b+C.c+D.d) columns every time from a static carthesian monster, and I don't see any 'where' clause so I just don't get this :p What SQL dialect is this?

In Fact! what does it matter?

First... Do you know how your init Passworts are created?
Second... Do you realy want to try to guess your neighbors password in this case (not as a general question)?

Maybe it is a little wtf, but at all it seems quite interesting (and fast). Not the worse method to create first-time passworts. How does he decide wich row to choose?

yeah, something's not right (or maybe the example has been highly
edited). the above just inserts all possible "passwords" into an
existing table, it is not returning anything or generating a single
random value .... doesn't make much sense ...

It generates 150 lines containing strings looking like passwords :)

no randomness

Maybe he just uses this as the source of the 150 available passwords.  Then when someone needs a password he selects one of these passwords that isn't already assigned.  Still not very random.  Still shitty.

As the author of the sql statement, I think I have to level at least some defense.

#1 point - this was run once, throw away code - it was never intended to be seen by anyone, nor was it to be used again.  Unfortunatly (?) Alf wandered by my desk as I was running it...

Beyond that, this was mostly an attempt to see how well / poorly the multi way cross join would work, which it does, though obviously not in any kind of a general way.

To answer someones question of what dialect, this was run on SQL serer 2000.

That means they store the passwords as clear text ????

:

As the author of the sql statement, I think I have to level at least some defense.

#1 point - this was run once, throw away code - it was never intended to be seen by anyone, nor was it to be used again.  Unfortunatly (?) Alf wandered by my desk as I was running it...

Beyond that, this was mostly an attempt to see how well / poorly the multi way cross join would work, which it does, though obviously not in any kind of a general way.

To answer someones question of what dialect, this was run on SQL serer 2000.

Cool! Thanks for coming on and letting us know WTF this code was about[;)]

Is this a first that the person who actually wrote the code came on and acknowledged the fact?

Instead of asking "try guessing how many rows this will return without running the query" the author should have asked "try guessing
how much this will return if you paste 3*5*2*5= to calc". Obviousely
some people that post wtf's always think they are the next big thing
and all the others are stupid. This IS a wtf way to generate random
passwords, but I would rather like to see the code that actually gets
the password out of result set.

The big WTF (besides the 150 result set, and SQL generated "random" password) is - the INSERT...  love when people do weird crap, or try to reinvent the wheel with their own 'hi tech' ideas... [:#]

 

INSERT INTO 
dbo.attendee_users (password)
SELECT a.A + b.B + c.C + d.D AS Password
FROM 
  (SELECT 'a' AS A
   UNION
   SELECT 'r'
   UNION
   SELECT 'i') a 
CROSS JOIN
  (SELECT '5' AS B
   UNION
   SELECT '2'
   UNION
   SELECT '19'
   UNION
   SELECT '4'
   UNION
   SELECT '87a3') b 
CROSS JOIN
  (SELECT 'vgh' AS C
   UNION
   SELECT '8R7y') c 
CROSS JOIN
  (SELECT '6' AS D
   UNION
   SELECT '7'
   UNION
   SELECT '8'
   UNION
   SELECT '9'
   UNION
   SELECT '1') d

 

I think the biggest WT... is how the code author saw this.  LOL

Scenario #1

Alf (thinking): SCORE! They posted the code I sent in (outloud) Hey, <Anonymous co-worker>, check this out... I sent some code to this site, and they posted it!

Anonymous Coworker: Dude, I wrote that, don't you remember?... m0tard.

Scenario #2

Alf (thinking): SCORE! They posted the code I sent in (outloud) Oh hi, <Anonymous co-worker>... how long have you been standing there?

Scenario #3

Alf (thinking): SCORE! They posted the code I sent in

<Anonymous co-worker> (who also visits this site): Alf!  May I have a word with you please?

If this query were the right approach to the problem (and don't get me
wrong, I don't think it is), all of those UNIONs should be replaced
with UNION ALL so it doesn't waste time trying to remove duplicate
records when there are none.

#3 is closest, but I knew befoer hand that he'd sent it in.

What better way to needle your cow-orkers then to post their bad code to sites they read [;)]

Most random password generators, you would think, would employ a fixed number of characters. But that would imply thinking, of course.

As always, there's a comment from someone that's a bigger WTF than the post. Ignoring the implementation details, a random-length password generator (as long as it's above a certain length) is more secure than a fixed length one. Understanding that would imply thinking, of course.

now now... c'mon... this place is too much fun to start flaming

[:)]

Chris Butler:

The big WTF (besides the 150 result set, and SQL generated "random" password) is - the INSERT...  love when people do weird crap, or try to reinvent the wheel with their own 'hi tech' ideas...

Isn't it obvious? He is generating a table (table attendee_users of scheme dbo) with one column (password) full of semi-random passwords to use later on when adding users. As he said, it's run-once code. And as always, if you put this code into context, it's less WTF-ish than people assume.

The real WTF is how this board software manages to suck so hard.

As always, there's a comment about a comment from someone that's a bigger WTF than the post.

newid() is the way to go!

s always, there's a comment about a comment from someone that's a bigger WTF than the post.

Hey now, I wasn't defending this implementation - I was attacking the comment that restricting your code to a fixed length password somehow involved "thinking".

The right way is to use the Windows API function CryptGenRandom and map the result onto a given character set. Base n computations are a little tricky but perfectly feasible. If you're calling from Visual Basic 6, VBScript or JScript, look at CAPICOM's Utilities.GetRandom method. For .NET Framework applications, use System.Security.Cryptography.RandomNumberGenerator. Use this random number generator when you need a truly secure unpredictable rarely-repeatable result. Don't use it if you're simply performing simulations or for games programming, it's much slower than the regular random number generator.

If you use a fixed length password, your generated passwords will always have a certain amount of entropy. I don't see any reason for weakening this. A short password always takes less time to crack using brute-force methods than a longer one. Very long passwords are difficult to remember.

While on the subject you should try to avoid storing passwords in plain text or in a reversible form (i.e. using a kind of encryption where you could feed in the encrypted text and retrieve the original plain text).

If you use a fixed length password, your generated passwords will always have a certain amount of entropy. I don't see any reason for weakening this.

Absolutely right. Now, if I have a generator that produces passwords between 10 and 14 characters, it is harder to crack than it is if the attacker knows it is always going to be 10 characters long. It's not about weakening anything at all - it's about the pure unalterable fact that a variable-length generated password is more secure than a fixed-length one.
To be more specific, it's the difference between brute forcing 52^10 possibilities and brute forcing 52^10 + 52^11 + 52^12 + 52^13 + 52^14 possibilities. Quite a substantial difference (please note the 52 is for illustrative purposes only).

(So I figured if I'm going to be posting, I may as well register)

Close - the table also had an identity field with an incrementing value that represented the userid.

The userids/passwords were intended to be given out at conference so that attendees could access a service that realistically didn't require all that much security, and for which the binding between the login process and the attendee_users table was already implemented.  Probably for a previous years conference.

So there I was with a 5 min task of make 150 new users with unique passwords, and send the passwords to the PM...

Clearly that not code to use in anything that needs to be maintained, but since the code was never to have been saved, why not have some fun writing in a way that I had never attempted...

While on the subject you should try to avoid storing passwords in plain
text or in a reversible form (i.e. using a kind of encryption where you
could feed in the encrypted text and retrieve the original plain text).

So in other words, one should try to avoid the use of
challenge-response protocols that avoid putting the password in
cleartext over a network?



There are tradeoffs to every cryptographic decision. I've made plenty
of decisions to weaken security because the increase in functionality
or ease of use or whatever gave a bigger advantage than the decrease in
security gave harm.

:

As the author of the sql statement, I think I have to level at least some defense.

#1 point - this was run once, throw away code - it was never
intended to be seen by anyone, nor was it to be used again. 
Unfortunatly (?) Alf wandered by my desk as I was running it...

Beyond that, this was mostly an attempt to see how well / poorly the
multi way cross join would work, which it does, though obviously not in
any kind of a general way.

To answer someones question of what dialect, this was run on SQL serer 2000.

Software live time is surely much longer than what the author expected, obviously in this case [+o(]

He-he-he... one of the best examples where the comment is a bigger WTF
than the WTF itself. I thought such stupidity did not exist

oooh - this could get interesting - I am sure Alf is trying to get all his checkin's password protected

Yes, the number of passwords between 10 an 14 characters long is much more than that of just 10 characters long.
But you are not comparing the right numbers. Just generating passwords of exact 14 characters is not that much more than the number of passwords of 10 to 14 characters. Do your math!

Number of passwords of 14 characters is 52^14 =
1.056.931.425.538.820.521.590.784
Number of passwords of 10-14 characters is 52^14+...+52^10 =
1.077.655.568.303.207.081.836.544
This is only 2% more!!!

So there is no REAL need to do variable-length generation. Just use MORE characters.

Just generating passwords of exact 14 characters is not that much more than the number of passwords of 10 to 14 characters

Should be not that much less ofcourse.

Mike Dimmick:

The right way is to use the Windows API function CryptGenRandom and map the While on the subject you should try to avoid storing passwords in plain text or in a reversible form (i.e. using a kind of encryption where you could feed in the encrypted text and retrieve the original plain text).

You should try to avoid storing the passwords in a reversible encrypted form as well - the best way to go is to 'salt' the password and then hash it. That means that even if your database would be hacked, the passwords would still be reasonably invisible for the hacker.

I like it. If I had a personal password scheme (who doesn't) that I wanted to make more random or secure, I could use this, replacing the strings with my own fragments, to get a bunch of passwords that I could remember, yet still be somewhat difficult to guess. Of course sticking them into a table would be a bad idea. Pasting the SQL on the internet would be another bad idea. Letting my coworker see this code would be the worst idea. I'm thinking that you keep this code in a text file, and when the 30/60/90 day cycle comes by that requires you to change your password, you run this and randomly pick a line as your new password. It works for 150 * interval days, then it is time to change the string fragments with parts of your dog's name, address, EX-coworkers name, etc..

Goudinov:

Scenario #3

Alf (thinking): SCORE! They posted the code I sent in

<Anonymous co-worker> (who also visits this site): Alf!  May I have a word with you please?

And bring your personal effects with you, please.

Sounds reasonable to me. Throw away code like that is always good to test things and have some to show for at the end.

Goudinov:

Scenario #3

Alf (thinking): SCORE! They posted the code I sent in

<Anonymous co-worker> (who also visits this site): Alf!  May I have a word with you please?

And bring your personal effects with you, please.[:@]

:

Yes, the number of passwords between 10 an 14 characters long is much more than that of just 10 characters long.
But
you are not comparing the right numbers. Just generating passwords of
exact 14 characters is not that much more than the number of passwords
of 10 to 14 characters. Do your math!

Number of passwords of 14 characters is 52^14 =
1.056.931.425.538.820.521.590.784
Number of passwords of 10-14 characters is 52^14+...+52^10 =
1.077.655.568.303.207.081.836.544
This is only 2% more!!!

So there is no REAL need to do variable-length generation. Just use MORE characters.

2% isn't much when you're talking about small numbers, but when the
numbers are huge it is fairly significant.  Wouldn't you like 2%
of a trillion dollars?



Also, variable password length is good simply from the standpoint of being less predictable.

Damn... tried to login as cjs, but none of the first 30 passwords have worked yet... only 120 to go.

Wow. Quite the firestorm here. Let me try to rephrase what I was saying so that it makes more sense.

In this code (which, we've already established, is not a normal password situation) I would think that it would make more sense to have all strings in each category the same length. You don't lose any combinations in this case because of the way that the algorithm is designed - you've got a fixed number to begin with. It just seemed more logical to me, at the time.

Not that I am saying it is a good idea to make all passwords the same length! Or that it is a good idea to generate all passwords in this manner!

Clearly there was not a great deal of thinking involved in my original post either...[8-)]

Damn it!....now I gotta change my password

It reminds me of SPACEBALLS -

That's amazing! I have the same password on my luggage!

Blue:

wrote:

Yes, the number of passwords between 10 an 14 characters long is much more than that of just 10 characters long. But you are not comparing the right numbers. Just generating passwords of exact 14 characters is not that much more than the number of passwords of 10 to 14 characters. Do your math! Number of passwords of 14 characters is 52^14 = 1.056.931.425.538.820.521.590.784 Number of passwords of 10-14 characters is 52^14+...+52^10 = 1.077.655.568.303.207.081.836.544 This is only 2% more!!! So there is no REAL need to do variable-length generation. Just use MORE characters.

2% isn't much when you're talking about small numbers, but when the
numbers are huge it is fairly significant.  Wouldn't you like 2%
of a trillion dollars?



Also, variable password length is good simply from the standpoint of being less predictable.

:

wrote:

Why not just do an md5 hash of the current time?

I'm sorry, I mean the current time as in seconds since the epoch.  

Wouldn't it be easier just to grab x length out of a uniqueidentifier?  Creating all the methods that you need to hash a string would probably end up being a WTF.