Basic PHP+MySQL Login System (example code)

Apolva · 31-05-2010, 02:13 PM

This was originally meant to be an extensive tutorial, until firefox crashed and lost it all, so here's just a simple example which can be played around with to help get to grips with PHP + MySQL:

It's recommended you try the following using Wamp/Mamp/Xampp.

Open phpMyAdmin, click "SQL" and paste the following and hit Go:

Code:

CREATE DATABASE `myLoginSystem`;

CREATE TABLE `myLoginSystem`.`users` (
  `userID` int(11) NOT NULL auto_increment,
  `username` varchar(50) NOT NULL,
  `password` varchar(32) NOT NULL,
  `emailAddress` varchar(200) NOT NULL,
  `rankID` int(6) NOT NULL,
  PRIMARY KEY  (`userID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `myLoginSystem`.`users` (`userID`, `username`, `password`, `emailAddress`, `rankID`) VALUES ('', 'admin', '5d5adc91dfbf5abb75b7faa42914d672', '[email protected]', '5');

Below is the complete example, which you are free to use/modify as you wish.

I have tried to include one type of each query to give he gist of how to use. It also uses sessions.

Note: This example is protected against SQL injection, but not CSRF (see here).

Username: admin
Password: lol

Code:

<?php
$dbhost="localhost"; // The location of the MySQL server, usually localhost (otherwise the IP/domain of it).
$dbuser="root"; // The username to use when connecting (using *AMP the default is root).
$dbpass=""; // The password for connecting (By default there is no password on *AMP).
$dbname="myLoginSystem"; // The name of the database.

// Bear in mind, the @ just suppresses any errors so we can show our own, more user friendly ones.
@mysql_connect($dbhost,$dbuser,$dbpass) or die("Error - Can't connect to database (host, user or password is wrong).");
@mysql_select_db($dbname) or die("Error - Connected, but database doesn't exist (go and create one).");

// Generates a hash of a string (used for securely storing passwords)
function passwordHash($password=""){return md5(sha1($password)."ef44");}

session_start(); // Initiate the session (to store info between page loads)

// This function is to sanitize strings used in queries, to prevent SQL injection.
function sanitize($string=""){
     if(get_magic_quotes_gpc()) $string=stripslashes($string); // If magic quotes is enabled, counteract it.
     return mysql_real_escape_string($string); // Escape apostrophes with backslashes (\'), so they don't mess up the queries.
}

if($_GET['page']=="login"){

     // Already logged in?
     if($_SESSION['userData']['userID']!=""){header("Location: ?page=cp");die();}
     
     if($_POST['do']=="submit"){
          // Trying to log in.
          $username=sanitize($_POST['username']);
          $password=passwordHash($_POST['password']);

          // Look for records in the "users" table which have the inputted username and password.
          $loginQuery=mysql_query("SELECT * FROM `users` WHERE `username`='{$username}' AND `password`='{$password}';");

          // If no results, the username/password are wrong, so show an error.
          if(@mysql_num_rows($loginQuery)==0)
          echo "<div style='background:red;color:white;font-weight:bold;text-align:center;'>Login Failed!</div>";
          else{
               // Otherwise put all the returned user info in a session, ready for another page load.
               $_SESSION['userData']=mysql_fetch_array($loginQuery);
               if($_SESSION['userData']['rankID']==0){ // If a user's rank is 0, they are banned.
                    session_destroy();
                    echo "<div style='background:red;color:white;font-weight:bold;text-align:center;'>You're banned!</div>";
               }else{
                    // Go to the control panel page
                    header("Location: ?page=cp");die();
               }
          }
     }
     // Output log in HTML form
     echo "<form method='post' action='?page=login'><div style='text-align:center;font-size:18px;'>Please log in</div>
     <input type='hidden' name='do' value='submit' />
     <table style='margin:0 auto;'>
     <tr><td>Username:</td><td><input type='text' name='username' /></td></tr>
     <tr><td>Password:</td><td><input type='password' name='password' /></td></tr>
     <tr><td></td><td><input type='submit' value='Log in' /></td></tr>
     </table></form>";
     die();
} elseif($_GET['page']=="cp"){ // This page is for users only, check we're logged in...
     if($_SESSION['userData']['userID']==""){
          header("Location: ?page=login");die(); // If not, send them to the login page.
     }
     if($_GET['do']=="changepw"){
          if($_POST['newpw']!=""){ // New password has been submitted
               if(passwordHash($_POST['oldpw'])!=$_SESSION['userData']['password']){
                    echo "<div style='background:red;color:white;font-weight:bold;text-align:center;'>Old password incorrect</div>";
               }else{ // Old password is correct :)
                    if(mysql_query("UPDATE `users` SET `password`='".passwordHash($_POST['newpw'])."' WHERE `userID`='".$_SESSION['userData']['userID']."' LIMIT 1;"))
                    {
                         echo "Password changed successfully! <a href='?page=cp'>Back to CP Home</a>";
                         $_SESSION['userData']['password']=passwordHash($_POST['newpw']);
                    } else echo "<h1>Database error!</h1>";
               }
          }
          die("<form method='post'><h1>Change password</h1><table><tr><td>Old password:</td><td><input type='password' name='oldpw' /></td></tr><tr><td>New password:</td><td><input type='password' name='newpw' /></td></tr><tr><td></td><td><input type='submit' value='Change Password &raquo;' /></td></tr></table></form><br /><a href='?page=cp'> &laquo; Cancel</a>");
     }
     // List some of their user info.
     
     echo "<div style='background:#EEE;'><a href='?page=cp&do=changepw'>Change password</a> | <a href='?page=users'>users</a> | <a href='?page=logout'>log out</a></div>";
     echo "<h1>Welcome, ".$_SESSION['userData']['username']."</h1>";
     echo "<b>Your email address is:</b> ".$_SESSION['userData']['emailAddress']."<br />";
     echo "<b>You are rank:</b> ".$_SESSION['userData']['rankID']."<br />";
} elseif($_GET['page']=="users"){
     // Get users with a query
     $usersQuery=mysql_query("SELECT * FROM `users`;");
     echo "<table style='width:100%;'><tr style='background:#BBB;'><td><b>Username</b></td><td><b>Email address</b></td><td><b>Rank</b></td></tr>\r\n";
     while($user=@mysql_fetch_array($usersQuery)){
          echo "<tr style='background:#EEE;'><td>".$user['username']."</td><td>".$user['emailAddress']."</td><td>".$user['rankID']."</td></tr>\r\n";
     }
     die("</table><br /><a href='?page=cp'>Go back to CP Home</a>");
} elseif($_GET['page']=="logout"){
     // Clear the session, then send them to the login page.
     session_destroy(); header("Location: ?page=login");
} else {
     // Default page, you might want to go to the home page or something.
     echo "<h1>Invalid page</h1><br /><a href='?page=login'>Click here to log in.</a>";
}
?>

LMS16 · 31-05-2010, 02:22 PM

Very good.

Although CSRF cant be overcome by simply making a new field in the users database named "token" and everytime the user logs in, generate a random key then fetch the key from the db and including it in all links to ensure attacks can not be performed.

Havent tried it yet but Im gonna give it a go

Good tut, should help people

Lew.