9

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.

Improve this question
1
  • You should re-use an existing authentication framework whenever possible, because, really, it's complex. For example, take a look at github.com/delight-im/PHP-Auth which is both framework-agnostic and database-agnostic. Commented Sep 22, 2016 at 5:08

7 Answers 7

Reset to default
12

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))";
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+1 for a very important point. Two corrections though: 1. The code should use mysql_escape_string/mysql_real_escape_string() to avoid being bypassed by SQL injections. 2. Using a salt for the hashing is preferred to avoid being cracked by rainbow tables. See: tinyurl.com/jwb8o
This answer is mainly unsafe because it doesn't use quotes around the password, even though the main question shows the usage of the mysql escape function, this still allows for sql injections in the form of $pwd = "1) OR ( 1 = 1 "
7

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.

Improve this answer

Comments

3

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.

Improve this answer

Comments

2

I like to use both $_SESSION and MYSQL Checks with any login POST. This should help get a few things started.

$username = mysql_real_escape_string($_POST[username]);
$password = strip_tags($_POST[password]);
$password = sha1($password);

if(isset($username) && isset($password) && !empty($username) && !empty($password))
{
$sql = mysql_query("SELECT * FROM users_column WHERE username = '$username' AND password = '$password'");

//Check the number of users against database
//with the given criteria.  We're looking for 1 so 
//adding > 0 (greater than zero does the trick). 
$num_rows = mysql_num_rows($sql);
if($num_rows > 0){

//Lets grab and create a variable from the DB to register
//the user's session with.
$gid = mysql_query("SELECT * FROM users_column WHERE username = '$username' AND password = '$password'");
$row = mysql_fetch_assoc($gid);
$uid = $row[userid];

// This is where we register the session.
$_SESSION[valid_user] = $uid;

//Send the user to the member page.  The userid is what the 
//session include runs against.
header('Location: memberpage.php?userid='.$userid);
}

//If it doesn't check out -- throw an error.
else
{
echo 'Invalid Login Information';
}
}

NOTE: You would need to start the page file with session_start() and create a separate Session Check include stating with session_start() and then your progressions e.g. if($_SESSION[valid_user] != $userid) do something.

Improve this answer

Comments

0

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).

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

I agree with the idea if using SESSION variables while authenticating the user.

The easy way to authenticate the user is as follows

//connect the mysql_db
$mysql_connect()
$mysql_select_db() 

//reading from mysql table
$res="SELECT * FROM table WHERE name=$username AND password=$password";
$val=mysql_query($res);

//authentication
$count=mysql_num_rows($val);
if($count==1)
//authenticate the user
else
through an error
Improve this answer

1 Comment

This answer is mainly unsafe because it doesn't use quotes around the password, even though the main question shows the usage of the mysql escape function, this still allows for sql injections in the form of $password = "1OR1=1"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.