1

how to test password strength using SQL ?

I have a legacy table with passwords, and i need to check test password strength in SSMS for once and correct them.

the requirements for the current system are:

  1. at least 2 lowercase characters ( abcdefg ... )
  2. at least 2 uppercase characters ( ABCDEFG ... )
  3. at least 2 numbers ( 123456 ... )
  4. at least 2 wild characters ( ~!@#$%^& ... )
  5. minimum length must be 8 characters

thank you

Dan
  • 10,480
  • 23
  • 49
armen
  • 1,253
  • 3
  • 23
  • 41
  • Validate in front end – Linga Feb 12 '14 at 10:15
  • there is no front end for this case – armen Feb 12 '14 at 10:16
  • And how it supposed to work in theory ? – Alexander Feb 12 '14 at 10:19
  • 1
    If there is no front end then what if your above specified criteria not satisfied ? i mean What you are trying to achieve ?? – Krishnraj Rana Feb 12 '14 at 10:21
  • there is an old table with passwords, and i need to check test password strength in SSMS for once and correct them – armen Feb 12 '14 at 10:22
  • 1
    Why was this question put on hold? The question is very simple, and does not need further information. It could be reworded as: "How do you use SQL to check a varchar column for the following requirements ...?". See my answer below. – Dan Feb 12 '14 at 10:53
  • @ling.s, OGHaza, DontVoteMeDown, Mario and Saurabh - what is exactly wrong with my question ? what exactly didn't you understand from the given question ? help me understand what I did wrong ! as you can notice my question was answered already. is this some kind of power demonstration ? please don't forget what stackoverflow is about ! – armen Feb 13 '14 at 08:00

1 Answers1

3

As others have mentioned, this kind of check is MUCH better suited for the application responsible of writing the data into the database in the first place. One reason is that SQL is downright horrible for advanced string operations.

But if you really must do it using SQL, something like this should point you in the right direction:

SELECT Password,
    CASE WHEN 
        Password COLLATE Latin1_General_BIN LIKE '%[a-z]%[a-z]%' AND
        Password COLLATE Latin1_General_BIN LIKE '%[A-Z]%[A-Z]%' AND
        Password LIKE '%[0-9]%[0-9]%' AND
        Password LIKE '%[~!@#$%^&]%[~!@#$%^&]%' AND
        LEN(Password) >= 8
    THEN 1 ELSE 0 END AS Valid
FROM TableWithPasswords

The "Valid" column will contain a 1 if the password satisfies the criteria of the CASE statements, 0 otherwise. If you want to add more special characters to the 4th requirement remember to escape them properly, according to this: http://technet.microsoft.com/en-us/library/ms179859.aspx

Note the COLLATE statements - this is to ensure case sensitive comparisons for the LIKE operator.

Dan
  • 10,480
  • 23
  • 49
  • very close, but '11aAAA##' must return 0 instead of 1 ( one lowercase character instead of 2+ ). also `COLLATE Latin1_General_CS_AS` in the end gives me an error ( Incorrect syntax near the keyword 'COLLATE' ) – armen Feb 12 '14 at 10:41
  • I've edited my post. Could you try again please? This time, the COLLATE statement should ensure that each of the comparisons are case sensitive, and therefore '11aAAA##' should return 0, since the first comparison will be false. – Dan Feb 12 '14 at 10:48
  • i tried and it still does not work correctly. '11aAAA##' still returns 1. you can check it here : http://sqlfiddle.com/#!6/b11c0/1 – armen Feb 12 '14 at 10:56
  • 1
    According to [this post](http://stackoverflow.com/questions/15079210/how-do-i-perform-a-case-sensitive-search-using-like) you may need to change the collation to Latin1_General_BIN. I've edited my post. http://sqlfiddle.com/#!6/b11c0/2 – Dan Feb 12 '14 at 11:47