17

I've created a user ... but forgotten the password

mysql> create user 'blayo'@'%' identified by 'right';

Which Linux command line tool can encrypt the password the same way mysql 5.5 does ?

mysql> select Password,User from mysql.user
------------------------------------------+-------+
*920018161824B14A1067A69626595E68CB8284CB | blayo |

...to be sure I use the right one

$ tool right
*920018161824B14A1067A69626595E68CB8284CB
2

9 Answers 9

17

Some one-liners:

MySQL (may require you add -u(user) -p):

mysql -NBe "select password('right')"

Python:

python -c 'from hashlib import sha1; print "*" + sha1(sha1("right").digest()).hexdigest().upper()'

Perl:

perl -MDigest::SHA1=sha1_hex -MDigest::SHA1=sha1 -le 'print "*". uc sha1_hex(sha1("right"))'

PHP:

php -r 'echo "*" . strtoupper(sha1(sha1("right", TRUE))). "\n";'

Ruby:

ruby -e 'require "digest/sha1"; puts "*" + Digest::SHA1.hexdigest(Digest::SHA1.digest("right")).upcase'

All output:

*920018161824B14A1067A69626595E68CB8284CB

2
  • Thanks, I've linked to your answer in serverfault.com/a/846281/236916
    – mwfearnley
    Commented Jun 26, 2017 at 14:04
  • @alexjhart Can i get an update for the new mysql 5.7.8 password hashing algorithm :) ? (maybe a little detail on how you found this out too?) Commented Jan 3, 2019 at 20:23
16

Well, the trivial (perhaps cheating) way would be to run:

mysql -NBe "select password('right')"

This will produce a password using whatever password hashing scheme your version of mysql uses. [EDIT: added -NB, which gets rid of the column names and ascii table art.]

5
  • 6
    And, handily, this will store your shiny new password in clear text both in your MySQL log and in your bash account history log. Commented Sep 14, 2014 at 18:16
  • @Craig To prevent both you can prepend a ' ' (space) right before the mysql to hide it from the bash history and you can use SET sql_log_off=ON; right before the SELECT ... to hide it from the MySQL log.
    – Murmel
    Commented Mar 13, 2019 at 9:54
  • 1
    @Murmel thanks for the update! Off course, it should default to not logging passwords. Security should always be the default setting. Commented Mar 13, 2019 at 17:23
  • @Craig To be fair, this is already done by mysql (v5.7, the last version this function exists, got removed with v8.0.11)) on the: 1. clientside (affecting .mysql_history) ignoring all statements matching *PASSWORD* and 2. serverside for several stmts (except SELECT). See: MySQL 5.7 Manual - Passwords and Logging and MySQL 5.7 - Manual - mysql Client Logging
    – Murmel
    Commented Mar 14, 2019 at 8:31
  • That's good feedback! Unfortunately, there are still quite a few people who for various reasons (myself included) are still stuck using mysql versions older than 5.7. Commented Apr 22, 2019 at 5:38
8

One more using the shell:

echo -n 'right' | sha1sum | xxd -r -p |\
sha1sum | tr '[a-z]' '[A-Z]' | awk '{printf "*%s", $1}'

Explanation:

  1. echo -n print without linebreak
  2. sha1sum first SHA1
  3. xxd -r -p unhex the hash
  4. sha1sum second SHA1
  5. tr '[a-z]' '[A-Z]' convert to uppercase
  6. awk '{print "*" $1}' add leading *

More details:

Between 2. and 3. an optional awk '{printf "%s", $1}' step could be insterted for newline- and hyphen-removal. But xxd will ignore them anyway (Thanks to dave_thompson_085).

Furthermore step 5 and 6 could be done at once by replacing them with {print "*" toupper($1)} (Thanks to dave_thompson_085).

1
  • 2
    awk can do the uppercase, and outputting an incomplete (last) line to terminal is incovenient: .. | sha1sum | awk '{print "*" toupper($1)}'. And you don't need to worry about the NL and even the hyphen on the xxd -r -p, they're ignored. Commented Dec 6, 2015 at 2:54
6

It still kind of blows my mind that MySQL doesn't bother obfuscating passwords on the command line and in logs. And that's the only reason I'm adding an answer rather than just commenting on @Gilles answer.

So, of course, you could just log into MySQL as an admin and set a new password for your blayo user, as @patrix suggested.

However, the standard way to do that is by using MySQL's password() function, which takes a plaintext password as an argument (seriously?).

If you do that, you leave the plaintext version of your MySQL user's password sitting around in your bash history and in your MySQL logs, for easy retrieval later on by whoever manages to get access to those log files.

Wouldn't it be better to have a little utility that would prompt for the password, without echoing it to the screen or to your logs, then provide you with the resulting MySQL-compatible hash?

So, modifying @Gilles's answer just a bit, how about a little shell script that uses Python, like the following. You could easily modify this to run a SQL statement against your MySQL database to set the password all at once. But even without going that far, just copy and paste the resulting hash into a SQL statement to update the users table:

#!/bin/bash

mysqlpwd=$(/usr/bin/python -c 'from hashlib import sha1; import getpass; print "*" + sha1(sha1(getpass.getpass("New MySQL Password:")).digest()).hexdigest()')

echo $mysqlpwd
3
  • @HalosGhost; what was the edit about? It didn't change anything? ;-) Commented Sep 15, 2014 at 2:42
  • It enforced syntax highlighting (as noted in the edit summary) and made obvious by the actual text of the edit.
    – HalosGhost
    Commented Sep 15, 2014 at 3:06
  • 3
    Ah, so. Honestly didn't realize what you'd done (you obviously added the <!-- language: lang-bsh --> line). Handy! I like it. Thanks! Commented Sep 15, 2014 at 4:29
4

The hash is sha1(sha1(password)). Since there is no salt (which is a grave security flaw), you can look up the hash in a table.

With just POSIX tools plus sha1sum from GNU coreutils or BusyBox, sha1(sha1(password)) is annoying to compute because the sha1sum command prints out the digest in hexadecimal and there is no standard tool to convert to binary.

awk "$(printf %s 'right' | sha1sum |
       sed -e 's/ .*//' -e 's/../, 0x&/g' \
           -e 's/^/BEGIN {printf "%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c"/' \
           -e 's/$/; exit}/')" | sha1sum

Python has standard digests in its standard libraries, so it's a simple one-liner.

printf %s 'right' |
python -c 'from hashlib import sha1; import sys; print sha1(sha1(sys.stdin.read()).digest()).hexdigest()'

Or with the password inside the one-liner:

python -c 'from hashlib import sha1; print sha1(sha1("right").digest()).hexdigest()'
3
  • I love this answer in spirit, but it isn't quite working. When I hit the enter key, it still wants to keep reading input instead of moving on. Also, the input echos back to the screen, which might not be desirable. How about something like this, though? unix.stackexchange.com/a/155583/37401 Commented Sep 14, 2014 at 19:33
  • @Craig How to read interactive input is beyond the scopre of the question. I assume that the script already has the password; usually it's stored in a file, it's rare to type a database password interactively. If you want to read interactive input, you can use the read shell builtin to read a line; run stty echo first if you want to turn off echo and ssty +echo to turn it back on. In Python, you can use the getpass module. Commented Sep 14, 2014 at 19:49
  • Yeah, I know... and fair enough! I just hate horrible security the further down the road I go, and MySQL security is pretty horrible in general, so I have this knee-jerk tendency to want to inject a little security insight wherever I get a chance. ;-) Commented Sep 14, 2014 at 20:42
2

Another using the shell

echo -n 'right' | openssl sha1 -binary | openssl sha1 -hex | \
    sed 's/^.* //' | \
    awk '{print "*"toupper($0)}'

...which outputs:

*920018161824B14A1067A69626595E68CB8284CB
2
  • 1
    It prints *(STDIN)= 920018161824B14A1067A69626595E68CB8284CB in Debian bash. But +1 for an intuitive alternative way.
    – o.v
    Commented Dec 23, 2020 at 10:33
  • @o.v thanks for commenting that issue - fixed above with the sed command Commented Jan 3, 2021 at 20:15
1

If you crated a user and forgotten the password this is what you should do and it's easier.

This also happened to me a dozen times while I was doing 10 billion things at once. The best way that I recovered my password was:

stopped the mysql server completely first

issued mysqld_safe --skip-grant-tables &

then I logged in as root without issuing a password, just mysql -u mysql-user (it lets you in after the mysqld_safe)

then go to the mysql > user table and update the password for the user

then go back and restart mysql

see if that works and let us know.

1
  • This seems like it would be more for the case that you can't log in at all, rather than just as a specific user.
    – mwfearnley
    Commented Jun 26, 2017 at 13:41
0

MySQL 4.1+ uses double SHA1

> SELECT PASSWORD("right")
*920018161824B14A1067A69626595E68CB8284CB

> SELECT SHA1(UNHEX(SHA1("right")))
920018161824B14A1067A69626595E68CB8284CB

sh-3.2# php -r 'echo "*" . sha1(sha1("right", TRUE)). "\n";'
*920018161824b14a1067a69626595e68cb8284cb

This algorithm can be easily ported to other languages​​. A difference is that password hashes in the "select password" always begin with a “*” character.

0

Python3 one liner, in case it's useful to anyone:

python3 -c 'from hashlib import sha1; print(f"""*{sha1(sha1("right".encode()).digest()).hexdigest().upper()}""")'

*920018161824B14A1067A69626595E68CB8284CB

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .