Hashing Algorithm in MySQL PASSWORD()

Posted in: Technical Track

Recently we had a question from a customer: what is the hashing algorithm implemented in PASSWORD() ?

The manual doesn’t give a straight answer in any of these two pages:




It is enough to dig a bit more to find the solution in https://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#4.1_and_later that specifies “mysql.user.Password stores SHA1(SHA1(password))” .


Instead of blindly trusting the documentation (even if I believe it is correct), I did some tests and was confused by the first result:

mysql> SELECT PASSWORD(“this_is_a_random_string”) `pass`\G

pass: *12E76A751EFA43A177049262A2EE36DA327D8E50

mysql> SELECT SHA1(SHA1(“this_is_a_random_string”)) `pass`\G

pass: 9b653fd9fb63e1655786bfa3b3e00b0913dfc177

So it looked like SHA1(SHA1(password)) wasn’t PASSWORD(password)), at least in this test.

The best documentation ever is the source code, so I read the source code and understood why my previous test was incorrect: the second SHA1() is applied to the binary data returned by the first SHA1() and not to its hex representation. Therefore in SQL I have to UNHEX() it before applying the second SHA1. In fact:

mysql> SELECT SHA1(UNHEX(SHA1(“this_is_a_random_string”))) `pass`\G

pass: 12e76a751efa43a177049262a2ee36da327d8e50


So yes, I confirmed that mysql.user.password stores SHA1(SHA1(password)) . I also hope this post is useful to understand how MySQL implements PASSWORD().

Want to talk with an expert? Schedule a call with our team to get the conversation started.

4 Comments. Leave new

Alexis Peters
October 7, 2015 8:10 am

Nice Article.
The PHP-equivalent for the Password function is as follows:

“*” . sha1(sha1(“password”, true))

Jean-Baptiste Lasselle
November 15, 2016 10:26 am

Hi, I searched the exact same subject, and imagine for the xact same resaon, with :
A/ a big thank you for sharing those findings of yours (soooooo good, after browsing any possibnle google search key phrase that makes sense in the context… :)
B/ Since it is obvious you wanna get control (outside mariadb) over the password generation / modification of your mariadb server users, I’ll be curious to read your experience on the subject: I am now designing a solution, and currrently am travaleing the whole security landscape. Just to sse If HAVE to kick mariadb off the project.

At any rate, jus another huge thank you for what you already shared !! :)

Bill McGonigle
December 16, 2019 7:38 pm

Perfect, thanks! Here’s one for ruby/puppet:

def hash_password(password)
double_sha1 = Digest::SHA1.hexdigest(Digest::SHA1.digest password)
return ‘*’ + double_sha1.upcase

Shah Alam Muhammedullah
September 26, 2020 2:18 am

PASSWORD function as a nodejs module (‘./password.js’):
let sha1 = (k, f) => {return require(‘crypto’).createHash(‘sha1’).update(k).digest(f)};
module.exports = secret => {return ‘*’ + sha1(sha1(secret, ‘bin’), ‘hex’).toUpperCase()};
// usage:
const PASSWORD = require(‘./password’);
PASSWORD function can be written as:
let PASSWORD = k => {return ‘*’ + require(‘crypto’).createHash(‘sha1’).update(require(‘crypto’).createHash(‘sha1’).update(k).digest(‘bin’)).digest(‘hex’).toUpperCase()};
// usage:


Leave a Reply

Your email address will not be published. Required fields are marked *