Constraints for UserName and Password fields in tables using MS SQL

Question

I wish to put a constraint on the password field in my table "Administrator" .The constraint I want is that the password should not be less than 6 characters and should have mixed characters (numbers + numeric).

What is the KEYWORD for this constraint and what is the syntax . I have Googledit for some time ,I am in my office and I have to complete this task soon . I am using MS SQL

Here is what I have written,

CREATE TABLE Administrators
(
 Id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
 UserName varchar(255) NOT NULL,
 PassWord varchar(255) NOT NULL,
 Email varchar(255) NOT NULL
);

I would put this logic elsewhere and pass/store a correctly hashed version of the password. — Alex K., Feb 27 '14 at 13:27
You're storing passwords wrong. I don't care how "simple" and "small" a system you might have, bad practice is still bad practice and if you never bother to learn the right way, you'll always do it wrong. Start now. — alroc, Feb 27 '14 at 13:54
First, you need to have the application do the checks and hashing. If that's absolutely forbidden, then, knowing it is THE WRONG WAY TO DO THIS, use the checks listed in the answers and see [Is there a SQL implementation of PBKDF2?](http://stackoverflow.com/q/7837547/1967612). For PBKDF2-HMAC-SHA-512, change Password to BINARY(64), add Salt BINARY(16) (use [CRYPT_GEN_RANDOM](http://technet.microsoft.com/en-us/library/cc627408.aspx) to generate the salt), and store Iterations INT DEFAULT 10000 or so. Also read [How to securely hash passwords?](http://security.stackexchange.com/q/211/39623). — Anti-weakpasswords, Feb 28 '14 at 05:29

Crono · Answer 1 · 2014-02-27T15:08:32.667

1

If you absolutely must make this verification on the DB layer and don't care your passwords being stored as plain text, use a TRIGGER. It will give you maximum control over how passwords can be accepted or rejected. You can also use Shantanu Gupta's strategy. Again, only if you don't mind passwords stored as plain text.

Now, the right thing to do IMHO would be for you to make this validation in the business logic tier of your application and store the password as a hash.

But since you mentionned in the comments that you don't want the burden of learning how triggers works OR split your app into logical tiers, then you can always do the validation on the client. It's awfully wrong but if you're not willing to learn the right way to do things and you're at ease with selling a cheaply-made application to your customer, it could be the alternative you're looking for.

edited Feb 27 '14 at 15:08

answered Feb 27 '14 at 13:26

Crono

10,211
6
43
75

The project i am working is of a very small scale and i do not want to waste my time right now in learning Triggers ,It could be done later if asked for ,so please give an alternate,it wold be really appriciated :) – user3085866 Feb 27 '14 at 13:43
LOL isnt there other ways than such hard ways,Its a POS project i am working on ,these ideas would have been good,actually great if it was a project of high scale – user3085866 Feb 27 '14 at 13:48
If I may ask, why do you even bother with password enforcement policies if your project's scale is so small? :) – Crono Feb 27 '14 at 13:51
Because client has requested for it :( come on give me some idea i need it badly and stackoverflow is my only hope i guess – user3085866 Feb 27 '14 at 13:54
Okay, I can relate with that. :p If you're at ease with selling him an app that won't follow the industry's standards, then just do all the code on the client application. I hate it (and you should too) but it will work. Of course, as long as nothing else ever uses the database. – Crono Feb 27 '14 at 14:03
1

Part of your job is to advise the client regarding best practices around this sort of thing. Remember that if they know how to do this stuff, they probably wouldn't have hired someone from the outside to do it. Just because they "want" it doesn't mean that it's the right thing to do. Storing credentials for **anything** in plain text is inherently bad - how many security breaches need to happen before people start doing things right? I would question why it's so important that they have an inherently insecure system in the first place - there is no need for such a thing. – alroc Feb 27 '14 at 14:08
@alroc I was about to write something very similar. While I fully agree with you, the fact remains that if the client is stubborn (and god some are) then he will just pass the job on someone else who will be glad to write cheap code for the money. :/ I believe we *do* have the responsibility to inform the customer about standards and best practices, yes, but in the end he gets to decide. Unfortunately. At this point your only decision left is whether you're at ease putting your name on the code or not. Today I wouldn't, but back when I just started and had a kid to feed... hmmm. :p – Crono Feb 27 '14 at 14:13
1

If I were in that position, I'd refuse the job. Reason: If there is ever any *hint* of a security-related problem that comes out of such a system, I don't want my name anywhere near it. – alroc Feb 27 '14 at 14:17
I don't see the argument here. For a case like this, I think doing the validation in the business logic of the app would be neither particularly better nor worse than doing it in a trigger. This isn't a database integrity issue. Doing it in the business logic has the advantage that you can write straight-forward code to give the user a clean error message, rather than having to catch an exception coming back from the db. – Jay Feb 27 '14 at 14:44
The argument is about best practices. A password shouldn't be stored "as is" in a database. It should be up to the bl layer to know about policies and password hashing key, how to write the password in the data store and how to find it back. – Crono Feb 27 '14 at 14:48

Shantanu Gupta · Answer 2 · 2014-02-27T14:27:56.533

You can have a check constraint on your table and validation logic can be encapsulated in UDF. This way you can change logic anytime you want.

    CREATE TABLE CheckTbl (col1 varchar(20), col2 varchar(20));

    CREATE FUNCTION CheckPassword(@pass varchar(20))
    RETURNS int
    AS 
    BEGIN
       DECLARE @retval int

        if len(@pass)>6 and PATINDEX('%[0-9]%', @pass) >0 and PATINDEX('%[a-zA-Z]%', @pass) >0
        SET @retval = 1
        else
        SET @retval = 0

       RETURN @retval
    END;

ALTER TABLE CheckTbl
ADD CONSTRAINT CheckPasswordRules CHECK (dbo.CheckPassword(col1) >= 1 );

SQL Fiddle

Test cases

insert into CheckTbl (col1, col2) values('as33dasd', '') --success
insert into CheckTbl (col1, col2) values('33sdasd', '') --success
insert into CheckTbl (col1, col2) values('sdasd22', '') --success
insert into CheckTbl (col1, col2) values('33dasd', '') --fail requires length >6

Constraints for UserName and Password fields in tables using MS SQL

2 Answers2

SQL Fiddle