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



Find/parse a string from within a string

So I noticed a few different questions and posts about parsing a string out of another string recently. While some solutions included creating new functions and etc it can also be done within a single query in some cases.

For example, let us say that we are looking to pull out the domain from a URL. I will try to go into detail as to why and how this works.
We have the following table.


CREATE TABLE `parse_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `urldemo` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
+----+----------------------------+
| id | urldemo                    |
+----+----------------------------+
|  1 | http://www.mysql.com/      |
|  2 | http://www.percona.com/    |
|  3 | https://tools.percona.com/ |
|  4 | https://mariadb.com/       |
|  5 | http://planet.mysql.com/   |
|  6 | http://dev.mysql.com/doc/  |
+----+----------------------------+


The goal for this example is to disregard the http:// or https:// and anything after the .com. So we use LOCATE to find the locations.

The .com reference is easy since that is constant so we can start with that.


SELECT LOCATE('.com', urldemo), urldemo FROM parse_example;
+-------------------------+----------------------------+
| LOCATE('.com', urldemo) | urldemo                    |
+-------------------------+----------------------------+
|                      17 | http://www.mysql.com/      |
|                      19 | http://www.percona.com/    |
|                      22 | https://tools.percona.com/ |
|                      16 | https://mariadb.com/       |
|                      20 | http://planet.mysql.com/   |
|                      17 | http://dev.mysql.com/doc/  |
+-------------------------+----------------------------+


OK so we want to remove the / , what location is that?


SELECT LOCATE('.com', urldemo) as start, LOCATE('.com', urldemo) +4 as end, SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 )  AS resulting , urldemo FROM parse_example;
+-------+-----+-----------+----------------------------+
| start | end | resulting | urldemo                    |
+-------+-----+-----------+----------------------------+
|    17 |  21 | /         | http://www.mysql.com/      |
|    19 |  23 | /         | http://www.percona.com/    |
|    22 |  26 | /         | https://tools.percona.com/ |
|    16 |  20 | /         | https://mariadb.com/       |
|    20 |  24 | /         | http://planet.mysql.com/   |
|    17 |  21 | /doc/     | http://dev.mysql.com/doc/  |
+-------+-----+-----------+----------------------------+

This gives us our end position, I only put the field aliases in to make the results easier to follow.

Now sorting out after http and https is actually very easy as well as they both have :// after them, so we just need the location of the second / in the string.


SELECT LOCATE('/', urldemo) as first, LOCATE('/', urldemo) +1 as second, urldemo
FROM parse_example;
+-------+--------+----------------------------+
| first | second | urldemo                    |
+-------+--------+----------------------------+
|     6 |      7 | http://www.mysql.com/      |
|     6 |      7 | http://www.percona.com/    |
|     7 |      8 | https://tools.percona.com/ |
|     7 |      8 | https://mariadb.com/       |
|     6 |      7 | http://planet.mysql.com/   |
|     6 |      7 | http://dev.mysql.com/doc/  |
+-------+--------+----------------------------+


These queries are just showing what the different aspects of the final query will be doing. So let us put it all together.


SELECT
TRIM(TRAILING SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 )
FROM SUBSTRING(urldemo FROM LOCATE('/', urldemo) + 2 ) )  AS parsed_domain ,
urldemo as original_url
FROM parse_example;
+-------------------+----------------------------+
| parsed_domain     | original_url               |
+-------------------+----------------------------+
| www.mysql.com     | http://www.mysql.com/      |
| www.percona.com   | http://www.percona.com/    |
| tools.percona.com | https://tools.percona.com/ |
| mariadb.com       | https://mariadb.com/       |
| planet.mysql.com  | http://planet.mysql.com/   |
| dev.mysql.com     | http://dev.mysql.com/doc/  |
+-------------------+----------------------------+


Now hopefully that helps you be able to parse out whatever you need. This example is limited to a url. But since some examples of functions already here is my example of a function that you can use to parse whatever you need.


CREATE FUNCTION PARSE_STRING(delimiterA VARCHAR(50), delimiterB VARCHAR(50),  passed_string VARCHAR(255) )
RETURNS VARCHAR(255) DETERMINISTIC
RETURN
TRIM(TRAILING SUBSTRING(passed_string FROM LOCATE(delimiterB, passed_string)  )
FROM SUBSTRING(passed_string FROM LOCATE(delimiterA, passed_string) + CHAR_LENGTH(delimiterA) ) ) ;

SELECT PARSE_STRING('//','.com', urldemo) FROM parse_example;
+------------------------------------+
| PARSE_STRING('//','.com', urldemo) |
+------------------------------------+
| www.mysql                          |
| www.percona                        |
| tools.percona                      |
| mariadb                            |
| planet.mysql                       |
| dev.mysql                          |
+------------------------------------+


Pull a last name from a full name field:


SELECT PARSE_STRING('John ','', 'John Smith') ;
+----------------------------------------+
| PARSE_STRING('John ','', 'John Smith') |
+----------------------------------------+
| Smith                                  |
+----------------------------------------+


Pull the first name


SELECT PARSE_STRING('',' Smith', 'John Smith') ;
+-----------------------------------------+
| PARSE_STRING('',' Smith', 'John Smith') |
+-----------------------------------------+
| John                                    |
+-----------------------------------------+


Granted with the name examples you would need to know the delimiter values. But this is just an example you can build on.

UPDATE OPTIONS with LIKE REGEXP SUBSTRING and LOCATE

A recent forum post made me stop and think for a moment..
http://forums.mysql.com/read.php?10,589573,589573#msg-589573

The problem was the user wanted to update just the word audi and not the word auditor.
It was solved by taking advantage of the period easily once I stopped trying to use SUBSTRING and LOCATE.  They wanted a quick and easy fix after all.


root@localhost [test]> CREATE TABLE `forumpost` (
    ->   `name` varchar(255) DEFAULT NULL
    -> ) ENGINE=InnoDB;

root@localhost [test]> insert into forumpost value ('An auditor drives an audi.'),('An auditor drives a volvo.');

root@localhost [test]> select * from forumpost;
+----------------------------+
| name                       |
+----------------------------+
| An auditor drives an audi. |
| An auditor drives a volvo. |
+----------------------------+


So now let us update it the quick and easy way by taking advantage of the period


root@localhost [test]>UPDATE forumpost SET name = REPLACE(name, 'audi.', 'toyota.') WHERE name LIKE '%audi.';
Query OK, 1 row affected (0.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [test]> select * from forumpost;
+------------------------------+
| name                         |
+------------------------------+
| An auditor drives an toyota. |
| An auditor drives a volvo.   |
+------------------------------+


But... what about the valid options of SUBSTRING and LOCATE.....


root@localhost [test]> insert into forumpost value ('An auditor drives an audi.');
root@localhost [test]> insert into forumpost value ('An auditor drives an audi car');
root@localhost [test]> select * from forumpost;
+-------------------------------+
| name                          |
+-------------------------------+
| An auditor drives an toyota.  |
| An auditor drives a volvo.    |
| An auditor drives an audi.    |
| An auditor drives an audi car |
+-------------------------------+


First test your options so you make sure you can find what you are after..


root@localhost [test]> SELECT * FROM forumpost WHERE name REGEXP 'audi car$';
+-------------------------------+
| name                          |
+-------------------------------+
| An auditor drives an audi car |
+-------------------------------+


root@localhost [test]> SELECT * FROM forumpost WHERE name LIKE '%audi car%';

+-------------------------------+

| name                          |

+-------------------------------+

| An auditor drives an audi car |

+-------------------------------+

That really did not do too much since we just changed the period for the word car.  So keep going....

We need to pull just the word audi from the line with audi car.


root@localhost [test]> SELECT SUBSTRING(name,-8,4), name FROM forumpost WHERE SUBSTRING(name,-8,4) = 'audi';
+----------------------+-------------------------------+
| SUBSTRING(name,-8,4) | name                          |
+----------------------+-------------------------------+
| audi                 | An auditor drives an audi car |
+----------------------+-------------------------------+


The SUBSTRING allowed me to pull the first 4 characters after I counted back 8 characters from the end.

So what if you do not know the location of the characters?
To start with you should review you data to make sure you know what you are after. But the characters might move around your string so lets work with LOCATE.

I will add another row just for tests.


root@localhost [test]>  insert into forumpost value ('An auditor drives an red audi car');
Query OK, 1 row affected (0.04 sec)

root@localhost [test]> select * from forumpost;
+------------------------------------+
| name                               |
+------------------------------------+
| An auditor drives an toyota.       |
| An auditor drives a volvo.         |
| An auditor drives an audi.         |
| An auditor drives an audi car      |
| An auditor drives an audi blue car |
| An auditor drives an red audi car  |
+------------------------------------+


So regardless of the ending we can see that audi always after auditor so we just need to skip over that word. The word auditor is in the first 8 character so skip those.


root@localhost [test]> SELECT LOCATE('audi', name,8), name FROM forumpost WHERE LOCATE('audi', name,8) > 0 ;
+------------------------+------------------------------------+
| LOCATE('audi', name,8) | name                               |
+------------------------+------------------------------------+
|                     22 | An auditor drives an audi.         |
|                     22 | An auditor drives an audi car      |
|                     22 | An auditor drives an audi blue car |
|                     26 | An auditor drives an red audi car  |
+------------------------+------------------------------------+


OK so we found the ones we are after. Now we need to write the update statement.

We cannot use the replace this time.


 UPDATE forumpost SET name = REPLACE(name, LOCATE('audi', name,8), 'mercedes') WHERE LOCATE('audi', name,8) > 0 ;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 4  Changed: 0  Warnings: 0


Notice it found the rows but did not change anything.

So try again and I do not want to assume the location 8. I want the 2nd value of audi.
So  a test shows that with SUBSTRING_INDEX I can skip the 1st one and USE CONCAT


SELECT name , CONCAT ( SUBSTRING_INDEX(name, 'audi', 2) , ' mercedes ' , SUBSTRING_INDEX(name, 'audi', -1) )  as newvalue
FROM forumpost
WHERE LOCATE('audi', name,10) > 0 ;
+-----------------------------------+-----------------------------------------+
| name                              | newvalue                                |
+-----------------------------------+-----------------------------------------+
| An auditor drives an audi.        | An auditor drives an  mercedes .        |
| An auditor drives an audi.        | An auditor drives an  mercedes .        |
| An auditor drives an audi car     | An auditor drives an  mercedes  car     |
| An auditor drives an red audi car | An auditor drives an red  mercedes  car |
+-----------------------------------+-----------------------------------------+

root@localhost [test]> UPDATE forumpost SET name = CONCAT(SUBSTRING_INDEX(name, 'audi', 2) , ' mercedes ' , SUBSTRING_INDEX(name, 'audi', -1) )
WHERE LOCATE('audi', name,10) > 0 ;
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4  Changed: 4  Warnings: 0

root@localhost [test]> select * from forumpost;
+-----------------------------------------+
| name                                    |
+-----------------------------------------+
| An auditor drives an  mercedes .        |
| An auditor drives a volvo.              |
| An auditor drives an  mercedes .        |
| An auditor drives an  mercedes  car     |
| An auditor drives an red  mercedes  car |
+-----------------------------------------+
5 rows in set (0.00 sec)


Now, granted the grammer with the use of "an" is invalid but that is another story.

More information on these can be found here:

• PATTERN MATCHING
• LIKE OPERATOR
• REGEXP OPERATOR
• SUBSTRING FUNCTION
• SUBSTRING_INDEX FUNCTION
• LOCATE FUNCTION