19.3 Putting encrypted passwords in databases

19.3 Putting encrypted passwords in databases

19.3.1 Creating an encrypted password table in MySQL

If your Web site has a large number of members, storing encrypted passwords in a file may not be efficient and may be difficult to maintain. In this case, a good alternative is to consider putting all passwords into a database. Since we already have some experience on the database package MySQL, we will set up a password table in MySQL and use it to protect a page.

In fact, putting user or member passwords into a database can have all the benefits of a database application. For example, the following features can be easily added or implemented as SQL statements:

• Search password for a particular user.

• Set up new member accounts with password.

• Update or change password.

We will show you in this section how to develop Web pages to demonstrate all these functionalities. First, you need to create a password table to be used in MySQL. For obvious reasons, our password table in MySQL is a simple one and contains three fields only.

• id		A number to store the identity or index of each row.
• name		Character string (max. 30 characters) to store the user name.
• password		Character string (max. 60 characters) to store the encrypted password.

You can add more fields to have more features if you want. For example, you can add a field for the expiry date so that the password in your page can be monitored by the date and time.

As an example, the data in ex19-02.txt are used to build the password table. First, you need to modify the data as in ex19-03.txt:

Listing: ex19-03.txt - Password Data File For MySQL: password3.txt

1:    NULL,Paul,2e69f107d4be5f743461cb66d55d5e6e
2:    NULL,JohnSmith,cd4388c0c62e65ac8b99e3ec49fd9409
3:    NULL,Peter,15c1c83a0ec7f06f4e63aa8224902efc
4:    NULL,Mary,980a37df7f08b2a6299329518f5f37ed
5:    NULL,Tom,9a453212fc8bca35646312f0b7b5106a
6:    NULL,Joe,15a04b72d7535ebc143cc6d729044ed7
7:    NULL,chip,6df39694a5db6f6ed331e53f07a99e56
8:    NULL,Emma,31d8c49fb2e6d2fdb28460db4d2751be
9:    NULL,John,6e4e8727006525d13c12dbe4d2b51abf

The next step is to create the password table in MySQL. You can do this by typing the following SQL statements with the MySQL client program mysql>. A dialog is shown below:

Listing: ex19-04.txt - Creating The Table password

 1:  mysql> CREATE TABLE password (
 2:     -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 3:     -> name VARCHAR(30),
 4:     -> password VARCHAR(60),
 5:     -> PRIMARY KEY (id)
 6:     -> );
 7:  Query OK, 0 rows affected (0.00 sec)

This table contains two fields, name and password. The maximum lengths for the name and password fields are 30 and 60 characters respectively. Once you have the table, you can load the data file password3.txt into this password table by the LOAD DATA statement. A dialog with the MySQL client is shown as follows:

Listing: ex19-05.txt - Load Data password3.txt Into Table password

 1:  mysql> LOAD DATA INFILE 'password3.txt'
 2:     -> INTO TABLE password
 3:     -> FIELDS TERMINATED BY ','
 4:     -> LINES TERMINATED BY '\r\n';
 5:  Query OK, 9 rows affected (0.00 sec)
 6:  Records: 9 Deleted: 0 Skipped: 0 Warnings: 9

You can verify the password data in the table by issuing the command

SELECT * FROM password;

using the mysql client program. If the data have been loaded successfully, you will see the dialog as illustrated in ex19-06.txt. You can also verify this result by using the program we developed in the last chapter if you prefer.

Listing: ex19-06.txt - The Details Of The Password Table

 1:  mysql> SELECT * FROM password;
 2:  +----+-----------+----------------------------------+
 3:  | id | name      | password                         |
 4:  +----+-----------+----------------------------------+
 5:  |  1 | Paul      | 2e69f107d4be5f743461cb66d55d5e6e |
 6:  |  2 | JohnSmith | cd4388c0c62e65ac8b99e3ec49fd9409 |
 7:  |  3 | Peter     | 15c1c83a0ec7f06f4e63aa8224902efc |
 8:  |  4 | Mary      | 980a37df7f08b2a6299329518f5f37ed |
 9:  |  5 | Tom       | 9a453212fc8bca35646312f0b7b5106a |
10:  |  6 | Joe       | 15a04b72d7535ebc143cc6d729044ed7 |
11:  |  7 | chip      | 6df39694a5db6f6ed331e53f07a99e56 |
12:  |  8 | Emma      | 31d8c49fb2e6d2fdb28460db4d2751be |
13:  |  9 | John      | 6e4e8727006525d13c12dbe4d2b51abf |
14:  +----+-----------+----------------------------------+
15:  9 rows in set (0.00 sec)

One straight application of this password table is to use it to build a page to allow users or members to sign on to the Web site.

19.3.2 A page for members to sign on

Now you have everything ready to build a Web page to use this password table to allow members to sign on with their passwords. As a quick reminder, the information you need to build a MySQL database application is:

• Host name (MySQL): www.pwt-ex.com

• User name (MySQL): johnsmith

• Password (MySQL): johnsmith

• Database name: company

• Table name: password

For this database page, we would like to use PHP for the development. The first part of this example is responsible for generating two text fields for the user to enter the user name and password. The program code is listed below:

Example: ex19-05.php - Putting Encrypted Password In Database (Part One)

 1: <?PHP echo"<?";?>xml version="1.0" encoding="iso-88591"<?PHP echo"?>";?>
 2: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 3:     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 4: <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 5: <head><title>Encrypted Password in Database - ex1905.php</title></head>
 6: <style>
 7:   .butSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
 8:      font-size:18pt;color:#880000;width:250px;height:35px}
 9:   .butSt2{background-color:#aaaaaa;font-family:arial;font-weight:bold;
10:      font-size:18pt;color:#880000;width:150px;height:35px}
11:   .txtSt{font-family:arial;font-weight:bold; text-align:left;
12:      font-size:18pt;color:#ffff00}</style>
13: <body style="background:#000088" class="txtSt">
14:
15: <?php
16: global $llst, $userId, $passId;
17:
18: $llst = 'Thank you For Visiting This Site !<br /> ' .
19: 'Enter Your User Name and Password<br /><br />' .
20: '<form action="ex19-05.php" method="post">'.
21: '<table class="txtSt"><tr>' .
22: '  <td>Username:</td><td>' .
23: '<input type="text" name="userId" id="userId" class="butSt" /></td>' .
24: ' </tr><tr>' .
25: '  <td>Password:</td><td>' .
26: '<input type="password" name="passId" id="passId" class="butSt" /></td>' .
27: '  </tr><tr><td colspan="2" style="text-align:center"><br />' .
28: '  <input type="submit" value="Submit" class="butSt2" />' .
29: '  <input type="reset" value="Clear" class="butSt2" /></td></tr>' .
30: ' </table>' .
31: ' </form> ';

This PHP page fragment is, in fact, a string defined in lines 1831. The string variable $llst contains a series of XHTML elements to define a form and two text input boxes. When you execute the following PHP echo or print statement

echo "$llst";  or print  "$llst";

the two text input boxes defined in lines 2226 will be displayed and provide a mechanism for the user to enter user name and password. Also, when you press the Submit button, the form action will submit the form to this PHP page.

The second part of this example is responsible for all database operations and password comparisons. First, it will contact the MySQL database, locate the table name, and search for the password for the user that matched the user name. Once the password has been extracted from the database, a simple comparison against the user input password is performed. The comparison is done using the MD string so that some degree of security is achieved. This part of the example is listed as follows:

Listing: Continuation Of The PHP Script ex1905.php (Part Two)

32:
33:  if ($passId && $userId)
34:  {
35:    $passId = md5($passId);
36:
37:    $db = mysql_connect("www.pwt-ex.com", "johnsmith","johnsmith");
38:    mysql_select_db("password",$db);
39:
40:    $query = "SELECT * FROM password WHERE name='$userId'";
41:    $result = mysql_query ($query) or die ("SQL Query Error..");
42:
43:    $row = mysql_fetch_array ($result);
44:
45:    if ($passId == $row['password'])
46:    {
47:    echo"Thank You $userId! <br /> Enjoy Your Visit<br /><br />";
48:    }
49:   else
50:   {
51:      echo "$llst";
52:    }
53:    mysql_free_result ($result);
54:  }
55:  else
56:  {
57:     echo "$llst";
58:  }
59:  ?>
60:  </body>
61:  </html>

First, we perform a test in line 33 to see whether the user has filled in both the user name and password fields. If one of the fields is not filled, the echo statement in line 57 is executed and hence the user has to input again. If both the user name and password are not empty, the statement in line 35 is used to obtain the MD string of the input password.

Once this encrypted password is ready, the mysql_connnect() function is called to contact the database. When the connection is successfully made and the desired database is selected, the following SQL statement is executed in line 40:

SELECT * FROM password WHERE name='$userId'

Since the variable $userId contains the input user name, this SQL statement will return the record (name and password) where the name equals $userId. The mysql_fetch_array() function in line 43 extracts the records and puts them into an array variable called $row. A simple comparison of $passId against the variable $row['password'] as illustrated in line 45 can therefore determine whether we have a match on the encrypted password. If there is a match, a simple welcome message is displayed. If the two passwords are different, the echo statement in line 51 will display the user name and password text boxes for the user to try again.

This example is a framework to demonstrate how to put encrypted passwords into a database. In real applications, you should put your Web page elements or statements inside the block in lines 4648. Some screen shots of this example are shown in Figs 19.7 and 19.8.

The unencrypted password for Paul, in this case, is paul123 and is listed in ex19-01.txt of section 19.2.3. Using the database, you can develop pages to set up new accounts easily.

19.3.3 Set up new password accounts

Before your Web site can have more members, you probably need to include a sign-up feature in your page to allow someone to join your community. One of the simplest forms for this feature may be a small page or area to allow the user to enter his or her user name and password. You can find all kinds of sign-up utilities on a large number of Web sites, in particular the commercial sites that can provide services and charges on the Internet.

In this section, we are going to develop a sign-up utility to allow someone to join a membership. This page displays two boxes, one for the user name and the other for the password. The password will be encrypted using the MD string. Both the user name and password are then inserted into the password table that we develop in ex19-06.txt. Also, we will add a feature to the utility such that if the user name has been used by someone else, the utility will let you know and ask you to sign up again with a new user name.

The implementation style is similar to ex19-05.php. The first part of this example is responsible for generating the user name and password fields and is listed below:

Example: ex19-06.php - Set Up New Password Accounts (Part One)

 1: <?PHP echo"<?";?>xml version="1.0" encoding="iso-88591"<?PHP echo"?>";?>
 2: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 3:     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 4: <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 5: <head><title>Set Up New Password Accounts - ex1906.php</title></head>
 6: <style>
 7:   .butSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
 8:      font-size:18pt;color:#880000;width:250px;height:35px}
 9:   .butSt2{background-color:#aaaaaa;font-family:arial;font-weight:bold;
10:      font-size:18pt;color:#880000;width:150px;height:35px}
11:   .txtSt{font-family:arial;font-weight:bold; text-align:left;
12:      font-size:18pt;color:#ffff00}</style>
13: <body style="background:#000088" class="txtSt">
14:
15: <?php
16: global $llst, $userId, $passId, $db, $query,$result,$row;
17:
18:  $llst = 'Welcome To ABC Site<br />Please Enter ' .
19:  ' Your User Name And <br />Password To Join <br /><br />' .
20:  ' <form action="ex19-06.php" method="post">'.
21:  ' <table class="txtSt"><tr>' .
22:  '   <td>Username:</td><td>' .
23:  ' <input type="text" name="userId" id="userId" class="butSt" /></td>' .
24:  '  </tr><tr>' .
25:  '   <td>Password:</td><td>' .
26:  ' <input type="password" name="passId" id="passId" class="butSt" />' .
27: '   </td></tr><tr><td colspan="2" style="text-align:center"><br />' .
28: '   <input type="submit" value="Submit" class="butSt2" />' .
29: '   <input type="reset" value="Clear" class="butSt2" /></td></tr>' .
30: '  </table>' .
31: '  </form> ';
32:

Similar to example ex19-05.php, the main characteristic of this PHP program fragment is the string $llst defined in lines 1831. This string contains a form and all the input fields to allow a user to enter his or her user name and password. A simple echo or print function can be used to display this string and hence the input fields anywhere on the page.

The second part of this example concerns processing. Basically, you need to capture the input user name and password and encrypt the password into MD string. Once you have the information at hand, you can contact the MySQL database and perform a data update with the following SQL statement:

INSERT INTO password (id,name,password)
    VALUES (NULL,'$userId','$passId');

In order to prevent data or user name duplication, we also perform a detection to see whether the user name has already been stored in the database before the insert statement above. If the sign-in user name has been used by someone else, a message is output and no insertion take place in the database.

The PHP program fragment for this part is listed below:

Listing: Continuation Of The PHP Script ex1906.php (Part Two)

33:  if ($passId && $userId)
34:  {
35:   $passId = md5($passId);
36:
37:   $db = mysql_connect("www.pwt-ex.com", "johnsmith","johnsmith");
38:   mysql_select_db("password",$db);
39:
40:   $query = "SELECT * FROM password WHERE name='$userId'";
41:   $result = mysql_query ($query) or die ("SQL Query Error..");
42:   $row = mysql_fetch_array ($result);
43:   if ($userId != $row['name'])
44:   {
45:    $sql = "INSERT INTO password (id,name,password)
46:            VALUES (NULL,'$userId','$passId')";
47:    $result = mysql_query($sql) or die ("SQL Query Error..");
48:
49:    echo "A New Account Has Been Set Up <br /><br />";
50:    echo "Username = $userId <br />";
51:    echo "Encrypted Password = <br />$passId <br />";
52:   }
53:   else
54:   {
55:    echo "The User Name You Picked <br />Has Been " .
56:         "Used By Someone.<br /> Please Try Again! <br />";
57:   }
58:  }
59:  else
60:  {
61:     echo "$llst";
62:   }
63:   ?>
64: <br /><img src="line1.gif" width="500" height="6" alt="pic" />
65: <br /><br />
66: </body>
67: </html>

When the user has filled in all the fields and pressed the Submit button, the variables $userId and $passId are defined to contain the user name and password respectively. If not all the fields are filled, the echo statement in line 61 is executed and hence the user can enter his or her selected user name and password.

The input password is encrypted by the md5() function in line 35. After the connection to the database in lines 3738, the following SQL statement is run to search inside the database for any existence of the user name (see line 40):

SELECT * FROM password WHERE name='$userId';

If the user name exists inside the database then the echo statement in lines 5556 is called. This echo statement displays a message to let the user knows that the input user name has already been used by someone else. He or she should try to sign in again. If the user name is a new one, the SQL insert statement is executed to add the user name and encrypted password to the database as a new record (see lines 4546):

INSERT INTO password (id,name,password)
       VALUES (NULL,'$userId','$passId');

The echo statements in lines 4951 output a message to let the user know that a new account has been set up successfully for him or her. Some screen shots of this example are shown in Figs 19.9 and 19.10.

The MD string or md5() function is a one-way function. To use it on an encrypted password provides strong protection against attack. The original password text is safe in a strong sense. It is virtually infeasible to calculate the original text provided the intruder doesn't know the length of your password. The disadvantage is that not even the network administrator of the Web site knows the original password string. If you lose or forget your password, the administrator of the Web site usually has to destroy the encrypted password before a new one can be issued.

19.3.4 A page to change your account's password

One of the common practices in computing to improve data security is to change passwords regularly. On the Web, this basic security measure still has not been implemented on a large scale. Although a large number of Web sites still haven't provided utilities to allow members to change their account passwords, in fact to develop a page to allow a user to change his or her password online is simple. Usually all you have to do is to set up some text fields for the user to enter:

• User name

• Current password

• New password

• Confirm password

When the user name and current password match the information in the database and the new password is the same as the confirm password, you have consistent data. Once the data are consistent or well defined, a simple SQL update statement can be employed to update the data inside the database.

On the other hand, if either the user name or current password does not match the data in the database, or the new password is not the same as the confirm password, you have inconsistent data and all the update operations should stop. Now, let's consider the first part of the example below:

Example: ex19-07.php - A Page To Change Password (Part One)

 1: <?PHP echo"<?";?>xml version="1.0" encoding="iso-88591"<?PHP echo"?>";?>
 2: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 3:     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 4: <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 5: <head><title>A Page To Change Password  ex1907.php</title></head>
 6: <style>
 7:   .butSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
 8:      font-size:18pt;color:#880000;width:250px;height:35px}
 9:   .butSt2{background-color:#aaaaaa;font-family:arial;font-weight:bold;
10:      font-size:18pt;color:#880000;width:150px;height:35px}
11:   .txtSt{font-family:arial;font-weight:bold; text-align:left;
12:      font-size:18pt;color:#ffff00}</style>
13: <body style="background:#000088" class="txtSt">
14:
15: <?php
16: global $id, $llst,$userId,$passId,$newId,$confId,$db,$query,$result,$row;
17:
18:  $llst = 'Change Your Password<br /> ' .
19:   ' Please Enter All Information Below:<br /><br />' .
20:   ' <form action="ex1907.php" method="post">'.
21:   ' <table class="txtSt"><tr>' .
22:   '   <td>Username:</td><td>' .
23:   ' <input type="text" name="userId" id="userId" class="butSt" /></td>' .
24:   '  </tr><tr>' .
25:   '   <td>Current Password:</td><td>' .
26:   ' <input type="password" name="passId" id="passId" class="butSt" />' .
27:   '  </td></tr> ' .
28:   '   <td>New Password:</td><td>' .
29:   ' <input type="password" name="newId" id="newId" class="butSt" />' .
30:   '  </td></tr> ' .
31:   '   <td>Confirm Your Password:</td><td>' .
32:   ' <input type="password" name="confId" id="confId" class="butSt" />' .
33:   '  </td></tr> ' .
34:   '<tr><td colspan="2" style="text-align:center"><br />' .
35:   '  <input type="submit" value="Submit" class="butSt2" />' .
36:   '  <input type="reset" value="Clear" class="butSt2" /></td></tr>' .
37:   ' </table>' .
38:   ' </form> ';
39:

Again, the main feature of this PHP program fragment is the string $llst defined in 1ines 1838. This string contains XHTML coding to generate four input text fields Username, Current Password, New Password, and Confirm Password. Note that the string $llst contains the definition only and you will need to use PHP echo() or print() functions to display the fields on browsers.

The second part of the example is to control the action and is listed as follows:

Listing: Continuation Of The PHP Script ex1907.php (Part Two)

40: if ($passId && $userId && $newId && $confId)
41: {
42:  $passId = md5($passId);
43:
44:  $db = mysql_connect("www.pwt-ex.com", "johnsmith","johnsmith");
45:  mysql_select_db("password",$db);
46:
47:  $query = "SELECT * FROM password WHERE name='$userId'";
48:  $result = mysql_query ($query) or die ("SQL Query Error..");
49:  $row = mysql_fetch_array ($result);
50:  $id = $row['id'];
51:
52:  if (($userId == $row['name']) && ($passId == $row['password']) &&
53:      ($newId == $confId))
54:  {
55:   $newId = md5($newId);
56:   $sql = "UPDATE password SET name='$userId',
57:   password='$newId' WHERE id='$id'";
58:   $result = mysql_query($sql) or die ("SQL Query Error..");
59:   echo "Thank you! $userId <br /><br />";
60:   echo "Your Password Has Been Updated <br />";
61:   echo "Encrypted Password Is <br />$passId <br />";
62:  }
63:  else
64:  {
65:  echo "Your Information Is Not Consistent: <br />" .
66:       "Please Check The Following:<br /><br />" .
67:       "&nbsp; Spelling Of Your Username <br />" .
68:       "&nbsp; Double Check Your Current Password <br />" .
69:       "&nbsp; Your New Password May Not Match Your<br />" .
70:       "&nbsp; Confirm Password <br /><br />" .
71:       "Please Try Again!<br />";
72:  }
73: }
74: else
75: {
76:    echo "$llst";
77: }
78: ?>
79: <br /><img src="line1.gif" width="500" height="6" alt="pic" />
80: <br /><br />
81: </body>
82: </html>

This program fragment begins with a detection to see whether all four fields are filled with data. If not, the echo statement in line 76 will generate the fields again. If all the fields are filled, the program starts a process (lines 4251) to contact the database and extract the user name and encrypted password information, which is stored in variables $row['name'] and $row['password'] respectively. Now, we want to perform a data consistency test as below:

• The input user name matches the user name in the database.

• The input password (encrypted) matches the password in the database.

• The new password is the same as the confirm password.

These tasks can be performed as a single if statement as illustrated in lines 5253. If the data fail the consistency test, the echo statement in lines 6571 is executed to suggest a possible reason. If the data are well defined, the following simple SQL update statement

UPDATE password SET name='$userId',
      password='$newId' WHERE id='$id';

is executed to update the record in the database. Before this update statement, don't forget to encrypt the new password as illustrated in line 55. Some screen shots of this example in action are shown in Figs 19.1119.13.