1

I'm trying to create a password constraint that must contain:

-at least 1 Upper case

-at least 1 number

-at least 5 characters long

I have searched a lot and i just can't make it work

for example, i have tried this

(len([PASSWORD])>(5) AND [PASSWORD] like '%[0-9]%' AND [PASSWORD] like '%[A-Z]%')

now, it will accept it if the password contains a number, but it will also accept it when the password contains no capitals at all, or only capitals, this confuses me, and i've tried many combinations

[password] LIKE '%[^a-zA-Z0-9]%'

that also didn't work, i have tried many code snippets from stackoverflow and people have marked it as answered, this makes me wonder, is it a problem on my part or am i doing something wrong? i am very confused right now

(len([PASSWORD])>(5) AND [PASSWORD] like '%[A-Z]%' AND [PASSWORD] like '%[0-9]%')

this should not allow the password: "wefwefew123" right? well, it does. it does not accept "wefwefew" though, so it does work with 0-9, what's wrong with the A-Z?

It's probably an dumb mistake and i will probably feel dumb after, but i can't solve it on my own, do keep in mind I'm no pro ;)

stefan
  • 165
  • 1
  • 15
  • 3
    Have you checked if the collation on your table/database is set to case sensitive? – Jeremy C. May 26 '15 at 08:23
  • 7
    Storing passwords is usually a really bad plan - it's usually better to store (salted) password hashes - which as a bonus means you don't have to set a maximum password length. The password policy is, then, usually better enforced in the application. – Damien_The_Unbeliever May 26 '15 at 08:25
  • 1
    i followed this http://stackoverflow.com/questions/3296428/changing-sql-server-collation-to-case-insensitive-from-case-sensitive and it turned out to be case insensative – stefan May 26 '15 at 08:27
  • @Damien_The_Unbeliever Yes i was planning on hasing it later, i just wanted to make this work first – stefan May 26 '15 at 08:28
  • Watch [this video](https://www.youtube.com/watch?v=8ZtInClXe1Q), and if you still think storing passwords in your database is a good idea, watch it again. – Zohar Peled May 26 '15 at 08:29
  • 1
    @stefan if you are going to hash it later then there is no point in putting this logic on the database side, put it in your application – Jeremy C. May 26 '15 at 08:29

1 Answers1

0

Try using a Case Sensitive Collation to help you out and compare password with a lower case version of it like below using your code:

(len([PASSWORD])>(5) AND [PASSWORD] like '%[0-9]%' 
AND [PASSWORD] <> Lower([PASSWORD]) COLLATE Latin1_General_CS_AI)

Or my own example would be like this:

Declare @Password varchar(20) = 'Hello'
if @Password <> Lower(@Password) COLLATE Latin1_General_CS_AI
    PRINT 'Upper'
else
    PRINT 'lower'
Christian Barron
  • 2,695
  • 1
  • 14
  • 22
  • I'm trying to have a combination of uppercase and number, this code accepts when its only lowercase example: Password1 = correct password1 = incorrect Password = incorrect – stefan May 26 '15 at 09:27
  • Apologies I had left in the = from my testing. It was meant to be <> The updated should work now – Christian Barron May 26 '15 at 09:31