Searching Users for specific Roles

[Peabo83]

Thank you for this git, it's pretty great.

For my current project I've got an admin display of all users and I'm using some PHP to build a SQL query that searches the USERS table for columns with the search value (I've added columns 'first' and 'last' to the USERS table).

if (isset($_GET['search']) && $_GET['search'] != '') { $clean_search = htmlspecialchars($_GET['search']); $query = explode(' ', $clean_search); $sql = 'SELECT * from users WHERE '; foreach ($query as $key => $value) { $sql .= "first like '%" . $value . "%' or last like '%" . $value . "%' or username like '%" . $value . "%' or email like '%" . $value . "%'"; } } else { $sql = 'SELECT * from users'; }

I'd like to also include a way to search for users with specific roles at the SQL level, however I'm having trouble wrapping my head around how I can do that with the additive/multiplicative values for roles in the roles_mask. Is searching at the SQL level possible, or do I need to refine results at the PHP level? Or is there a function I've not seen that does what I'm trying to accomplish?

Thank you for taking a moment to read this!

[eypsilon]

The first and practically most important thing: SQL is not HTML. Escape the variable strings that you use to build your SQL statements ($_GET['search']) with an appropriate function/method like mysqli::real_escape_string.

See wikipedia/SQL_injection#Incorrectly_constructed_SQL_statements for more details.

To get User with specific Roles will be a more difficult task. If the total number of Users is not that high, you could loop over the Users Table and check each User for the currently requested Role using PHP-Auth#user-content-checking-roles-1.

// just for illustration
$_GET['has_role'] = 8;
$usersWithRole = [];
foreach($users as $user) 
    if ($auth->admin()->doesUserHaveRole($user, $_GET['has_role']))
          $usersWithRole[] = $user;

// print all
print_r($usersWithRole);

If you're planning to do more stuff with SQL Statements, i would consider to try PDO. It's already used by PHP-Auth, so you can use the established connection and do lot of additional stuff pretty fast and pretty secure.

[Peabo83]

Thank you for the quick reply!

I agree that sterilizing input is very important, in the code provided the _GET variable is cleaned using HTMLSPECIALCHARS. I'm not terribly used to using PDO but I'm going to switch my code over to real-escape-string.

I was hoping for a way to, for example, see a user with role_mask of "7" and know that the user is "ADMIN AUTHOR COLLABORATOR" in some way in my SQL statement, so that I'm not pulling extra sql results and then filtering them. If you know of any PDO resources you could link me that might guide me to a more efficient solution I'd be much obliged.

Ultimately I think I'll have to add a new ROLES table and do a USER_ROLES table for easy and quick SQL searches. This particular project will have several thousand users so I should probably go the extra mile for smooth operation. I was just kind of hoping there might be an elegant solution to searching all users role_mask values and pull just ADMINs.

[eypsilon]

There's a Gist, that exactly does, what you need. I've totally forgotten this one :)

If you call the Method with a specific UserId, you get all the Roles, the User has.

Custom Roles helper for delight-im/PHP-Auth

[eypsilon]

But overall, there's currently no way to do it just with SQL or just with PDO, you've to write some Custom Logic to achieve your goal.

[Peabo83]

To be honest, when I first started messing with the roles it kind of blew my mind. I've never seen roles structured the way they are in this repo, and I find it fascinating. I passed this repo around my office and it sparked some real interesting conversations regarding roles and saving SQL data.

Out of curiosity (and if you don't mind me bending your ear further), what was the reasoning for using this kind of structure vs something more traditional like a USERS_ROLES sql table? I'm guessing there are other befits to the structure I'm not yet catching.

Thanks again for responding to a random internet stranger, I appreciate you and this git!

[ocram]

The SQL injection issue is indeed serious. Just don’t use htmlspecialchars somewhere (which escapes for HTML, not SQL, anyway), but instead use the appropriate function for SQL escaping right in those places where you put the values into the query. PDO with prepared statements is even better, but that’s a (less important) second step. Thanks for pointing this out, @eypsilon!

The reason why roles have been implemented the way they are is that, at the time, roles were just a minor feature added on top of all the main features (authentication), and the current implementation uses just a single integer field instead of a whole separate table, and it can be used efficiently from within PHP. That’s it. Downsides would have to be discussed, obviously, but otherwise, this implementation seems to be doing what it is supposed to do.

As for the actual issue (your search feature), this would not be incredibly fast (with potentially millions of users in the table), because it probably won’t be able to use indexes well. But for thousands of users, this should be fine.

The main objective should be, and I think that’s what you’re already trying to do, avoiding the transfer of all the data to PHP (and instead do the filtering right in the database), not making the SQL query for the search feature extremely fast.

You can just copy the bitwise operation from Auth#hasRole to your database query:

SELECT *
FROM users
WHERE (roles_mask & 16) = 16

Here, 16 is the integer value of the role that you want to search for (twice). So all you still need to do is translate the roles (names) that you want to search for to the correct integers. You could use Role::getMap and build a <select> field. But from what your search looks like so far, you probably want text-based free input, and thus would have to iterate over the map values and find your ID for the keywords that are in your search query.

Hope this helps.

[eypsilon]

I've never seen roles structured the way they are in this repo

Same here :)

what was the reasoning for using this kind of structure vs something more traditional like a USERS_ROLES

See ocrams answer, i'm just an avid User.

Thanks again for responding to a random internet stranger, I appreciate you and this git!

You're welcome :)