Limiting Access by Permission
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 A开发者_C百科ssociate 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.
Try replacing "LEFT JOIN" by "INNER JOIN"
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.
精彩评论