Passwords lists abound on the net. They exist to:
- Get a laugh out of silly passwords… 123456 anyone?
- Develop a dictionary file to check user passwords either for validation or cracking.
From MySQL 5.6 the validate_password plugin has been available. It enables password strength to be checked against known security best practice for passwords.
I downloaded two password lists from https://github.com/danielmiessler/SecLists
10-million-password-list-top-1000000.txt
xato-net-10-million-passwords-1000000.txt
Then I loaded them into a MySQL 5.7 db and checked how many would pass the MEDIUM password policy. The MEDIUM policy is basically Length=8; at least one numeric, lowercase/uppercase, and at least one special character.
Result:
Only at max of 0.13% of the passwords scored at least 75 using the validate_password_strength function.
https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_validate-password-strength
So if you were going to use those lists to build a dictionary file for the validate_password plugin you are mostly wasting your time. Most will already be blocked by the MEDIUM policy.
Recommendation: Make sure the validate_password plugin is on (it is on by default in MySQL 5.7 and MySQL 8.0) and that will force strong passwords.
Disabling is a super bad idea. Do you really want passwords like ‘123456’ or ‘qwerty’??
So what bad passwords remain?
Enter the Western Aust. Information Systems Audit Report 2018.
https://audit.wa.gov.au/wp-content/uploads/2018/08/report2018_14-IS-GCC-App-Pass.pdf
They found people were using nominally strong passwords, which were still too easy to guess!
Stuff like ‘October2017’ and ‘Summer2016’
So maybe we need a dictionary file for those kind of passwords. Because they pass the strength requirement but are too easy to guess.
Generating guessable strong passwords in the db.
Note: The trick using select 1 union select 2 is a common method if you don’t understand it, run the inner selects first to see what is happening. The SQL below will return the dates between two dates in the where clause.
SQL to generate Season and year type guessable passwords:
select distinct concat(monthname(selected_date),year(selected_date),'!') as SeasonPassword from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2018-01-01' and '2026-01-01' limit 10;
Sample output:
+----------------+ | SeasonPassword | +----------------+ | January2018! | | February2018! | | March2018! | | April2018! | | May2018! | | June2018! | | July2018! | | August2018! | | September2018! | | October2018! | +----------------+ 10 rows in set (0.01 sec)
SQL to generate guessable passwords with dayname and date:
select distinct concat(dayname(selected_date),date_format(selected_date,'%m%y'),'!') as DaynamePassword from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2018-01-01' and '2026-01-01' limit 10;
Sample Output:
+-----------------+ | DaynamePassword | +-----------------+ | Monday0118! | | Tuesday0118! | | Wednesday0118! | | Thursday0118! | | Friday0118! | | Saturday0118! | | Sunday0118! | | Thursday0218! | | Friday0218! | | Saturday0218! | +-----------------+ 10 rows in set (0.01 sec)
SQL to generate guessable passwords using l33t character replacements:
select distinct replace(concat(dayname(selected_date),date_format(selected_date,'%m%d'),'!'),'a','@') as DaynamePassword2Special from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2018-01-01' and '2026-01-01' limit 10;
Sample output:
+-------------------------+ | DaynamePassword2Special | +-------------------------+ | Mond@y0101! | | Tuesd@y0102! | | Wednesd@y0103! | | Thursd@y0104! | | Frid@y0105! | | S@turd@y0106! | | Sund@y0107! | | Mond@y0108! | | Tuesd@y0109! | | Wednesd@y0110! | +-------------------------+ 10 rows in set (0.01 sec)
Got this far? Seriously you must be in the 0.13% of all readers. Truly l33t.
If you would like a 1 Mbyte dictionary file to pass to the validate_password plugin, drop a comment below or like the post.
If there is enough interest I will post a dictionary file…
Until next time..
Appendix:
The worklog:
https://github.com/danielmiessler/SecLists
mysql> load data infile '/var/lib/mysql-files/10-million-password-list-top-1000000.txt' into table 10million_passwords; Query OK, 999999 rows affected (1.68 sec) Records: 999999 Deleted: 0 Skipped: 0 Warnings: 0s mysql> select validate_password_strength(password),count(*) from 10million_passwords group by validate_password_strength(password); +--------------------------------------+----------+ | validate_password_strength(password) | count(*) | +--------------------------------------+----------+ | 0 | 854 | | 25 | 511016 | | 50 | 486815 | | 100 | 1314 | +--------------------------------------+----------+
mysql> select password from 10million_passwords where validate_password_strength(password) >=75 limit 10; +-----------------+ | password | +-----------------+ | L58jkdjP! | | P@ssw0rd | | !QAZ2wsx | | 1qaz!QAZ | | 1qaz@WSX | | ZAQ!2wsx | | !QAZxsw2 | | NICK1234-rem936 | | xxPa33bq.aDNA | | !QAZ1qaz | +-----------------+ 10 rows in set (0.05 sec)
mysql> load data infile '/var/lib/mysql-files/xato-net-10-million-passwords-1000000.txt' into table xato_passwords; Query OK, 999998 rows affected (1.54 sec) Records: 999998 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select validate_password_strength(password),count(*) from xato_passwords group by validate_password_strength(password); +--------------------------------------+----------+ | validate_password_strength(password) | count(*) | +--------------------------------------+----------+ | 0 | 830 | | 25 | 510787 | | 50 | 487709 | | 100 | 672 | +--------------------------------------+----------+ 4 rows in set (0.68 sec) mysql> select password from xato_passwords where validate_password_strength(password)>=75 limit 10; +-----------------+ | password | +-----------------+ | L58jkdjP! | | P@ssw0rd | | 1qaz!QAZ | | !QAZ2wsx | | 1qaz@WSX | | ZAQ!2wsx | | !QAZxsw2 | | NICK1234-rem936 | | xxPa33bq.aDNA | | g00dPa$$w0rD | +-----------------+ 10 rows in set (0.04 sec)
Information Systems Audit Report 2018
https://audit.wa.gov.au/wp-content/uploads/2018/08/report2018_14-IS-GCC-App-Pass.pdf
Variants of date and season like Summer2017 or October2017
mysql> select validate_password_strength('Summer2017');
+------------------------------------------+
| validate_password_strength('Summer2017') |
+------------------------------------------+
| 50 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select validate_password_strength('October2017');
+-------------------------------------------+
| validate_password_strength('October2017') |
+-------------------------------------------+
| 50 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select validate_password_strength('Summer2017!');
+-------------------------------------------+
| validate_password_strength('Summer2017!') |
+-------------------------------------------+
| 100 |
+-------------------------------------------+
1 row in set (0.00 sec)
DBA Dojo 