Collation
-
Hello! I'd like to check password policy in SP. Unfortunately, the DB collation is case insensitive. I try to use collation hint but it doesn't help me. What may be the cause of this behaviour or what I do wrong?
use master
go
SELECT CONVERT (varchar, DATABASEPROPERTYEX('master','collation'));
Declare
@Password VarChar (1024) = 'VV'
If @Password COLLATE SQL_Latin1_General_CP1_CS_AS like '%[a-z]%' COLLATE SQL_Latin1_General_CP1_CS_AS
Select 1
Else
Select 0
-
What you have here is an XY Problem; you think the problem is fixing the case sensitivity, but really the problem you need to fix is the design.
Your problem isn't your collation, it's the method of storing your passwords. You should be salting and hashing your values, and a hashed value is different depending on the case of the character. Change (fix) your design, and you'll also fix the problem.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Ok, let's put aside password storing. I just would like to check whether lower or upper case letters exists in a string and distinguish them.
-
Well, your collation has CI, which means Case Insensitive. CS means Case Sensitive. But do we really need to pretend that you aren't going to use this answer to store passwords in plain text? That's something you should never do.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I don't want to store pass in plain text. I would like to check it strength in DB and then hash it as I wrote in the first post.
- Peter Shilovich wrote:
I don't want to store pass in plain text. I would like to check it strength in DB and then hash it as I wrote in the first post.
Why check it in the DB? That means you have to pass the value in the SQL statement in plain text; that's a problem. For example, any on with a trace could see the value, and it would be captured in Extended Events. You should be validating it in the application.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
-- As said, do not store passwords.
-- But then again using upper and lower case as significant in passwords works counterproductive.
-- People do not think in upper en lower case, spelling alphabets do not 'understand' this.
--
-- But for educational reasons :
-- ben brugman 20190411
Declare @w varchar(300) = 'ben brugman'
Declare @v varchar(300) = 'Ben Brugman'
IF @w = @v BEGIN PRINT 'THESE ARE THE SAME' END ELSE PRINT 'NOT THE SAME'
IF CONVERT(varbinary, @w) = CONVERT(varbinary, @v) BEGIN PRINT 'THESE ARE THE SAME' END ELSE PRINT 'NOT THE SAME' -
Perhaps, I did not put the question clearly. The initial question is how to check whether the string contains lower case letters.
- Peter Shilovich wrote:
Perhaps, I did not put the question clearly. The initial question is how to check whether the string contains lower case letters.
That is far simpler:
SELECT V.String,
CASE V.String COLLATE SQL_Latin1_General_CP1_CS_AS WHEN UPPER(V.String) THEN 'No' ELSE 'Yes' END AS ContainsLowerCase
FROM (VALUES('This has lowercase letters'),('THIS HAS NO LOWERCASE LETTERS'))V(String);Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thanks!
-
As I know, there is no true REGEX in SQL Server. Is the method to solve the question with "like" and wildcard?
- Peter Shilovich wrote:
As I know, there is no true REGEX in SQL Server. Is the method to solve the question with "like" and wildcard?
You can use a different collation for that:
SELECT V.String,
CASE WHEN V.String COLLATE Latin1_General_100_BIN LIKE '%[a-z]%' THEN 'Yes' ELSE 'No' END AS ContainsLowerCase
FROM (VALUES('This has lowercase letters'),('THIS HAS NO LOWERCASE LETTERS'))V(String);Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Should this be considered as an error in earlier collations before 100?
-
This behavior:
SELECT V.String,
CASE WHEN V.String COLLATE Latin1_General_100_BIN LIKE '%[a-z]%' THEN 'Yes' ELSE 'No' END AS ContainsLowerCase
FROM (VALUES('This has lowercase letters'),('THIS HAS NO LOWERCASE LETTERS'))V(String);
SELECT V.String,
CASE WHEN V.String COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%[a-z]%' THEN 'Yes' ELSE 'No' END AS ContainsLowerCase
FROM (VALUES('This has lowercase letters'),('THIS HAS NO LOWERCASE LETTERS'))V(String);CS in the collation name means case sensitive
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply