Database Password Hashing

Okay, so you want to make sure your database is secure, no matter what your front-end is? Here is a simple script that does exactly that, hashes passwords in the tables as they go in with a salt and provides an easy way to check logins against the user table. If you want, you can even hash user-names too, that way even if your database is completely stolen it isn't insecure:

-- Users is a table, password hash, up to 100 chars, currently used is
-- sha1 because it is nativly supported in PHP.
CREATE TABLE `Users` (
 `usr_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
 `username` VARCHAR(20) NOT NULL, 
 `usr_password` VARCHAR(100) NOT NULL
) ENGINE = INNODB;

-- Yes, secure things as they are added, using the username as a salt.  
CREATE TRIGGER user_input BEFORE INSERT ON `Users` FOR EACH ROW SET NEW.usr_password = SHA1(CONCAT(NEW.usr_password, NEW.username));

CREATE TRIGGER user_update BEFORE UPDATE ON `Users` FOR EACH ROW SET NEW.usr_password = SHA1(CONCAT(NEW.usr_password, NEW.username));

-- Checks if a user with the given username and password exists in the 
-- db, returns null if no and the ID if true.
delimiter |
CREATE FUNCTION CHECK_USER (usrname VARCHAR(20), password VARCHAR(1000))
 RETURNS INT DETERMINISTIC
 BEGIN
  DECLARE tmp INT;
  SELECT usr_id INTO tmp FROM Users WHERE username = usrname AND usr_password = SHA1(CONCAT(password,usrname));
 RETURN tmp;
 END|
delimiter ;

A small sample of the program in action:

    mysql> select * from Users;  
    Empty set (0.00 sec)  
      
    mysql> INSERT INTO Users (username, usr_password) VALUES ("Cookie", "Monster"), ("Big", "Bird");  
    Query OK, 2 rows affected (0.07 sec)  
    Records: 2  Duplicates: 0  Warnings: 0  
      
    mysql> select * from Users;  
    +--------+----------+------------------------------------------+  
    | usr_id | username | usr_password                             |  
    +--------+----------+------------------------------------------+  
    |      3 | Cookie   | 00c66ad8335364be46f67da3699be142189b2aa9 |  
    |      4 | Big      | 4e49d9c043ee8733f6019cc777d65421721333c7 |  
    +--------+----------+------------------------------------------+  
    2 rows in set (0.00 sec)  
      
    mysql> SELECT CHECK_USER("Big", "Bird");  
    +---------------------------+  
    | CHECK_USER("Big", "Bird") |  
    +---------------------------+  
    |                         4 |  
    +---------------------------+  
    1 row in set (0.01 sec)  
      
    mysql> SELECT CHECK_USER("Oscar", "Grouch");  
    +-------------------------------+  
    | CHECK_USER("Oscar", "Grouch") |  
    +-------------------------------+  
    |                          NULL |  
    +-------------------------------+  
    1 row in set, 1 warning (0.00 sec)  
      
    mysql> SELECT CHECK_USER("Big", "Baby");  
    +---------------------------+  
    | CHECK_USER("Big", "Baby") |  
    +---------------------------+  
    |                      NULL |  
    +---------------------------+  
    1 row in set, 1 warning (0.00 sec)  
      
    mysql>   

Note that while all hashing may be done in the DB, it is still recommended you do it server side/client side too, as passwords may end up in SQL log files if not hashed beforehand. Everything will still work even if a password is hashed multiple times.