SQL Password encryption?

Question

In class we were trying to figure out how to write SQL code that would require a password to have at least one number and upper and lower case letters. Not even my professor could find out how so can anyone help?

Answer 1

This isn't exactly encrypting. What you are wanting to do is enforce a particular format for data consitancy. That would be done through a field constraint by passing the information between your front end application and then validates against the data field constraint and returns a value if the constraint is not met.

The Check constraint would look something like .....



CONSTRAINT user_password CHECK (password LIKE

'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][a-z]' OR

emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][a-z]')









Here is the information about constraints from Books Online:



Constraints allow you to define the way Microsoft® SQL Server™ 2000 automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.



Classes of Constraints

SQL Server 2000 supports five classes of constraints.



NOT NULL specifies that the column does not accept NULL values.





CHECK constraints enforce domain integrity by limiting the values that can be placed in a column.

A CHECK constraint specifies a Boolean (evaluates to TRUE or FALSE) search condition that is applied to all values entered for the column; all values that do not evaluate to TRUE are rejected. You can specify multiple CHECK constraints for each column. This sample shows the creation of a named constraint, chk_id, that further enforces the domain of the primary key by ensuring that only numbers within a specified range are entered for the key.



CREATE TABLE cust_sample

(

cust_id int PRIMARY KEY,

cust_name char(50),

cust_address char(50),

cust_credit_limit money,

CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )

)





Good luck!!

Answer 2

Well, perhaps I'm off base here but, one, that's not encryption and two, that would be done via some code outside of SQL and the database. I suppose you could do it as a stored proceduce but having your password validation code inside the DB would be bad design. Given you want a password to have an upper case letter, a lower case letter and at least one number this would need to be checked during registration. As such, your registration validation code/class should check this. A generally simple regular expression would accomplish what you want.



EDIT: Yeah, if this is for a new user or a password change you definately want your validation checking to take care of this. For example, your user class could have a method setPassword which calls a private method validateCredentials to ensure the user has a valid password (1+ numbers, 1+ upper case and 1+ lower case letters). Again, a regex would accomplish this.

Answer 3

Not sure I'm understanding this completely so forgive me if this is way off.



The sql statement query's the database. Any password protection would be approved BEFORE the query is submitted.



You would prompt for the password and if correct, submit a query to pull the protected information. Otherwise, display a message for the un-authorized attempt.

Answer 4

Use regular expressions to check for characters and numbers.

To "encrypt" after checking the password passes the char and number test, convert it to md5 or sha (sha is stronger) hash and save it to the db. When logging in convert what they type to the hash and compare to the database.

Answer 5

in basic terms one betta can stay in a 5 gallon tank, no different fis can stay to tell the story in a tank that small. "different than possibly for some very uncommon costly fish" neons do suitable in communities of seven or greater. bettas are carnivours and can be certain to consume the neons. additionally, neons could grow to be nippy and tear a bettas fins. tetras are sensative fish, they do no longer deal nicely with chemical, organic and organic or temperature shifts. those shifts take place in many situations in new and unastablished tanks. and additionally in small tanks, or overstocked tanks, or underfiltered tanks etc.