Does anyone know a good method for checking case sensitive user input using the control forms?
I would like to use Control forms to validate whether a user has entered at least one uppercase and one lowercase character, this is for validating password requirements.
By default SQL is case-insensitive. A common workaround is using:
COLLATE SQL_Latin1_General_CP1_CS_AS
I have tried to implement this but it does not seem to be working. Am I doing something wrong? Or is there another method available for implementing case sensitive comparisons?
My implementation
Best answer by Mark Jongeling
Hi Emiel,
After checking myself, I notice the collation you use does not work for this purpose. I tried this:
Both if statements should be False
For another purpose, cleaning table names, I have used the ASCII() function. It could also work for this. I rebuilded it a little for you. You can them make the changes needed to return the correct error(s).
declare @length int = len(@password) , @counter int = 1 , @correct int = 1 , @ascii_code int
--Go through the password, one by one character while @counter <= @length and @correct = 1 begin --Make a ASCII code of the string select @ascii_code = ascii(substring(@password, @counter, 1))
--What is allowed if @ascii_code between 48 and 57 or --Numbers @ascii_code between 65 and 90 or --Uppercase letters @ascii_code between 97 and 122 or --Lowercase letters @ascii_code = 95 --Underscore begin --Up the counter select @counter = @counter + 1 end else begin select @correct = 0 --Password incorrect end
end
if @correct = 1 begin select 'Correct' end else begin select 'Incorrect' end
After checking myself, I notice the collation you use does not work for this purpose. I tried this:
Both if statements should be False
For another purpose, cleaning table names, I have used the ASCII() function. It could also work for this. I rebuilded it a little for you. You can them make the changes needed to return the correct error(s).
declare @length int = len(@password) , @counter int = 1 , @correct int = 1 , @ascii_code int
--Go through the password, one by one character while @counter <= @length and @correct = 1 begin --Make a ASCII code of the string select @ascii_code = ascii(substring(@password, @counter, 1))
--What is allowed if @ascii_code between 48 and 57 or --Numbers @ascii_code between 65 and 90 or --Uppercase letters @ascii_code between 97 and 122 or --Lowercase letters @ascii_code = 95 --Underscore begin --Up the counter select @counter = @counter + 1 end else begin select @correct = 0 --Password incorrect end
end
if @correct = 1 begin select 'Correct' end else begin select 'Incorrect' end
To add to Mark’s answer, the reason it doesn’t work is because of the [A-Z] range in your LIKE comparison. These ranges appear to always be case-insensitive, but it does work if you write out the letters of the range.
Try out the following two queries and see how their results differ:
select 1 where 'abc' COLLATE SQL_Latin1_General_CP1_CS_AS like '%[A-Z]%'
select 1 where 'abc' COLLATE SQL_Latin1_General_CP1_CS_AS like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
It's important to note that the _AS part of this collation indicates that it is accent-sensitive. This means that é will not match e and therefore a password like ÀàààÉééé will not match either of your LIKE-comparisons, causing the password to be rejected. You might want to consider the Latin1_General_CS_AI collation instead.
