MySQL I : Encryption and Passwords

Encoding Passwords: PASSWORD()

The PASSWORD() function creates a non-decryptable hash of the given password.

INSERT INTO users VALUES (NULL, ‘user_name’, PASSWORD(‘user_pw’));

Resetting a password:

mysqladmin -u user_name password
#you will be prompted for the new password

 

The ENCRYPT() function creates a non-decryptable encrypted string.

INSERT INTO clients VALUES (NULL, ENCRYPT(‘client_name’));

Note that the ENCRYPT() function uses the Unix crypt() function, so it may not work in Windows (unless you’re using CygWin).

You can use the DES_ENCRYPT() function when you’re connecting over SSL instead, if necessary.

 

ENCODE() and DECODE() allow you to create encrypted (encoded) strings which can be decrypted. ENCODE() requires a “salt,” or a beginning encryption value. If you lose or forget your salt, you will never be able to DECODE().

INSERT INTO clients VALUES (NULL, ENCODE(‘client_name’, salt));

For instance:

INSERT INTO clients VALUES (NULL, ENCODE(‘Sun Microsystems’, ‘mysalt’));

DECODE() can decrypt these strings, provided you know the salt:

SELECT client_id, DECODE(client_name, ‘mysalt’)client_name FROM clients WHERE client_id=24;

 

Encryption

1. Create a new users table:
CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_pass CHAR(16),
user_name TINYBLOB,
PRIMARY KEY (user_id)
);

Note the TINYBLOB datatype. This will be used to hold encrypted binary information.
2. Add a user to the user name and password to the table, and encrypt them both Type:
INSERT INTO users VALUES ( NULL, PASSWORD(‘mypass’), ENCODE(‘jwilson’, ‘hello’) );

The password mypass is stored encrypted, and is the user name with, with encryption generated by a random seed.

View your results with:
SELECT * FROM users;
3. You can select on encoded password and user name information, by decoding:
This works:
SELECT user_id FROM users
WHERE (user_pass = PASSWORD(‘mypass’)) AND
(DECODE(user_name, ‘hello’) = ‘jwilson’);

This does not work:
SELECT user_id FROM users
WHERE (user_pass = PASSWORD(‘mypass’)) AND
(DECODE(user_name, ‘hel’) = ‘jwilson’);

Question: Why does the second query fail?

Note: Password information should generally be encrypted. Other information may or may not be appropriate to encode (SSN might be). Make sure you can decode anything that you encode. If you use the salt feature this can difficult to keep track of!