How to save a hash of a password in MySQL

Question

I have the following table in my database called db_pass:

id | pass
=================
1  | dalmation123

I understand that I cannot store any password in plain text format in my database, how do I go about setting up a hash? This is the code I am using below. I would appreciate some help on how to change my table db_pass as well.

if(isset($_POST['pmsubmit']))
{
  LoginSubmit('pm', 'pmname', 'pmpass');
}

if(isset($_POST['tssubmit']))
{
  LoginSubmit('ts', 'dept', 'tspass');
}

function LoginSubmit($pm_or_ts, $the_name_input, $the_pass_input)
{
  global $pdo;
  $posted_name = $_POST[$the_name_input];
  $posted_pass = $_POST[$the_pass_input];
  // check if password matches the one in the table
  $query = $pdo->prepare("SELECT * FROM db_pass WHERE pass = :pass");
  $query->execute(array(":pass" => $posted_pass));
  // if there is a match then we log in the user
  if ($query->rowCount() > 0)
  {
    // session stuff
    $_SESSION[$the_name] = $posted_name;
    // refresh page
    header( 'Location: ' . $pm_or_ts . '/index.php' ) ;
    exit;
  } 
  // if there is no match then we present the user with an error
  else
  {
    echo "error";
    exit;
  }
}

Use `bcrypt` to hash passwords. There are literally thousands of articles for hashing passwords on Google... Also your current table for storing passwords is horrible; I could create account with the password "12345" and then log into *any* account using it. You should be storing the account name along with the hashed password. — Supericy, Feb 05 '13 at 21:59
absolute simplest and least secure method... `INSERT ... password=MD5(:password)`. not saying you should use this, but just showing how simple it can be. — Marc B, Feb 05 '13 at 22:07
There are some good examples [here](http://stackoverflow.com/q/4795385/492983) — Jon Hulka, Feb 05 '13 at 22:13
`bcrypt` is **not** a defined function. If you want to suggest a library do that rather than simply spouting unhelpful advice. Otherwise people will be tempted to implement it themselves, which is typically worse than using `sha1` — Philip Whitehouse, Feb 05 '13 at 22:23

Philip Whitehouse · Accepted Answer · 2013-02-05T22:32:45.157

1

$query = $pdo->prepare("SELECT * FROM db_pass WHERE pass = :pass");
$query->execute(array(":pass" => crypt($posted_pass)));

Don't ask me which algorithm crypt actually uses. The manual entry is totally nonsensical - apparently just checking the value of a constant changes the algorithm used by crypt() which is ridiculous ....

And it's alright people saying bcrypt. But bcrypt isn't a core PHP function. If they mean write your own, then it's a stupid idea - because your implementation would undoubtedly have flaws. If they mean a library they need to point one out - PHPass is commonly recommended, but I have no knowledge to recommend it myself.

It's hardly surprising most people still use sha1 is it?

edited Feb 05 '13 at 22:32

answered Feb 05 '13 at 22:19

Philip Whitehouse

4,293
3
23
36

Thanks Philip I managed to figure it out using your code as a reference! – methuselah Feb 05 '13 at 22:29
1

Philip, you've read the docs wrong. It's not "checking the value of a constant' that changes the crypt type, it's the length/formatting of the salt string. It *is* still a stupid way for the function to work, and the way they've written the docs is cryptic at best. – Sammitch Feb 05 '13 at 22:59

score 0 · Answer 2 · answered Feb 05 '13 at 22:15

It all comes down to this. you need to perform an operation on the user password before you save it to the database. you then perform the same operation on the submitted password before checking the if that password is valid for the username/password combination.

in most cases the "operation" is a hashing or encryption process such as MD5 or bcrypt

feeela · Answer 3 · 2013-02-05T22:12:25.123

In MySQL you could use the BINARY type to actually store hashes. A simple hash table in MySQL could look like:

CREATE TABLE IF NOT EXISTS `hastable` (
  `hash` binary(20) NOT NULL,
  `value` blob NOT NULL,
  PRIMARY KEY (`hash`)
);

For example a SHA1 hash is always 160 bits/20 bytes long and could be stored such a binary column. Using PHP, you could get the hash as follows: hash( 'sha1', $key, true );

But that has nothing to do with storing passwords…

How to save a hash of a password in MySQL

3 Answers3