Mysql encrypt sometimes returns null

Question

Why the first and last command line returns null??

$ mysql -u root -ppass -Bse "SELECT ENCRYPT('p@ssW0rd', 'ñsñsñsñ');"
NULL
$ mysql -u root -ppass -Bse "SELECT ENCRYPT('p@ssW0rd', 'dafdadsfe');"
dac0rB9hTC86M
$ mysql -u root -ppass -Bse "SELECT ENCRYPT('p@ssW0rd', 'dafdadsfeñññ');"
dac0rB9hTC86M
$ mysql -u root -ppass -Bse "SELECT ENCRYPT('p@ssW0rd', CONCAT('6', SUBSTRING(SHA(RAND()), -16)));"
63YRpxd2B8u1A
$ mysql -u root -ppass -Bse "SELECT ENCRYPT('p@ssW0rd', CONCAT('$6', SUBSTRING(SHA(RAND()), -16)));"
c7hTUIQiUwO02
$ mysql -u root -ppass -Bse "SELECT ENCRYPT('p@ssW0rd', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)));"
NULL

I've also executed:

$ mysql -u root -ppass -Bse "SELECT CONCAT('$6$', SUBSTRING(SHA(RAND()), -16));"
$7b07151fc5373796

And then here it comes the magic:

MariaDB [(none)]> SELECT ENCRYPT('p@ssW0rd', 'ñsñsñsñ');
+------------------------------------+
| ENCRYPT('p@ssW0rd', 'ñsñsñsñ')     |
+------------------------------------+
| NULL                               |
+------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT ENCRYPT('p@ssW0rd', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)));
+------------------------------------------------------------------------------------------------------------+
| ENCRYPT('p@ssW0rd', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))                                            |
+------------------------------------------------------------------------------------------------------------+
| $6$0edfe4f3541e5035$kYDyVhQ2sEHDlZE2GsNX0s2Xstg8z7Mj.D3ly.A0DzItg/5WIGoZldKQ0uSiaW9X/ljmmjGbWkMJwsY/WZ4/p. |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This is very annoying as I would like to insert from command line into a table which has password field not null:

$ mysql -u root -ppass  -Bse "INSERT INTO servermail.virtual_users (id, domain_id, password , email) VALUES ('1', '1', ENCRYPT('s1perP@$$', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'new@user.com');"
ERROR 1048 (23000) at line 1: Column 'password' cannot be null
$ "INSERT INTO servermail.virtual_users (id, domain_id, password , email) VALUES ('1', '1', ENCRYPT('s1perP@$$', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'new@user.com');" | mysql -u root -ppass
ERROR 1048 (23000) at line 1: Column 'password' cannot be null

I executed the SELECT ENCRYPT("p@ssW0rd", CONCAT("$6$", SUBSTRING(SHA(RAND()), -16))) about 6000 times in a loop and never to a NULL. It shouldn't be possible as RAND() is not supposed to return null. — lathspell, Mar 15 '17 at 08:21
Have you executed it in `bash` command line or in `mysql` command line? In my case, Ubuntu 16.04 LTS Mariadb 15.1, every time returns null — David, Mar 15 '17 at 15:35

score 1 · Answer 1 · answered Mar 14 '17 at 23:41

1

First: Don't use the MySQL ENCRYPT() function. By using it, you are sending unencrypted passwords to your database server, which may expose them to compromise (particularly if your database server is on a remote machine). It will no longer be available in future versions of MySQL, so it would be unwise to use in a new application.

Second: If you are going to use the ENCRYPT() function despite my warnings, you need to understand how it uses the salt argument. Randomly chosen values are unlikely to be valid, especially if they contain non-ASCII characters like ñ, and will cause the function to return NULL. If you do not have a salt, leave that argument out to use a randomly generated salt, instead of trying to generate one yourself.

answered Mar 14 '17 at 23:41

thanks. I'm following a tutorial from https://www.digitalocean.com/community/tutorials/how-to-configure-a-mail-server-using-postfix-dovecot-mysql-and-spamassassin to install dovecot and postfix. So the passwords are to be used by dovecot. Is there another way to encrypt passwords so dovecot can read them? Another question is that I'm generating the password randomly in the last case (which doesn't contain `ñ`). If not generating it randomly, will dovecot be able to match the password (leaving the salt generated by mysql)? – David Mar 15 '17 at 00:10
@duskwuff: MySQL encryt() is using Unix crypt() - Leaving the salt argument away would cause crypt() to create an old-style DES hashed password which is totally insecure nowadays. You have to pass $6$ to indicate a SHA2-512 hash. The SELECT should be fine. – lathspell Mar 15 '17 at 08:25

lathspell · Answer 2 · 2017-03-16T12:47:10.257

@David pointet in the right direction: your problem has nothing to do with MySQL - you're just not using Shell escaping right!

"crypt('$6', ..." is evaluated by the Shell to "crypt('', ..." which, as well as "crypt('6', ..." is treated as traditional DES salt.

"crypt('$6$', ..." is evaluated to "crypt('$', ..." which gives NULL as "$" is not a valid character for salt.

For the first examples, if the salt does not start with "$", it's regarded as tradional DES salt and only the first two characters are taken. They must be of [a-zA-Z0-9./]. Thus the first special chars give NULL but in the third example they are irrelevant.

See the crypt(3) manpage for more information about the salts.

[edit:] Better ways to write the commands: a) Quote $ characters:

  mysql -Bse "SELECT ENCRYPT('p@ssW0rd', CONCAT('\$6\$', SUBSTRING(SHA(RAND()), -16)));"

b) Use single-qutoes in shell and double quotes in SQL even if that's not strict to the SQL standard (MySQL accepts it, Postgres wont)

  mysql -Bse 'SELECT ENCRYPT("p@ssW0rd", CONCAT("$6$", SUBSTRING(SHA(RAND()), -16)));'

c) write the SQL into a file and use

   mysql < my.sql

Good point! Do you know how would it be the correct shell expression? thanks!! — David, Mar 16 '17 at 10:23

Mysql encrypt sometimes returns null

2 Answers2