Jump to content
Larry Ullman's Book Forums

Ch18, My Solution To The Last Pursue Exercise


Recommended Posts

Larry and all,

Please you take a look at my solutions and my questions. This is the last pursue question of Chapter 18 - 'administrator pages'. Other pursue questions were posted in my last post.

 

This exercise is great - it put what is taught in Chapter 9 & 10 into Chapter 18 example(which is the best example in the book coz it is the closest to a real-world web app)

 

Ch18 - PURSUE - Create view_users.php and edit_user.php scripts for site administration. Restrict access to these scripts to administrators (those users whose access level is 1).

 

These 3 scripts here are modified based on the PURSUE version of Chapter 9 & 10, and I highlighted the changes in red.

 

1. view_users.php

This one doesn't have much change, just to make it consistent with examples in Ch18.

 

<?php # Script 10.5 - #5, modified for Ch18
# Ch18 - PURSUE - Create view_users.php and edit_user.php scripts for site administration. Restrict access to
# these scripts to administrators (thoseusers whose access level is 1).

 
// This script retrieves all the records from the users table.
// This new version allows the results to be sorted in different ways.

// Include the configuration file:
require ('includes/config.inc.php');


// Set the page title and include the HTML header:
$page_title = 'View the Current Users';
include ('includes/header.php');

echo '<h1>Registered Users</h1>';

require (MYSQL);

// Number of records to show per page:
$display = 10;

// Determine how many pages there are...
if (isset($_GET['p']) && is_numeric($_GET['p'])) { // Already been determined.
    $pages = $_GET['p'];
} else { // Need to determine.
     // Count the number of records:
    $q = "SELECT COUNT(user_id) FROM users";
    $r = @mysqli_query ($dbc, $q);
    $row = @mysqli_fetch_array ($r, MYSQLI_NUM);
    $records = $row[0];
    // Calculate the number of pages...
    if ($records > $display) { // More than 1 page.
        $pages = ceil ($records/$display);
    } else {
        $pages = 1;
    }
} // End of p IF.

// Determine where in the database to start returning results...
if (isset($_GET['s']) && is_numeric($_GET['s'])) {
    $start = $_GET['s'];
} else {
    $start = 0;
}

// Determine the sort...
// Default is by registration date.
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'rd';

// Determine the sorting order:
switch ($sort) {
    case 'ln':
        $order_by = 'last_name ASC';
        break;
    case 'fn':
        $order_by = 'first_name ASC';
        break;
    case 'rd':
        $order_by = 'registration_date ASC';
        break;
    default:
        $order_by = 'registration_date ASC';
        $sort = 'rd';
        break;
}
    
// Define the query:
$q = "SELECT last_name, first_name, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr, user_id
    FROM users ORDER BY $order_by LIMIT $start, $display";        
$r = @mysqli_query ($dbc, $q); // Run the query.

// Table header:
echo '<table align="center" cellspacing="0" cellpadding="5" width="75%">
<tr>
    <td align="left"><b>Edit</b></td>
    <td align="left"><b>Delete</b></td>
    <td align="left"><b><a href="view_users.php?sort=ln">Last Name</a></b></td>
    <td align="left"><b><a href="view_users.php?sort=fn">First Name</a></b></td>
    <td align="left"><b><a href="view_users.php?sort=rd">Date Registered</a></b></td>
</tr>
';

// Fetch and print all the records....
$bg = '#eeeeee';
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
        $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
        echo '<tr bgcolor="' . $bg . '">
    <td align="left"><a href="edit_user.php?id=' . $row['user_id'] . '&ln=' . $row['last_name'] . '&fn=' . $row['first_name'] . '">Edit</a></td>
    <td align="left"><a href="delete_user.php?id=' . $row['user_id'] . '&ln=' . $row['last_name'] . '&fn=' . $row['first_name'] . '">Delete</a></td>
    <td align="left">' . $row['last_name'] . '</td>
    <td align="left">' . $row['first_name'] . '</td>
    <td align="left">' . $row['dr'] . '</td>
</tr>
';
} // End of WHILE loop.

echo '</table>';
//mysqli_free_result ($r);
//mysqli_close($dbc);

// Make the links to other pages, if necessary.
if ($pages > 1) {
    
    echo '<br /><p>';
    $current_page = ($start/$display) + 1;
    
    // If it's not the first page, make a Previous button:
    if ($current_page != 1) {
        echo '<a href="view_users.php?s=' . ($start - $display) . '&p=' . $pages . '&sort=' . $sort . '">Previous</a> ';
    }
    
    // Make all the numbered pages:
    for ($i = 1; $i <= $pages; $i++) {
        if ($i != $current_page) {
            echo '<a href="view_users.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&sort=' . $sort . '">' . $i . '</a> ';
        } else {
            echo $i . ' ';
        }
    } // End of FOR loop.
    
    // If it's not the last page, make a Next button:
    if ($current_page != $pages) {
        echo '<a href="view_users.php?s=' . ($start + $display) . '&p=' . $pages . '&sort=' . $sort . '">Next</a>';
    }
    
    echo '</p>'; // Close the paragraph.
    
} // End of links section.
    
include ('includes/footer.php');
?>

 

 

2. edit_users.php

Major change happens here. Add two radio buttons for account activating and administrator setting in the form on the page. Then the script itself will handle these two cases when the form is submitted, and according SQL queries are executed to UPDATE the users table for the specific record.

 

 

<?php # Script 10.3 - edit_user.php - PURSUE, modified for Ch18
# 1. Change the delete_user.php and edit_user.php pages so that they both display the user being affected
# in the browser window’s title bar.
# 2. Modify edit_user.php so that you can also change a user’s password.
# 3. If you’re up for a challenge, modify edit_user.php so that the form elements’ values come from
# $_POST, if set, and the database if not.

# # 2014-1-26: modified for Ch18
# Administrator could manually activate an account, declare that a user is an administrator,
# or change a person's password.


// This page is for editing a user record.
// This page is accessed through view_users.php.

require ('includes/config.inc.php');
$page_title = 'Edit a User';
include ('includes/header.php');
echo '<h1>Edit a User</h1>';

// Check for a valid user ID, through GET or POST:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { // From view_users.php
    $id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) { // Form submission.
    $id = $_POST['id'];
} else { // No valid ID, kill the script.
    echo '<p class="error">This page has been accessed in error.</p>';
    include ('includes/footer.html');
    exit();
}

require (MYSQL);

// Check if the form has been submitted:
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    $errors = array();

    // Check for a first name:
    if (empty($_POST['first_name'])) {
            $errors[] = 'You forgot to enter your first name.';
    } else {
            $fn = mysqli_real_escape_string($dbc, trim($_POST['first_name']));
    }

    // Check for a last name:
    if (empty($_POST['last_name'])) {
            $errors[] = 'You forgot to enter your last name.';
    } else {
            $ln = mysqli_real_escape_string($dbc, trim($_POST['last_name']));
    }

    // Check for an email address:
    if (empty($_POST['email'])) {
            $errors[] = 'You forgot to enter your email address.';
    } else {
            $e = mysqli_real_escape_string($dbc, trim($_POST['email']));
    }

    // PURSUE 2 - Check for a NEW password(updated) and match against the confirmed password:
    //If these values are submitted, update the password in the database as well. If these inputs are
    //left blank, do not update the password in the database(do not put an error message in $errors[] & use the original $q).
    if (!empty($_POST['pass1'])) {
            if ($_POST['pass1'] != $_POST['pass2']) {
                    $errors[] = 'Your new password did not match the confirmed password.';
            } else {
                    $np = mysqli_real_escape_string($dbc, trim($_POST['pass1']));
            }
    } // if pass1 is empty, then do nothing here, delete the original else statement. But nend to
               // add judge it again in the below query($q)

    if (empty($errors)) { // If everything's OK.

            //  Test for unique email address:
            $q = "SELECT user_id FROM users WHERE email='$e' AND user_id != $id";
            $r = @mysqli_query($dbc, $q);
            if (mysqli_num_rows($r) == 0) { // Not find it, so the email address is unique

            // Make the query to UPDATE the table:
            // PURSUE 2 - add pass=SHA1('$np')
            if (!empty($_POST['pass1'])) {
            $q = "UPDATE users SET first_name='$fn', last_name='$ln', email='$e', pass=SHA1('$np') WHERE user_id=$id LIMIT 1";
            } else { // if no pass1 input, then do not update the password in the database; the original query
            $q = "UPDATE users SET first_name='$fn', last_name='$ln', email='$e' WHERE user_id=$id LIMIT 1";                                 
                                    }

            $r = @mysqli_query ($dbc, $q);
            if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.

                    // Print a message:
                    echo '<p>The user has been edited.</p>';    
//To handle the case when there's no update for name, email and password, I added this conditional. When an administrator is activating a user, seems he doesn't often need to change the user's name/email/password.
            } else if (mysqli_affected_rows($dbc) == 0) { // No actual update

                        echo "</p>The user's name, email and/or password are not changed.</p>";

                        }
else { // If it did not run OK.
                    echo '<p class="error">The user could not be edited due to a system error. We apologize for any inconvenience.</p>'; // Public message.
                    echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.
            }

            } else { // Already registered.
                    echo '<p class="error">The email address has already been registered.</p>';
            }

                    //Ch18 new added function - Active account
                    if (isset($_POST['active']) && $_POST['active'] == "Yes") {
                        //Query to active the user
                        $q = "UPDATE users SET active=NULL WHERE (email='$e' AND user_id='$id') LIMIT 1";
                        $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

                        // Print a customized message:
                        if (mysqli_affected_rows($dbc) == 1) {
                                echo "<h3>This user's account is now active.</h3>";
                        } else {
                                echo '<p class="error">This account could not be activated. Please check.</p>';
                        }                                    
                    }

                    //CH18 new added function - Set administrator
                    if (isset($_POST['admin']) && $_POST['admin'] == "Yes") {
                        //Query to set the user to an administrator
                        $q = "UPDATE users SET user_level=1 WHERE (email='$e' AND user_id='$id') LIMIT 1";
                        $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

                        // Print a customized message:
                        if (mysqli_affected_rows($dbc) == 1) {
                                echo "<h3>This user is site administrator now.</h3>";
                        } else {
                                echo '<p class="error">This account could not be set to an administrator. Please check.</p>';
                        }                                    
                    }

//One question here: is it better to use if (isset($_POST['admin']) && $_POST['admin'] == "Yes")

or just use

if ($_POST['admin'] == "Yes") (like in the delete_users.php)

here?
    } else { // Report the errors.

            echo '<p class="error">The following error(s) occurred:<br />';
            foreach ($errors as $msg) { // Print each error.
                    echo " - $msg<br />\n";
            }
            echo '</p><p>Please try again.</p>';

    } // End of if (empty($errors)) IF.

} // End of submit conditional.

// Always show the form...

// Retrieve the user's information:
$q = "SELECT first_name, last_name, email FROM users WHERE user_id=$id";        
$r = @mysqli_query ($dbc, $q);

if (mysqli_num_rows($r) == 1) { // Valid user ID, show the form.

      // Get the user's information:
      $row = mysqli_fetch_array ($r, MYSQLI_NUM);

      // PURSUE 3 - form elements’ values come from $_POST, if set, and the database if not
      // Note: if UPDATE check is passed/UPDATE is succeful, then $row[] has the same value as $_POST[]
      $form_fn = isset($_POST['first_name']) ? $_POST['first_name'] : $row[0];
      $form_ln = isset($_POST['last_name']) ? $_POST['last_name'] : $row[1];
      $form_email = isset($_POST['email']) ? $_POST['email'] : $row[2];

      // Create the form:
      // PURSUE 2 - also change a user’s password, adding the new password input and a confirmation
      // PURSUE 3 - substitute original $row[0], $row[1], $row[2] with $form_fn...
      echo '<form action="edit_user.php" method="post">
<p>First Name: <input type="text" name="first_name" size="15" maxlength="15" value="' . $form_fn . '" /></p>
<p>Last Name: <input type="text" name="last_name" size="15" maxlength="30" value="' . $form_ln . '" /></p>
<p>Email Address: <input type="text" name="email" size="20" maxlength="60" value="' . $form_email . '"  /></p>
<p>New Password: <input type="password" name="pass1" size="10" maxlength="20" value="" ></p>
<p>Confirm Password: <input type="password" name="pass2" size="10" maxlength="20" value="" ></p>

<p>Active the account: No<input type="radio" name="active" value="No" checked="checked">
                       Yes<input type="radio" name="active" value="Yes"></p>
<p>Make the user an administrator: No<input type="radio" name="admin" value="No" checked="checked">
                                   Yes<input type="radio" name="admin" value="Yes"></p>


<p><input type="submit" name="submit" value="Submit" /></p>
<input type="hidden" name="id" value="' . $id . '" />
</form>';

} else { // Not a valid user ID. If no record was returned from the database, because an invalid user ID was
               // submitted, this message is displayed:
    echo '<p class="error">This page has been accessed in error.</p>';
}

mysqli_close($dbc);
        
include ('includes/footer.php');
?>

 

 

3. delete_users.php

Just a little change here.

 

<?php # Script 10.2 - delete_user.php, modified for Ch18

// This page is for deleting a user record.
// This page is accessed through view_users.php.

require ('includes/config.inc.php');
$page_title = 'Delete a User';
include ('includes/header.php');
echo '<h1>Delete a User</h1>';

// Check for a valid user ID, through GET or POST:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { // From view_users.php
    $id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) { // Form submission.
    $id = $_POST['id'];   //when the confirmation form is submitted, user_id is transfered using from hidden field, in $_POST['id']! Smart!
} else { // No valid ID, kill the script.
    echo '<p class="error">This page has been accessed in error.</p>';
    include ('includes/footer.html');
    exit();
}

require (MYSQL);

// Check if the form has been submitted:
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    if ($_POST['sure'] == 'Yes') { // Delete the record.

        // Make the query:
        $q = "DELETE FROM users WHERE user_id=$id LIMIT 1";        
        $r = @mysqli_query ($dbc, $q);

        if (mysqli_affected_rows($dbc)== 1) { // If it ran OK.

            // Print a message:
            echo '<p>The user has been deleted.</p>';    

        } else { // If the query did not run OK.
            echo '<p class="error">The user could not be deleted due to a system error.</p>'; // Public message.
            echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.
        }
    
    } else { // No confirmation of deletion.
        echo '<p>The user has NOT been deleted.</p>';    
    }

} else { // Show the form, to confirm that this user should be deleted.

    // Retrieve the user's information:
    $q = "SELECT CONCAT(last_name, ', ', first_name) FROM users WHERE user_id=$id";
    $r = @mysqli_query ($dbc, $q);
                $test_rows = mysqli_num_rows($r);
    if (mysqli_num_rows($r) == 1) { // Valid user ID, show the form. (Just 1 result as user_id is PK)

        // Get the user's information:
        $row = mysqli_fetch_array ($r, MYSQLI_NUM);
        
        // Display the record being deleted:
        echo "<h3>Name: $row[0]</h3>
        Are you sure you want to delete this user?";
        
        // Create the form:
        echo '<form action="delete_user.php" method="post">
    <input type="radio" name="sure" value="Yes" /> Yes
    <input type="radio" name="sure" value="No" checked="checked" /> No
    <input type="submit" name="submit" value="Submit" />
    <input type="hidden" name="id" value="' . $id . '" />
    </form>';
    
    } else { // Not a valid user ID.
        echo '<p class="error">This page has been accessed in error.</p>';
    }

} // End of the main submission conditional.

mysqli_close($dbc);   
        
include ('includes/footer.php');
?>

 

 

 

The result page of editing a user:

Edit a User

First Name:

Last Name:

Email Address:

New Password:

Confirm Password:

Active the account: No Yes

Make the user an administrator: No Yes

 

 

Link to comment
Share on other sites

Christopher, first, thanks for sharing your work. It's so great to see. And thanks for the syntax highlighting. Three things...

 

- I'm missing where you've impelled the "deny access to administrators" feature. Maybe I'm overlooking it?

 

- Ideally edit users would still only run one query, but that's more complicated.
 
- Yes, it's definitely better to use 
if (isset($_POST['admin']) && $_POST['admin'] == "Yes")
Link to comment
Share on other sites

Larry,

 

Thanks for the reply! I will address the two points one by one.

 

 

- I'm missing where you've impelled the "deny access to administrators" feature. Maybe I'm overlooking it?

Yes, I forgot to do it. Now in view_users.php, delete_users.php and edit.users.php, at the beginning of the script, I added in this code block:

 

<?php # Script 10.5 - view_users.php, modified for Ch18
# Ch18 - PURSUE - Create view_users.php and edit_user.php scripts for site administration. Restrict access to these scripts to administrators (those users whose access level is 1).


// This script retrieves all the records from the users table.
// This new version allows the results to be sorted in different ways.

// Include the configuration file:
require ('includes/config.inc.php');

// Set the page title and include the HTML header:
$page_title = 'View the Current Users';
include ('includes/header.php');

// Check for a valid admin ID (restrict access to this script to administrators(whose access level is 1)):
if ( (isset($_SESSION['user_level']) && $_SESSION['user_level']) != 1 ) { // Not an admin, kill the script.
        
        echo '<p class="error">This page has been accessed in error.</p>';
        include ('includes/footer.php');
        exit();
        
}

echo '<h1>Registered Users</h1>';  // I put the validation before generating any text on the content part of the page

require (MYSQL);

// Number of records to show per page:
$display = 10;

 

 

<?php # Script 10.3 - edit_user.php - PURSUE, modified for Ch18


// This page is for editing a user record.
// This page is accessed through view_users.php.

require ('includes/config.inc.php');
$page_title = 'Edit a User';
include ('includes/header.php');

// Check for a valid admin ID (restrict access to this script to administrators(whose access level is 1)):
if ( (isset($_SESSION['user_level']) && $_SESSION['user_level']) != 1 ) { // Not an admin, kill the script.
        
        echo '<p class="error">This page has been accessed in error.</p>';
        include ('includes/footer.php');
        exit();
        
}

 

echo '<h1>Edit a User</h1>';

// Check for a valid user ID, through GET or POST:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { // From view_users.php
    $id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) { // Form submission.
    $id = $_POST['id'];
} else { // No valid ID, kill the script.
    echo '<p class="error">This page has been accessed in error.</p>';
    include ('includes/footer.php');
    exit();
}

require (MYSQL);

 

 

 

<?php # Script 10.2 - delete_user.php, modified for Ch18
# 2014-1-26:  modified for Ch18

// This page is for deleting a user record.
// This page is accessed through view_users.php.

require ('includes/config.inc.php');
$page_title = 'Delete a User';
include ('includes/header.php');

// Check for a valid admin ID (restrict access to this script to administrators(whose access level is 1)):
if ( (isset($_SESSION['user_level']) && $_SESSION['user_level']) != 1 ) { // Not an admin, kill the script.
        
        echo '<p class="error">This page has been accessed in error.</p>';
        include ('includes/footer.php');
        exit();
        
}

 

echo '<h1>Delete a User</h1>';

// Check for a valid user ID, through GET or POST:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { // From view_users.php
    $id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) { // Form submission.
    $id = $_POST['id'];   //when the confirmation form is submitted, user_id is transfered using from hidden field, in $_POST['id']! Smart!
} else { // No valid ID, kill the script.
    echo '<p class="error">This page has been accessed in error.</p>';
    include ('includes/footer.php');
    exit();
}

require (MYSQL);

 

 

Just added in this access validation routine(using $_SESSION['user_level'] which was already set in login.php), anything else is the same.

 

One question here is: I first just added this validation routine to view_users.php, because I thought only when a user gets there can he have access to delete and edit user scripts. But later I guess if these two scripts have no such access validation routine, then a malicious user could just guess the name of edit_users and delete_users, and type and try it in the browser, so I added them in. Is my understanding correct?  If so, then I guess every page of a CMS should contain certain access validation at the beginning of the scripts, right??

 

 

For the second suggestion:

 

- Ideally edit users would still only run one query, but that's more complicated.

 

I am still trying to figure it out, one query. Maybe any hint(just a key word)?

Link to comment
Share on other sites

  • 2 weeks later...

Thanks again for sharing all your work, Christopher. Great to see! 

 

As for your question about validation, that's exactly correct. Security through obfuscation (i.e., no one will know a thing exists or what it's name is) is not real security. You need to add authorization to anything that not everyone should be able to access, not just to the links to that thing. 

 

As for the edit query, I was referring specifically to running just one UPDATE query. And you mostly have it, except for your additions of "active" and "admin" checks.

Link to comment
Share on other sites

 Share

×
×
  • Create New...