Best Practice

The following is an example function which checks that a password conforms to these requirements:

• Password length
• Password is not simple (based on dictionary)
• Password contains an upper case character
• Password contains a lower case character
• Password contains a digit
• Password contains punctuation
• Password is not the same as the username

delimiter //

DROP FUNCTION password_complexity//

CREATE FUNCTION password_complexity( password VARCHAR(64) )
  RETURNS VARCHAR(256)
  SQL SECURITY INVOKER
BEGIN
  DECLARE message VARCHAR(256);
  DECLARE count INT;
  DECLARE username VARCHAR(64);

  -- check the password length  
  IF LENGTH(password) < 8 THEN
     SET message = 'Password should be at least 8 characters';
  END IF;

  -- check whether the password is too simple by comparing it against
  -- a table that holds a dictionary of simple words (admin.dict)
  SELECT COUNT(*) INTO count FROM admin.dict WHERE word = password;
  IF count > 0 THEN
    SET message = CONCAT_WS(',',message,' Password too simple');
  END IF;

  -- check for a lower case character  
  IF password NOT RLIKE '[[:lower:]]' THEN
     SET message = CONCAT_WS(',',message,
                           ' Password should contain lower case character');
  END IF;

  -- check for an upper case character  
  IF password NOT RLIKE '[[:upper:]]' THEN
     SET message = CONCAT_WS(',',message,
                            ' Password should contain upper case character');
  END IF;

  -- check for a digit  
  IF password NOT RLIKE '[[:digit:]]' THEN
     SET message = CONCAT_WS(',',message,
                            ' Password should contain a digit');
  END IF;

  -- check for punctuation  
  IF password NOT RLIKE '[[:punct:]]' THEN
     SET message = CONCAT_WS(',',message,
                             ' Password should contain punctuation');
  END IF;

  -- lastly check whether username and password are the same 
  -- if it is, admonish!
  SET username = substring_index(CURRENT_USER(), '@', 1);
  IF username = password THEN
     SET message = 'Username and password are the same!';
  END IF;

  -- if message is still NULL then password is OK
  IF message IS NULL THEN
     SET message = 'Password is acceptible!';
  END IF;

  RETURN(message);
END;
//

Note that the function uses a table that holds a list of simple passwords within a word column, this simple table can be built as below:

CREATE TABLE dict (word VARCHAR(128), KEY (word));

There is also a sample data set of simple passwords attached to this article (dict.txt). This can be loaded to the dict table with the LOAD DATA INFILE statement:

LOAD DATA INFILE '/path/to/dict.txt'
INTO TABLE dict
LINES TERMINATED BY '\r\n';

Here is some sample output:

SELECT password_complexity('mark@2201');

+-----------------------------------------------+
| password_complexity('mark@2201')              |
+-----------------------------------------------+
|  Password should contain upper case character |
+-----------------------------------------------+
1 row in set (0.00 sec)