Select a list of STRONG passwords from password column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Select a list of STRONG passwords from password column?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-12 : 11:20:19

Gooser writes "I need to select a list of passwords from my USERS table, PASSWORD column, only those which are Strong Passwords, in that they meet ALL OF the following criteria.

1. Must be 8 characters in length (or more)
2. Must contain AT LEAST one Uppercase letter
3. Must contain AT LEAST one lowercase letter
4. Must contain AT LEAST one digit (0-9)
5. Must contain AT LEAST one Special Character from this list (`~!@#$%^&*()_-+={[}]|\:;"'<,>.?/)

I cannot figure out the WHERE clause...Please Help,

Thanks,
v/r
Gooser"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-12 : 11:44:44

Well.. have you even tried?


SELECT pw FROM tbl
WHERE len(pw) => 8
AND

You will probably need to use charindex or patindex (Check BOL for them)

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-07-12 : 12:09:55

You might play around with jamming this into a function like this. It would cool if you added a FailureReason return to this resultset, to find out which rule failed the strength check.

create function dbo.ufn_CheckPassword (@Password varchar(25))
returns bit
as 
begin
declare	@SpecialChars varchar(255),
		@Place int,
		@Len int

set @SpecialChars = '$%^&*()_-+={[}]|\:;"''<,>.?/'

select	@Len = LEN(@Password),
		@Place = 1

	-- check for len / range of chars
	if	@Password not like '%[0-9]%' 
	or	@Password not like '%[A-Z]%'
	or	@Password not like '%[a-z]%'
	or	len(@Password) < 8
	begin
		return 0
	end

	-- check for special chars 
	-- exit when we find first special char because other conditions have already passed
	while @Place <= @Len 
	begin 
		if charindex(substring(@SpecialChars, @Place, 1), @Password) > 0 
			return 1
		set @Place = @Place + 1
	end

return 0
end




-- example usage
declare @Users table (UserName varchar(25), Password varchar(25))
insert into @Users
	select	'Nathan', '9Aa$'	union
	select	'John', 'somelongpassword' union
	select	'Adam',	'9Aa$....'	union
	select	'Natalie', null		 

select UserName, dbo.ufn_CheckPassword(Password)
from @Users

Nathan Skerl

Gooser
Starting Member

15 Posts

Posted - 2006-07-12 : 12:44:56

After much trial-and-error, and Googling, I came up with the following query:


SELECT count(*) as [Strong Passwords]
   FROM <table_containing_user_information>
   WHERE  (LEN(Password) >= 8)
      AND (Password LIKE '%[0-9]%')
      AND (Password COLLATE Latin1_General_BIN LIKE '%[A-Z]%')
      AND (Password COLLATE Latin1_General_BIN LIKE '%[a-z]%')
      AND ((Password LIKE '%[~`!@#$%^&*()_+-={}|\:";<>E[E],.?/]%' Escape 'E')
           OR (Password LIKE '%[[]%')
           OR (Password LIKE '%[]]%'))

Remarkable results from this query. If you don't make the user do the right thing, the user WILL NOT do the right thing. Check out the results of this script:


SELECT count(*) as [Strong Passwords]
   FROM <table_containing_user_information>
   WHERE  (LEN(Password) >= 8)
      AND (Password LIKE '%[0-9]%')
      AND (Password COLLATE Latin1_General_BIN LIKE '%[A-Z]%')
      AND (Password COLLATE Latin1_General_BIN LIKE '%[a-z]%')
      AND ((Password LIKE '%[~`!@#$%^&*()_+-={}|\:";<>E[E],.?/]%' Escape 'E')
         OR (Password LIKE '%[[]%')
         OR (Password LIKE '%[]]%'))

SELECT count(*) as [total Users]
   FROM <table_containing_user_information>

SELECT count(*) as [Four Digits (10000 possibilities)]
   FROM <table_containing_user_information>
   WHERE (Password LIKE '[0-9][0-9][0-9][0-9]')

SELECT count(*) as [Three Digits (1000 possibilities)]
   FROM <table_containing_user_information>
   WHERE (Password LIKE '[0-9][0-9][0-9]')

SELECT count(*) as [Two Digits (100 possibilities)]
   FROM <table_containing_user_information>
   WHERE (Password LIKE '[0-9][0-9]')

SELECT count(*) as [One Digit (10 possibilities)]
   FROM <table_containing_user_information>
   WHERE (Password LIKE '[0-9]')

SELECT count(*) as [Four Letters (456976 possibilities)]
   FROM <table_containing_user_information>
   WHERE (Password LIKE '[a-z][a-z][a-z][a-z]')

SELECT count(*) as [is ok]
   FROM <table_containing_user_information>
   WHERE (Password LIKE 'ok')

SELECT count(*) as [is password]
   FROM <table_containing_user_information>
   WHERE (Password LIKE 'password')

SELECT count(*) as [Three Letters (17576 possibilities)]
   FROM <table_containing_user_information>
   WHERE (Password LIKE '[a-z][a-z][a-z]')

SELECT count(*) as [Two Letters (676 possibilities)]
   FROM <table_containing_user_information>
   WHERE (Password LIKE '[a-z][a-z]')

SELECT count(*) as [One Letter (26 possibilities)]
   FROM <table_containing_user_information>
   WHERE (Password LIKE '[a-z]')

Results:


Strong Passwords
7

total Users
8914

Four Digits (10,000 possibilities)
247

Three Digits (1000 possibilities)
6

Two Digits (100 possibilities)
1

One Digit (10 possibilities)
0

Four Letters (456976 possibilities)
438

is ok
2

is password
45

Three Letters (17576 possibilities)
63

Two Letters (676 possibilities)
7

One Letter (26 possibilities)
0

Seven of nearly nine-thousand users have a strong password! Makes me want to throw up.

Luckily this is a cheesy database and they are only granted select permissions, but jeez, is there no accountability?

Are people really that clueless? I wonder, of the people with four-digit passwords, how many of these people have the same four numbers as their ATM PINs, bank PINs, home alarm PINs, etc.?

Tweak this and try it in YOUR databases. If you don't force the users to have good passwords, you may be surprised what you find they are using.

v/r
Gooser

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-12 : 12:47:34

Great reading! Thanks Gooser

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-07-12 : 12:54:16

The fact that they are being stored in plain text is also a concern

Too bad you cant write a query to find the number of users who write their password on a post it and stick it to the side of their monitor!

Nathan Skerl

Gooser
Starting Member

15 Posts

Posted - 2006-07-12 : 13:09:07

Or, write a stored procedure that forces them to at-least stick it to the bottom of their keyboard.

v/r
Gooser

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-12 : 15:04:14

Gooser: Awesome! And NEVER underestimate the user. They can hose a database faster than you can think of it.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources