Quick Password generation with MySQL

Here is a quick and simple way to generate a password your application using MySQL.
This query will create a upper and lower case randomly generated password in length and values.


SELECT CONCAT(UCASE(SUBSTRING(  SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) )   , FLOOR( 1 + (RAND() * 4 )) , FLOOR( 4 + (RAND() * 12 )) )),
SUBSTRING('!@#$%^&*', FLOOR( 1 + (RAND() * 4))  , FLOOR( 1 + (RAND() * 8))  ),
SUBSTRING(  SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , 5)) as password \G


This query will take a random value for the 1st part of the dynamic password and upper case it when applicable then the second half it adds some symbols then the remaining is in lower case because MD5 does that automatically. You of course can adjust whatever symbols you would prefer.

Example of results:


 CREATE TABLE `generated` (
  `password` varchar(255) NOT NULL
) ENGINE=InnoDB ;

MariaDB [(none)]> INSERT INTO test.generated SELECT CONCAT(UCASE(SUBSTRING(  SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) )   , FLOOR( 1 + (RAND() * 4 )) , FLOOR( 4 + (RAND() * 12 )) )), SUBSTRING('!@#$%^&*', FLOOR( 1 + (RAND() * 4))  , FLOOR( 1 + (RAND() * 8))  ),SUBSTRING(  SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , 5)) as password; 

MariaDB [(none)]> select * from test.generated;
+-----------------------------------+
| password                          |
+-----------------------------------+
| 7232E1D9$%^&*f7de22c1b14f15c8a6   |
| DEE22B7F7#$%541adcdd1b8e2         |
| 2C4BB81001@#$%^&d0d9a2126         |
| 941D3B92D@#$%^a4b7be92            |
| 1CF2563254FC@#a79bb5              |
| 2AE86E7D6!@#$%^&*4ca03f2e8        |
| 074DD8D3#$e70a1                   |
| B2DDC!@#$%^47a252f79              |
| 3A6D0A#$%^&*01ce9278a2a           |
| E9FB4CD16E19!739db9faa1616505c    |
| 1ED7A2E1379B4!@#$%ebe60b          |
| E9B57D71DB1@#$%^e9a4f8c2e94bf3d35 |
| C97982!@#$8b5c534653c06           |
| DE8998057C8!@#$%^8977a807         |
| 7C7172EB1AE5$%^&*79d2b27          |
| B52CE71@#$%^&*4508                |
| AB17714F!@#$%^&203ba4ff80f8a6     |
| 558D@#$04087871e29ff54            |
| 27A78E8EF#$%^b8cee8628d81593      |
| 315F2EC4#ad9913ec0595c            |
+-----------------------------------+



Fibonacci Sequence Procedure

Well it has been awhile since I posted, sorry about that. Today I was watching reruns of the TV show Fringe and when Walter referenced the Fibonacci Sequence I got side tracked with MySQL options for this.

Now this post already existed:

• http://slava-technical.blogspot.com/2010/10/fibonacci-numbers-built-with-sql.html

So I took that post and expanded on it a little, the result is a procedure that you can call and return the range within the Fibonacci Sequence that you are after.

The procedure is below:


delimiter //
CREATE PROCEDURE `Fibonacci`(IN POS INT, IN RANG INT, IN LIMTED INT)
BEGIN
       select FORMAT(Fibonacci,0) AS Fibonacci from (
       select @f0 Fibonacci, @fn:=@f1+@f0, @f0:=@f1, @f1:=@fn
       from   (select @f0:=0, @f1:=1, @fn:=1) x,
       information_schema.STATISTICS p limit LIMTED) y LIMIT POS, RANG;
END//
delimiter ;


You can call this and pass whatever values and ranges you are after.

So if you want the 5th value (starting from 0) in the sequence and the next value


> CALL Fibonacci(5,2,100);
+-----------+
| Fibonacci |
+-----------+
|         5 |
|         8 |
+-----------+


So if you want the 30th value (starting from 0) in the sequence and the next value


> CALL Fibonacci(30,2,100);
+-----------+
| Fibonacci |
+-----------+
| 832,040   |
| 1,346,269 |
+-----------+


So if you want the 150th value (starting from 0) in the sequence and the next value


> CALL Fibonacci(150,2,1000);
+--------------------------------------------+
| Fibonacci                                  |
+--------------------------------------------+
| 9,969,216,677,189,305,000,000,000,000,000  |
| 16,130,531,424,904,583,000,000,000,000,000 |
+--------------------------------------------+


 So you get the idea. Now you can also expand the range of results if you want more than 2 just change the 2nd value in the procedure call.


> CALL Fibonacci(0,10,100);
+-----------+
| Fibonacci |
+-----------+
| 0         |
| 1         |
| 1         |
| 2         |
| 3         |
| 5         |
| 8         |
| 13        |
| 21        |
| 34        |
+-----------+

> CALL Fibonacci(30,5,100);
+-----------+
| Fibonacci |
+-----------+
| 832,040   |
| 1,346,269 |
| 2,178,309 |
| 3,524,578 |
| 5,702,887 |
+-----------+


 Anyway, hope someone finds it helpful and credit for the base of query does go to the original post.