Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to generate random passwords according to the rules in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Generate random passwords according to rules in MySQL

Later versions of MySQL support stored procedures, which are unique, efficient and secure. Stored procedures are not supported in the previous version of MySQL 5.0. however, with the improvement of MySQL technology, stored procedures will be widely used in future projects.

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

We provide the following password requirements rules, which can be used in combination:

1-requires uppercase letters UPPERCASE = > abbreviation [U]

2-lowercase letter LOWERCASE = > abbreviation [L] is required

3-requires numeric NUMBER = > abbreviation [N]

4-can be any character ANY_CHARACTER = > abbreviation [A]

5-must have non-alphanumeric characters NON_ALPHANUMERIC_CHARACTER = > abbreviation [S]

So I want to create a dynamic function "RANDOM_PASSWORD" to return random passwords as required.

Www.2cto.com

The system administrator only needs to pass the rules of the required password and the corresponding random password will be returned.

For example, the requirements are as follows:

The first character must be capitalized = > U

The second character must be lowercase = > L

The third character must be a number = > N

The fourth character is random = > A

The fifth character must be non-alphabetic and numeric = > S

The sixth character must be a number = > N

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

The degree of the generated password is consistent with the length of the passed parameter. The length of the password generated in our example is 6.

You can call this function using the following method:

one

RANDOM_PASSWORD ('ULNASN')

Before writing a function definition on the console of MySQL, delete this function definition that may already exist in the, and then change the delimiter to $. In fact, I wanted to change the delimiter to #, but later found that it was unsuccessful. I don't know why. Delete the definition of the RANDOM_PASSWORD () function under the command line and change the delimiter using the following command:

Www.2cto.com

one

> DROP FUNCTION IF EXISTS RANDOM_PASSWORD

two

Mysql > delimiter $

Next, you can write this function, and the source code I debugged is as follows:

01

CREATE FUNCTION RANDOM_PASSWORD (str VARCHAR)

02

RETURNS VARCHAR (255)

03

BEGIN

04

DECLARE UPPER_CASE VARCHAR (26) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

05

DECLARE LOWER_CASE VARCHAR (26) DEFAULT 'abcdefghijklmnopqrstuvwxyz'

06

DECLARE NUMBERS VARCHAR (10) DEFAULT '0123456789'

07

DECLARE TEMP_CHARACTER VARCHAR (255) DEFAULT''

08

DECLARE NON_ALPHANUMERIC_CHARACTERS VARCHAR (255) DEFAULT'~! @ # $% ^ & * () _ +-= `:;,.? /'

09 www.2cto.com

DECLARE ALL_STRING VARCHAR (255) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 percent percent ^ & * () _ +-= `:;,.

ten

DECLARE STR_LENGTH INT DEFAULT 0

eleven

DECLARE i INT DEFAULT 0

twelve

DECLARE RANDOM_CHARACTER CHAR (1) DEFAULT''

thirteen

DECLARE PASSWORD_RETURNED VARCHAR (255) DEFAULT''

fourteen

fifteen

SET STR_LENGTH = CHAR_LENGTH (str)

sixteen

seventeen

WHILE i

< STR_LENGTH DO 18 SET TEMP_CHARACTER = SUBSTR(str, i + 1, 1); 19 CASE TEMP_CHARACTER 20 WHEN 'N' THEN 21 SET RANDOM_CHARACTER = SUBSTR(NUMBERS, CEIL( RAND() * ( LENGTH( NUMBERS ) - 1 )), 1); 22 WHEN 'U' THEN 23 SET RANDOM_CHARACTER = SUBSTR(UPPER_CASE, CEIL( RAND() * ( LENGTH( UPPER_CASE ) - 1 )), 1); 24 www.2cto.com WHEN 'L' THEN 25 SET RANDOM_CHARACTER = SUBSTR(LOWER_CASE, CEIL( RAND() * ( LENGTH( LOWER_CASE ) - 1 )), 1); 26 WHEN 'S' THEN 27 SET RANDOM_CHARACTER = SUBSTR(NON_ALPHANUMERIC_CHARACTERS, CEIL( RAND() * ( LENGTH( NON_ALPHANUMERIC_CHARACTERS ) - 1 )), 1); 28 WHEN 'A' THEN 29 SET RANDOM_CHARACTER = SUBSTR(ALL_STRING, CEIL( RAND() * ( LENGTH( ALL_STRING ) - 1 )), 1); 30 ELSE 31 SET RANDOM_CHARACTER = ''; 32 END CASE; 33 SET PASSWORD_RETURNED = CONCAT(PASSWORD_RETURNED, RANDOM_CHARACTER); 34 SET i = i + 1; 35 END WHILE; 36 37 RETURN PASSWORD_RETURNED; 38 END 39 $ 使用方法： 1 mysql>

Select RANDOM_PASSWORD ('ULNASN') PASSWORD

2 www.2cto.com

+-+

three

| | PASSWORD |

four

+-+

five

| | Bv1n`8 |

six

+-+

seven

1 row in set (0.00 sec)

The random password that I will return here is: Bv1n`8

After reading the above, do you have any further understanding of how to generate random passwords according to the rules in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.