Many times, a developer will require a random alpha numeric string to be generated that can vary in length. I can think of several possible uses. Random Key Generation, Random Password Generation, creating random data used for testing text-based processing routines, random file name generation.

This is one of those routines you can use over and over. If you have read some prior posts, you probably can see where I could use the random text generator function. For more information, see the prior posts starting here, regarding use case for a membership / user database. In those posts, we discussed user validation or newly registered accounts, and many other aspects of a healthy membership database.

Along the above lines the random generation of strings could be used in a couple of areas. It could be used in creating  a ‘salt’ value. The Salt is a string or in some cases a binary value that you add to another string to make the combination more complex and harder to guess. It is a well established best practice that all membership, user databases should use if they must validate a user in a non trusted network environment.

The original idea for the membership user account database verification process is to allow users on a public network (the internet) to register an account and then allow them to validate the account via a simple link that is embedded into the body of a standard email message. For this reason, it is required when adding a new user to the membership database, both a username and a valid email is required. If a user enters an incorrect or fake email address, the verification can not be completed because the user never gets the email.

But where does the random string come in? The idea is to use it in a query string in the link found within the body of the email message.

The link would look something like this http://[my Simple Test Site.com] ? T = 123ABC, and when the user clicks the link, the page that displays runs some php to ‘read’ the query string value 123ABC. using the value, a database procedure would then match up the query string with a value in the database, associated with that  particular user. If it matches exactly then the user would be validated. Why not take the additional step of  prompting the user to logon? In some higher security situations, you might do this however I think it adds to the burden for the user to force them to logon at that particular moment. Ultimately, the user will have to logon anyway, so we decide to allow just the click on the link to be our means of validation.

So, the call to generate the random string token is from PHP or other server-side language for example c# or vb.net if you use asp.net. A connection is opened by a php script that is within a page on the web server. The call the database could be a select statement of some fashion however following best practices, the preferred method is to use a procedure or routine rather than accessing a table directly. By using a routine (in this case a mysql routine), we only need grant ‘execute’ rights to the routine for it to return the data, we do not have to expose the schema’s table.

The below code is used to generate the random string. It has been only tested in a development environment to a small degree and worked in several tests with a few adjustments. Notice that there is a limit imposed of 130 characters. This limitation is imposed to prevent entering a length that is too big. I believe there are many improvements that can be made to the below code, however, though flawed, it may be a good starting point from someone.

Here it is:

PROCEDURE `gen_randomText`(IN P_TOKEN_LEN INTEGER,OUT P_TOKEN VARCHAR(130))
BEGIN
declare w_counter integer;
declare w_token varchar(130);
declare w_char varchar(130);
declare w_limit integer;
declare w_token_len integer;
set w_token_len = P_TOKEN_LEN;
SET P_TOKEN = '';
set w_token ='';
SET w_limit = 130;
SET w_counter = 0; -- explicitly set the value to 0
set w_char = '';
if (P_TOKEN_LEN>w_limit) then
set P_TOKEN_LEN = w_limit;
end if;
WHILE  ((w_counter <  w_token_len) && (w_counter<w_limit))  DO
        IF w_counter < w_limit THEN
  SET w_char = SUBSTRING(MD5(RAND()) FROM 1 FOR 1);

  SET w_token = concat(w_char,w_token);     
  SET w_counter=w_counter+1;
  END IF;
end WHILE;
SET P_TOKEN = w_token;
end

 

Now, how would you call this routine? In your sql window, use this command:

CALL gen_RandomText(50,@P);
select @P;

 The above routines can be used with a development environment like MySQL workbench. Notice t he above parameter value of 50 represents the number of random characters to be returned.

But where to use this? This routine would be called from php or some other languages used on the web server. If your web server is Linux based probably go with PHP. If it is MS Windows based, you would use dot net (ASP.net).