Jump to content
Larry Ullman's Book Forums

Ch.18 Review And Pursue - User Registration Example


Recommended Posts

I created a chart demonstrating the site architecture of this example, which can be found here

 

REVIEW:

1.     What is output buffering? What are the benefits of using it?

Output Buffering prevents everything that a PHP script prints and any HTML outside of the PHP tags from immediately being sent to the Web browser by placing them in a memory buffer until it is flushed. Therefore, you are free to call header(), setcookie(), and session_start() functions, which can normally only be called if nothing has been sent to the Web browser, at any point in a script. This can be a performance improvement and eliminates "headers already sent" error messages (Ullman 561).

 

2.     Why shouldn't detailed error information be displayed on live sites?

For security and aesthetic purposes, it's generally unwise for a public user to see PHP's detailed error messages. Frequently, error messages - particularly those dealing with the database - will reveal certain behind-the-scenes aspects of your Web application that are best not shown (Ullman 250).

 

3.     Why must the active column in the users table allow for NULL values? What is the result if active is defined as NOT NULL?

The active column in the users table will either store the 32-chacaracter-long activation code or have a NULL value. These values will be used in login.php to indicate whether a user has activated their account (by clicking a link in the registration email) or not. A NULL value indicates an active account, so if the active column is defined as NOT NULL, no one will ever be able to log in (Ullman 573, 586 & 589).

 

4.     What are the three steps in terminating a session?

·      Clear an individual session variable or every session variable:

unset($_SESSION['var']);
$_SESSION = array();

·      Destroy all of the session data from the server:

session_destroy();

·      Destroy the session ID stored in a cookie:

setcookie('PHPSESSID', '', time() -3600, '/', 0, 0);

(see Ullman 393)

 

5.     What does the session_name() function do?

The session_name() function allows you to set the name of a session to a more user-friendly one. The benefits of creating your own session name are twofold: it's marginally more secure and it may be better received by the end user (since the session name is the cookie name the end user will see) (Ullman 396).

 

6.     What are the differences between truly encrypting data and creating a hash representation of some data?

With a hash representation of a value comparisons can still be made, but the original data cannot be retrieved from the database. Data stored in an encrypted form is more secure but can also be decrypted if needed (Ullman 237).

 

 

PURSUE

•    Apply the same validation techniques to login.php as used in register.php

•    Make the login form sticky

•    Add a last_login DATETIME field to the users table and update its value when a user logs in. Use this info to indicate to the user how long it has been since the last time she or he accessed the site.

•    If you’ve added the last_login field, se it to print a message on the home page as to how many users have logged in in the past say, hour or day.

•    Validate the submitted email address in forgot_password.php using the Filter extension or a regular expression.

•    Create view_users.php and edit_user.php scripts (see p.309 and 316) as recommended in the final sidebar. Restrict access to these scripts to administrators (those users whose access level is 1).

Link to comment
Share on other sites

Apply the same validation techniques to login.php as used in register.php

 

In Chapter 9, "Using PHP with MySQL" - page 285, Larry explains that "database security with respect to PHP comes down to three broad issues:

1.    Protecting the MySQL access information

2.    Not revealing too much about the database.

3.    Being cautious when running queries, particularly those involving user-submitted data." (Ullman 285)

 

This bullet point concerns the third objective in that we are applying extra precautions when using user-supplied data to query the database:

a.    Validate that some value has been submitted (Ullman 45 & 49).

b.    Use typecasting (Ullman 409-411) or the filter extension (Ullman 421) to guarantee that some value is the proper type (number, string, etc.) (also see type hinting a functions parameters in "PHP Advanced and Object-Oriented Programming, 15)

c.     Use regular expressions to make sure that submitted data matches what you would expect, see ch.14, perl-compatible regular expressions.

d.    Run user-submitted data through the mysqli_real_escape_string() function in order to make data safe to use in a query by escaping what could be problematic characters (Ullman 285).

 

Larry was kind enough to show us how to implement a lot of these solutions in the single script, register.php, so that we can use that as a reference for login.php and in our future projects as well. Looking over register.php, the validation process seems to consist of five steps:

 

1.    Use the array_map() function (Ullman 406) to apply the trim() function to each element submitted through the form in order to trim off extraneous spaces (Ullman 154).

    //update: use the array_map() function to trim all incoming data through $_POST:
    $trimmed = array_map('trim', $_POST);

2.    Establish some flag variables with a default value of FALSE.

    //establish some flag variables and set them all to FALSE:
    $e = $p = FALSE; //this is a shortcut syntax (email, password)

3.    Validate the user-inputted values.

    //validate the email address with the Filter extension:
    if ( filter_var($trimmed['email'], FILTER_VALIDATE_EMAIL) ) {

and:

    //validate the password:
    if ( preg_match ('/^\w{4,20}$/', $trimmed['pass']) ) { //must be between 4 and 20 characters and only contain letters, numbers, and the underscore (\w)

4.    Prevent problematic characters by using mysqli_real_escape_string() function.

        //assign the value to a variable:
        $e = mysqli_real_escape_string($dbc, $trimmed['email']);

and:

        $p = mysqli_real_escape_string($dbc, $trimmed['pass']);

5.    Check that every flag variable passed as TRUE and then continue to execute the database query.

    //if both validation routines passed:
    if ($e && $p) {

P.S.

Nice chart, what tool do you use to draw it?

 

 

Hi Christopher, I'm originally trained as a graphic designer, so I just used illustrator to create all of the charts and their graphics that I'm posting here.

Link to comment
Share on other sites

Make the login form sticky

 

Starting on page 91, in Chapter 3, "Creating Dynamic Websites," Larry discusses several strategies for presetting the value of text inputs, as well as radio buttons and check boxes. Basically, by printing the appropriate variables if and when they exist. The login form, login.php currently only has 2 fields, the username and password. Easy enough, however, I wanted to know if I should have to use the original $_POST['value'] or the trimmed value that is passed through the array_map() function, $trimmed['value'] such as:

<p><b>Email Address:</b> <input type="text" name="email" size="20" maxlength="60" value="<?php if (isset($trimmed['email'])) echo $trimmed['email']; ?>" /></p>
<p><b>Password:</b> <input type="password" name="pass" size="20" maxlength="20" value="<?php if (isset($trimmed['pass'])) echo $trimmed['pass']; ?>" /></p>

NOTE:

Attempting to register as a new user in order to test this out, I am getting a ton of error messages in register.php, but I think the gist of it is:

 

An error occured in script '../Sites/user_registration/htdocs/register.php' on line 98: Query: INSERT INTO users (email, pass, first_name, last_name, active, registration_date) VALUES ( 'example@yahoo.com', SHA1('test'), 'Joe', 'Schmoe', 'b4dd87dc00e1e9a0376d7ea461b90945', NOW() )

 

MySQL Error: Unknown column 'first_name' in 'field list'

 

I have compared my code with the downloaded code and there are no discrepancies. The SQL creating the database cannot be the issue, as that was copied and pasted into phpMyAdmin from the downloaded code. A possible explanation for this is that there is no email service installed on my virtual server/local environment...

THIS ISSUE IS RESOLVED IN REPLY #10 (thank you to abigail and Antonio Conte).

Link to comment
Share on other sites

Hi Larry, 

Thanks for the tip, I understood that it had something to do with the database because the same error occurs on both the script that I've written based on the code in the book, and the unadulterated script that I downloaded. I did notice that the database's collation was set as swedish for some crazy reason and so I have since opted to drop the whole database and start over, making sure that the first_name column existed and that the character set and collation was utf8. However, I'm still getting the same exact error message! Just to be sure, here is the SQL commands that I'm using in phpMyAdmin:

CREATE DATABASE userRegistration CHARACTER SET utf8 COLLATE utf8_general_ci;

USE userRegistration;

CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
email VARCHAR(80) NOT NULL,
pass CHAR(40) NOT NULL,
user_level TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
active CHAR(32),
registration_date DATETIME NOT NULL,
PRIMARY KEY (user_id),
UNIQUE KEY (email),
INDEX login (email, pass)
);

As a test, I decided to try inserting the records directly into the users table through phpMyAdmin and was able to do so that way. I then manually typed out the url to activate the account: 

http://localhost/user_registration/htdocs/activate.php?x=myemail@yahoo.com&y=67212e1fabb98f9890b7c84bca14b428

I didn't get a confirmation message after loading this page and checking back in the database the activate row was not set to NULL as activate.php is meant to do, so that didn't work.

 

After doing all of this, I STILL can't understand where the issue is, and I've ensured that the first_name column exists and that the database CHARSET is utf8. I was able to manually insert records into the users table using the same MySQL query as in register.php, but am still unable to utilize register.php, activate.php or to test login.php. The error message in register.php is telling me to look at the first_name column in the database, but there's not much else to look at there:

An error occured in script '/Users/indigital03/Sites/user_registration/htdocs/login.php' on line 30: Query: SELECT user_id, first_name, user_level FROM users WHERE (email='indigital03@yahoo.com' AND pass=SHA1('test')) AND active IS NULL

MySQL Error: Unknown column 'first_name' in 'field list'

Link to comment
Share on other sites

Thank you Antonio very much for responding. I have now confirmed beyond any doubt that it is in fact a database issue, but even after narrowing that down I've run out of ideas. I hope there is a great lesson in my troubles with this (and hopefully not a completely ridiculous noob mistake), but so far, I have established the following:

  • The database query fails with the exact same error message regardless if I use the code that I have written or if I use the downloaded code, (only changing the database access information). The error that I'm getting is:

An error occured in script '/Users/indigital03/Sites/user_registration/htdocs/login.php' on line 30: Query: SELECT user_id, first_name, user_level FROM users WHERE (email='myemail@yahoo.com' AND pass=SHA1('test')) AND active IS NULL


MySQL Error: Unknown column 'first_name' in 'field list'

  • The database is named userRegistration, the table at issue is named users and the first_name column is of data type varchar(20) and is set to NOT NULL. The entire SQL code that created the database was written in response #6 and a snapshot of the table's structure in phpMyAdmin can be viewed here.
  • In the file mysqli_connect.php, the constants that establish the database connection are as follows:
//Set the MySQL host, username, password, and db name as constants P268
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_USER', '*******');
DEFINE ('DB_PASSWORD', '*******');
DEFINE ('DB_NAME', 'userRegistration');
  • The constant established in config.inc.php that sets up site-wide access to the database is:
//establish two constants for site-wide settings:
DEFINE('BASE_URL', 'http://localhost/');
DEFINE('MYSQL', '../../mysqli_connect.php');
  • The location of mysqli_connect.php is: Sites>user_registration>mysqli_connect.php
  • The location of config.inc.php is Sites>user_registration>htdocs>includes>config.inc.php

 

Hopefully there is something here that a more experienced professional will be able to see plain-as-day. I really want to move forward with this example, but this issue is just weird!

Link to comment
Share on other sites

Did you try doing that query without selecting first_name?

And did you try the query in MyphpAdmin?

If neither of those reveals any new information, you could try changing the name of the table. Leave old table there and make a new one. Than new one should work then.

Link to comment
Share on other sites

Hi abigail,

 

Removing first_name from the database query results in the same error message, except for the last_name column. This tells me that it's not the column, but either the table or database. I believe its most likely something to do with the table since the email and pass columns are requested before the first_name and last_name columns and they aren't creating any error messages. Following your advice, I created another table, that I named 'registered,' using the exact same code used to create the users table and ran the script in the browser. What occurred was very interesting, it threw the error message: 

MySQL Error: Table 'forum2.registered' doesn't exist

 

What's so interesting about that is, in the mysqli_connect.php file that I thought was being used for this website the database is userRegistration, not forum2! So, I went back to my sites folder and discovered this -freakin'- site is using the wrong mysqli_connect.php. I know the config.inc.php file establishes the location of the mysqli_connect.php file to use, and it currently is:

DEFINE('MYSQL', '../../mysqli_connect.php');

I had thought that "../.." meant that the file resided two folders up from config.inc.php, which it does, but it's actually directed at the mysqli_connect.php that is 3 folders up from config.inc.php. Ok, so I changed the path to the mysqli_connect.php file to:

DEFINE('MYSQL', '../mysqli_connect.php');

Boom! That was the problem... (What a putz)

Link to comment
Share on other sites

  • 2 weeks later...

Add a last_login DATETIME field to the users table and update its value when a user logs in. Use this info to indicate to the user how long it has been since the last time she or he accessed the site.

 

Thank you everybody for all of your help with the last task. I've found that this next one was a bit over my head also, as my first attempt ended up being way off. However, having searched the forum I've found several previous threads that I was able to use as a reference, particularly this one (Thank you Christopher for your work). So my work here is going to largely be a detailed walk-through of others work. I have to learn this stuff somehow!

 

The first thing that I'll do is create the last_login column in the users table of the database:

ALTER TABLE users ADD COLUMN last_login DATETIME NOT NULL;

Next, I want to retrieve the last_login date from the users table within login.php in the main database query that logs the user in. However, I want to retrieve the last_login data as both a formatted date to display and an unformatted date for performing further calculations:

$q = "SELECT user_id, first_name, user_level, DATE_FORMAT(last_login, '%a, %b %e at %l:%i%p') as formatted_login, last_login FROM users WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL";

The DATE_FORMAT() function formats the date and time as: Sat, Feb. 21 at 3:42AM, as an example (see Ullman 162). The returned rows are already being registered to the session as an associative array and can be accessed as $_SESSION['last_login'].

 

After doing that I need to update last_login, but only after validating that the main db query ran successfully, so within if (@mysqli_num_rows($r) == 1) {  I do so with the following:

$q = "UPDATE users SET last_login=UTC_TIMESTAMP()";
$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br>MySQL Error: " . mysqli_error($dbc));

if (@mysqli_num_rows($r) != 1) {
    echo '<p class="error">Oops! Something went wrong.</p>';
 }

I'm choosing to use UTC_TIMESTAMP here over NOW(), but what's the difference between NOW() and UTC_TIMESTAMP()? There is a handy table on page 159 differentiating the various Date and Time functions. The UTC_TIMESTAMP() function, for instance, returns "the number of seconds since the epoch until the current moment or until the date specified, in Coordinated Universal Time (UTC) and the NOW() function returns the current data and time (on the server) (Ullman 159). On page 189, Ullman explains that the NOW() function reflects the date and time on the server, which doesn't account for timezones. Storing dates and times using the UTC_* functions store date and times in a time-zone neutral way. Therefore, I'll prefer UTC_TIMESTAMP() over NOW().

 

In order to print the message when the user logs-in, I go into index.php (where users are redirected when logging-in) and begin a conditional to test if the last_login date was stored in the session.

//If the user is logged-in:
if (isset ($_SESSION['last_login'])) {

Next, I want to begin performing the calculations for the number of days, hours, minutes and seconds since the user last logged in. But first, I want to store the current time:

$date1 = time();

The php manual says the time() function returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT). After doing that, I get the last_login value stored in the database using the $_SESSION[]. The strtotime() function parses about any English textual datetime description into a Unix timestamp:

 $date2 = strtotime($_SESSION['last_login']);

Now I can find out the span of time that has passed between the two dates:

$dateDiff = $date1 - $date2;

And break it down in order to determine the number of days, hours, minutes, and seconds that have passed. floor() will round any fractions down so only whole numbers are returned:

$fullDays = floor($dateDiff/(60*60*24));
$fullHours = floor(($dateDiff - ($fullDays*60*60*24))/(60*60));
$fullMinutes = floor(($dateDiff-($fullDays*60*60*24)-($fullHours*60*60))/60);

Since the time() function is already set in seconds, I need to determine how many seconds are remaining after taking into account the days, hours, and minutes. To do this I determine how many seconds that consists of and then find the remainder of seconds left over:

$sec_used = ($fullDays * 60 * 60 * 24) + ($fullHours * 60 * 60) + ($fullMinutes * 60);
$sec_rem = $dateDiff - $sec_used;

Finally, I print the messages displaying the date the user last logged-in (which was the formatted last_login value stored as the alias, formatted_login) and the time that has passed since the last login:

echo "<h4>The last time you logged in is: " . $_SESSION['formatted_login'] . "</h4>";
echo "<h4>There have been $fullDays days, $fullHours hours $fullMinutes minutes and $sec_rem seconds since you last logged in.</h4>";

NOTE:

This code had a known issue with $fullDays resulting in a negative number. Mr. Ullman mentioned something about storing the last_login as a timestamp so that we don't have to convert it to a timestamp in PHP. I've replaced NOW() with UTC_TIMESTAMP() in the update query and seem to have resolved the issue, but I'm not sure that's exactly what Mr. Ullman meant.  

Link to comment
Share on other sites

If you’ve added the last_login field, set it to print a message on the home page as to how many users have logged in in the past say, hour or day.

 

After validating that the main db query ran successfully, run the db query to retrieve the necessary data:

$q = "SELECT COUNT(user_id) AS num_login FROM users WHERE last_login > DATE_SUB(NOW(), INTERVAL 60 MINUTE)";
$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br>MySQL Error: " . mysqli_error($dbc));

                  

The DATE_SUB() function subtracts an amount of days, months, years, hours, minutes and seconds from a DateTime object. The first parameter is a DateTime object and the second parameter is a DateInterval object, here 60 minutes. So we are counting all user_id's whose last_login value is greater than the span of time over the last 60 minutes. This is given the alias of num_login so we know what the data refers to.

 

Next, we fetch the results as an associative array:

if (@mysqli_num_rows($r) == 1) {  
  $row  = mysqli_fetch_array($r, MYSQLI_ASSOC); 
} else {
  echo '<p class="error">Oops, A system error occurred!</p>';
}

Finally, display the results in index.php:

echo "<h5>There have been " . $row['num_login'] . " users logged-in, in the last hour.</h5>";
Link to comment
Share on other sites

  • 1 month later...

Validate the submitted email address in forgot_password.php using the Filter extension or a regular expression.

 

It’s been a while since I’ve worked on this, but I’m glad I left it at this example, as it’s very easy to implement. Really, all I’m doing is taking the example from post #3 and applying the portion that relates to the email input. $_POST['email'] is already run through the mysqli_real_escape_string() function, which makes data safe to use in a query by escaping what could be problematic characters, within the db query itself, so this seems to be all that is needed:

if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
Link to comment
Share on other sites

 Share

×
×
  • Create New...