Limiting Access by Permission

Question

Hi, thanks for viewing this. I have a db that has users, roles & user_roles. What I am trying to achieve is a login that will select users who have Admin or Associate permissions. The login then uses name and password to permit access.

My SQL syntax thus far is -

SELECT * FROM users 
LEFT JOIN ON user_roles 
ON user.id=user_roles.userid AND roleid IN (Administrator, Associate) 
WHERE username = '$username' AND password = '$password'";

I am not sure where I am going wrong.

Thanks in advance for your help.

Answer 1

Try replacing "LEFT JOIN" by "INNER JOIN"

Answer 2

Here's how I'd write the query:

$stmt = $pdo->prepare("
SELECT (u.password = :password) AS password_is_correct,
  (r.roleid IS NOT NULL) AS role_is_authorized
FROM users u
LEFT JOIN ON user_roles r
  ON u.id=r.userid AND r.roleid IN (Administrator, Associate) 
WHERE u.username = :username");

$stmt->execute(array(":password"=>$password, ":username"=>$username));

This allows you to distinguish between the three conditions: (1) username does not exist, (2) password is wrong, or (3) role is not authorized.

PS: Should "Administrator" and "Associate" be quoted or something? The way you're using them, they look like identifiers rather than values.