PHP and MySQL: The Missing Manual (2011)

Part 4. Security and the Real World

Chapter 11. Authentication and Authorization

There’s something a little weird that happens at just about this point in your application design and creation. You have four, five, or maybe more core features in place. You have a few tables set up. You have a lot of the guts of your application built, and even though things are simple, you have a sense of where you’re taking things.

And at this point, you add some new feature, like the ability to delete users. It seems like just another feature; just another user requirement to tick off the list. But wait a second…deleting users? Do you want to offer all your users that power? Of course not. That’s an administrative feature.

NOTE

You might even remember that an early candidate for the name of delete_user.php was admin.php (Your PHP Controls Your Output).

But what’s an administrator? Obviously, in the non-digital world, it’s just a person or group of people who are managing accounts, probably someone who has a few extra passwords stickied to their monitor. But in your application, there’s no such thing as an administrator. Right now, anyone can hop over to delete_user.php and nuke poor Bill Shatner, or James Roday, or whatever other celebrities have signed up through create_user.html and its friends.

But it’s worse than that! Because of that little red “x” appears when you go to show_users.php, someone you can just be viewing users, and boom, there you have it: a little red “x” that can delete data forever. And with nothing more than a confirmation box in the way, anyone can access this functionality.

So here’s that moment in your application’s life. You’ve added one piece of functionality, but it causes you to realize you need several other things…and you need them soon. Here’s the quick list of problems that need to be solved for you and your users to have a sensible app:

§  Viewing all users (you’ve got this; Building a Simple Admin Page)

§  Deleting users (you’ve got this, with way too much freedom; Deleting a User)

§  A way to identify users on your system (you kind of have this, through create_user.html, from Saving a User’s Information, but there’s no logging in right now)

§  A way to indicate that a user is an administrator

§  A way that users can log in and verify who they are (say, with a password)

§  A way to only show certain functionality—like deleting a user—if a user is an administrator

Basically, your system needs authentication. Users should have to log in, and then your system should know whether a user is a certain type—like an admin. And then, based on that type, the user sees (or doesn’t see) certain things. This selective display of resources—or even selectively not allowing access to a resource at all—is authentication’s bed fellow, authorization.

So you authenticate, and let a system know who you are. And based upon who you are, you’re authorized to see certain things. And of course, like so many things, these terms are often confused for each other, or even casually used interchangeably.

NOTE

There are people that would rather be tarred and feathered than mistake authentication for authorization, or the other way around. Then again, those people probably have separate drawers for every color of sock they have, so while it’s good to know the difference, you don’t have to sweat the details.

Back to authentication and authorization on your growing application: It’s certainly not surprising that you need to add these features. You log in to almost every site you regularly visit online. Even YouTube and Google have logins, and of course there’s Twitter and Facebook and a slew of other options. All of them use authentication to know who is who. It’s time that your application joined the party.

Start with Basic Authentication

Authentication, like everything else, can be done simply, or with tremendous complexity. And, also like almost everything else, it’s usually best to start with the basics and add in complexity as it’s needed. Honestly, for a simple application, you don’t need thumbprint readers and lasers scanning your users’ faces. (Well, it might be fun, but it’s not necessary. James Bond almost certainly isn’t going to fill out your create_user.html form.)

Basic Authentication Using HTTP Headers

With authentication, the basics literally are basic authentication. Basic authentication, also known as HTTP authentication, is a means of getting a username and password in a web application through HTTP headers. You’ve already worked with headers a bit (Updating Your User Creation Script). Remember this bit of code?

function handle_error($user_error_message, $system_error_message) {

  header("Location: " . get_web_path(SITE_ROOT) .

         "scripts/show_error.php" .

         "?error_message={$user_error_message}" .

         "&system_error_message={$system_error_message}");

}

NOTE

handle_error is in scripts/app_config.php.

This code consists of nothing more than an HTTP header, the Location header, to send a redirect to the browser. You’ve also used the Content-type and Content-length headers in displaying an image:

header('Content-type: ' . $image['mime_type']);

header('Content-length: ' . $image['file_size']);

NOTE

This code was part of show_image.php from Chapter 9, used to display an image stored in the database (Show Me the Image).

With basic authentication, there are a couple of other HTTP headers you can send. The first doesn’t have a key value, like Content-type or Location. You simply send this header:

HTTP/1.1 401 Unauthorized

When a browser gets this header, it knows that a page that’s been requested requires authentication to be displayed. 401 is a special status code, one of many, that tell the browser something about the request. 200 is the code used to say “Everything is OK,” for example, and 404 is the HTTP error code.

NOTE

You can read up on all the HTTP status codes at w3.org/Protocols/rfc2616/rfc2616-sec10.html.

It’s one thing to tell the browser that access to a page is restricted. But that’s not much help—how do you get that page unrestricted? The answer is to send a second header:

WWW-Authenticate: Basic realm="The Social Site"

This header lets the browser know that authentication needs to happen. Specifically, the browser should pop up a box and ask for some credentials.

The header here is WWW-Authenticate, and you tell it what type of authentication to require: basic. Then, you give a realm to which that authentication should be applied. In this case that’s “The Social Site.” So as long as different pages use this same realm, authentication to one of those pages applies to other pages in that same realm.

Basic Authentication is…Pretty Basic

To see how basic authentication works, try adding it to your show_users.php script. Enter these two header lines near the top of the script:

<?php

require_once '../scripts/app_config.php';

require_once '../scripts/database_connection.php';

require_once '../scripts/view.php';

header('HTTP/1.1 401 Unauthorized');

header('WWW-Authenticate: Basic realm="The Social Site"');

// Build the SELECT statement

$select_users =

  "SELECT user_id, first_name, last_name, email " .

  "  FROM users";

// Remaining PHP

?>

NOTE

As usual, you might want to think about making a backup of this script, or copying all your scripts into a new ch11/ directory. That way you’ve got all your older, working scripts to fall back to in case something goes wrong.

Now navigate over to show_users.php. You should see a nice pop-up window asking you to log in, like Figure 11-1. Well, it’s not that nice…but it does the trick. Basic authentication, pure and simple.

WARNING

If your web server is using a .htpasswd file (popular particular on Apache web servers) to restrict certain directories from web access, you could have problems here. .htpasswd doesn’t play very nicely with PHP’s basic authentication sometimes. Your best bet would be to call your provider and simply ask them to not use any .htpasswd files on the directories in which you’re working.

It’s battleship gray, it’s forbidding, it’s terse… everything you could want a fence around your application to be. Of course, it’s also almost never seen these days in web applications, so this step is thankfully just the first in a series of steps toward solid authentication.

Figure 11-1. It’s battleship gray, it’s forbidding, it’s terse… everything you could want a fence around your application to be. Of course, it’s also almost never seen these days in web applications, so this step is thankfully just the first in a series of steps toward solid authentication.

The Worst Authentication Ever

There’s a pretty gaping hole in your security, though. Navigate to show_users.php if you’re not there already, and leave both the username and password fields blank. Then simply click “Cancel.” What do you get? Figure 11-2.

Wow. Of all the things you might have expected to see when you canceled out of a login box, the secure page probably wasn’t one of them. While you’re triggering a login request, you’re not doing anything with that request.

Figure 11-2. Wow. Of all the things you might have expected to see when you canceled out of a login box, the secure page probably wasn’t one of them. While you’re triggering a login request, you’re not doing anything with that request.

As if that’s not enough, enter in any username and password, and click Log In. There you go: Figure 11-2 again. In fact, spend some time trying to get anything other than the normal show_users.php page. You won’t be able to.

Pretty poor security, isn’t it? Obviously, something’s not right. Canceling should not just take you on to the supposedly secure page. What you need to do is get the username and password entered in, check them against acceptable values, and then show the page. In every other case, the user should not get to see show_users.php.

Get Your User’s Credentials

Unfortunately, try as you might, you’ll never be able to check the username and password against any values—not without some changes to your script. That’s because your code doesn’t ever grab those values, let alone compare them against any other values that are OK for viewing theshow_users.php page. There’s clearly some work to do here.

Because HTTP authentication is defined in a standard way, though, it’s easy for PHP to interact with users that enter their username and password into a basic authentication pop-up. In fact, PHP gives you access to both the username and password entered through two special values in a superglobal variable you’ve used before, $_SERVER:

§  $_SERVER[‘PHP_AUTH_USER’] gives you the user’s entered username.

§  $_SERVER[‘PHP_AUTH_PW’] gives you the user’s entered password.

NOTE

$_SERVER is used in app_config.php (A Few Quick Revisions to app_config.php) to define the SITE_ROOT constant as well as in the get_web_path utility function.

Now, you might think your flow should go something like this:

1.    At the beginning of a script, send the HTTP headers that trigger authentication.

2.    Once the authentication code is complete, check $_SERVER[‘PHP_AUTH_USER’] and $_SERVER[‘PHP_AUTH_PW’] for values, and compare those values to some constants or a database.

3.    Decide whether or not to let the user see the content your script normally outputs.

That makes a lot of sense, but turns out to be wrong…entirely wrong.

Here’s what happens:

1.    Your script gets called.

2.    Authentication headers (that is, headers that say a user is unauthorized, and should be allowed to sign in) are sent.

3.    Once the user enters in a username and password, the browser recalls your script, from the top once again.

So you need to check and see whether there are any available credentials before authentication headers are sent. And then, if there are credentials, check them against allowed values. Finally, if the credentials don’t match or don’t exist, then send the authentication headers.

Once again, then, isset (Expect the Unexpected) becomes your friend. Start with code like this:

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW'])) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

}

But all this code does is pop up the login box if the username and password haven’t previously been set. It still allows access to your page—through a couple of different ways. So you need to not just pop up a login box, but ensure that any preset usernames and passwords match an allowed set of values.

Cancel is Not a Valid Means of Authentication

Before you deal with checking usernames and passwords, though, there’s something more pressing to deal with: even trickier than accepting any username and password is accepting a press of the Cancel button.

A Cancel press is easy to deal with, albeit a bit unintuitive. Here’s your code right now:

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW'])) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

}

The login box is prompted by the two calls to header:

header('HTTP/1.1 401 Unauthorized');

header('WWW-Authenticate: Basic realm="The Social Site"');

When a user clicks Cancel, your PHP continues to run, right from after the second header line:

header('HTTP/1.1 401 Unauthorized');

header('WWW-Authenticate: Basic realm="The Social Site"');

// This line is run if Cancel is clicked

So at its simplest, you could simply bail out of the script:

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW'])) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

So now if a user hits Cancel, the script runs the exit command—which is a lot like die—and bails out with an error message, as shown in Figure 11-3.

Now if a user tries the ever-so-clever Cancel-without-signing-in trick, your script handles the situation. Then again, this code is pretty crude, and doesn’t exactly live up to your application’s standard in user-friendly views and web pages. You’ll be able to improve the script, in the next chapter.

Figure 11-3. Now if a user tries the ever-so-clever Cancel-without-signing-in trick, your script handles the situation. Then again, this code is pretty crude, and doesn’t exactly live up to your application’s standard in user-friendly views and web pages. You’ll be able to improve the script, in the next chapter.

Get Your User’s Credentials (Really!)

Now you can get back to seeing what your user actually supplied to the login box. Remember, the flow here isn’t what you might expect. Once the user has entered in a username and password, your script is basically recalled. It’s almost as if the server is giving you a free while loop, something like this:

while (username_and_password_are_wrong) {

  ask_for_username_and_password_again();

}

NOTE

This script isn’t running, working PHP. It’s something called pseudocode. For more on what pseudocode is—and why it’s your friend—check out the box on the next page.

Right now, you have an if that sees if the username and password have been set. If not, send the headers, and if Cancel is clicked, bail out.

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW'])) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

UP TO SPEED: PSEUDOCODE IS CODE BEFORE YOU WRITE CODE

Lots of times you’ll find that you need a happy medium before writing full-on working code, syntactically accurate, debugged, ready to run; and scribbling a list down in a notebook. You want to think about the details of how things will work, without getting bogged down by minutiae.

This situation is the very type of thing for which pseudocode was designed. Like code, it typically uses the syntax of the language you’re using. So you might use an if, a while, an else, and you’ll probably throw in curly braces or angle brackets, if you’re writing pseudocode that you’ll eventually turn into PHP. That’s why this…

while (username_and_password_are_wrong) {

  ask_for_username_and_password_again();

}

…is a great example of pseudocode that will later be PHP. But in the case above, it’s not helpful to type out all the $_SERVER stuff, because it’s long, full of little commas and apostrophes, and you already know the basic idea. So whether you’re explaining to a coworker what you’re doing, or just planning out your code, this is a perfectly good stand-in:

while (username_and_password_are_wrong) {

In your head, you may be translating that to something like this:

if (($_SERVER['PHP_AUTH_USER'] !=

      VALID_USERNAME) ||

    ($_SERVER['PHP_AUTH_PW'] !=

      VALID_PASSWORD)) {

And then what will you do once you make that determination? Something…you’re not sure what yet. You know basically what has to happen, but the details are still up in the air. That leaves you with this:

ask_for_username_and_password_again();

It’s clear, it’s understandable, but it’s not bogged down by intricacy. It’s pseudocode. It’s great for getting an idea going, or communicating about code. It’s also great in a situation like this, where something tells you the way you’re doing things might need to change. And if change is coming, the less work you put into a solution that isn’t permanent, the better.

In an else part of this script—yet to be written—you could check the username and password against the acceptable values. If they match, go on and display the output from show_users.php. If not…well, you actually want to re-send the headers that cause the browser to prompt the user to log in again. So you want something like this:

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW'])) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

} else {

  if (($_SERVER['PHP_AUTH_USER'] != VALID_USERNAME) ||

      ($_SERVER['PHP_AUTH_PW'] != VALID_PASSWORD)) {

    header('HTTP/1.1 401 Unauthorized');

    header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

  }

}

NOTE

Technically, the if block is supplying an incorrect message to exit. That exit deals with the case where the user hit Cancel, rather than entering a wrong username and password. As a rule, though, you want to provide minimal information to users on security failures, so a generic “one size fits all” message is the better approach here.

Given that, you can consolidate things a bit. Whether the user has never attempted to log in, or incorrectly entered their username or password, the script needs to send HTTP headers to force authentication. It’s only if the user has entered information, and it matches the appropriate values, that the rest of the page’s action should be taken, and the output should be displayed. So what you want is this:

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW']) ||

    ($_SERVER['PHP_AUTH_USER'] != VALID_USERNAME) ||

    ($_SERVER['PHP_AUTH_PW'] != VALID_PASSWORD)) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

NOTE

Go ahead and add this code to your version of show_users.php.

So now go up to the top of show_users.php—make sure it’s before your new if statement—and add in a few new constants:

<?php

require_once '../scripts/app_config.php';

require_once '../scripts/database_connection.php';

require_once '../scripts/view.php';

define(VALID_USERNAME, "admin");

define(VALID_PASSWORD, "super_secret");

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW']) ||

    ($_SERVER['PHP_AUTH_USER'] != VALID_USERNAME) ||

    ($_SERVER['PHP_AUTH_PW'] != VALID_PASSWORD)) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

Try visiting show_users.php again, and entering in “admin” and “super_secret” as the username and password (as in Figure 11-4. You should be greeted with the normal show_users.php view (Figure 11-5). Otherwise, you’ll just get the authentication pop-up over and over (as shown back inFigure 11-1).

Finally, entering a username and password truly matters. The browser responds to your headers with a login box, and reports the values to PHP through the $_SERVER superglobal variable.

Figure 11-4. Finally, entering a username and password truly matters. The browser responds to your headers with a login box, and reports the values to PHP through the $_SERVER superglobal variable.

Once you’ve made it through security, you’re back to seeing users again. And that’s the point: authentication is separate from the core content of your pages.

Figure 11-5. Once you’ve made it through security, you’re back to seeing users again. And that’s the point: authentication is separate from the core content of your pages.

Abstracting What’s the Same

So here you are again, with some code in show_users.php that probably doesn’t belong in show_users.php. Why is that? Because the same authorization and authentication you have in show_users.php belongs in every other script that should require logging in—like delete_user.php. You don’t want to write that code over and over; it becomes just like other repeated code you now have in app_config.php and database_connection.php: you should take it out of individual scripts and place it someplace where all your scripts can use it.

Another Utility Script: authorize.php

Fire up your editor once more, and this time create authorize.php. You can start by adding in that valid username/password combination:

<?php

define(VALID_USERNAME, "admin");

define(VALID_PASSWORD, "super_secret");

?>

At this point you’d usually write a function, maybe authorize or get_credentials or something like that. But is that really what you want? Do you want to have to require_once authorize.php, and then explicitly call a function?

More likely, you want to identify scripts that require authorization with a single line:

require_once "../scripts/authorize.php;"

Then, ideally, the authorization would all just magically happen for you.

FREQUENTLY ASKED QUESTION: ISN’T AN INFINITE NUMBER OF LOGIN ATTEMPTS BAD?

Yes. Absolutely. And that’s exactly what you’re providing in show_users.php right now: the opportunity to try, over and over and over, to get a valid username and password. Truth be told, the sample code and patterns you’ll see all over the Web for using basic authentication look just like what you’ve got in show_users.php.

There certainly are ways to get around this problem, but they’re not as easy as you might wish. Since the browser is making multiple requests to your script, you’d have to figure out a way to pass the number of requests that have been made to your script…from your script. Yeah, it gets a little tricky.

There are ways to detect multiple requests, and you’ll learn about them (although for a much better purpose) in the next chapter on sessions. For now, realize that the basic authentication approach is temporary anyway, and all this code is a starting point, not a stopping point.

Given that, then, you don’t want a function that has to be called. You just want some PHP code, in the main part of authorize.php. That way, by requiring authorize.php, that code runs, handles authentication, and your script doesn’t have to do anything to get the benefits of authentication and authorization.

In a lot of ways, authorization here is like having JavaScript inside a set of <script> tags with no function:

<script type="text/javascript">

  dashboard_alert("#hits_count_dialog");

  $("#hits_count_dialog").dialog("open");

  query_results_tables();

</script>

As soon as a browser hits that JavaScript, it runs it. The same is true of PHP outside of a function. So you can drop your authorization code right into authorize.php:

<?php

define(VALID_USERNAME, "admin");

define(VALID_PASSWORD, "super_secret");

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW']) ||

    ($_SERVER['PHP_AUTH_USER'] != VALID_USERNAME) ||

    ($_SERVER['PHP_AUTH_PW'] != VALID_PASSWORD)) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

?>

Now, any script that has a require_once for authorize.php will cause authorize.php to be processed, which in turn will run the authorization code. That, in turn, will ensure that either users are logged in, or are forced to log in. So things look pretty nice.

Go ahead and remove this code from show_users.php and add in a require_once for authorize.php:

<?php

require_once '../scripts/app_config.php';

require_once '../scripts/authorize.php';

require_once '../scripts/database_connection.php';

require_once '../scripts/view.php';

// Authorization code is no longer in this script

// Build the SELECT statement

$select_users =

  "SELECT user_id, first_name, last_name, email " .

  "  FROM users";

// and so on...

?>

Now you can hit show_users.php again and get a nice login box. But that’s not all this change buys you. Add a similar line into delete_user.php:

<?php

require_once '../scripts/app_config.php';

require_once '../scripts/authorize.php';

require_once '../scripts/database_connection.php';

// and so on...

Now, close out your browser, so any passwords are lost. Then, re-open your browser and navigate directly to delete_user.php. You’ll be greeted with a login box (see Figure 11-6). What’s significant about this? Most obviously, it took a single line of PHP to add security to another page.

Once you’ve made it through security, you’re back to seeing users again. And that’s the point: authentication is separate from the core content of your pages.

Figure 11-6. Once you’ve made it through security, you’re back to seeing users again. And that’s the point: authentication is separate from the core content of your pages.

But there’s more! If you’ve logged in, close your browser again and head over to show_users.php. As you’d expect, you’ll have to login. Do so, and then click the Delete icon on one of your users. This will take you to delete_user.php, and the PHP in authorize.php will be triggered. But because you’ve already logged in to the realm identified as “The Social Site”, you’re not prompted to login again. Remember your code that specifies a realm:

header('WWW-Authenticate: Basic realm="The Social Site"');

Any page that uses this realm will effectively “share” credentials with other pages in the same realm. So since you logged in to access show_users.php, and that realm is identical to the realm for delete_user.php, your delete request goes through without a problem (Figure 11-7 shows the result—no login box in sight).

Sharing credentials works only if the realm is the same for these two pages. That’s yet another reason to pull authentication and authorization code out of individual scripts and put it in one single place that’s referenced by your other scripts. Your realm will be identical across all those referencing scripts.

Figure 11-7. Sharing credentials works only if the realm is the same for these two pages. That’s yet another reason to pull authentication and authorization code out of individual scripts and put it in one single place that’s referenced by your other scripts. Your realm will be identical across all those referencing scripts.

There’s still a pretty glaring problem, though…

Passwords Don’t Belong in PHP Scripts

At this point, it’s easy to forget that behind every good script lies a great database. (Or something like that.) It’s simply a horrible idea to have a PHP script—even if it’s a utility script like app_config.php or authorize.php—that has a few constants defining allowable usernames and passwords. That’s very much the job of a database.

Databases are typically more difficult to access than your scripts, because your scripts are to some degree Web-accessible. Your database, on the other hand, is generally at least a layer further removed from the typical Web user. Additionally, your database and SQL require structural knowledge to be very useful. Scripts are just files that can be browsed, and often the information in those files is just text. The list could go on, but suffice it to say that a database is a safer place for passwords than authorize.php.

NOTE

You can do a few things to make your scripts—especially utility ones—less accessible from the Web. And you can certainly make bad decisions that make your database more accessible from the Web. But in their default states, scripts are meant to be accessed by a browser, and raw database columns and rows aren’t, apart from a healthy authentication system in place by default.

And then there’s yet another key reason to get your passwords into a database: you already are storing user information! So you can connect that information to a password by adding a column. And, as you’ll see soon, groups of users aren’t far away, either. So before you get too comfortable, you need to dig back into MySQL and improve that authentication situation.

Updating the users Table

The first thing you need to do is update users. It’s been a while, so here’s what you should have at this point:

mysql> describe users;

+----------------+--------------+------+-----+---------+----------------+

| Field          | Type         | Null | Key | Default | Extra          |

+----------------+--------------+------+-----+---------+----------------+

| user_id        | int(11)      | NO   | PRI | NULL    | auto_increment |

| first_name     | varchar(20)  | NO   |     |         |                |

| last_name      | varchar(30)  | NO   |     |         |                |

| email          | varchar(50)  | NO   |     |         |                |

| facebook_url   | varchar(100) | YES  |     | NULL    |                |

| twitter_handle | varchar(20)  | YES  |     | NULL    |                |

| bio            | text         | YES  |     | NULL    |                |

| user_pic_path  | text         | YES  |     | NULL    |                |

+----------------+--------------+------+-----+---------+----------------+

8 rows in set (0.02 sec)

There’s nothing wrong here, but there are some omissions: most notably a username and a password. Those are the two essential pieces of information that your basic authentication requires.

Add both columns to your table:

mysql> ALTER TABLE users

    ->         ADD username VARCHAR(32) NOT NULL

    ->       AFTER user_id,

    ->         ADD password VARCHAR(16) NOT NULL

    ->       AFTER username;

NOTE

The AFTER keyword tells MySQL exactly where to add a column. Using AFTER helps avoid important columns—and username and password are certainly important columns—from getting stuck at the end of a table’s structure. You can leave it off, but it tends to make for more organized tables, especially when you’re using DESCRIBE.

You should make sure these changes are in place now:

mysql> describe users;

+----------------+--------------+------+-----+---------+----------------+

| Field          | Type         | Null | Key | Default | Extra          |

+----------------+--------------+------+-----+---------+----------------+

| user_id        | int(11)      | NO   | PRI | NULL    | auto_increment |

| username       | varchar(32)  | NO   |     | NULL    |                |

| password       | varchar(16)  | NO   |     | NULL    |                |

| first_name     | varchar(20)  | NO   |     |         |                |

| last_name      | varchar(30)  | NO   |     |         |                |

| email          | varchar(50)  | NO   |     |         |                |

| facebook_url   | varchar(100) | YES  |     | NULL    |                |

| twitter_handle | varchar(20)  | YES  |     | NULL    |                |

| bio            | text         | YES  |     | NULL    |                |

| user_pic_path  | text         | YES  |     | NULL    |                |

+----------------+--------------+------+-----+---------+----------------+

10 rows in set (0.03 sec)

Deal with Newly Invalid Data

As was the case when you added columns before, you now actually have a table full of invalid rows. Since both username and password are required (NOT NULL), and none of the existing rows have values in those columns, all your table’s rows are in violation of that table’s rules.

You can fix the invalid rows with some more SQL. So, for example, to update James Roday, you’d use something like this:

mysql> UPDATE users

    ->    SET username = "jroday",

    ->        password = "psych_rules"

    ->  WHERE user_id = 45;

You can confirm these changes were made, as well:

mysql> SELECT user_id, username, password, first_name, last_name

    ->   FROM users

    ->  WHERE user_id = 45;

+---------+----------+-------------+------------+-----------+

| user_id | username | password    | first_name | last_name |

+---------+----------+-------------+------------+-----------+

|      45 | jroday   | psych_rules | James      | Roday     |

+---------+----------+-------------+------------+-----------+

1 row in set (0.00 sec)

You should make similar changes to your own users table so that all the users you’ve added have a username and password.

FREQUENTLY ASKED QUESTION: ISN’T A NON-EMAIL USERNAME SO 2006?

It seems like every time you turn around, a new social website is popping up…a site that you simply must join. And more and more of those sites are using email addresses as your login. There’s a lot to like about this approach, too.

§  Most people remember their email address more readily than one of 50 different usernames floating around.

§  Email addresses like tommy.n@dbc.org are a lot more readable (and typeable) than a username like tn1954a.

§  It’s less information to store in your database.

So if that’s the way the username-wind is blowing, why create a username column in users? Why not just use the email address?

First, a lot of people have just as many email addresses as they have usernames these days. With Gmail, Apple’s MobileMe (now iCloud), at least one business email, and perhaps a personal domain or two, it can be pretty ridiculous.

Second, plenty of people don’t like using their emails as their username. A username seems more anonymous, while an email is an actual way to get something into your inbox. It might seem odd, but lots of people are fine with supplying an email as part of sign-up, but they don’t want to be reminded that tons of sites have that piece of information. Typing it into a login box isn’t the greatest way to avoid that reminder.

And perhaps most important, if an email is the username, how do you retrieve a user’s password? Typically, with a username system, you require a user to supply their username when a password is lost as some sort of verification. When the user’s email is their username, what do you request for verification?

Even though there’s nothing horribly wrong with using an email address as a username, it’s still probably best to require a username. Besides, with fantastic programs like 1Password (www.agilebits.com/products/1Password), it’s not that hard to manage multiple logins anymore. (And seriously, although it might seem a bit pricey at $59.99, go buy 1Password today. It’s a Web-life changer.)

You Need to Get an Initial Username and Password

So now you’ve got to go back…way back. Remember create_user.html? That was the rather simple HTML form that got you the user’s initial information (Write a PHP Script). But it needs some real improvement: a username and password field, to start.

Here’s a significantly updated version of create_user.html, which adds—among a lot of other things—a field to get a username and two fields that combine to get a password from new users.

<html>

 <head>

  <link href="../css/phpMM.css" rel="stylesheet" type="text/css" />

  <link href="../css/jquery.validate.password.css" rel="stylesheet"

type="text/css" />

  <script type="text/javascript" src="../js/jquery.js"></script>

  <script type="text/javascript" src="../js/jquery.validate.js"></script>

  <script type="text/javascript" src="../js/jquery.validate.password.js">

  </script>

  <script type="text/javascript">

    $(document).ready(function() {

      $("#signup_form").validate({

        rules: {

          password: {

            minlength: 6

          },

          confirm_password: {

            minlength: 6,

            equalTo: "#password"

          }

        },

        messages: {

          password: {

            minlength: "Passwords must be at least 6 characters"

          },

          confirm_password: {

            minlength: "Passwords must be at least 6 characters",

            equalTo: "Your passwords do not match."

          }

        }

      });

    });

  </script>

 </head>

 <body>

  <div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>

  <div id="example">User Signup</div>

  <div id="content">

    <h1>Join the Missing Manual (Digital) Social Club</h1>

    <p>Please enter your online connections below:</p>

    <form id="signup_form" action="create_user.php"

          method="POST" enctype="multipart/form-data">

      <fieldset>

        <label for="first_name">First Name:</label>

        <input type="text" name="first_name" size="20" class="required" />

        <br />

        <label for="last_name">Last Name:</label>

        <input type="text" name="last_name" size="20" class="required" />

        <br />

        <label for="username">Username:</label>

        <input type="text" name="username" size="20" class="required" />

        <br />

        <label for="password">Password:</label>

        <input type="password" id="password" name="password"

               size="20" class="required password" />

        <div class="password-meter">

          <div class="password-meter-message"> </div>

          <div class="password-meter-bg">

            <div class="password-meter-bar"></div>

          </div>

        </div>

        <br />

        <label for="confirm_password">Confirm Password:</label>

        <input type="password" id="confirm_password" name="confirm_password"

               size="20" class="required" /><br />

        <label for="email">E-Mail Address:</label>

        <input type="text" name="email" size="30" class="required email" />

        <br />

        <label for="facebook_url">Facebook URL:</label>

        <input type="text" name="facebook_url" size="50" class="url" /><br />

        <label for="twitter_handle">Twitter Handle:</label>

        <input type="text" name="twitter_handle" size="20" /><br />

        <input type="hidden" name="MAX_FILE_SIZE" value="2000000" />

        <label for="user_pic">Upload a picture:</label>

        <input type="file" name="user_pic" size="30" /><br />

        <label for="bio">Bio:</label>

        <textarea name="bio" cols="40" rows="10"></textarea>

      </fieldset>

      <br />

      <fieldset class="center">

        <input type="submit" value="Join the Club" />

        <input type="reset" value="Clear and Restart" />

      </fieldset>

    </form>

  </div>

  <div id="footer"></div>

 </body>

</html>

In addition to the two new fields, this version of the form adds in jQuery, available from www.jquery.com. jQuery is a free, downloadable JavaScript library that makes almost everything in JavaScript a lot easier. In addition to the core jQuery library, there are two jQuery plug-ins: one for general validation and another specifically for password validation. You can download both of these plug-ins from www.jquery.bassistance.de.

NOTE

If you’re completely new to jQuery, you should pick up JavaScript & jQuery: The Missing Manual. You’ll get up to speed on how to use jQuery, and discover a whole host of reasons—besides the nifty validation plug-ins now used by create_user.html—that it’s worth your time to learn.

The new version of create_user.html looks largely the same. It does add a password strength bar, although that’s not apparent until the user tries to enter a password. Most importantly, this form adds a username and two places to enter a password: an initial entry and a place to confirm that entry. Make sure these fields are the “password” type to hide the user’s typing, too.

Figure 11-8. The new version of create_user.html looks largely the same. It does add a password strength bar, although that’s not apparent until the user tries to enter a password. Most importantly, this form adds a username and two places to enter a password: an initial entry and a place to confirm that entry. Make sure these fields are the “password” type to hide the user’s typing, too.

Save this updated version of create_user.html and check it out. The initial page looks the same (see Figure 11-8), but now you get validation of most of the form fields (shown in Figure 11-9) and a nice password strength indicator, too (check out Figure 11-10).

jQuery and the jQuery validation plug-in make field validation a piece of cake. With minimal work, you get type validation, length validation, optionally customized error messages, and more. You can validate emails and zip codes and phone numbers. All that for a quick download and a few lines of JavaScript… pretty good stuff.

Figure 11-9. jQuery and the jQuery validation plug-in make field validation a piece of cake. With minimal work, you get type validation, length validation, optionally customized error messages, and more. You can validate emails and zip codes and phone numbers. All that for a quick download and a few lines of JavaScript… pretty good stuff.

Now you’re getting the right information from your users. It’s time to update your PHP to do something with this.

The password validator is an add-on for the jQuery validation plug-in, and adds a strength indicator that requires “strong” passwords. It’s a nice feature, and best of all, doesn’t increase your work load at all. You get all this “for free” before data ever makes it to your PHP scripts.

Figure 11-10. The password validator is an add-on for the jQuery validation plug-in, and adds a strength indicator that requires “strong” passwords. It’s a nice feature, and best of all, doesn’t increase your work load at all. You get all this “for free” before data ever makes it to your PHP scripts.

Inserting the User’s Username and Password

Now you can update create_user.php as well. This update is simple, and certainly requires a lot less work, although the result of these changes is pretty significant.

<?php

require_once '../scripts/app_config.php';

require_once '../scripts/database_connection.php';

$upload_dir = SITE_ROOT . "uploads/profile_pics/";

$image_fieldname = "user_pic";

// Potential PHP upload errors

$php_errors = array(1 => 'Maximum file size in php.ini exceeded',

                    2 => 'Maximum file size in HTML form exceeded',

                    3 => 'Only part of the file was uploaded',

                    4 => 'No file was selected to upload.');

$first_name = trim($_REQUEST['first_name']);

$last_name = trim($_REQUEST['last_name']);

$username = trim($_REQUEST['username']);

$password = trim($_REQUEST['password']);

$email = trim($_REQUEST['email']);

$bio = trim($_REQUEST['bio']);

$facebook_url = str_replace("facebook.org", "facebook.com", trim($_

REQUEST['facebook_url']));

$position = strpos($facebook_url, "facebook.com");

if ($position === false) {

  $facebook_url = "http://www.facebook.com/" . $facebook_url;

}

$twitter_handle = trim($_REQUEST['twitter_handle']);

$twitter_url = "http://www.twitter.com/";

$position = strpos($twitter_handle, "@");

if ($position === false) {

  $twitter_url = $twitter_url . $twitter_handle;

} else {

  $twitter_url = $twitter_url . substr($twitter_handle, $position + 1);

}

// Make sure we didn't have an error uploading the image

($_FILES[$image_fieldname]['error'] == 0)

  or handle_error("the server couldn't upload the image you selected.",

                  $php_errors[$_FILES[$image_fieldname]['error']]);

// Is this file the result of a valid upload?

@is_uploaded_file($_FILES[$image_fieldname]['tmp_name'])

  or handle_error("you were trying to do something naughty. Shame on you!",

                  "Uploaded request: file named '{$_FILES[$image_fieldname]

['tmp_name']}'");

// Is this actually an image?

@getimagesize($_FILES[$image_fieldname]['tmp_name'])

  or handle_error("you selected a file for your picture that isn't an image.",

                  "{$_FILES[$image_fieldname]['tmp_name']} isn't a valid image

file.");

// Name the file uniquely

$now = time();

while (file_exists($upload_filename = $upload_dir . $now .

                                     '-' .

                                     $_FILES[$image_fieldname]['name'])) {

    $now++;

}

// Finally, move the file to its permanent location

@move_uploaded_file($_FILES[$image_fieldname]['tmp_name'],

                    $upload_filename)

  or handle_error(

     "we had a problem saving your image to its permanent location.",

     "permissions or related error moving file to {$upload_filename}");

$insert_sql = sprintf("INSERT INTO users " .

                             "(first_name, last_name, username, " .

                              "password, email, " .

                              "bio, facebook_url, twitter_handle, " .

                              "user_pic_path) " .

                      "VALUES ('%s', '%s', '%s', '%s', '%s',

                               '%s', '%s', '%s', '%s');",

                      mysql_real_escape_string($first_name),

                      mysql_real_escape_string($last_name),

                      mysql_real_escape_string($username),

                      mysql_real_escape_string($password),

                      mysql_real_escape_string($email),

                      mysql_real_escape_string($bio),

                      mysql_real_escape_string($facebook_url),

                      mysql_real_escape_string($twitter_handle),

                      mysql_real_escape_string($upload_filename));

// Insert the user into the database

mysql_query($insert_sql);

// Redirect the user to the page that displays user information

header("Location: show_user.php?user_id=" . mysql_insert_id());

?>

NOTE

Even though only a few lines have changed, this is a good chance for you to check your current version of create_user.php (along with create_user.html). Make sure they’re current, especially since all the changes from Chapters Chapter 9 and Chapter 10 related to image handling. If you feel your code is hopelessly out-of-date, you can always re-download these scripts from the Missing CD page (page xvii).

As usual, try entering some sample data, and make sure you get something like Figure 11-11 as a validation that all your changes work. Also, make sure that you do not add authorize.php to your scripts list of require_once statements. You can hardly require users to log in to the form by which they tell your application about the username and password they want to use for those logins.

FREQUENTLY ASKED QUESTION: SHOULDN’T CREATE_USER.PHP CHECK THE REQUESTED USERNAME?

Yes. It’s a pretty big issue that in the current version of create_user.php, users are inserted into the database without checking the uniqueness of their usernames. Certainly, you could enforce that at the database level, but then you’d just get a nasty error.

In its simplest form, you could do a SELECT on the desired username, and if any users are returned, redirect the user to an error page using handle_error. That’s pretty primitive, though. It completely shuts down any flow, and the user—if they don’t bail from your application completely—will have to enter all their information into the user sign-up form again.

A better approach would be to convert create_user.html into a script, or even roll it into the current version of create_user.php. In either case, if the username is already taken, the user should be redirect back to the sign-up form, with all their previous information filled in, and a message should tell them to try another password. Then, if you want to move into the deep end of the pool, do everything above, but do it with Ajax so that the sign-up page never reload.

So where’s the code for creating this message? It’s in your head and at your fingertips. At this stage of your PHP journey, you’re ready to increasingly tackle problems like this yourself. Use a book or the Web as a resource for new techniques—like authentication in this chapter or sessions in Chapter 12—but you’re plenty capable of working out new uses for things you already know on your own.

In fact, tweet a link to your solution to preventing multiple usernames to @missingmanuals on Twitter or post it on the Missing Manuals Facebook page at www.facebook.com/MissingManuals. Free books, videos, and swag are always available for clever and elegant solutions.

Connect authorize.php to Your users Table

At this point, there’s just one glaring hole to plug: authorize.php. Right now, the only username and password accepted is this rather silly bit of constant work:

define(VALID_USERNAME, "admin");

define(VALID_PASSWORD, "super_secret");

But now authorize.php has a users table from which to pull users’ usernames and passwords.

Fortunately, connecting authorize.php to users isn’t very hard. In fact, fixing up authorize.php involves only stringing together things you’ve already done. First, remove those two constants, and add in require_once for database_connection.php, which you’ll need for interacting with theusers table.

<?php

require_once 'database_connection.php';

// define(VALID_USERNAME, "admin");         DELETE THIS LINE

// define(VALID_PASSWORD, "super_secret");  DELETE THIS LINE

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW']) ||

    ($_SERVER['PHP_AUTH_USER'] != VALID_USERNAME) ||

    ($_SERVER['PHP_AUTH_PW'] != VALID_PASSWORD)) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

?>

Now, that big burly if needs to be trimmed some. The first portion still works; if the $_SERVER superglobal has no value for PHP_AUTH_USER or PHP_AUTH_PW, then headers should still be sent to the browser instructing it to pop up a login box. But now, there’s no VALID_USERNAMEor VALID_PASSWORD constant to which the user’s values should be compared. So that part of the if has to go. Here’s what should be left:

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW'])) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

NOTE

Everything after the if is effectively an else, even though there’s no else keyword. If the body of the if executes, it will call exit, ending the script. So it’s only if there is a value for PHP_AUTH_USER and PHP_AUTH_PW in $_SERVER that the rest of the script runs.

The next thing the script needs to do is get anything the user entered—and if the script gets here, then the user did enter something—and compare it to values in the database. But that’s something you’ve done a number of times. It’s just more sprintf and mysql_real_escape_string, both of which you’ve used a ton:

<?php

require_once 'database_connection.php';

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW'])) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

// Look up the user-provided credentials

$query = sprintf("SELECT user_id, username FROM users " .

                 " WHERE username = '%s' AND " .

                 "       password = '%s';",

            mysql_real_escape_string(trim($_SERVER['PHP_AUTH_USER'])),

            mysql_real_escape_string(trim($_SERVER['PHP_AUTH_PW'])));

$results = mysql_query($query);

?>

There’s nothing particularly new here. And you know how to get the results. But this time, before worrying about the actual values from the response, the biggest concern is seeing if there are any results. If a row matches the username and password provided, then the user is legitimate. (Or, at a minimum, they’ve borrowed someone else’s credentials. And “borrowed” is being used pretty loosely there.)

So the first thing to do is to see if there are any results. If not, then the script has reached the same place as the earlier version, when the username and password weren’t valid. That means sending those headers again:

if (mysql_num_rows($results) == 1) {

  // Everything's ok! Let this user through

} else {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

NOTE

Move to the head of the class if you’re bothered that the code that sends these headers here is identical to the code earlier in the script (Connect authorize.php to Your users Table). Before moving on, go ahead and create a function that outputs those headers, takes in a message to pass to exit, and then call that function twice in authorize.php.

Now there’s just one more thing to do, and it’s a bit of a nicety. Since the user’s just logged in, go ahead and let any script that calls authorize.php have access to the user that’s just logged in:

if (mysql_num_rows($results) == 1) {

  $result = mysql_fetch_array($results);

  $current_user_id = $result['user_id'];

  $current_username = $result['username'];

} else {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

So now the entire script, new and certainly improved, looks like this:

<?php

require_once 'database_connection.php';

if (!isset($_SERVER['PHP_AUTH_USER']) ||

    !isset($_SERVER['PHP_AUTH_PW'])) {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

// Look up the user-provided credentials

$query = sprintf("SELECT user_id, username FROM users " .

                 " WHERE username = '%s' AND " .

                 "       password = '%s';",

            mysql_real_escape_string(trim($_SERVER['PHP_AUTH_USER'])),

            mysql_real_escape_string(trim($_SERVER['PHP_AUTH_PW'])));

$results = mysql_query($query);

if (mysql_num_rows($results) == 1) {

  $result = mysql_fetch_array($results);

  $current_user_id = $result['user_id'];

  $current_username = $result['username'];

} else {

  header('HTTP/1.1 401 Unauthorized');

  header('WWW-Authenticate: Basic realm="The Social Site"');

  exit("You need a valid username and password to be here. " .

       "Move along, nothing to see.");

}

?>

Test it out. Create a user (or add a username and password to an existing user in your database), and then close and re-open your browser to reset any saved credentials. Hit show_users.php or any other page in which you’ve required authorize.php. You should get a login box, be able to enter in database values, and see the page you requested.

Passwords Create Security, But Should Be Secure

With your new database-driven login facility, there are lots of things that are new and possible. First and foremost, you could create groups in the database, and allow users access to certain parts of your application based on their group membership. So instead of letting just anyone accessshow_users.php, you only want users that are members of an admin group.

But before you do all that, take a second look at one of your recent SQL statements and the results:

mysql> SELECT user_id, username, password, first_name, last_name

    ->   FROM users

    ->  WHERE user_id = 45;

+---------+----------+-------------+------------+-----------+

| user_id | username | password    | first_name | last_name |

+---------+----------+-------------+------------+-----------+

|      45 | jroday   | psych_rules | James      | Roday     |

+---------+----------+-------------+------------+-----------+

1 row in set (0.00 sec)

Anything odd there? (Other than James Roday’s lousy choice of password…sure, Psych is fantastic, but it’s not exactly a hard-to-crack passcode.)

All the same, the more glaring issue is that the password just sits there in the database. It’s plain old text. And even if you’re new to the world of authentication and authorization, you probably have heard the term encryption. Encryption is simply taking a piece of information—usually something secure like a password—and making it unreadable for the normal mortal. The idea is that other than the user who “owns” a password, nobody—even you, the all-wise all-knowing programmer—should see a user’s password in normal text.

So what you need is a means of encrypting that password into something unreadable. And, you know what’s coming: PHP has a function for that.

Encrypt Text with the crypt Function

First, you’ve got to convert the password into something that’s non-readable. You can do that using PHP’s crypt function. crypt takes a string (and an optional second parameter you’ll need shortly), and produces what looks a bit like gibberish:

$encrypted_password = crypt($password);

To see this in action, make the following change to create_user.php:

$insert_sql = sprintf("INSERT INTO users " .

                             "(first_name, last_name, username, " .

                              "password, email, " .

                              "bio, facebook_url, twitter_handle, " .

                              "user_pic_path) " .

                      "VALUES ('%s', '%s', '%s', '%s', '%s',

                               '%s', '%s', '%s', '%s');",

                      mysql_real_escape_string($first_name),

                      mysql_real_escape_string($last_name),

                      mysql_real_escape_string($username),

                      mysql_real_escape_string(crypt($password)),

                      mysql_real_escape_string($email),

                      mysql_real_escape_string($bio),

                      mysql_real_escape_string($facebook_url),

                      mysql_real_escape_string($twitter_handle),

                      mysql_real_escape_string($upload_filename));

Now create a new user, allow create_user.php to save that user, and then check out that user in your users table:

mysql> SELECT user_id, username, password, last_name

    ->   FROM users

    ->  WHERE user_id = 51;

+---------+----------+------------------+-----------+

| user_id | username | password         | last_name |

+---------+----------+------------------+-----------+

|      51 | traugott | $1$qzifqLu4$0C88 | Traugott  |

+---------+----------+------------------+-----------+

1 row in set (0.00 sec)

That’s quite an improvement. In fact, you should probably increase the size of the password field, as crypt adds a good bit of length to the originally entered password.

ALTER TABLE users

     CHANGE password

            password VARCHAR(50) NOT NULL;

NOTE

That doubled “password” field name is intentional. When you’re changing a column, you first give the original name of the column. Then, you provide the new column name, the new column type, and any modifiers (like NOT NULL). In this instance, since the original name and new name are identical, you simply double “password.”

Now, that gets the password into your database, but what about getting it out?

crypt is One-Way Encryption

crypt, by definition, is one-way encryption. That means that once a password has been encrypted, it can’t be unencrypted. While that presents you some problems as a programmer, it’s a good thing for your users. It means that even the admins of applications they use can’t go digging into their databases and pulling out users’ passwords.

If they try, they get only an encrypted version. And there’s no special formula or magical command that lets them get at the original of those passwords. Users are protected; you’re protected. If you can’t get at an encrypted password, you can’t be blamed for identity fraud, for example.

So then, how do you see if a user has entered a valid password if you can’t decrypt their password value in the database?

Easy: you can encrypt their supplied password, and compare that encrypted value to the encrypted value in the database. If the encrypted values match, then things are good—and you still haven’t had to look at what that user’s real password is.

So you want something like the following code in authorize.php, where passwords are checked:

// Look up the user-provided credentials

$query = sprintf("SELECT user_id, username FROM users " .

                 " WHERE username = '%s' AND " .

                 "       password = '%s';",

            mysql_real_escape_string(trim($_SERVER['PHP_AUTH_USER'])),

            mysql_real_escape_string(

              crypt(trim($_SERVER['PHP_AUTH_PW']))));

WARNING

Take your time with all those closing parentheses. It can get pretty hairy, and the last thing you want is a nasty hard-to-find bug because you’re one parenthesis shy.

Now, you should be able to try things out. You’re encrypting passwords on user creation, and you’re encrypting the value to compare with that password on user login.

Unfortunately, try as you might, you’re going to be stuck with Figure 11-11.

So what gives?

Encryption Uses Salt

Remember that briefly-mentioned second argument to crypt? It’s called a salt. A salt is a key—usually a few characters—that is used in generating the one-way encryption used by functions like crypt. The salt helps ensure the randomness and security of a password.

No, it’s not groundhog day. But no matter how many users you create, you’ll never get past this forbidding login box. There’s one thing missing, and it has to do with the inner workings of crypt.

Figure 11-11. No, it’s not groundhog day. But no matter how many users you create, you’ll never get past this forbidding login box. There’s one thing missing, and it has to do with the inner workings of crypt.

So far, by not providing a salt, you’ve been letting crypt figure one out on its own. But unless the salt provided in two different calls to crypt is identical, the resulting encryption will not match. In other words, calling crypt on the same string two times without providing a salt will give you two different results.

Create a simple script called test_salt.php:

<?php

$input = "secret_string";

$first_output = crypt($input);

$second_output = crypt($input);

echo "First output is {$first_output}\n\n";

echo "Second output is {$second_output}\n\n";

?>

Now run this script in your command line terminal:

yellowta@yellowtagmedia.com [~/www/phpMM/ch11]# php test_salt.php

X-Powered-By: PHP/5.2.17

Content-type: text/html

First output is $1$ciU1qEcc$XFT9G7FD/4K/L1Kl.bd.q/

Second output is $1$7cLtF/bc$Js6rEk5RHg4PujAkVOOSG1

That’s not all. Run it again, and you’ll get two different results from those two.

But with one change, things get back to what you’d expect:

<?php

$input = "secret_string";

$salt = "salt";

$first_output = crypt($input, $salt);

$second_output = crypt($input, $salt);

echo "First output is {$first_output}\n\n";

echo "Second output is {$second_output}\n\n";

?>

Now run this updated version, and smile at the results:

yellowta@yellowtagmedia.com [~/www/phpMM/ch11]# php test_salt.php

X-Powered-By: PHP/5.2.17

Content-type: text/html

First output is sazmIw2D3KJ/M

Second output is sazmIw2D3KJ/M

So you need to ensure that both calls to crypt in your application’s scripts use the same salt. Now you could just create a new constant, but there’s an even better solution: use the user’s username itself as the salt! You could completely lose your scripts—and any constant that defines a salt—and your authentication would still work.

The user’s username always stays with the password, so you’re essentially ensuring that the username and password are truly a united combination.

First, update create_user.php (yes, one more time!) to use the user’s supplied username as a salt:

$insert_sql = sprintf("INSERT INTO users " .

                             "(first_name, last_name, username, " .

                              "password, email, " .

                              "bio, facebook_url, twitter_handle, " .

                              "user_pic_path) " .

                      "VALUES ('%s', '%s', '%s', '%s', '%s',

                               '%s', '%s', '%s', '%s');",

                      mysql_real_escape_string($first_name),

                      mysql_real_escape_string($last_name),

                      mysql_real_escape_string($username),

                      mysql_real_escape_string(crypt($password, $username)),

                      mysql_real_escape_string($email),

                      mysql_real_escape_string($bio),

                      mysql_real_escape_string($facebook_url),

                      mysql_real_escape_string($twitter_handle),

                      mysql_real_escape_string($upload_filename));

Now, make the exact same change in authorize.php. Remember in this script, the username comes in through the $_SERVER superglobal:

// Look up the user-provided credentials

$query = sprintf("SELECT user_id, username FROM users " .

                 " WHERE username = '%s' AND " .

                 "       password = '%s';",

            mysql_real_escape_string(trim($_SERVER['PHP_AUTH_USER'])),

            mysql_real_escape_string(

              crypt(trim($_SERVER['PHP_AUTH_PW']),

                    $_SERVER['PHP_AUTH_USER'])));

Now, finally, create a new user (hopefully you’re not running out of friends yet!). Then, try and log in using that user’s username and password.

And…finally. That same old show_users.php screen you say means a lot more than the ability to delete users. It means you’ve got a solid, working authentication system. Congratulations. Enjoy it…there’s one more big hurdle left to overcome.