How do I authenticate a user in PHP / MySQL?

Question

So recently I learned how to properly add a username and password to a database. My database is usersys, and the table storing user information is called userdb. The table has two columns - username (primary), password.

The registration form works great, enters the users input into the database correctly and also checks to see whether the user's username is already in the database or not.

With that said, I am asking if anyone could help me create a login script. So far, this is what I have:

$username = $_POST['username'];
$password = $_POST['password'];
$displayname = $_POST['username'];
$displayname = strtolower($displayname);
$displayname = ucfirst($displayname);           
echo "Your username: " . $displayname . "<br />";

mysql_connect("localhost", "root", "******") or die(mysql_error());
echo "Connected to MySQL<br />";

mysql_select_db("usersys") or die(mysql_error());
echo "Connected to Database <br />";

$lcusername = strtolower($username);
$esclcusername = mysql_real_escape_string($lcusername);
$escpassword = mysql_real_escape_string($password);

$result = mysql_query("SELECT * FROM userdb WHERE username='$esclcusername' AND     password='$escpassword'") or die(mysql_error());
$row = mysql_fetch_array( $result );
$validateUser = $row['username'];
$validatePass = $row['password'];

The POST data is from the previous log in page. I want this script to check the table (userdb) and find the row for the username that the user entered from the previous form and verify that the password entered matches the username's password set in that row, in userdb table.

I also want some type of way to check whether or not if the username entered exists, to tell the user that the username entered does not exists if it can not be found in the table.

Answer 1

You can use sessions. Sessions are global variables that when set, stay with the user while he is browsing through the site.

Since you are learning PHP, try out this tutorial on the official website.

But what you would do in theory is when the username and password match, you set a session variable

$_SESSION["auth"] = true;
$_SESSION["user_id"] = $row["user_id"];

And then do a check to see if the user is authenticated.

Answer 2

One way to do it (DISCLAIMER: not necessarily best-practice):

$result = mysql_query("SELECT id FROM userdb WHERE username='$esclcusername' AND  password='$escpassword'") or die(mysql_error());
$row = mysql_fetch_array( $result );
$id = (int)$row['id'];
if($id > 0) {
    //log in the user
    session_start();
    $_SESSION['userId'] = $id;
    $_SESSION['username'] = $displayname;
}

... and on pages that require authentication:

session_start();
if(!isset($_SESSION['userId'])) {
    die('You need to be logged in!!!');
} else {
    echo 'Welcome ' . $_SESSION['username'];
}

Read more about PHP sessions.

Answer 3

Hello, You could use a select statement to retreive from MySQL the password for the specified username. If you have an empty result set, then you do not have the username in the table.

If you need the user to be authenticated in more than one php page, then one choice whould be using sessions (http://www.php.net/manual/en/function.session-start.php).

Also, I think you should think about security, i.e. preventing SQL injection:

$variable = mysql_real_escape_string($_POST['variable'])

and avoiding to "die" (treating errors and returning user-friendly messages from the script).

Answer 4

I would also think about not storing passwords in your database. One way hashes with MD5 or SHA1 are a way of adding a layer of security at the db level.

See http://php.net/md5 or http://php.net/sha1 for further information.

Answer 5

This is not a direct answer to this question but a GOOD value-add. You should use MYSQL SHA1 function to encrypt the password before storing into the database.

$user = $_POST['userid'];
$pwd = $_POST['password'];
$insert_sql = "INSERT into USER(userid, password) VALUES($user, SHA1($pwd))";

$select_sql = "SELECT * FROM USER WHERE userid=$user AND password=SHA1($pwd))";