PHP array vs mysql query

Question

Hello, I am working on an authorization script that checks for user name, password and access level (roles). It works fine as long as there is only one role to check.

I would like to learn how I can put the roles into an array and have the database check if any of them are present in the database for the logged in user. Right now it only check for one role.

Question: How do I construct and array for the allowed roles and then have the query check if any of them are a match?

<?php
// allowed roles  
$allowedRoles = 'role1';  

// needs to be like:  
$allowedRoles = array('role1','role2','role3','etc.');

//------------------------------------------------------------  
// instantiate sessions  
//------------------------------------------------------------  
if (!isset($_SESSION)) {  
  session_start();  
}  

//------------------------------------------------------------  
// define auth variables  
//------------------------------------------------------------  
$first_pass = 0; // sessions  
$second_pass = 0; // password  
$third_pass = 0; // role  

//------------------------------------------------------------  
// check if sessions exist and are valid  
//------------------------------------------------------------  
if(!empty($_SESSION['UserName']) && !empty($_SESSION['Password']) && !empty($_SESSION['LoggedIn']) && $_SESSION['LoggedIn'] == 1)  
{  
    // FIRST PASS OK!  
    $first_pass = 1;  
    echo 'PASSED: 1st ';  
}  

if($first_pass == 1)  
{  
    //------------------------------------------------------------  
    // include db connection  
    //------------------------------------------------------------  
    require_once('../../connections/mysql.php');  

    // set variables
    $session_un = $_SESSION['UserName'];
    $session_pw = $_SESSION['Password'];

    // DB QUERY: check username SESSION credential against db
    // ------------------------------------------------------------------
    $session_auth = mysqli_query($conn, "SELECT UserId, UserName, Password FROM users WHERE UserName = '$session_un' AND IsApproved = 1 AND IsLockedOut = 0 LIMIT 1")
    or die($dataaccess_error);
    // ------------------------------------------------------------------

    if(mysqli_num_rows($session_auth) == 1)
    {
        $row = mysqli_fetch_array($session_auth);
        $auth_UserId = $row['UserId'];
        $auth_Password = sha1(sha1($row['Password']));

        // if passwords match
        if($auth_Password == $session_pw)
        {
            // SECOND PASS OK!
            $second_pass = 1;
            echo 'PASSED: 2nd ';

            if($second_pass == 1)
            {
                // DB QUERY: check ROLE credentials in db
                // ------------------------------------------------------------------
                $auth_roles = mysqli_query($conn, "SELECT UserId, RoleId, RoleName FROM users_in_roles WHERE UserId IN ($auth_UserId) AND RoleName IN ('$allowedRoles')")
                or die($dataaccess_error);
                // ------------------------------------------------------------------

                if(mysqli_num_rows($auth_roles) > 0)
                {
                    // THIRD PASS OK!
                    $third_pass = 1;
                    echo 'PASSED: 3rd ';
                }
                else
                {
                    // redirect back to login page
                    header('Location: ../../login.php');
                }
            }
        }
    }
}

?>

Thank you!

Answer 1

You're almost there. You just need to convert your list of allowed roles from an array into a string:

$allowedRoles = "'" . implode("', '", $allowedRoles) . "'";

$auth_roles = mysqli_query($conn, "SELECT UserId, RoleId, RoleName FROM users_in_roles WHERE UserId IN ($auth_UserId) AND RoleName IN ($allowedRoles)")