Page 1 of 1

MySQL Triggers and random password generation

#1 Munawwar  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 162
  • View blog
  • Posts: 457
  • Joined: 20-January 10

Posted 06 October 2010 - 03:19 AM

Aim

To generate a random password for each user during an INSERT operation.
After a 3 hour struggle I got the code to work.Phew! So I thought I'll write a small tutorial for D.I.C.

Before you start, crete a test database and table. Name the database dbs and create a table with the following SQL command:
CREATE TABLE users(
ID INT PRIMARY KEY AUTO_INCREMENT ,
name VARCHAR(20) ,
password CHAR(7)
);


Start MySQL and fire up the MySQL console. I use wampserver, so I click the icon on the taskbar and navigate to MySQL->MySQL console.
Try writing a sql query:
SELECT * FROM dbs.users;
Result: empty set.
Our aim is to write a MySQL trigger that is executed on every insertion operation on the users table. The trigger should create a random password for each user.

So the first step is to work on the password generation algorithm.
The generation algorithm is simple. All we need is to generate 7 random alphabets.How? Get a random number from 0 to 25, add it with capital A's ASCII value (that's 65), and then cast it to a character.
So in pseudo-code:
alphabet = cast_to_char(65+(randomInteger modulus 26))

To get numeric digits, we add a random number from 0 to 9 to ASCII value of 0 (that's 48):
digit = cast_to_char(48+(randomInteger modulus 10))

To get an alpha-numeric password,
alpha_or_num = randomInteger (mod 10);
if(alpha_or_num<5)
	//Generate a digit
else
	//Generate an alphabet


In SQL we have got the RAND function that generates a random floating point value between the range 0 to 1.
SELECT RAND() AS 'random floating point';

To get a random integer, we multiply the float value with, say 100 (so that we get a two digit integer) and then we round it to the nearest integer.
SELECT ROUND(RAND()*100) AS 'Random integer';

So now we can generate a random alphabet using the SQL MOD function (for modulus operation) and CHAR function (to cast the integer to a character):
SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) AS 'Random alphabet';


Procedures

Procedures are like functions in C/C++/many other programming languages - they will help you to exceute multiple instructions in a single line of code.
Before we continue, we must change the delimiter from semi-colon to some other character sequence like //.This is done so that we can write multiple lines of code.
delimiter //

Now our code/SQL queries should always end with //.
SELECT * FROM dbs.users;//


So lets begin :) :
CREATE PROCEDURE dbs.randomPass()
BEGIN
	#All the code goes here. Right now this procedure does nothing.
END;//


CREATE,BEGIN and END are pre-defined keywords. To write comments we use #.To write multi-line comments, write between /* and */ like so: /*This is a comment*/
To execute the function:
CALL dbs.randomPass();//
Result: Query OK, 0 rows affected (0.00 sec)
To edit a procedure, you will have to delete the procedure and re-write the procedure.
DROP PROCEDURE dbs.randomPass;//

You can set the delimiter back to semi-colon (if you wish to do so):
delimiter ;


User-defined variables

To make our procedure useful, we should be able to declare variables and do calculations within the BEGIN..END block.
To declare variables we use the following format
DECLARE variable_name data_type;

Examples:
DECLARE someName INT;
DECLARE someOtherName VARCHAR(100);


Set variable default value:
DECLARE someName INT DEFAULT 0;
DECLARE someOtherName VARCHAR(100) DEFAULT "";


We can later set the values of the variables to 'whatever we want' like so:
SET someOtherName="whatever we want";

Increment operations on integers:
DECLARE count INT DEFAULT 0;
SET count=count+1;


We can also set the result of an SQL query into a variable using the INTO keyword:
SELECT count(*) FROM dbs.users INTO count;

The variables that I have shown above have local scope - means they can be only accessed within the BEGIN...END block in which they are decalred. They get destroyed after executing the block.
You can declare variables that won't get destroyed until the end of the session:
SET @count=1; #The @ symbol denotes that this variable is a session variable

Displaying its value:
SELECT @count AS 'count value';



Control structures

You have seen the if,while and for statements in many programming languages, haven't you?
In MySQL we write an IF statement in the following format:
IF expr THEN
	#do something
ELSEIF expr THEN
	#do something
ELSE
	#do something
END IF;


where expr is an expression.
Here ELSEIF and ELSE statements are optional.

Example:
IF count<7 THEN
	SET count=count+1;


The format for a WHILE statement is as follows:
WHILE expr DO
	#Statements here
END WHILE;



Now we can write the complete random password generator procedure - I hope it is self-explanatory:
CREATE PROCEDURE dbs.randomPass(OUT _password VARCHAR(7))
BEGIN
	DECLARE count INT DEFAULT 0;
	DECLARE alphanum INT;
	DECLARE randomCharacter CHAR(1);
	DECLARE password VARCHAR(10) DEFAULT "";
	
	WHILE count<7 DO
		SET count=count+1;
		SELECT ROUND(RAND()*10) INTO alphanum;

		IF alphanum<5 THEN
			#Generate a random digit
			SELECT CHAR(48+MOD(ROUND(RAND()*100),10)) INTO randomCharacter;
			
			#Debug message
			#SELECT alphanum AS 'RAND','less than 5' AS 'flow', randomCharacter AS 'random character';
		ELSE
			#Generate a random digit
			SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) INTO randomCharacter;
			
			#Debug message
			#SELECT alphanum AS 'RAND','more than or equal to 5' AS 'flow', randomCharacter AS 'random character';
		END IF;
		#CONCAT function concatenates two or more strings and returns result
		SELECT CONCAT(password,randomCharacter) INTO password;
	END WHILE;
	
	#Debug message
	#SELECT password AS 'Password';
	SET _password=password;
END; //


Your immediate question will be what's "OUT _password VARCHAR(7)"? Well, procedures can set(in some sense, return) external variables using the OUT keyword, so that we can use the generated password later.
SET @pass="";
CALL dbs.randomPass(@pass);
SELECT @pass AS 'Password';//


Within the procedure @pass will have a local name of _password. At the end of the procedure I set _password to the generated password:
SET _password=password;

That would set @pass to the generated password


Triggers

A trigger is a piece of code that gets executed after an event such as an insertion,deletion or and update operation on a table.

In the above procedure I have commented out the debug messages. You can uncomment it if you wish to. But you MUST remove/comment any such debug messages before using the procedure within a trigger.
If you don't, you will get a 'Not allowed to return a result set from a trigger' error message.

The format for creating a trigger is as follows (taken from MySQL reference manual):
CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body


trigger_event can be a database operation like insert,update or delete.
trigger_time can be 'before' or 'after' - indicates to MySQL whether the trigger must be called before or after the database operation.

Example:
CREATE TRIGGER dbs.user_insert BEFORE INSERT ON dbs.users
	FOR EACH ROW BEGIN
		#Code goes here
	END;//


To edit the trigger you will have to drop the trigger and re-write the trigger (AAH! This is a pain!)
drop trigger dbs.user_insert;//

It's now time to finish this:
CREATE TRIGGER dbs.user_insert BEFORE INSERT ON dbs.users
	FOR EACH ROW BEGIN
		CALL dbs.randomPass(NEW.password);
	END;//


Here the procedure sets NEW.password with the generated password, and the trigger ends, after which the table gets updated.
Now anytime you insert a new user name into the table a random password gets saved along with it :cool2: .

Note: The NEW object contains the information inserted into the table.
That is, say the following insertion is done:
INSERT INTO dbs.users(name) VALUES ('Munawwar');

then New.name (within the trigger) would be 'Munawwar'.

That's all folks!

Is This A Good Question/Topic? 3
  • +

Replies To: MySQL Triggers and random password generation

#2 chathuraguy  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 1
  • Joined: 30-March 11

Posted 31 March 2011 - 10:28 PM

seems to be nice dude.i would have like much it. thanks

This post has been edited by chathuraguy: 31 March 2011 - 10:34 PM

Was This Post Helpful? -1
  • +
  • -

#3 ChubbyNinja  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 06-December 13

Posted 06 December 2013 - 06:26 AM

I like the way you generate a random password on insert, but if I am understanding this correctly - you're storing the password and not a password hash, so I feel that this is a "good example of what you can do with create procedure" but that's as far as it should go in terms of using this in a public domain.

If I was to generate a random password on insert, I would create trigger and sys_exec at a PHP script which returns a password hash and random salt.

That way you are not storing plain text passwords.

But good example like I said.
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Posted 12 December 2013 - 05:01 PM

View PostChubbyNinja, on 06 December 2013 - 01:26 PM, said:

If I was to generate a random password on insert, I would create trigger and sys_exec at a PHP script which returns a password hash and random salt.

That way you are not storing plain text passwords.

I agree that storing plain-text passwords is a bad thing, but it wouldn't really be much use, generating a random hashed password on INSERT. The original plain-text password would have to be stored somewhere, or the hash would be useless; if nobody has the original, nobody can pass any password validation against the hash. - Unless the PHP script were to, say, email the plain-text password to the user. (Which in itself is not a great idea.)

Overall I don't think generating random passwords on insert has much use; passwords should be chosen by users. However there are situations where it can be useful to generate random strings on insert, which the method provided in the tutorial would be good for.

There are easier ways to generate strings, though. For example, MySQL has a UUID function built in, which generates a "Universal Unique Identifier"; a string that will (in theory) always be totally unique from any other such string generated. Combine that with a hashing function, such as MD5 and SHA1, and you'll have a pretty random looking string. If a certain length is required, it's just a matter of selecting a portion of the generated hash.
SELECT SUBSTRING(MD5(UUID()), 10, 7);


This would get you a 7 letter alpha-numeric that is somewhat unique. - Seeing as it's only a portion of a MD5 hash, it won't be entirely unique, but if you're only looking to generate a random string, it will suffice. Stronger methods would be required if an actual unique string is required for security purposes; such as just using the UUID() string by itself.
Was This Post Helpful? 0
  • +
  • -

#5 ChubbyNinja  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 06-December 13

Posted 14 December 2013 - 08:28 AM

Good reply Atli, and I agree that it should usually be a user generated password, the only practical use for this situation i can think this could be useful is generating temporary passwords for account recovery or something along those lines.

I was thinking when storing the hash that yes the php script would handle any password emailing or notifications.

Sent from my GT-I9505 using Tapatalk
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1