PHP login adding role

Question

<?php
session_start();

include 'dbConnection.php';

$uid = $_POST['uid'];
$pwd = $_POST['pwd'];

$sql = "SELECT * FROM user WHERE uid='$uid' and pwd='$pwd'";
$result = $conn->query($sql);


if(!$row = $result->fetch_assoc())
 {
   echo '<script language="javascript">';
   echo 'alert("Username or Password Incorret!")';
   echo '</script>';
   header("Location:index.php");

}
else{

$_SESSION['id']= $row['id'];    
echo '<script language="javascript">';
echo 'alert("Logged!")';
echo '</script>';

}
?>

Hello community, The code above is a simple login that I'm trying to develop. I'm kinda new to PHP still learning, the script works fine (but is missing encryption) the next step for the login is to add a role but I don't know where to add or adapt my code! If someone has suggestions they are all welcome!

Answer 1

Based of what you have said in the comments:

Firstly add a column for "role" in your database table.

Then your PHP script could just be a matter of adding the following into the successful login block.

if($row['role'] == "Admin"){
    header('Location: admin.php');
}elseif($row['role'] == "Student"){
    header('Location: student.php');
}

As others have said, you should really use a proper authentication system which takes care of escaping sql queries, security, password hashing etc.

Answer 2

I see some serious problems with your script:

1. Don't use mysqli - it's deprecated and shouldn't be used. Use PDO instead.
2. You should hash your passwords. Read about password_hash().

3. Don't create SQL statements by conca3. You concatenating string from user input. It is vulnerable to SQL injection. You should use placeholders.

   $stmt = $db->prepare("SELECT * FROM user WHERE uid=:uid AND password=:password");
   $stmt->execute(array(':password' => $password, ':uid' => $uid));
   $rows = $stmt->fetchAll();
   

If you want a more detailed review of your code you can post on codereview.

As for implementation of roles, you could create an additional table ROLE with single column NAME. For example (in MySql):

CREATE TABLE role (
  name varchar(255) NOT NULL
) 

ALTER TABLE role ADD PRIMARY KEY ('name');

INSERT INTO role (name) VALUES
('ADMIN'),
('STUDENT');

Then you could add an additional column to your user table with foreign key to table roles.

ALTER TABLE user ADD (
     role varchar(255) NOT NULL,
     FOREIGN KEY(role) REFERENCES role(name)
)

Ten you could just fetch the name of the role and redirect to the right location.

if($row['ROLE'] == "ADMIN"){
    header("Location: http://example.com/admin.php");
    die();
} else {
     ....
}

What are the advantages of this approach? You have all your roles listed in one table and because of a foreign key, you will preserve data integrity. In column ROLE of the USER table, you will be able to store the only role which is also stored in the ROLE table. If you would try to remove role which is assigned still assigned to any user database wouldn't allow it.

This answer assumes your user could have only one role. If you want to be able to assign multiple roles to your user, you would have to learn how to map many-to-many relationship in the database.

Answer 3

To get this properly, you'll need a separate table in the database for roles, and an extra column in the user-table with user_role, where you store the ID from the role-table that the user should have assigned to him. When pulling data, you join the two tables on the ID, and get other information about the role, like name.

So, the role-table would contain something like: role_id, role_name you can of course have other information here as well, like user_level (int), but that is for a more advanced project.

In the user-table, you add a user_role column, where you store the id-number from the role-table.

To get the actual name of the role, you'll have to join the two tables, something like this would do: $sql = "SELECT * FROM user t1 LEFT JOIN role t2 ON t1.user_role = t2.role_id WHERE uid='$uid' and pwd='$pwd'";

That way you will have another result in the returned info from the database, containing the role.