Mysql – How to store a 64-digit key of alpha numerical values into MySQL Database

database-designMySQLregexregular expression

I am student studying in Database Administration (first year), one of our task requires a to design a login form for corporate and regular users. Below are the requirements for the username and password fields:

  1. User name should be any combination of letters and numbers at least
    five characters in length and no more than 20. (Same requirements as
    user login ID for corporate account ID).

  2. The corporate password is a 64-digit key of alpha numerical values
    that all employees in the corporation has access to.

  3. The regular password for both corporate client employees/users and
    regular users should contain these types of characters: one
    alphanumeric, one number, one uppercase english letter, one lowercase english letters and one special character and should be at least 8 characters in length and no more than 20.

I do not have any problem in designing the regular expression for regular username and password.

The following is the regular expression for the regular user's password field (meets above requirements):

^(?=.*?[A-Z])(?=.*?[a-z])(?=.*?[0-9])(?=.*?[#?!@$%^&*-]).{8,20}$

The problem now is that I have no clues on how to create a 64-digit key of alpha numerical values nor to store in Database for the corporate id and password

Best Answer

Storing: Use a field with datatype BINARY(64) or CHAR(64) CHARSET ascii COLLATE ascii_bin.

Generating:

This is not an SQL question because it should not be done in the database. It should be done in the UI layer that receives and checks passwords, etc.

So, given that you should write this in Perl/PHP/VB/Java, you have much stronger tools with which to generate the corporate password.

Of what use is a password that everyone knows? As soon as a disgruntled employee gets fired, the password may become known to the world.

I'm serious -- some things do not belong in SQL; instead they should be in the code that access the database.

But if you must do it (for the class) in SQL, consider creating a table of the possible characters, one per row, plus another table of (at least) 64 distinct integers. Then use a JOIN, GROUP_CONCAT, ORDER BY RAND(), LIMIT 64, and probably a subquery to get the INTs.

Related Question