Truly Random and Complex Password Generator - Part 2 of 2

Permalink: http://bit.ly/1tMM9h2



In the first part of this entry, it was shown how its possible that a password from a normal user would significantly be weaker than that from a complex and randomly generated one.

Note: in the strictest sense, there is no such thing as an uncrackable password. Passwords can be uncrackable only in theory, i.e. the mathematical probability of a password being guessed correctly-- no matter how infinitesimally small the odds are, the possibility of a right guess is always present. Also, passwords are uncrackable only technically-- given enormous resources and time any password can be cracked.

Here is a function for a truly random and complex password generator which is based on the formulas given in the first part of this entry:

DELIMITER $$
DROP FUNCTION IF EXISTS `randomPasswordGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomPasswordGenerator`(
  ) RETURNS varchar(64) CHARSET utf8
BEGIN
  DECLARE charCount TINYINT(1) DEFAULT 0;
  DECLARE charDiceRoll TINYINT(2);
  DECLARE randomChar CHAR(1);
  DECLARE randomPassword CHAR(8) DEFAULT '';
  REPEAT
    SET charCount = charCount + 1;
    SET charDiceRoll = 1 + FLOOR(RAND() * 94);
    IF (charDiceRoll <= 32)
    THEN
      SET randomChar = ELT(charDiceRoll,
      '`', '~', '!', '@', '#', '$', '%', '^',
      '&', '*', '(', ')', '-', '=', '_', '+',
      '[', ']', '{', '}', '\\', '/', '|', '?',
      ';', ':', '\'', '"', ',', '.', '<', '>');
    ELSEIF (charDiceRoll >= 33)
      AND (charDiceRoll <= 68)
    THEN
      SET charDiceRoll = charDiceRoll - 33;
      SET randomChar = CONV(
        charDiceRoll,
        10, 36);
    ELSE
      SET charDiceRoll = charDiceRoll - 59;
      SET randomChar = LOWER(
        CONV(
          charDiceRoll,
          10, 36)
      );
    END IF;
    SET randomPassword = CONCAT(randomPassword, randomChar);
  UNTIL (charCount = 8)
  END REPEAT;
  RETURN randomPassword;
END $$
DELIMITER ;

This function will return an 8-character password string. Each character has an equal chance of 1/94 to be generated. Given a short period of time and a normal amount of resources, this qualifies as a theoretical technically uncrackable password. It can be modified to return a longer password length or even a random length, say between 8-12 characters long. A separate user defined function, randomRangePicker(), can be used, if refactoring is desired.



The output can be checked with a simple SELECT statement:

SELECT randomPasswordGenerator();

See the first part of this entry or a similar random string/name generator.

Truly Random and Complex Password Generator - Part 1 of 2

Permalink: http://bit.ly/1pJlpHz



Skip to the 2nd part for the code snippet.

Its an important matter of security to enforce complex passwords that have a sufficient length. From personal experience, if you ask a normal user to create their own passwords, their passwords will be based on a character set consisting of 36 case-insensitive alphanumeric characters: a-z, 0-9 instead of the full 94 character set typable on all keyboard layouts. Also, most normal users would use dictionary based passwords with a predictable pattern: dictionary words at the beginning and numbers at the end.

Relying solely on the client-side or front-end to enforce the creation of passwords of at least 8 characters long and the use of special characters will not be practical in preventing the use of dictionary words as well as the usage of a certain pattern. Whatever the mechanism is on the client-side, the backend MySQL database should complement it.

Assigning complex passwords to users will, in effect, increase the number of characters from 36 to 94. By making the password randomly generated, the predictability of dictionary words and pattern matching is removed. The number of possible passwords is substantially increased. For an 8-character password string, under a reasonable time limitation, say 6 hours, and using a single modern computer, this results to a theoretical technically uncrackable password:

SELECT FORMAT(POW(32, 8), 0); 
  -- Results to 1,099,511,627,776 possible combinations. Note that the number of possible combinations is greatly reduced when the user limits the password to use dictionary words and pattern matching. This results to a crackable password in a short period of time.  

  
 SELECT FORMAT(POW(94, 8), 0); 
  -- Results to 6,095,689,385,410,816 possible combinations. By being randomly generated, the number of combinations is not reduced as explained above. This results to a theoretical technically uncrackable password given a short period of time.  

A password generator, to be truly random, should satisfy the following:

• The character set for the generator should include all the typable characters on any keyboard layout: 
  
  a-z, A-Z, 0-9, and ` ~ ! @ # $ % ^ & * ( ) - = _ + [ ] { } \ / | ? ; : ' " , . < >
  
  This results to 26 + 26 + 10 + 32 = 94 characters.
• Each of the allowed characters should all have an equal chance of being generated.

For practical purposes, we'll take aside arguments on password complexity versus password length, and we'll assume an 8-character password string. To generate any of the 62 alphanumeric characters, we'll use a base 36 statement as the formula:

SELECT CONV(
          FLOOR(
            RAND() * 36),
      10, 36);

Using a base 36 statement gives us the most compact alphanumeric numeral system. The case sensitivity will be based on odds from a random number range in order to include the LOWER case of the alphabet.

The special characters can be generated by using the ELT function as the basis for the formula like:

SELECT ELT(1 + FLOOR(RAND() * 32),
      '`', '~', '!', '@', '#', '$', '%', '^',
      '&', '*', '(', ')', '-', '=', '_', '+',
      '[', ']', '{', '}', '\\', '/', '|', '?',
      ';', ':', '\'', '"', ',', '.', '<', '>');

In the continuation of this entry is an example of a true random and complex password generator function.

True Random Database and Table Name Generator - Part 2 of 2

Permalink: http://bit.ly/QuBLVB



Read part 1 for the rationale behind the code.

As discussed in the first part of this blog entry, we'll be utilizing a statement that uses base 36 to generate the random name. We will be adding the $ and _ characters using the ELT function. Here is a true random database and table name generator:

DELIMITER $$
DROP FUNCTION IF EXISTS `randomNameGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomNameGenerator`(
 ) RETURNS varchar(64) CHARSET utf8
BEGIN
 DECLARE numberOfChars, charDiceRoll TINYINT(2);
 DECLARE charCount TINYINT DEFAULT 0;
 DECLARE randomChar CHAR(1);
 DECLARE randomName VARCHAR(64) DEFAULT '';
 SET numberOfChars = randomRangePicker(1, 64);
 REPEAT
  SET charCount = charCount + 1;
  SET charDiceRoll = randomRangePicker(1, 38);
  IF (charDiceRoll <= 2)
  THEN
   SET randomChar = ELT(charDiceRoll, '$', '_');
  ELSE
   SET charDiceRoll = charDiceRoll - 3;
   SET randomChar = LOWER(
    CONV(
     charDiceRoll,
     10, 36)
   );
  END IF;
  SET randomName = CONCAT(randomName, randomChar);
 UNTIL (charCount = numberOfChars)
 END REPEAT;
 RETURN randomName;
END $$
DELIMITER ;

Total of 38 characters: 36 case-insensitive alphanumeric characters a-z, 0-9, including $ and _. For code portability between Windows and Linux, the random name generated is all in lowercase characters. Each character has a 1/38 chance to be generated.

Note that we utilized the random number range picker function mentioned in a previous blog entry. Here's an explanation of the code:

• The length of the name is random. Anywhere from 1 to 64 characters long can be generated. The maximum number of characters is based on the value of numberOfChars.
• A character will be generated based on the result of a dice roll from 1 to 38. The 1 to 38 range represents each of the 38 characters.
• The name will be created random character by random character by the CONCAT function until the length is equal to numberOfChars.

Here's a version of the above function that doesn't depend on the random number range picker function:

DELIMITER $$
DROP FUNCTION IF EXISTS `randomNameGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomNameGenerator`() RETURNS varchar(64) CHARSET utf8
BEGIN
 DECLARE numberOfChars, charDiceRoll TINYINT(2);
 DECLARE charCount TINYINT DEFAULT 0;
 DECLARE randomChar CHAR(1);
 DECLARE randomName VARCHAR(64) DEFAULT '';
 SET numberOfChars = 1 + FLOOR(RAND() * 64);
 REPEAT
  SET charCount = charCount + 1;
  SET charDiceRoll = 1 + FLOOR(RAND() * 38);
  IF (charDiceRoll <= 2)
  THEN
   SET randomChar = ELT(charDiceRoll, '$', '_');
  ELSE
   SET charDiceRoll = charDiceRoll - 3;
   SET randomChar = LOWER(
     CONV(
       charDiceRoll,
       10, 36)
   );
  END IF;
  SET randomName = CONCAT(randomName, randomChar);
 UNTIL (charCount = numberOfChars)
 END REPEAT;
 RETURN randomName;
END $$
DELIMITER ;

You can check out the function's output by:

SELECT randomNameGenerator();

This function can be modified to become a random string generator of fixed length or even to become a random password generator. Additional characters can be added to the ELT function, and the statement that uses base 36 can be converted to randomly generate uppercase and lowercase characters. If numbers are not desired to be in the random name, simply modify the range for the base 36 statement so that it will not generate 0-9.

Back to part 1.



Updated: 10/19/2012

True Random Database and Table Name Generator - Part 1 of 2

Permalink: http://bit.ly/UZY7xT



Skip to part 2 to go straight to the code snippet.

See also a similar generator: Truly Random and Complex Password Generator

Database names and table names have certain restrictions in MySQL:

• The maximum name length for both are 64 characters
• Allowed characters are a-z, A-Z, 0-9, $, and _

It is possible to create a table or database with a dot (.) in its name, however this is not recommended as it will cause some of MySQL's built-in functions to not work as expected.

Using uppercase characters in names are also not recommended. The case sensitivity of the name is dependent on the underlying operating system where the MySQL server is installed. For example, in Linux, the name "dbName" is different from "dbname" but are both the same in Windows. For consistency and to allow the database to be portable between the two, as well as to future-proof your database in case at some point you suddenly need to port from Linux to Windows, using all lower case names is recommended.

A database or table random name generator, to be truly random, should satisfy the following:

• The length of the name should be random, between 1 to 64 characters
• The character set for the generator should include all the allowed characters, except uppercase characters since these are not recommended as explained above
• Each of the allowed characters should all have an equal chance of being generated

Using the MD5() function will not satisfy the above conditions and it won't be a true random string generator. For a simple example:

SELECT MD5(RAND());

The above example will always be 32 characters and its first character will never be 0. Therefore, this violates the condition that every character should all have an equal chance of being generated as the character 0 has no chance of being generated as a first character. However, the main defect here is that the function MD5() uses only A-F and 0-9, a range of only 16 characters.

Another approach is to use the ELT function. This function can be used to generate a random character which can then be concatenated to form a random string. A simple example to create a random character:

SELECT ELT(1 + FLOOR(RAND() * 38), 
  '$', '_', 'a', 'b', 'c', 'd', 'e',
  'f', 'g', 'h', 'i', 'j', 'k', 'l',
  'm', 'n', 'o', 'p', 'q', 'r', 's',
  't', 'u', 'v', 'w', 'x', 'y', 'z', 
  0, 1, 2, 3, 4, 5, 6, 7, 8, 9
);

This statement can be used in a user defined function to create a random string with a random length ranging from 1 to 64. However, this implementation is lengthy and there is a cleaner way to do the same by using the following formula:

SELECT LOWER(
  CONV(
    FLOOR(
      RAND() * 36
    ),
  10, 36)
);

This is a shorter way to generate a-z, 0-9. The characters $ and _ can be added as will be shown in the 2nd part of this blog entry.

Here's an explanation of the formula:

• Implicit conversion between numbers and strings automatically occurs during expression evaluation at run-time.
• The CONV statement converts the random integer from a base 10 number to a base 36 number. Using base 36 gives the most compact case-insensitive alphanumeric numeral system. This converts the number to range from 0-9 and A-Z.

The second part of this blog entry will show an example of a user defined function that uses the statement which uses base 36 as a true random database and table name generator.



Updated: 10/15/2012

Random Number Range Picker

Permalink: http://bit.ly/R5rizQ



A function that can randomly pick an integer from a given range can be used by applications that need a number dice roll and random generation of strings, numbers, and even random generation of complex passwords. Let's say you would like to randomly pick a number from 1 to 10. This gives us 10 choices to randomly pick, not 9 as can be mistakenly thought of since 10 minus 1 equals 9. The same is with the range of choices from 0 to 10. This gives 11 possibilities, not 10. To illustrate:

• The range of choices should include the value of the lower end of the range
• It should also include the value of the higher end of the range

To generate a random number from a given lower value and a higher value, use the formula below, where minRange is the lower end of the range and maxRange is the higher end of the range:

minRange + FLOOR(RAND() * (maxRange - minRange + 1))

The formula gives minRange a random integer to add with, where the return from the  RAND() function is passed to the math flooring function. The return from RAND() is multiplied with the difference between minRange and maxRange to set the decimal range that can be randomly created. The + 1 added to the difference between the two values is needed so that the range includes the maxRange number itself. This addition of an extra 1 can easily be missed which results in erroneous results especially when the lower value for the range is a negative number.

To illustrate the common mistakes on how a formula for a simple random range picker often leads to a wrong analysis, let us first fill the values with minRange = 1 and maxRange = 10. This gives us:

SELECT 1 + FLOOR(RAND() * 10);

The common mistake here is that some devs will think a range from 1 to 10 should result in a formula that would include "RAND() * 9" when they use a range formula of 10 - 1 = 9.

And filling the values with minRange = -10 and maxRange = 10 gives us:

SELECT -10 + FLOOR(RAND() * 21);

The common mistake happens when a quick mental analysis of a range formula is made, some devs think that the solution for the second formula will result to "RAND() * 20".

The given formula is used in the function below. The integers for minRange and maxRange can be positive or negative, including zero. The range of numbers returned will correctly include the values of minRange and maxRange as was explained above.

DELIMITER $$
DROP FUNCTION IF EXISTS `randomRangePicker` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomRangePicker`(
 minRange INT,
 maxRange INT) RETURNS int(11)
BEGIN
 DECLARE pick INT;
 SET pick = minRange + FLOOR(RAND() * (maxRange - minRange + 1));
 RETURN pick;
END $$
DELIMITER ;

To use the function, simply pass the two needed parameters like:

SELECT randomRangePicker(-1, 1);

This will return either -1, 0, or 1.

See the following user defined functions for samples where randomRangePicker() can be used:

• True Random Database and Table Name Generator
• Truly Random and Complex Password Generator

Updated: 10/14/2012