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:
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 (http://en.wikipedia.org/wiki/Cross-site_request_forgery)).
Username: admin
Password: lol
<?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 »' /></td></tr></table></form><br /><a href='?page=cp'> « 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>";
}
?>
It's recommended you try the following using Wamp/Mamp/Xampp.
Open phpMyAdmin, click "SQL" and paste the following and hit Go:
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 (http://en.wikipedia.org/wiki/Cross-site_request_forgery)).
Username: admin
Password: lol
<?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 »' /></td></tr></table></form><br /><a href='?page=cp'> « 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>";
}
?>