Saturday, July 25, 2015

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            |
+-----------------------------------+