Generate random passwords in MySQL based on Rules

MySQL 5.0 and later versions support stored procedures, which are consistent, efficient, and secure. MySQL 5.0 and earlier versions do not support stored procedures. However, with the increasingly sophisticated MySQL technology, stored procedures will be widely used in future projects.

In my application, I need to generate a random password for this account when the user registers for the first time. The generated password must meet certain requirements. These requirements must be configured by the system administrator.

We provide the following password-related rules that can be used in combination:

1-The upper case letter is required.
2-LOWERCASE letter LOWERCASE ====> abbreviation [L]
3-digit NUMBER ====> abbreviated [N]
4-any character can be ANY_CHARACTER ======> [A]

5-The NON_ALPHANUMERIC_CHARACTER must contain non-letters and numbers

Therefore, I want to return a random password as required by creating a dynamic function "RANDOM_PASSWORD.

The system administrator only needs to pass the required password rules and returns the corresponding random password.

For example, the requirements are as follows:

The first character must be in uppercase ====> U
The second character must be in lower case ======> L
The third character must be a number ======> N
The fourth character is random ====>
The fifth character must be a non-letter or number =====> S
The sixth character must be a number ======> N

Then you can use the "ULNASN" parameter to obtain the random password.

The password generated is consistent with the length of the passed parameter. In this example, the password length is 6.

You can use the following method to call this function:

RANDOM_PASSWORD ('ulnasn ')

Before writing a function definition in the MySQL console, delete the function definition that may already exist in the Database and change the separator to $. In fact, I want to change the separator #, later I found that it was not successful. I don't know why. Use the following command to delete the definition of the RANDOM_PASSWORD () function and change the Separator under the command line:

Mysql> drop function if exists RANDOM_PASSWORD;
Mysql> delimiter $

Next, you can write this function. The source code after debugging is as follows:

Create function RANDOM_PASSWORD (str VARCHAR (255 ))
Returns varchar (255)
BEGIN
DECLARE UPPER_CASE VARCHAR (26) DEFAULT 'abcdefghijklmnopqrstuvwxy ';
DECLARE LOWER_CASE VARCHAR (26) DEFAULT 'abcdefghijklmnopqrstuvwxy ';
Declare numbers varchar (10) DEFAULT '123 ';
DECLARE TEMP_CHARACTER VARCHAR (255) DEFAULT '';
DECLARE NON_ALPHANUMERIC_CHARACTERS VARCHAR (255) DEFAULT ,.? /'"> '~! @ # $ % ^ & * () _ +-= ':; <> ,.? /';
DECLARE ALL_STRING VARCHAR (255) DEFAULT ,.? /'"> 'Abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz0123456789 ~! @ # $ % ^ & * () _ +-= ':; <> ,.? /';
DECLARE STR_LENGTH int default 0;
DECLARE I INT DEFAULT 0;
DECLARE RANDOM_CHARACTER CHAR (1) DEFAULT '';
DECLARE password_returnevarchar (255) DEFAULT '';
 
SET STR_LENGTH = CHAR_LENGTH (str );
 
WHILE I <STR_LENGTH DO
SET TEMP_CHARACTER = SUBSTR (str, I + 1, 1 );
CASE TEMP_CHARACTER
WHEN 'n' THEN
SET RANDOM_CHARACTER = SUBSTR (NUMBERS, CEIL (RAND () * (LENGTH (NUMBERS)-1), 1 );
WHEN 'U' THEN
SET RANDOM_CHARACTER = SUBSTR (UPPER_CASE, CEIL (RAND () * (LENGTH (UPPER_CASE)-1), 1 );
WHEN 'l' THEN
SET RANDOM_CHARACTER = SUBSTR (LOWER_CASE, CEIL (RAND () * (LENGTH (LOWER_CASE)-1), 1 );
WHEN's THEN
SET RANDOM_CHARACTER = SUBSTR (NON_ALPHANUMERIC_CHARACTERS, CEIL (RAND () * (LENGTH (NON_ALPHANUMERIC_CHARACTERS)-1), 1 );
WHEN 'A' THEN
SET RANDOM_CHARACTER = SUBSTR (ALL_STRING, CEIL (RAND () * (LENGTH (ALL_STRING)-1), 1 );
ELSE
SET RANDOM_CHARACTER = '';
End case;
SET PASSWORD_RETURNED = CONCAT (PASSWORD_RETURNED, RANDOM_CHARACTER );
SET I = I + 1;
End while;
 
RETURN PASSWORD_RETURNED;
END
$

Usage:

Mysql> select RANDOM_PASSWORD ('ulnasn') PASSWORD;
+ ---------- +
| PASSWORD |
+ ---------- +
| Bv1n' 8 |
+ ---------- +
1 row in set (0.00 sec)

The random password that will be returned here is:Bv1n'8

Of course, the running results may be different because they are random.