PHP Solutions: Dynamic Web Design Made Easy, Third Edition (2014)

Chapter 17. Authenticating Users with a Database

Chapter 9 showed you the principles of user authentication and sessions to password protect parts of your website, but the login scripts all relied on usernames and passwords stored in a CSV file. Keeping user details in a database is both more secure and more efficient. Instead of just storing a list of usernames and passwords, a database can store other details, such as first name, family name, email address, and so on. Databases also give you the option of using either one- or two-way encryption. In the first section of this chapter, we’ll examine the difference between the two. Then you’ll create registration and login scripts for both types of encryption.

What this chapter covers:

·     Deciding how to encrypt passwords

·     Using one-way encryption for user registration and login

·     Using two-way encryption for user registration and login

·     Decrypting passwords

Choosing an Encryption Method

The PHP solutions in Chapter 9 use the one-way encryption method—once a password has been encrypted, there’s no way of reversing the process. This is both an advantage and a disadvantage. It offers the user greater security because passwords encrypted this way remain secret. However, there’s no way of reissuing a lost password, since not even the site administrator can decrypt it. The only solution is to reset it.

The alternative is to use two-way encryption, which relies on a pair of functions: one to encrypt the password and another to convert it back to plain text, making it easy to reissue passwords to forgetful users. Two-way encryption uses a secret key that is passed to both functions to perform the conversion. The key is simply a string that you make up yourself. Obviously, to keep the data secure, the key needs to be sufficiently difficult to guess and should never be stored in the database. However, you need to embed the key in your registration and login scripts—either directly or through an include file—so if your scripts are ever exposed, your security is blown wide apart.

MySQL and MariaDB offer a number of two-way encryption functions, but AES_ENCRYPT() is considered the most secure. It uses the Advanced Encryption Standard with a 128-bit key length (AES-128) approved by the U.S. government for the protection of classified material up to the SECRET level (TOP SECRET material requires AES-192 or AES-256).

Both one-way and two-way encryption have advantages and disadvantages. Many security experts recommend that passwords should be changed frequently. So, forcing a user to change a forgotten password because it can’t be decrypted could be regarded as a good security measure. On the other hand, users are likely to be frustrated by the need to deal with a new password each time they forget the existing one. I’ll leave it to you to decide which approach is best suited to your circumstances, and I’ll concentrate solely on the technical implementation.

Using One-Way Encryption

In the interests of keeping things simple, I’m going to use the same basic forms as in Chapter 9, so only the username and encrypted password are stored in the database.

Image Note  The following PHP solutions use password_hash() and password_verify(), which require PHP 5.5 or later. You can emulate these functions on older versions of PHP (minimum 5.3.7) using the password_compat library fromhttps://github.com/ircmaxell/password_compat.

Creating a Table to Store Users’ Details

In phpMyAdmin, create a new table called users in the phpsols database. The table needs three columns with the settings listed in Table 17-1.

Table 17-1. Settings for the users table

Table17-1

To ensure no one can register the same username as one that’s already in use, the username column is given an UNIQUE index.

The pwd column, which is for the password, allows a string of up to 255 characters to be stored. This is much longer than the 60 characters required by the default encryption method used by password_hash() in PHP 5.5 and 5.6. But the PASSWORD_DEFAULT constant is designed to change over time as new and stronger algorithms are added to PHP. So, the recommended size is 255 characters.

Registering New Usersin the Database

To register users in the database, you need to create a registration form that asks for a username and password. The username column has been defined with a UNIQUE index, so the database will return an error if anyone attempts to register the same username as an existing one. In addition to validating the user input, the processing script needs to detect the error and advise the user to choose a different username.

PHP Solution 17-1: Creating a User Registration Form

This PHP solution shows how to adapt the registration script from Chapter 9 to work with MySQL or MariaDB. It uses the CheckPassword class from PHP Solution 9-6 and register_user_csv.php from PHP Solution 9-7.

If necessary, copy CheckPassword.php from the ch17/PhpSolutions/Authenticate folder to the PhpSolutions/Authenticate folder in the phpsols site root, and copy register_user_csv.php from the ch17 folder to the includes folder. You should also read the instructions in PHP Solutions 9-6 and 9-7 to understand how the original scripts work.

1.    Copy register_db.php from the ch17 folder to a new folder called authenticate in the phpsols site root. The page contains the same basic user registration form as in Chapter 9, with a text input field for the username, a password field, another password field for confirmation, and a button to submit the data, as shown in the following screenshot:

9781484206362_unFig17-01.jpg

2.    Add the following code in a PHP block above the DOCTYPE declaration:

if (isset($_POST['register'])) {
    $username = trim($_POST['username']);
    $password = trim($_POST['pwd']);
    $retyped = trim($_POST['conf_pwd']);
    require_once '../includes/register_user_mysqli.php';
}

This is very similar to the code in PHP Solution 9-7. If the form has been submitted, the user input is stripped of leading and trailing whitespace and assigned to simple variables. Then an external file called register_user_mysqli.php is included. If you plan to use PDO, name the include file register_user_pdo.php instead.

3.    The file that processes the user input is based on register_user_csv.php, which you created in Chapter 9. Make a copy of your original file and save it in the includes folder as register_user_mysqli.php or register_user_pdo.php.

4.    In the file you have just copied and renamed, locate the conditional statement that begins like this (around line 24):

if (!$errors) {
    // encrypt password using default encryption
    $password = password_hash($password, PASSWORD_DEFAULT);

5.    Delete the rest of the code inside the conditional statement. The conditional statement should now look like this:

if (!$errors) {
    // encrypt password using default encryption
    $password = password_hash($password, PASSWORD_DEFAULT);
}

6.    The code that inserts the user’s details in the database goes inside the conditional statement. Begin by including the database connection file and creating a connection with read and write privileges.

if (!$errors) {
    // encrypt password using default encryption
    $password = password_hash($password, PASSWORD_DEFAULT);
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write');
}

The connection file is also in the includes folder, so you need only the filename. For PDO, add 'pdo' as the second argument to dbConnect().

7.    The final section of the code prepares and executes the prepared statement to insert the user’s details into the database. Because the username column has a UNIQUE index, the query fails if the username already exists. If that happens, the code needs to generate an error message. The code is different for MySQLi and PDO.

For MySQLi, add the code highlighted in bold:

if (!$errors) {
    // encrypt password using default encryption
    $password = password_hash($password, PASSWORD_DEFAULT);
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write');
    // prepare SQL statement
    $sql = 'INSERT INTO users (username, pwd) VALUES (?, ?)';
    $stmt = $conn->stmt_init();
    if ($stmt = $conn->prepare($sql)) {
        // bind parameters and insert the details into the database
        $stmt->bind_param('ss', $username, $password);
        $stmt->execute();
    }
    if ($stmt->affected_rows == 1) {
        $success = "$username has been registered. You may now log in.";
    } elseif ($stmt->errno == 1062) {
        $errors[] = "$username is already in use. Please choose another username.";
    } else {
        $errors[] = $stmt->error;
    }
}

The new code begins by binding the parameters to the prepared statement. The username and password are strings, so the first argument to bind_param() is 'ss' (see “Embedding variables in MySQLi prepared statements” in Chapter 11). After the statement has been executed, the conditional statement checks the value of the affected_rows property. If it’s 1, the details have been inserted successfully.

Image Tip  You need to check the value of affected_rows explicitly because it’s –1 if there’s an error. Unlike some programming languages, PHP treats –1 as true.

The alternative condition checks the value of the prepared statement’s errno property, which contains the MySQL error code. The code for a duplicate value in a column with a UNIQUE index is 1062. If that error code is detected, an error message is added to the$errors array asking the user to choose a different username. If a different error code is generated, the message stored in the statement’s error property is added to the $errors array instead.

The PDO version looks like this:

if (!$errors) {
    // encrypt password using default encryption
    $password = password_hash($password, PASSWORD_DEFAULT);
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write', 'pdo');
    // prepare SQL statement
    $sql = 'INSERT INTO users (username, pwd) VALUES (:username, :pwd)';
    $stmt = $conn->prepare($sql);
    // bind parameters and insert the details into the database
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':pwd', $password, PDO::PARAM_STR);
    $stmt->execute();
    if ($stmt->rowCount() == 1) {
        $success = "$username has been registered. You may now log in.";
    } elseif ($stmt->errorCode() == 23000) {
        $errors[] = "$username is already in use. Please choose another username.";
    } else {
        $errorInfo = $stmt->errorInfo();
        if (isset($errorInfo[2])) {
 $errors[] = $errorInfo[2];
        }
    }
}

The prepared statement uses named parameters for the username and pwd columns. The submitted values are bound to it by the bindParam() method, using the PDO::PARAM_STR constant to specify the data type as a string. After the statement has been executed, the conditional statement uses the rowCount() method to check if the record has been created.

If the prepared statement fails because the username already exists, the value generated by the errorCode() method is 23000. PDO uses error codes defined by the ANSI SQL standard instead of those generated by MySQL. If the error code matches, a message is added to the $errors array asking the user to choose a different username. Otherwise, the error message from the errorInfo() method is used.

Image Note  In both the MySQLI and PDO scripts, replace the code in the else block with a generic error message when deploying the registration script on a live website. Displaying the value of the statement’s error property (MySQLi) or $errorInfo[2] (PDO) is intended for testing purposes only.

8.    All that remains is to add the code that displays the outcome on the registration page. Add the following code just before the opening <form> tag in register_db.php:

<h1>Register user</h1>
<?php
if (isset($success)) {
    echo "<p>$success</p>";
} elseif (isset($errors) && !empty($errors)) {
    echo '<ul>';
    foreach ($errors as $error) {
        echo "<li>$error</li>";
    }
    echo '</ul>';
}
?>
<form method="post" action="">

9.    Save register_db.php, and load it in a browser. Test it by entering input that you know breaks the rules for the strength of the password. If you make multiple mistakes in the same attempt, a bulleted list of error messages should appear at the top of the form, as shown in the next screenshot.

9781484206362_unFig17-02.jpg

10.Now fill in the registration form correctly. You should see a message telling you that an account has been created for the username you chose.

11.Try registering the same username again. This time you should get a message similar to the one shown in the following screenshot:

9781484206362_unFig17-03.jpg

12.Check your code, if necessary, against register_db_mysqli.php and register_user_mysqli.php, or against register_db_pdo.php and register_user_pdo.php, all found in the ch17 folder.

Now that you have a username and password registered in the database, you need to create a login script. The ch17 folder contains a set of files that replicates the setup in PHP Solution 9-9: a login page and two password-protected pages.

PHP Solution 17-2: Authenticating a user’s credentials with a database

This PHP solution shows how to authenticate a user’s stored credentials by querying the database to find the username’s encrypted password and then passing it as an argument to password_verify() together with the user-submitted password. If password_verify() returns true, the user is redirected to a restricted page.

1.    Copy login_db.php, menu_db.php, and secretpage_db.php from the ch17 folder to the authenticate folder. Also copy logout_db.php and session_timeout_db.php from the ch17 folder to the includes folder.

This sets up the same basic test platform as was used in Chapter 9. The only difference is that the links have been changed to redirect to the authenticate folder.

2.    In login_db.php add the following code in a PHP block above the DOCTYPE declaration:

$error = '';
if (isset($_POST['login'])) {
    session_start();
    $username = trim($_POST['username']);
    $password = trim($_POST['pwd']);
    // location to redirect on success
    $redirect = 'http://localhost/phpsols/authenticate/menu_db.php';
    require_once '../includes/authenticate_mysqli.php';
}

This follows a similar pattern to the code in the login form in Chapter 9. It begins by initializing $error as an empty string. The conditional statement initiates a session if the form has been submitted. Whitespace is trimmed from the user input fields, and the location of the page the user will be redirected to on success is stored in a variable. Finally, the authentication script, which you’ll build next, is included.

If you’re using PDO, use authenticate_pdo.php as the processing script.

3.    Create a new file called authenticate_mysqli.php or authenticate_pdo.php and save it in the includes folder. The file will contain only PHP script, so strip out any HTML markup.

4.    Include the database connection file, create a connection to the database with the read-only account, and use a prepared statement to fetch the user’s details.

For MySQLi use the following code:

<?php
require_once 'connection.php';
$conn = dbConnect('read');
// get the username's encrypted password from the database
$sql = 'SELECT pwd FROM users WHERE username = ?';
// initialize and prepare statement
$stmt = $conn->stmt_init();
$stmt->prepare($sql);
// bind the input parameter
$stmt->bind_param('s', $username);
$stmt->execute();
// bind the result, using a new variable for the password
$stmt->bind_result($storedPwd);
$stmt->fetch();

This is such a straightforward SELECT query that I haven’t used a conditional statement when passing it to the MySQLi prepare() method. The username is a string, so the first argument to bind_param() is 's'. If a match is found, the result is bound to$storedPwd. You need to use a new variable for the stored password to avoid overwriting the password submitted by the user.

After the statement has been executed, the fetch() method gets the result.

For PDO, use the following code instead:

<?php
require_once 'connection.php';
$conn = dbConnect('read', 'pdo');
// get the username's encrypted password from the database
$sql = 'SELECT pwd FROM users WHERE username = ?';
// prepare statement
$stmt = $conn->prepare($sql);
// pass the input parameter as a single-element array
$stmt->execute([$username]);
$storedPwd = $stmt->fetchColumn();

This code does the same as the MySQLi version does, but uses PDO syntax. The username is passed to the execute() method as a single-element array. Because there’s only one column in the result, fetchColumn() returns the value and assigns it to$storedPwd.

5.    Once you have retrieved the username’s password, all you need to do is to pass the submitted and stored versions to password_verify(). If password_verify() returns true, create the session variables to indicate a successful login and the time the session began, regenerate the session ID, and redirect to the restricted page. Otherwise, store an error message in $error.

Insert the following code after the code you entered in the preceding step. It’s the same for both MySQLi and PDO.

// check the submitted password against the stored version
if (password_verify($password, $storedPwd)) {
    $_SESSION['authenticated'] = 'Jethro Tull';
    // get the time the session started
    $_SESSION['start'] = time();
    session_regenerate_id();
    header("Location: $redirect");
    exit;
} else {
    // if not verified, prepare error message
    $error = 'Invalid username or password';
}

As in Chapter 9, the value of $_SESSION['authenticated'] is of no real importance.

6.    Save authenticate_mysqli.php or authenticate_pdo.php, and test login_db.php by logging in with the username and password that you registered at the end of PHP Solution 17-1. The login process should work in exactly the same way as inChapter 9. The difference is that all the details are stored more securely in a database.

You can check your code, if necessary, against login_mysqli.php and authenticate_mysqli.php, or login_pdo.php and authenticate_pdo.php, all found in the ch17 folder. If you encounter problems, the most common mistake is creating too narrow a column for the encrypted password in the database. It must be at least 60 characters wide, and it’s recommended to make it capable of storing up to 255 characters in case future encryption methods generate longer strings.

Although storing an encrypted password in a database is more secure than using a text file, the password is sent from the user’s browser to the server in plain, unencrypted text. This is adequate for most websites, but if you need a higher level of security, the login and access to subsequent pages should be made through a Secure Sockets Layer (SSL) connection.

Using Two-Way Encryption

The main differences in setting up user registration and authentication for two-way encryption are that the password needs to be stored in the database as a binary object using the BLOB data type (see “Storing binary data” in Chapter 10 for more information), and that the password verification takes place in the SQL query, rather than in the PHP script.

Creating the table to store users’ details

In phpMyAdmin, create a new table called users_2way in the phpsols database. It needs three columns, with the settings listed in Table 17-2.

Table 17-2. Settings for the users_2way table

Table17-2

Registering new users

The AES_ENCRYPT() function takes two arguments: the value to be encrypted and an encryption key. The encryption key can be any string of characters you choose. For the purposes of this example, I have chosen takeThisWith@PinchOfSalt, but a random series of alphanumeric characters and symbols would be more secure.

The basic registration scripts for one-way and two-way encryption are the same. The only difference lies in the section that inserts the user’s data into the database.

Image Tip  The following scripts embed the encryption key directly in the page. If you have a private folder outside the server root, it’s a good idea to define the key in an include file and store it in your private folder.

The code for MySQLi looks like this (the full listing is in register_2way_mysqli.php in the ch17 folder):

if (!$errors) {
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write');
    // create a key
    $key = 'takeThisWith@PinchOfSalt';
    // prepare SQL statement
    $sql = 'INSERT INTO users_2way (username, pwd)
            VALUES (?, AES_ENCRYPT(?, ?))';
    $stmt = $conn->stmt_init();
    if ($stmt = $conn->prepare($sql)) {
        // bind parameters and insert the details into the database
        $stmt->bind_param('sss', $username, $password, $key);
        $stmt->execute();
    }
    if ($stmt->affected_rows == 1) {
        $success = "$username has been registered. You may now log in.";
    } elseif ($stmt->errno == 1062) {
        $errors[] = "$username is already in use. Please choose another username.";
    } else {
        $errors[] = $stmt->error;
    }
}

For PDO, it looks like this (see register_2way_pdo.php in the ch17 folder for the full listing):

if (!$errors) {
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write', 'pdo');
    // create a key
    $key = 'takeThisWith@PinchOfSalt';
    // prepare SQL statement
    $sql = 'INSERT INTO users_2way (username, pwd)
            VALUES (:username, AES_ENCRYPT(:pwd, :key))';
    $stmt = $conn->prepare($sql);
    // bind parameters and insert the details into the database
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':pwd', $password, PDO::PARAM_STR);
    $stmt->bindParam(':key', $key, PDO::PARAM_STR);
    $stmt->execute();
    if ($stmt->rowCount() == 1) {
        $success = "$username has been registered. You may now log in.";
    } elseif ($stmt->errorCode() == 23000) {
        $errors[] = "$username is already in use. Please choose another username.";
    } else {
        $errors[] = 'Sorry, there was a problem with the database.';
    }
}

Strictly speaking, it’s not necessary to use a bound parameter for $key because it doesn’t come from user input. If you embed it directly in the query, however, the whole query needs to be wrapped in double quotes, and $key needs to be in single quotes.

To test the preceding scripts, just include them in register_db.php instead of register_db_mysqli.php or register_db_pdo.php.

User authentication with two-way encryption

Creating a login page with two-way encryption is very simple. After connecting to the database, you incorporate the username, secret key, and unencrypted password in the WHERE clause of a SELECT query. If the query finds a match, the user is allowed into the restricted part of the site. If there’s no match, the login is rejected. The code is the same as in PHP Solution 17-2, except for the following section.

For MySQLi, it looks like this (see authenticate_2way_mysqli.php):

<?php
require_once 'connection.php';
$conn = dbConnect('read');
// create key
$key = 'takeThisWith@PinchOfSalt';
$sql = 'SELECT username FROM users_2way
        WHERE username = ? AND pwd = AES_ENCRYPT(?, ?)';
// initialize and prepare statement
$stmt = $conn->stmt_init();
$stmt->prepare($sql);
// bind the input parameters
$stmt->bind_param('sss', $username, $password, $key);
$stmt->execute();
// to get the number of matches, you must store the result
$stmt->store_result();
// if a match is found, num_rows is 1, which is treated as true
if ($stmt->num_rows) {
    $_SESSION['authenticated'] = 'Jethro Tull';
    // get the time the session started
    $_SESSION['start'] = time();
    session_regenerate_id();
    header("Location: $redirect"); exit;
} else {
    // if not verified, prepare error message
    $error = 'Invalid username or password';
}

Note that you need to store the result of the prepared statement before you can access the num_rows property. If you fail to do this, num_rows will always be 0, and the login will fail even if the username and password are correct.

The revised code for PDO looks like this (see authenticate_2way_pdo.inc.php):

<?php
require_once 'connection.php';
$conn = dbConnect('read', 'pdo');
// create key
$key = 'takeThisWith@PinchOfSalt';
$sql = 'SELECT username FROM users_2way
        WHERE username = ? AND pwd = AES_ENCRYPT(?, ?)';
// prepare statement
$stmt = $conn->prepare($sql);
// bind variables by passing them as an array when executing statement
$stmt->execute([$username, $password, $key]);
// if a match is found, rowCount() produces 1, which is treated as true
if ($stmt->rowCount()) {
    $_SESSION['authenticated'] = 'Jethro Tull';
    // get the time the session started
    $_SESSION['start'] = time();
    session_regenerate_id();
    header("Location: $redirect"); exit;
} else {
    // if not verified, prepare error message
    $error = 'Invalid username or password';
}

To test these scripts, use them in place of authenticate_mysqli.php and authenticate_pdo.php.

Decrypting a password

Decrypting a password that uses two-way encryption simply involves passing the secret key as the second argument to AES_DECRYPT() in a prepared statement, like this:

$key = 'takeThisWith@PinchOfSalt';
$sql = "SELECT AES_DECRYPT(pwd, '$key') AS pwd FROM users_2way
        WHERE username = ?";

The key must be exactly the same as the one originally used to encrypt the password. If you lose the key, the passwords remain as inaccessible as those stored using one-way encryption.

Normally, the only time you need to decrypt a password is when a user requests a password reminder. Creating the appropriate security policy for sending out such reminders depends a great deal on the type of site that you're operating. However, it goes without saying that you shouldn’t display the decrypted password onscreen. You need to set up a series of security checks, such as asking for the user’s date of birth or mother’s maiden name, or posing a question whose answer only the user is likely to know. Even if the user gets the answer right, you should send the password by email to the user’s registered address.

All the necessary knowledge should be at your fingertips if you have succeeded in getting this far in this book.

Updating User Details

I haven’t included any update forms for the user registration pages. It’s a task that you should be able to accomplish by yourself at this stage. The most important point about updating user registration details is that you should not display the user’s existing password in the update form. If you’re using one-way encryption, you can’t, anyway.

Where Next?

This book has covered a massive amount of ground. If you’ve mastered all the techniques covered here, you are well on your way to becoming an intermediate PHP developer, and with a little more effort, you will enter the advanced level. If it’s been a struggle, don’t worry. Go over the earlier chapters again. The more you practice, the easier it becomes.

You’re probably thinking, “How on earth can I remember all this?” You don’t need to. Don’t be ashamed to look things up. Bookmark the PHP online manual (http://php.net/manual/en/) and use it regularly. It’s constantly updated, and it has lots of useful examples. Type a function name into the search box at the top right of every page to be taken straight to a full description of that function. Even if you can’t remember the correct function name, the manual takes you to a page that suggests the most likely candidates. Most pages have practical examples showing how the function or class is used.

What makes dynamic web design easy is not an encyclopedic knowledge of PHP functions and classes but a solid grasp of how conditional statements, loops, and other structures control the flow of a script. Once you can visualize your projects in terms of “if this happens, what should happen next?” you’re the master of your own game. I consult the PHP online manual frequently. To me, it’s like a dictionary. Most of the time, I just want to check that I have the arguments in the right order, but I often find that something catches my eye and opens up new horizons. I may not use that knowledge immediately, but I store it at the back of my mind for future use and go back when I need to check the details.

The MySQL online manual (http://dev.mysql.com/doc/refman/5.6/en/index.html) is equally useful. The documentation for MariaDB is at https://mariadb.com/kb/en/. Make both the PHP and database online manuals your friends, and your knowledge will grow by leaps and bounds.