MySQL hashed column password+script bash

Question

I'd a little problem.

I'd created a MySQL table with the password column encrypted with SHA1.

mysql> CREATE TABLE IF NOT EXISTS user_encrypted (
    username varchar(50) COLLATE utf8_unicode_ci NOT NULL PRIMARY KEY DEFAULT 'username',
    hashed_password varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'password',
    user_mail varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL DEFAULT 'your@email.com',
    user_phone varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL DEFAULT '+33 1 23 45 67 89',
    user_online tinyint(1) NOT NULL DEFAULT '0',
    user_enable tinyint(1) NOT NULL DEFAULT '1',
    user_max_connection tinyint(1) NOT NULL DEFAULT '2',
    user_start_date date NOT NULL DEFAULT '2015-05-01',
    user_end_date date NOT NULL DEFAULT '0000-00-00',
    KEY  hashed_password (hashed_password)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Result

Query OK, 0 rows affected (0.01 sec)

I'd a script, which call the MySQL table :

#!/bin/bash
. /etc/openvpn/script/config.sh

#New Encrypted_password
#username=$(mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -sN -e "select username from user_encrypted where username = '$username' AND hashed_password = '$password' AND user_enable=1 AND user_max_connection=2 AND user_start_date != user_end_date AND TO_DAYS(now()) >= TO_DAYS(user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user_end_date) OR user_end_date='0000-00-00')")

##Check user
[ "$username" != '' ] && [ "$username" = "$username" ] && echo "user : $username" && echo 'authentication ok.' && exit 0 || echo 'authentication failed.'; exit 1

The problem is, when I use the script, and OpenVPn ask me for user / password, I use Test and Test1234, but, the system refuse the password. It seems the 'non-encrypted" password can't be translated to encrypted password for checking if the password for the table is the same as i entered in the client OpenVPN.

I don't know how to do, in my script bash for the password I enter is "converted" to encrypted password for checking with the database...

Rhank you

Just a note: it's hashed, not encrypted. Latter can be decrypted, former cannot. — Sami Kuhmonen, Aug 29 '15 at 19:39
Another note: You should [salt the password](http://stackoverflow.com/a/2178251/4811803) before hashing. — Snild Dolkow, Aug 29 '15 at 19:43

score 0 · Answer 1 · edited May 23 '17 at 11:58

Your bash script doesn't hash the password. Hence, you're comparing the password with a hash of the password.

You say it's a SHA1 hash, which conveniently exists as a built-in function in MySQL. Changing

where username = '$username' AND hashed_password = '$password' ...

into

where username = '$username' AND hashed_password = SHA1('$password') ...

should fix your immediate problem.

I want to bring up two other problems with your code -- these are unfortunately pretty common mistakes:

It appears you are not using a salt for the password hash. You should -- it would make it significantly harder for attackers to figure out your password from the hash. Please read this.
Your login script is vulnerable to SQL injection. What if an attacker puts a string like admin'; SELECT * FROM user_encrypted WHERE 0 AND ' (or something to that effect) as a username? Please read this.

Hi Snild Dolkow, Thanks for your answer. I was so fast for posting my problem, because after thinking about some minutes, I solved it by myself. This case can be closed, or can be used for sysadmin Jr when they don't know how to do ;) Thnaks again. — max524, Aug 30 '15 at 05:29

MySQL hashed column password+script bash

1 Answers1