First Login Script

StevenF · 02-13-2008, 06:43 PM

Hello,

I've created a little project for myself to improve my PHP. I've decided to create a website which uses a login script. I need to allow new users to register and existing users to login.

So far I have the register page working: It's a form with a Username, email and password field and the details entered are entered into the database.

I'm unsure how to do the login page

I need to do something like:

Search database for matching username
Search database for matching password
If both of the above match, then login and go to <insert page>

I don't really know how to do this. I was wondering if someone could point me in the right direction?

Thanks,
Steven

webtuto · 02-13-2008, 07:56 PM

ok here is the code

PHP Code:


			
$sql = "select * from `admin` where name='$_POST[name]' and pass='$_POST[pass]'";

$res=mysql_query($sql);

$count = mysql_num_rows($res);

if($count == 1){

header("Location: admin.php");

}else{

 echo"<center><h2>The username or password are mistaken please check theme again<h2>";

}

SOCK · 02-13-2008, 08:03 PM

Quote:

Originally Posted by webtuto

PHP Code:


			
$sql = "select * from `admin` where name='$_POST[name]' and pass='$_POST[pass]'";

Never a good idea to allow POST data to directly interface with your database. Please be careful about passing bad code to new users!!

Google search : SQL injection

Rendair · 02-13-2008, 08:11 PM

I would also say encrypt any passwords before putting them in the database and like SOCK said do some prevention of mysql injections.

SOCK · 02-13-2008, 08:01 PM

Quote:

Originally Posted by StevenF

I need to do something like:

Search database for matching username
Search database for matching password
If both of the above match, then login and go to <insert page>

Yes, yes & yes.

Retrieve the values via POST from the login form, validate (check that they're strings, length, etc if you want to), escape (using mysql_real_escape_string() for example, if working in MySQL) and then check against the values in the database, e.g.

PHP Code:


			
// $uname is our username value / $upass is the password

// validated & escaped at this point, if not, redirect to login

$sql= 'SELECT userID, COUNT(*) FROM users WHERE ';

$sql.= "username = '{$uname}' AND userpass = '{$upass}'";



if ( mysql_result( mysql_query($sql), 0, 1 ) !== 1 ) {

    // no login

    // redirect to login form, show error message

} else {

    // login process

    // recommend creating a unique token to store for this login

    // set in the `users` table or a second `logins` table

    // set session data (usually userID value, unique token)

    // redirect to index

}

Extremely glib example, take it for what it's worth. I usually have a second `logins` table (as noted in the comments) that stores the `userID` value, the session_id() value, a unique token, etc. to match against each subsequent page request to make sure the user is valid.

Check the PHP manual entries for function definitions.

SOCK · 02-13-2008, 10:19 PM

In revisiting this thread, it occurred to me to pass some additional advice (as per Rendair's comment on encrypting or hashing the password). Most of these things I take for granted and forget to mention. At any rate, here are other notes / tips in no specific order-

Try to keep the login process simple, don't add anything that will hang you up later.
Make sure you store the password as a hashed value, minimum MD5, SHA1 / SHA256 recommended. Do NOT use the database 'Password()' function to hash the password. It's not portable and I don't trust it. MD5 and SHA1 are both innate PHP / MySQL functions. You'll want to find a class or use PHP's mhash functions for anything stronger.
Store ONLY non-critical information in the session, e.g. the userID value or the unique token to match against a `logins` table.
DO use session_regenerate_id() on each successful login check, e.g. each page request that checks against valid session data. Once the user is validated against the session / `logins` table, then regenerate the session_id and restore the data.
Consider creating a 'remember me' cookie so the user can automagically log back in after closing the browser.
Consider using a `registrationID` column in the database to match against to help root out spammers (in other words, once they've registered, have an email sent to them that will have a link matching their `registrationID` column value).
DO NOT allow any database errors to show, e.g. using mysql_error() to send an error message back to the user on a non-login or an error. This is what SQL injection attackers look for. Trap all errors in a log if need be. Shut off the PHP ini display_errors setting.

Then create a cool web 2.0ish design wrapped around all that, maybe implement an XHR (Ajax) request for the login process, etc.

Once you've got it all done, trash it all and redo it as in OOP.

StevenF · 02-13-2008, 10:24 PM

Thanks everyone, I really appreciate all of your help! I'm going to give it a shot just now.

SOCK - Thanks for that. A lot of it doesn't make much sense and I don't know how to do. I have however stored the password in encripted md5, I just found out about that today.

Can I ask what UserID is? Is it an auto number field given to each user?

Thanks
Steven

SOCK · 02-13-2008, 10:50 PM

Quote:

Originally Posted by StevenF

Can I ask what UserID is? Is it an auto number field given to each user?

Yes; in my table designs I will often use an AUTO_INCREMENT INT value as the PRIMARY KEY to link tables on. So each user has a `userID` field that is the most basic, simple method to refer to that user, regardless of how many columns and how much data is stored in them. Link it in the session data and the `logins` table if you are so inclined to add one. No need to store the username or any other data if you simply store the `userID`.

SOCK · 02-13-2008, 11:00 PM

Something else to consider when designing the `user` table, make sure you use a UNIQUE index on the `username` column - keep people from registering the same name, and makes the logic simple when registering users.

StevenF · 02-13-2008, 11:44 PM

My eyes are getting tired, so I'm going to leave it for today. I've suddenly gotten a strange error:

Quote:

MySQL Error:'.mysql_error()); mysql_select_db($database) or die ('MySQL Error:'.mysql_error()); //setting variables $reg_username = addslashes($_POST['reg_username']); $reg_email = addslashes($_POST['reg_email']); $reg_password = md5($_POST['reg_password']); //creating a query that inserts the data into the database $query = 'INSERT INTO users SET user_name = "'.mysql_real_escape_string($reg_username).'", email = "'.mysql_real_escape_string($reg_email).'", user_pass = "'.mysql_real_escape_string($reg_password).'"' ; //execcute a query on a MySQL database mysql_query($query); //close database connection mysql_close(); ?>

I don't know what's causing it, it was working fine. Here's the code bellow: (the database is called scotlandbands).

Code:

	//Database Structute
		//Setting username and password
		$username = '';
		$password = '';
		$database = "scotlandbands";
		
	//MySQL Connections
	mysql_connect($localhost, $username, $password) or die ('<strong>MySQL Error:</strong>'.mysql_error());
	mysql_select_db($database) or die ('<strong>MySQL Error:</strong>'.mysql_error());
	
	//setting variables 
	$reg_username = addslashes($_POST['reg_username']);
	$reg_email = addslashes($_POST['reg_email']);
	$reg_password = md5($_POST['reg_password']);
	
	//creating a query that inserts the data into the database
	$query = 'INSERT INTO users SET

          user_name = "'.mysql_real_escape_string($reg_username).'",
          email = "'.mysql_real_escape_string($reg_email).'",
          user_pass = "'.mysql_real_escape_string($reg_password).'"';
	
	//execcute a query on a MySQL database
	mysql_query($query);
	
	//close database connection
	mysql_close();

SOCK · 02-14-2008, 12:28 AM

Ok, the error message is really odd. It almost looks like the httpd server isn't parsing PHP correctly and instead outputting all your PHP code. It seems as if it begins with the call to mysql_connect() but I don't see why.

Have you been successfully connecting to MySQL from this or another PHP script? Does this script have the .php extension (or the appropriate extension to parse PHP)?

Please use the provided PHP code tags when posting, it makes it much easier to spot errors, e.g.

PHP Code:


			
$variable = 'something';

Something else in your code important to mention; you're using addslashes() and mysql_real_escape_string(). Bad combination. You're going to get slashes upon slashes in your stored data. Not to mention if you have magic_quotes turned "On" as well, you'll have three sets of slashes. It can get intense.

Make sure magic_quotes is turned "Off", and use mysql_real_escape_string() only.

StevenF · 02-14-2008, 12:42 AM

I don't know what was wrong there, but I seemed to have fixed it!

I didn't know that addslashes and mysql_real_escape_string() weren't to be used together. Thanks for letting me know!

I am getting somewhere now, I have the following:

Code:

<?php

	//Database Structure
		//Setting username and password
		$username="";
		$password="";
		$database="scotlandbands";

		mysql_connect($localhost, $username, $password) or die ('<strong>MySQL Error:</strong>'.mysql_error());
		mysql_select_db($database) or die ('<strong>MySQL Error:</strong>'.mysql_error());
		
	
	//setting variables 
	$reg_username = ($_POST['reg_username']);
	$reg_email = ($_POST['reg_email']);
	$reg_password = md5($_POST['reg_password']);
	
	//creating a query that inserts the data into the database
	$query = 'INSERT INTO users SET		user_name = "'.mysql_real_escape_string($reg_username).'",
										email = "'.mysql_real_escape_string($reg_email).'",
										user_pass = "'.mysql_real_escape_string($reg_password).'"';
	
	//execcute a query on a MySQL database
	mysql_query($query);
	
	//close database connection
	mysql_close();

?>

Hope that's more readable now!

Now, when I try to enter a username and password which is stored in the database, I'm being taken to login.html - which means the login was not successful. I don't know why this is happening, because I'm entering the correct details.

SOCK · 02-14-2008, 01:19 AM

Quote:

Originally Posted by StevenF

Now, when I try to enter a username and password which is stored in the database, I'm being taken to login.html - which means the login was not successful. I don't know why this is happening, because I'm entering the correct details.

Obviously what you've posted isn't your login script; can you show us that?

StevenF · 02-14-2008, 09:47 AM

Quote:

Originally Posted by SOCK

Obviously what you've posted isn't your login script; can you show us that?

See, I told you I was tired.

I posted the register script by mistake.

PHP Code:


			
<?php

    //Database Structure
        //Setting username and password
        $username="";
        $password="";
        $database="scotlandbands";

        mysql_connect($localhost, $username, $password) or die ('<strong>MySQL Error:</strong>'.mysql_error());
        mysql_select_db($database) or die ('<strong>MySQL Error:</strong>'.mysql_error());
        
        //Setting variables from form data
        $user = $_POST['check_username'];
        $pass = $_POST['check_password'];
        $login = $_POST['login'];
        
        $get = mysql_query("SELECT count(userID) FROM users WHERE user_name = '$user' AND user_pass = '$pass'");
        $result = mysql_result($get,0);
        
        mysql_close();
        
        //Determine if there is a result
        if ($result != 1) header ("Location: login.html");
        else {
            header ("Location: index.html");
            $_SESSION['user_name'] = $user;
            };
            
            
?>

I'm getting somewhere now: If I take out the md5 encryption, and register a username and password, I can use that to log in. But, when I try it with md5 encryption, I can't login! Do I have to decrypt it or something?

This is how I'm using md5:

PHP Code:


			
$reg_password = md5($_POST['reg_password']);

SOCK · 02-14-2008, 03:22 PM

Quote:

Originally Posted by StevenF

PHP Code:


			
<?php
//Setting variables from form data
$user = $_POST['check_username'];
$pass = $_POST['check_password'];
$login = $_POST['login'];
        
$get = mysql_query("SELECT count(userID) FROM users WHERE user_name = '$user' AND user_pass = '$pass'");

I'm getting somewhere now: If I take out the md5 encryption, and register a username and password, I can use that to log in. But, when I try it with md5 encryption, I can't login! Do I have to decrypt it or something?

No, but you do have to compare an MD5 hashed value with another MD5 hashed value. You need to either hash the $pass value again prior to the query, or hash it within the query, e.g.

PHP Code:


			
// use $md5pass in your query instead of $pass
$md5pass= md5($_POST['check_password']);

-- or --

Code:

SELECT COUNT(userID)
FROM users
WHERE user_name = 'username'
AND user_pass = MD5('userpass');

Because MD5() is also a MySQL function, you can do it straight in the query. Just make sure not to hash the password in PHP and then attempt to hash it again!

Quote:

Originally Posted by StevenF

PHP Code:


			
//Determine if there is a result
if ($result != 1) header ("Location: login.html");
else {
    header ("Location: index.html");
    $_SESSION['user_name'] = $user;
};

A couple of comments here on the above code.

That last line uses a semicolon to end the if-else block. Not sure if that's even legal. At any rate, unnecessary.
Don't assign session data after the call to header().
Try not to mix statement styles, i.e. use a single indented line after the if conditional, then use braces after the else statement. Assume you'll want to have more than one statement after the if and use braces. Be uniform in your code structure. It makes it much easier to read and troubleshoot. Once in awhile I'll use syntax like that, but it's a one-liner only.

Alan @ CIT · 02-14-2008, 01:26 AM

Tip: you can use the [ php ][ /php ] tags to save having to manually color your code when posting

The query in the script you posted is in the wrong format. You have mixed up INSERT and UPDATE syntax.

An INSERT query should look something like:

PHP Code:


			
$query = "INSERT INTO table (column1, column2, etc) VALUES ('value1', 'value2', 'etc')"

An UPDATE query should look something like:

PHP Code:


			
$query = "UPDATE table SET column1 = 'value1, column2 = 'value2, etc = 'etc' WHERE something = 'else'"

Alan

SOCK · 02-14-2008, 01:33 AM

Alan@CIT> Not to nitpick, but that INSERT format is perfectly fine. You just don't see it as often as the (column list) VALUES (data list) format.

MySQL Manual : INSERT syntax

ReSpawN · 02-14-2008, 08:01 PM

Quote:

Originally Posted by SOCK

Alan@CIT> Not to nitpick, but that INSERT format is perfectly fine. You just don't see it as often as the (column list) VALUES (data list) format.

MySQL Manual : INSERT syntax

Not to go off topic and or anything but the SET command works better for some people. Thanks for the INSERT documentation SOCK.

StevenF · 02-14-2008, 09:57 PM

I think I'm nearly done now, thanks everyone for your input so far:

PHP Code:


			
<?php

    //Database Structure
        //Setting username and password
        $username="";
        $password="";
        $database="scotlandbands";
        
        //Start connection to database
        $connection = mysql_connect($localhost, $username, $password) or die ('<strong>MySQL Error:</strong>'.mysql_error());
        mysql_select_db($database) or die ('<strong>MySQL Error:</strong>'.mysql_error());
        
    
    //setting variables 
    $reg_username = mysql_real_escape_string($_POST['reg_username']);
    $md5reg_password = mysql_real_escape_string($_POST['reg_password']);
    $reg_pass_conf = mysql_real_escape_string($_POST['reg_pass_conf']);
    $reg_email = mysql_real_escape_string($_POST['reg_email']);

    
    //Error array
    $errors = array();
        //Check if the following exist
        
        //If no username display error
        if(!$reg_username) {
            $errors[] = "Username is not defined!";
        }
        
        //If no password display error
        if(!$reg_password) {
            $errors[] = "Password is not defined!";
        }
        
        //if no password and no password comfirmation display error
        if($reg_password) {
            if(!$reg_pass_conf) {
                $errors[] = "Confirmation password is not defined!";
            }
        }
        
        //if no email display error
        if(!$reg_email) {
            $errors[] = "Email is not defined!";
        }
        
        //If passwords do not mach display error
        if ($reg_password && $reg_pass_conf) {
            if ($reg_password != $reg_pass_conf) {
                $errors[] = "Passwords do not match!";
            }
        }
        
        //Split errors up and show them
        if (count($errors) > 0) {
            foreach($errors AS $error) {
                echo $error . "<br>\n";
            }
        } else {
            
    //creating a query that inserts the data into the database
    $query = 'INSERT INTO users SET        user_name = "'.($reg_username).'",
                                        email = "'.($reg_email).'",
                                        user_pass = "'.md5($reg_password).'",
                                        user_pass_conf = "'.($reg_pass_conf).'"';
    
    //execcute a query on a MySQL database
    $result = mysql_query($query);
    
    //Message
    echo "Thank you for registering, you can now log in";
    }
    
?>

I'm still having problems with MD5 encryption. Could someone please look over that and check that I'm using it properly?

Also, would I have to create a piece of code on every page the user visits, checking if they are logged in or not? Otherwise they could visit the page if they knew the URL, without logging in.

SOCK · 02-15-2008, 01:11 AM

It looks like you edited your post as I was responding here. I'll have to take a look at your use of MD5 again. Remember, the idea is to store an MD5 hashed value of the password, and then check that against an MD5 hashed value of the password input during login. So if your `user_pass` field is storing something like: 74add9df670c36b147c19dd93a27d8d0, your login script has to pass the same hash value to the query to match it.

Have you checked your `users` table manually to actually see what's being stored there? Gotta do it when troubleshooting a script that interacts with the database!

Quote:

Originally Posted by StevenF

I had a problem when trying to load a new page if the password is correct, still now sure how to do that correctly. I was using the header function, but it kept loading the page specified in the header function instead of the index page.

pseudocode example:

PHP Code:


			
if ( result == 1 ) {
    // one match found, redirect to the index
    // implement session data / `logins` table data storage
    // redirect
    header('Location: http://www.yoursite.com/index.php');
    exit();
} else {
    // either no match (or more than one, let's hope not)
    // wipe out any potential session data
    $_SESSION= array();
    // redirect to error page or login page again
    header('Location: http://www.yoursite.com/login.php');
    exit();
}

Quote:

Originally Posted by StevenF

Would I have to create a piece of code on every page the user visits, checking if they are logged in or not? Otherwise they could visit the page if they knew the URL, without logging in.

Yes. Something like (more pseudocode example)

PHP Code:


			
<?php
// top of script

// start session, naturally
session_start();

// db defaults, read connection settings, connect, etc

// check session data
if ( !isset($_SESSION['userID'])
    && !isset($_SESSION['login_token']) ) {

    // no proper session data set
    // automatically unset session data & redirect
    $_SESSION= array();
    header('Location: http://www.yoursite.com/login.php');
    exit();    
    
} else {
    // potentially viable session login data
    // run SQL query against the `logins` table, matching:
    // `userID` | `login_token` | `session_id`
    if ( !login_verify ) {
        // no login data match
        // unset session data, redirect
        $_SESSION= array();
        header('Location: http://www.yoursite.com/login.php');
        exit();    
    }
    // anything else is assumed to be properly logged in
    // begin "relogin process"
    session_regenerate_id();
    $sessID= session_id();
    // update `logins` table data
    $updateLogin= "
        REPLACE INTO logins
        (userID,login_token,session_id,login_dt)
        VALUES
        (
          {$_SESSION['userID']},'{$_SESSION['login_token']}'
          ,'{$sessID}', NOW()
        )
    ";
    @mysql_query($updateLogin);
}

// continue with the script

That's the basic gist. Check for session login data values, redirect if they don't exist. Next, check for the valid login data against the `logins` table. Note you might set the login_dt (date-time) value in the session as well and check for session / login expiry times, etc. Store all that in a function and make it easy on yourself on every script that needs a login check.