Tutorial: PHP/MySQL Membership System

[Jesse]

I am going to show you how to make a simple membership system. This included, registering for an account, logging in, security for pages, and logging out.

Now shall we begin? I say yes!


Our database will be setup like the following:

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `username` varchar(225) NOT NULL default '',
  `password` varchar(225) NOT NULL default '',
  `email` varchar(225) NOT NULL default '',
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Breakdown:
user_id is the default value that keeps track of users.
username is the users log in name.
password is the users log in password.
email is the users email, so in later versions of the member system, a forgot password can be added.

Our 1st bit of code will be a file named conf.inc.php. This file holds all of our mysql and function data, so we don’t have to enter it over and over :D.

<?php
$db_user = ""; // Username
$db_pass = ""; // Password
$db_database = ""; // Database Name
$db_host = ""; // Server Hostname
$db_connect = mysql_connect ($db_host, $db_user, $db_pass); // Connects to the database.
$db_select = mysql_select_db ($db_database); // Selects the database.
 
function form($data) { // Prevents SQL Injection
   global $db_connect;
   $data = ereg_replace("[\'\")(;|`,<>]", "", $data);
   $data = mysql_real_escape_string(trim($data), $db_connect);
   return stripslashes($data);
}
?>

Breakdown:
The 1st part is all the mySQL information in order to view and insert data.
The 2nd part prevents SQL injection, so people cant gain unauthorized access.

Our next file will be register.php, it will allow users to register an account so they may login and view parts of the website that others cant.

<?php
include("conf.inc.php"); // Includes the db and form info.
if (!isset($_POST['submit'])) { // If the form has not been submitted.
    echo "<form action=\"register.php\" method=\"POST\">";
    echo "<table>";
    echo "<tr>";
    echo "<td colspan=\"2\">Register:</td>";
    echo "</tr>";
    echo "<tr>";
    echo "<td width=\"50%\">Username:</td><td width=\"50%\"><input name=\"username\" size=\"18\" type=\"text\" />";
    echo "</tr>";
    echo "<tr>";
    echo "<td width=\"50%\">Password:</td><td width=\"50%\"><input name=\"password\" size=\"18\" type=\"text\" />";
    echo "</tr>";
    echo "<tr>";
    echo "<td width=\"50%\">Email:</td><td width=\"50%\"><input name=\"email\" size=\"18\" type=\"text\" />";
    echo "</tr>";
    echo "<tr>";
    echo "<td colspan=\"2\"><input type=\"submit\" name=\"submit\" value=\"submit\"</td>";
    echo "</tr>";
    echo "</table>";
    echo "</form>";
} else { // The form has been submitted.
    $username = form($_POST['username']);
    $password = md5($_POST['password']); // Encrypts the password.
    $email = form($_POST['email']);
 
    if (($username == "") || ($password == "") || ($email == "")) { // Checks for blanks.
        exit("There was a field missing, please correct the form.");
    }
 
    $q = mysql_query("SELECT * FROM `users` WHERE username = '$username' OR email = '$email'") or die (mysql_error()); // mySQL Query
    $r = mysql_num_rows($q); // Checks to see if anything is in the db.
 
    if ($r > 0) { // If there are users with the same username/email.
        exit("That username/email is already registered!");
    } else {
        mysql_query("INSERT INTO `users` (username,password,email) VALUES ('$username','$password','$email')") or die (mysql_error()); // Inserts the user.
        header("Location: login.php"); // Back to login.
    }
}
mysql_close($db_connect); // Closes the connection.
?>

Breakdown:
We 1st include the database details and make sure the form has not been submitted. If it has not been submitted then we display the register form.
If the form is submitted, we make some variables so we can incorporate the form() function.
We then make sure that the users email or user name are not already in the database.
Then we insert the user into the database and redirect them to the login page.

The next page is login.php.

<?php
include("conf.inc.php"); // Includes the db and form info.
session_start(); // Starts the session.
if ($_SESSION['logged'] == 1) { // User is already logged in.
    header("Location: index.php"); // Goes to main page.
    exit(); // Stops the rest of the script.
} else {
    if (!isset($_POST['submit'])) { // The form has not been submitted.
        echo "<form action=\"login.php\" method=\"POST\">";
        echo "<table>";
        echo "<tr>";
        echo "<td colspan=\"2\">Login:</td>";
        echo "</tr>";
        echo "<tr>";
        echo "<td width=\"50%\">Username:</td><td width=\"50%\"><input name=\"username\" size=\"18\" type=\"text\" />";
        echo "</tr>";
        echo "<tr>";
        echo "<td width=\"50%\">Password:</td><td width=\"50%\"><input name=\"password\" size=\"18\" type=\"text\" />";
        echo "</tr>";
        echo "<tr>";
        echo "<td colspan=\"2\"><input type=\"submit\" name=\"submit\" value=\"submit\"</td>";
        echo "</tr>";
        echo "</table>";
        echo "</form>";
    } else {
        $username = form($_POST['username']);
        $password = md5($_POST['password']); // Encrypts the password.
 
        $q = mysql_query("SELECT * FROM `users` WHERE username = '$username' AND password = '$password'") or die (mysql_error()); // mySQL query
        $r = mysql_num_rows($q); // Checks to see if anything is in the db. 
 
        if ($r == 1) { // There is something in the db. The username/password match up.
            $_SESSION['logged'] = 1; // Sets the session.
            header("Location: index.php"); // Goes to main page.
            exit(); // Stops the rest of the script.
        } else { // Invalid username/password.
            exit("Incorrect username/password!"); // Stops the script with an error message.
        }
    }
}
mysql_close($db_connect); // Closes the connection.
?>

Breakdown:
1st we include the db and function file, and start the session, telling the browser that sessions will be used.
We then make sure the form has not been submitted in order to show the login form.
If the form has been submitted we make 2 variables for user name and password. We encrypt the password with md5() so it is a bit more secure. (To all those who are experts in PHP, you would normally salt a password to make it harder to crack, but for beginners stick with md5())
We then have a query checking the database if any users match the use rname and password, and if there are matches it will be counted in $r.
If there are matches we set a login session.

Now we will make logout.php.

<?php
session_unset(); // Destroys the session.
header("Location: login.php"); // Goes back to login.
?>

Breakdown:
We destroy all sessions and forward the user to the login page.

And last but not least, the page where you want only logged in users to view.

<?php
include("conf.inc.php"); // Includes the db and form info.
session_start(); // Starts the session.
if ($_SESSION['logged'] != 1) { // There was no session found!
    header("Location: login.php"); // Goes to login page.
    exit(); // Stops the rest of the script.
}
echo "This is the main page!";
echo "<br />";
echo "<a href=\"logout.php\">Logout?</a>"
?>

Breakdown:
We include the config page.
Check to see if the logged in session is set, otherwise forward user to login page.
Allow the user to log out if needed.

Well thats the basic member system

[gptsven]

nice tutorial

[GTKILLA]

Yeah, this is a good tut! Thanks!

[Jacob, Kid Mania Creator]

Super Helpful

[Jesse]

Thanks Guys ^^

[Zenax]

That is a very good tutorial. One question is, would you need to echo out each part of the form if someone hasnt already registered?

[hezuo]

great man!!! thanks lot.

[atag headquarters]

Just a little help can you specify where to these pages get place into a web site?
cause I got Tags on my website but they don't work..My site( http://www.botwr.x10hosting.com) just a little help Thanks.

[misson]

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `username` varchar(225) NOT NULL default '',
  `email` varchar(225) NOT NULL default '',

`username` and `email` should have a UNIQUE index.

function form($data) { // Prevents SQL Injection
   global $db_connect;
   $data = ereg_replace("[\'\")(;|`,<>]", "", $data);
   $data = mysql_real_escape_string(trim($data), $db_connect);
   return stripslashes($data);
}
?> 

Calling stripslashes after mysql_real_escape_string will most likely undo the latter's affects. stripslashes should be called first, and only if magic_quotes_gpc is enabled. Rather than doing it in form(), you could do it once globally in conf.inc.php, though I recommend splitting up conf's functionality into conf.inc.php (for settings; contains nothing more complex than variable assignments) and an init.php (needn't be edited by site admin; contains more complex code than conf.inc.php, such as function definitions).
init.php (or conf.inc.php):

if (get_magic_quotes_gpc()) {
    $_REQUEST; // make sure $_REQUEST exists
    foreach (array('_GET', '_POST', '_COOKIE', '_REQUEST') as $k) {
        $GLOBALS[$k] = array_map('stripslashes', $GLOBALS[$k]);
    }
} 

Also, if you use PDO rather than the outdated mysql driver, you can use prepared statements, which are immune to SQL injection.

    $password = md5($_POST['password']); // Encrypts the password. 

(Note: MD5 hashes data, it doesn't encrypt it. The difference is encryption is reversible, hashing is one-way.)

MD5 has been broken. Use a newer hashing function, such as whirlpool or something from the SHA2 family (SHA256, SHA512). No less than Bruce Schneier has written:

But -- come on, people -- no one should be using MD5 anymore.

Also, salt should be added to the password before hashing to prevent dictionary attacks. The salt should vary from user to user, but doesn't need to be kept secret. You could use the username + a system salt, or give each user a unique salt (a "nonce") and store that in a column in table `users`.

If you're currently using MD5:

1. Add a new column to your users table indicating which hash function was used. It could be a BOOLEAN value indicating that the p/w needs updating, or a string naming the hash function:
   • `md5` BOOLEAN NOT NULL DEFAULT TRUE,
   • `hash` VARCHAR(16) NOT NULL DEFAULT 'md5',
   The latter option allows you to easily support whatever hashing functions are available on the host.
2. Register new users using the newer hashing function.
3. When a user logs in, check whether their password is hashed using MD5 or not. If it is, expire their password. This is a good chance to have users enter new passwords.
4. If using the 1st column option, drop the column when there are no more MD5 hashed passwords (SELECT COUNT(*) FROM users WHERE `md5`=TRUE is 0)

    if (($username == "") || ($password == "") || ($email == "")) { // Checks for blanks.
        exit("There was a field missing, please correct the form.");
    } 

This check should happen before input is fetched, and it should check the raw input using empty. For one thing, $password will never equal "" since a hashed value will never equal the empty string. For another, if any of the fields aren't set, you'll get an error at level E_WARNING. Also, set the content-type if you're not outputting HTML.

    if (empty($_POST['username']) || empty($_POST['password']) || empty($_POST['email'])) { // Checks for blanks.
        header('Content-type: text/plain');
        exit("There was a field missing, please correct the form.");
    } 

    $q = mysql_query("SELECT * FROM `users` WHERE username = '$username' OR email = '$email'") 
         or die (mysql_error()); // mySQL Query 

Don't use or die. Also, mysql_error() will disclose too much information. Instead, log the full error message someplace only an admin can read it. Since there's probably nothing the visitor can do, inform them that there was an internal error and that someone will look into it.

The next page is login.php.

Note that register.php and login.php overlap in view (the registration & login forms) and functionality. This means some of my comments on register.php apply to login.php. The similarities should be refactored out into another script. This will ease changing the password hash function, among other reasons.

Speaking of the forms, their use of tables borders on table abuse. CSS is the way to go: "Applying CSS to forms", "Fancy Form Design Using CSS".

(To all those who are experts in PHP, you would normally salt a password to make it harder to crack, but for beginners stick with md5())

It doesn't take an expert to use salt. Adding salt is a matter of concatenating strings before calling the hash function.

Now we will make logout.php.

<?php
session_unset(); // Destroys the session.
header("Location: login.php"); // Goes back to login.
?>

session_start() must be called to open the session before it can be destroyed. session_unset() isn't the recommended way of clearing data stored in sessions when using $_SESSION. Also, the session data might not be cleared (for that, you need to use session_destroy).

<?php
session_start();
$_SESSION = array();
if (ini_get("session.use_cookies")) {
    $params = session_get_cookie_params();
    setcookie(session_name(), '', 1 /* 1s after start of epoch */,
        $params["path"], $params["domain"],
        $params["secure"], $params["httponly"]
    );
}
session_destroy();
...

[serverbjb]

:nuts: hem.....