How do I create and store md5 passwords in mysql

Question

Probably a very newbie question but, Ive been reading around and have found some difficulty in understanding the creation and storage of passwords. From what i've read md5/hash passwords are the best ways to store them in a database. However, how would I go about creating those passwords in the first place?

So say I have a login page with user bob, and password bob123 - how will I 1. get bobs password into the database to begin with (hashed) 2. how do I retrive and confirm the hashed password?

Thanks

Acetylate

Answer 1

Edit 2017/11/09: Be sure to take a look at the answer from O Jones.

First off MD5 isn't the greatest hashing method you could use for this try sha256 or sha512

That said lets use hash('sha256') instead of md5() to represent the hashing part of the process.

When you first create a username and password you will hash the raw password with some salt (some random extra characters added to each password to make them longer/stronger).

Might look something like this coming in from the create user form:

$escapedName = mysql_real_escape_string($_POST['name']); # use whatever escaping function your db requires this is very important.
$escapedPW = mysql_real_escape_string($_POST['password']);

# generate a random salt to use for this account
$salt = bin2hex(mcrypt_create_iv(32, MCRYPT_DEV_URANDOM));

$saltedPW =  $escapedPW . $salt;

$hashedPW = hash('sha256', $saltedPW);

$query = "insert into user (name, password, salt) values ('$escapedName', '$hashedPW', '$salt'); ";

Then on login it'll look something like this:

$escapedName = mysql_real_escape_string($_POST['name']);
$escapedPW = mysql_real_escape_string($_POST['password']);

$saltQuery = "select salt from user where name = '$escapedName';";
$result = mysql_query($saltQuery);
# you'll want some error handling in production code :)
# see http://php.net/manual/en/function.mysql-query.php Example #2 for the general error handling template
$row = mysql_fetch_assoc($result);
$salt = $row['salt'];

$saltedPW =  $escapedPW . $salt;

$hashedPW = hash('sha256', $saltedPW);

$query = "select * from user where name = '$escapedName' and password = '$hashedPW'; ";

# if nonzero query return then successful login

Knipe

note - you should not use the same salt for every password you intend to hash. It is better to use a unique (random) salt for each password – Ozan

Why do you escape the password before it is hashed? I think you should escape the hashed password you want to inject in the database. – Annieannihilate

You're assuming hash returns binary output. It can but you have to pass it true as a third parameter see php.net/manual/en/function.hash.php . Since that third parameter defaults to false hash returns hexvalues only which are safe. I escape most things incoming from forms as a matter of best practice. We might be able to avoid it here but always doing it and then examining where we might go without it feels safer to me. – Knipe

I know this thread is very old, however I am curious how exactly to write the salt parsing query... – Satterwhite

See the manual on how to execute a query string and then parse the result: php.net/manual/en/function.mysql-query.php. It is similar for other databases too. Most readable is the fetch_assoc variety like Example #2 in that link. Then you can grab the salt string value with something like: $row = mysql_fetch_assoc($result); $salt = $row['salt']; – Knipe

As time marches on and computers get faster, password-hashing methods need updating. Please read this. php.net/manual/en/function.password-needs-rehash.php – Lamonicalamont

Answer 2

you have to reason in terms of hased password:

store the password as md5('bob123'); when bob is register to your app

$query = "INSERT INTO users (username,password) VALUES('bob','".md5('bob123')."');

then, when bob is logging-in:

$query = "SELECT * FROM users WHERE username = 'bob' AND password = '".md5('bob123')."';

obvioulsy use variables for username and password, these queries are generated by php and then you can execute them on mysql

Paperboy

You should add a salt to this function to make it harder to crack all passwords in the table: $salt="1qTR3"; ... md5('bob123'.$salt).... Otherwise - Love your solution! – Brana

Answer 3

Please don't use MD5 for password hashing. Such passwords can be cracked in milliseconds. You're sure to be pwned by cybercriminals.

PHP offers a high-quality and future proof password hashing subsystem based on a reliable random salt and multiple rounds of Rijndael / AES encryption.

When a user first provides a password you can hash it like this:

 $pass = 'whatever the user typed in';
 $hashed_password = password_hash( "secret pass phrase", PASSWORD_DEFAULT );

Then, store $hashed_password in a varchar(255) column in MySQL. Later, when the user wants to log in, you can retrieve the hashed password from MySQL and compare it to the password the user offered to log in.

 $pass = 'whatever the user typed in';
 $hashed_password = 'what you retrieved from MySQL for this user';
 if ( password_verify ( $pass , $hashed_password )) {
    /* future proof the password */
    if ( password_needs_rehash($hashed_password , PASSWORD_DEFAULT)) {
       /* recreate the hash */
       $rehashed_password = password_hash($pass, PASSWORD_DEFAULT );
       /* store the rehashed password in MySQL */
     }
     /* password verified, let the user in */
 }
 else {
     /* password not verified, tell the intruder to get lost */
 }

How does this future-proofing work? Future releases of PHP will adapt to match faster and easier to crack encryption. If it's necessary to rehash passwords to make them harder to crack, the future implementation of the password_needs_rehash() function will detect that.

Don't reinvent the flat tire. Use professionally designed and vetted open source code for security.

Lamonicalamont

Answer 4

Insertion:

INSERT INTO ... VALUES ('bob', MD5('bobspassword'));

retrieval:

SELECT ... FROM ... WHERE ... AND password=md5('hopefullybobspassword');

is how'd you'd do it directly in the queries. However, if your MySQL has query logging enabled, then the passwords' plaintext will get written out to this log. So... you'd want to do the MD5 conversion in your script, and then insert that resulting hash into the query.

Feeney

Answer 5

PHP has a method called md5 ;-) Just $password = md5($passToEncrypt);

If you are searching in a SQL u can use a MySQL Method MD5() too....

 SELECT * FROM user WHERE Password='. md5($password) .'

or SELECT * FROM ser WHERE Password=MD5('. $password .')

To insert it u can do it the same way.

Photogene

Answer 6

Why don't you use the MySQL built in password hasher:

http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html

mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+

for comparison you could something like this:

select id from PassworTable where Userid='<userid>' and Password=PASSWORD('<password>')

and if it returns a value then the user is correct.

Eisteddfod

Answer 7

I'm not amazing at PHP, but I think this is what you do:

$password = md5($password)

and $password would be the $_POST['password'] or whatever

Eaton

Answer 8

just get the hash by following line and store it into the database:

$encryptedValue = md5("YOUR STRING");

Raki

This is neither an encrypted value, nor should passwords be hashed with MD5. The password_hash() function is the way to go. – Scoreboard

Answer 9

To increase security even more, You can have md5 encryption along with two different salt strings, one static salt defined in php file and then one more randomly generated unique salt for each password record.

Here is how you can generate salt, md5 string and store:

    $unique_salt_string = hash('md5', microtime()); 
    $password = hash('md5', $_POST['password'].'static_salt'.$unique_salt_string);
    $query = "INSERT INTO users (username,password,salt) VALUES('bob','".$password."', '".$unique_salt_string."');

Now you have a static salt, which is valid for all your passwords, that is stored in the .php file. Then, at registration execution, you generate a unique hash for that specific password.

This all ends up with: two passwords that are spelled exactly the same, will have two different hashes. The unique hash is stored in the database along with the current id. If someone grab the database, they will have every single unique salt for every specific password. But what they don't have is your static salt, which make things a lot harder for every "hacker" out there.

This is how you check the validity of your password on login.php for example:

     $user = //username input;
     $db_query = mysql_query("SELECT salt FROM users WHERE username='$user'");
     while($salt = mysql_fetch_array($db_query)) {
            $password = hash('md5',$_POST['userpassword'].'static_salt'.$salt[salt]);
}

This method is very powerful and secure. If you want to use sha512 encryption, just to put that inside the hash function instead of md5 in above code.

Listel

MD5 should never be used for security purposes. Nowadays, the only acceptable way to hash passwords is via password_hash(). Also, the mysql_* functions are deprecated, and your code is vulnerable to SQL injection, which makes the password irrelevant. – Charge

This is a good example of how it should not be done. MD5 is way too fast and allows brute-forcing of 10Giga MD5/sec. The salt is better created by using the random source of the OS, because the current time is predictable and helps planning an attack. – Scoreboard