PHP Programming

Sometimes it’s helpful to take the handling of the passwords away from PHP and let the database do all the hard work. With the help of triggers and a simple MySQL function its possible to generate a salt and a hash from a plain text string. Doing password security this way makes it harder for the PHP programmers to make a mistake and ensures that passwords encoded as plain text will never appear in the database.

Lets start by creating a table to store users email addresses, passwords and salts.

CREATE TABLE `users` (
`email` VARCHAR( 255 ) NOT NULL ,
`salt` VARCHAR( 40 ) NOT NULL ,
`password` VARCHAR( 32 ) NOT NULL ,
PRIMARY KEY ( `email` )
) ENGINE = InnoDB

Now lets create a function to generate a random string for us – this will be our salt

CREATE FUNCTION `make_salt`(len TINYINT UNSIGNED) RETURNS varchar(255) CHARSET latin1
BEGIN

DECLARE salt varchar(255);
DECLARE i TINYINT UNSIGNED;

SET i = 0;
SET salt = ”;

WHILE i < len DO
SET salt = CONCAT(salt, CHAR(FLOOR(40 + (RAND() * 210)+1)));
SET i = i + 1;
END WHILE;

RETURN salt;

END//

Finally lets insert 2 triggers, one for the insert, one for the update. This will encrypt all passwords sent to the MySQL table and also generate salts for them.

CREATE TRIGGER encrypt_password_insert BEFORE INSERT ON users
FOR EACH ROW BEGIN

SET NEW.salt = make_salt(40);
SET NEW.password = SHA1(CONCAT(NEW.salt, NEW.password, NEW.email));

END;

The update trigger will re-encrypt the password and re-generate the salt when the table is updated

CREATE TRIGGER encrypt_password_update BEFORE UPDATE ON users
FOR EACH ROW BEGIN

SET NEW.salt = make_salt(40);
SET NEW.password = SHA1(CONCAT(NEW.salt, NEW.password, NEW.email));

END;

Simple eh? You might well wonder why I’ve hashed their email along with the password, this forces the programmer to ask the user to re-specify their password when they change their email address. The salt function ensures that every time they update their password or email that the salt will also change. Lets look at the results of the following insert on the table

INSERT INTO users SET email=’john@malcom.com”, password=”hello world”;

If you now look in at the user john@malcom.com you’ll see that their password has been turned into the SHA1 hash of the salt field (which is now populated) and the password and email. It should look something like this:

82b6f04c63640a2dd5da7650d36001b4a5f10707

Now if you want to verify that the password / username you can simple do the following

SELECT * FROM users WHERE email=’john@malcom.com’ AND SHA1(CONCAT(salt, ‘hello world’, email))

If the number of returned rows is 1 then you can grant the user access.

This approach to password security is fine as long as the connection between PHP and MySQL is secure. If your connecting to a remote MySQL server over an un-secure connection then you’ll want to hash your passwords before they are sent out. Its also worth mentioning that the password may be visible on the server when viewing processes.