How do I create and store md5 passwords in mysql

Question 1

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

Question 2

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

Question 3

You don't need "MD5 hashes", you simply need a random string of characters. These need not have anything to do with MD5 at all. So all you need is a good PRNG. For instance:

$token = mcrypt_create_iv($rawLength, MCRYPT_DEV_URANDOM);
// or
$token = openssl_random_pseudo_bytes($rawLength);
// or
$token = file_get_contents('/dev/urandom', false, null, 0, $rawLength);

Then base64_encode or bin2hex the raw value to get an ASCII character string.

Question 4

How about taking another approach or angle at this problem? Ask why the password is required to be in plaintext: if it's so that the user can retrieve the password, then strictly speaking you don't really need to retrieve the password they set (they don't remember what it is anyway), you need to be able to give them a password they can use.

Think about it: if the user needs to retrieve the password, it's because they've forgotten it. In which case a new password is just as good as the old one. But, one of the drawbacks of common password reset mechanisms used today is that the generated passwords produced in a reset operation are generally a bunch of random characters, so they're difficult for the user to simply type in correctly unless they copy-n-paste. That can be a problem for less savvy computer users.

One way around that problem is to provide auto-generated passwords that are more or less natural language text. While natural language strings might not have the entropy that a string of random characters of the same length has, there's nothing that says your auto-generated password needs to have only 8 (or 10 or 12) characters. Get a high-entropy auto-generated passphrase by stringing together several random words (leave a space between them, so they're still recognizable and typeable by anyone who can read). Six random words of varying length are probably easier to type correctly and with confidence than 10 random characters, and they can have a higher entropy as well. For example, the entropy of a 10 character password drawn randomly from uppercase, lowercase, digits and 10 punctuation symbols (for a total of 72 valid symbols) would have an entropy of 61.7 bits. Using a dictionary of 7776 words (as Diceware uses) which could be randomly selected for a six word passphrase, the passphrase would have an entropy of 77.4 bits. See the Diceware FAQ for more info.

a passphrase with about 77 bits of entropy: "admit prose flare table acute flair"
a password with about 74 bits of entropy: "K:&$R^tt~qkD"

I know I'd prefer typing the phrase, and with copy-n-paste, the phrase is no less easy to use that the password either, so no loss there. Of course if your website (or whatever the protected asset is) doesn't need 77 bits of entropy for an auto-generated passphrase, generate fewer words (which I'm sure your users would appreciate).

I understand the arguments that there are password protected assets that really don't have a high level of value, so the breach of a password might not be the end of the world. For example, I probably wouldn't care if 80% of the passwords I use on various websites was breached: all that could happen is a someone spamming or posting under my name for a while. That wouldn't be great, but it's not like they'd be breaking into my bank account. However, given the fact that many people use the same password for their web forum sites as they do for their bank accounts (and probably national security databases), I think it would be best to handle even those 'low-value' passwords as non-recoverable.

Question 5

The easiest way is, like you said, to use a configuration file.

Many frameworks use this (Zend, CakePHP, Kohana, etc) and it's the most common way of doing things (even in a non-PHP environment such as ASP.NET with its web.config files). This allows you also to copy over configuration values from environment to environment by just copying the files for the site, which is a benefit over relying on server-setup environment variables (which can very quickly be lost and forgotten).

You shouldn't need to worry about obfuscation of the password since it's not a world-accessible file, it certainly shouldn't be web accessible. What I mean by this is that you would either a) Tell your web server not to serve your configuration file (IIS already does this with web.config files and serves a HTTP 404.8 status instead of the contents) or b) Move it outside of your web served directory. If somebody can see your configuration file, it's worse than having it in your source code.

It's also going to be a good idea to have a base (empty / default) version of the configuration file, and separate it out per environments, so that you could have a different configuration file for production, development, and testing platforms.

An environment variable is the most common way to differentiate between these environments, something like the below code:

// Check if it's been set by the web server
if (!empty($_ENV['ENVIRONMENT'])) {
    // Copy from web server to PHP constant
    define('ENVIRONMENT', $_ENV['ENVIRONMENT']);
}

if (!defined('ENVIRONMENT')) {
    // Default to development
    define('ENVIRONMENT', 'development');
}

// Load in default configuration values
require_once 'config.default.php';

// Load in the overridden configuration file for this environment
require_once 'config.' . ENVIRONMENT . '.php';

Another way that is pretty common is to use an XML configuration file and only read in the values that you need as appropriate (storing a cached copy of the config file in memory). This can very easily be restricted to only load in certain values, rather than allowing arbitrary inclusion of PHP files and is overall a better solution in my opinion, but the above should get you started in the right direction.

You'll probably want your VCS to ignore the file. On the other hand, you might want a skeleton of the file, or one with reasonable defaults (the latter does not apply to login data, of course), to be version controlled. A common way to deal with that is to have a checked-in template configuration file, and the installation procedure copies that file to the location of the real configuration file, where it is customized. This can be a manual, or an automated, process.

_{(Whilst somewhat unrelated to the main question, introducing a constant for your environment allows you to do some other cool stuff like deferring to a fake mail implementation instead of a live SMTP one, but of course this could also be done with a configuration file)}

Question 6

Goodness, that was fun... :-/

This is tested code that does as required. There are lots of comments. Feel free to remove them to see the code more clearly. Whatever...

You should be able to change the $allRoles array to get the roles to print in a different order. I have tried it and it works fine.

It runs on PHP 5.3.18 on windows XP (XAMPP).

Added some css to make the table clearer.

Changed the code to read the data from a 'mysqli' query rather than an array

see the lines marked '!important' to ensure it works correctly.

sample output:

Roles       01/02/14        05/02/14        08/02/14
musician    Bob             Donald          Charles
leader      Jerry           --              Baz
singer      Carol           Freddy          Norman

code:

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Q2220229 - Pivot table</title>
    <style>
      td {
        border-bottom: 1px solid grey;
        width: 10em;
      }
    </style>
  </head>

  <body>
<?php

/*
 * Some test data base on:
 * Date     |Role      |Name
   =============================
   01/02/14 |Musician  |Bob
   01/02/14 |Leader    |Jerry
   01/02/14 |Singer    |Carol
   08/02/14 |Musician  |Charles
   08/02/14 |Leader    |Baz
   08/02/14 |Singer    |Norman
 *
 */

 /* sample output:
  *
  * Role     |01/02/14  |08/02/14
    ===============================
    Musician |Bob       |Charles
    Leader   |Jerry     |Baz
    Singer   |Carol     |Norman
  */

$db = mysqli_connect('localhost', 'test', 'test', 'testmysql');

// 1) Must return three columns only.
// 2) Can return any number of 'roles' - one per row
// 3) Any date range but beware you may need a wide page!
// 4) Must sort by date!  
$query = mysqli_query($db, "SELECT service_date, role, member FROM role_by_date ORDER BY service_date ASC, role ASC");

// i prefer to used named subscripts to make the code easier to read.
// These MUST match up with column alias from the above query!
define('THE_DATE', 'service_date'); // !important
define('ROLE',     'role');         // !imortant
define('MEMBER',   'member');       // !important

/*
 * Now, we need a complete array of Roles in the order that they are to be displayed.
 *
 * These names must match with the names of the roles in the input data.
 * They will be printed out in the order that they appear in the array.
 *
 * These are the only roles that will appear in the $outputDates array.
 * Add more and in any order to control which 'roles' are shown.  
 *
 */
$allRoles = array('musician', 'leader', 'singer'); // !important

/*
 * At some point we will need an output array that we can easily traverse and
 * print out as a row of dates. i.e. a 'page' of data.
 *
 * We will build it up as we go along...
 */
$outputDates = array(); // !important -- this is the 'pivoted' output array

/*
 * Start to process the input data.
 *
 * To make my life easier, i will use the 'read ahead' technique to simplify the code.
 */

$currentInputRow = mysqli_fetch_array($query);

while (isset($currentInputRow[THE_DATE])) { // process all the input array...

  // must be a new day...
  $currentDay = $currentInputRow[THE_DATE];

  // create an array to hold ALL the possible roles for this day...
  $theDayRoles = array();

  // initialise the array with default values for all the requested roles.
  foreach ($allRoles as $role) {
    $theDayRoles[$role] = '--';
  }

  // now we need to fill theDayRoles with what we actually have for the current day...
  while ($currentInputRow[THE_DATE] == $currentDay) { // loop around all records for the current day

    // set the appropiate DayRole to the current MEMBER
    $theDayRoles[$currentInputRow[ROLE]] = $currentInputRow[MEMBER];

    // read the next input row - may be current day, new day or no more
    $currentInputRow = mysqli_fetch_array($query);
  }
  // end of day on the input for whatever reason...

  /* we now have:
   *   1) Current Date
   *
   *   2) an array of members for ALL the roles on that day.
   *
   *   We need to output it to another array ($outputDates) where we can print it out
   *   by scanning the array line by line later.
   *
   *   I will 'pivot' the array and produce an output array we can scan sequentially later.
   */

   // to ensure that we are updating the correct $outputDates row i will use a subscript
   $currentOutputRowIdx = 0;

   // first add the current date to the output...
   $outputDates[$currentOutputRowIdx][] = $currentDay;
   $currentOutputRowIdx++; // next output row

   // we need to drive off the '$allRoles' array to add the role data in the correct order
   foreach ($allRoles as $outRole) {
     $outputDates[$currentOutputRowIdx][] = $theDayRoles[$outRole];
     $currentOutputRowIdx++; // next output row
   }

} // end of all the input data


/*
 * Now we just need to print the outputDates array one row at a time...
 */

// need the roles as the first column...
// so we need an index for which one we are currently printing

$currentRoleIdx = -1; // increment each time but allow for the first row being the title 'Roles'

echo '<table>';
foreach ($outputDates as $oneOutputRow) {

  echo '<tr>';

  // this is the first column...
  if ($currentRoleIdx < 0) {
    echo '<td>'. 'Roles' .'</td>';
  }
  else {
    echo '<td>'. $allRoles[$currentRoleIdx] .'</td>';
  }

  // now output the day info
  foreach($oneOutputRow as $column) {
    echo '<td>'. $column .'</td>';
  }
  echo '</tr>';
  $currentRoleIdx++; // next output Role to show...

}
echo '</table>';

?>
</body>
</html>

How do I create and store md5 passwords in mysql

Answers

php,mysql,password-storage