How to store passwords in a database

Often we write applications that must perform users and permissions validations. In these cases it is rather common that the developers include fields like 'User' and 'Password' in the users table. Also happens that the passwords are stored in plain text, which brings security problems, as we will see next.
Database administrators should NOT have access to the passwords. Also, if an attacker gains access to the database, he'll get a very easy way to get user's passwords.
Besides, depending on the database configuration, it is possible that when we query "SELECT * FROM USERS WHERE USER='my_user' AND PASSWORD='my_password';", the password is case insensitive. This facilitates the attackers to test different passwords because the case (upper and lower) is not being validated.
Sometimes the developers encrypt the passwords using some encryption algorithm. Care must be taken for the following reasons:
- In general these algorithms are bidirectional, which means that if we have the encryption key, we can get the original password from the ciphered text.
- Good algorithms are well known and checked by the community of developers and mathematicians. You must not trust those who offer 'magic' formulas or do not want to make the algorithm public.
Solution:
What we must do is to hash the password (e.g. using SHA, SHA1, SHA2, MD5, etc) to obtain a scrambled code from which the original text cannot be obtained. This must be done each time the user supplies a password, be it when logging in, or when the user modifies his password. Remember to store the result of hashing the password supplied by the user in the database.
When we look for the user, the SQL query would look like this: "SELECT * FROM USERS WHERE USER='my_user' AND PASSWORD='5eb942810a75ebc850972a89285d570d484c89c4';".
Note: before, the user could ask the administrators for the passwords if he forgot it, now, you must implement a password blanking routine for these cases.
Hope this helps.

Cheers!

Versión en español

Technorati tags: database security sql passwords