How can I decrypt MySQL passwords

by

If a proper encryption method was used, it’s not going to be possible to easily retrieve them.

Just reset them with new passwords.

Edit: The string looks like it is using PASSWORD():

UPDATE user SET password = PASSWORD("newpassword");

How can I decrypt MySQL passwords

You can’t really because they are hashed and not encrypted.

Here’s the essence of the PASSWORD function that current MySQL uses. You can execute it from the sql terminal:

mysql> SELECT SHA1(UNHEX(SHA1("password")));

+------------------------------------------+
| SHA1(UNHEX(SHA1("password")))            |
+------------------------------------------+
| 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+------------------------------------------+
1 row in set (0.00 sec)

How can I change or retrieve these?

If you are having trouble logging in on a debian or ubuntu system, first try this (thanks to tohuwawohu at https://askubuntu.com/questions/120718/cant-log-to-mysql):

$ sudo cat /etc/mysql/debian.conf | grep -i password
...
password: QWERTY12345...

Then, log in with the debian maintenance user:

$ mysql -u debian-sys-maint -p
password:

Finally, change the user’s password:

mysql> UPDATE mysql.user SET Password=PASSWORD('new password') WHERE User="root";
mysql> FLUSH PRIVILEGES;
mysql> quit;

When I look in the PHPmyAdmin the passwords are encrypted

Related, if you need to dump the user database for the relevant information, try:

mysql> SELECT User,Host,Password FROM mysql.user;
+------------------+-----------+----------------------+
| User             | Host      | Password             |
+------------------+-----------+----------------------+
| root             | localhost | *0123456789ABCDEF... |
| root             | 127.0.0.1 | *0123456789ABCDEF... |
| root             | ::1       | *0123456789ABCDEF... |
| debian-sys-maint | localhost | *ABCDEF0123456789... |
+------------------+-----------+----------------------+

And yes, those passwords are NOT salted. So an attacker can prebuild the tables and apply them to all MySQL installations. In addition, the adversary can learn which users have the same passwords.

Needles to say, the folks at mySQL are not following best practices. John Steven did an excellent paper on Password Storage Best Practice at OWASP’s Password Storage Cheat Sheet. In fairness to the MySQL folks, they may be doing it because of pain points in the architecture, design or implementation (I simply don’t know).

If you use the PASSWORD and UPDATE commands and the change does not work, then see http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html. Even though the page is named “resetting permissions”, its really about how to change a password. (Its befuddling the MySQL password change procedure is so broken that you have to jump through the hoops, but it is what it is).

More Related Contents:

  1. How do I set the time zone of MySQL?
  2. SQL statement is ignoring where parameter
  3. How to set initial value and auto increment in MySQL?
  4. Types in MySQL: BigInt(20) vs Int(20)
  5. Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
  6. nodejs mysql Error: Connection lost The server closed the connection
  7. When should I use a composite index?
  8. Order a MySQL table by two columns
  9. How can I restore the MySQL root user’s full privileges?
  10. Mysql: Select rows from a table that are not in another
  11. add column to mysql table if it does not exist
  12. Sorting varchar field numerically in MySQL
  13. setting global sql_mode in mysql
  14. Compare dates in MySQL
  15. MySQL root password change
  16. MySQL join query using like?
  17. MySQL: update a field only if condition is met
  18. MySql Query: Select top 3 rows from table for each category
  19. MySQL: How to select records for this week?
  20. Split a string and loop through values in MySQL stored procedure
  21. MySQL Select Query – Get only first 10 characters of a value
  22. Create an index on a huge MySQL production table without table locking
  23. MySQL concurrency, how does it work and do I need to handle it in my application
  24. mysql select id and name from other table and join query
  25. List of Constraints from MySQL Database
  26. MySQL licensing and GPL [closed]
  27. How to do unique constraint works with NULL value in MySQL
  28. make an ID in a mysql table auto_increment (after the fact)
  29. How do I make a MySQL database run completely in memory?
  30. Using OR in LIKE Query in MySQL to compare multiple fields

Leave a Comment