How to check mysql db is user is part of a group

Question

This might be a simple question but I have a login page where I want users to authenticate against LDAP and that's working fine.

I created a mysql database where I created a table with the user's logon name and would like a check after the successful logon to verify in my DB that the user trying to authenticate is part of the Administrator group (defined in my database table).

If this is valid then redirect them to url1 and if not redirect them to URL2. At this point I just need help with the SQL query portion as im not too familiar with it.

Localhost
DB name=imc.directory.tool
Table name=tbl_staff
------------------------------------------------
| ID    | username           | group            |
------------------------------------------------
| 1     | username1          | Administrator    |
------------------------------------------------
| 2     | username2          | Guest            |
------------------------------------------------
| 3     | username3          | Guest            |
------------------------------------------------

`SELECT ID FROM tbl_staff WHERE username='username1' AND group='Administrator'` then use `num_rows()` or `count()` to iterate over the rows in the query to see if they exist/match. Plus, I noticed you're using the word "group" as a column name. Be sure to use ticks `\`` around the word "group", since it's a MySQL reserved word. Let me know if that's what your question is about. If that isn't it, I'll just delete this comment. — Funk Forty Niner, Feb 27 '15 at 16:10
By the way, any relation to your other question? http://stackoverflow.com/q/28734042/ — Funk Forty Niner, Feb 27 '15 at 16:18
Thanks Fred ill give this a try once im back on my pc. This is not related to my other question, gave up on that idea trying something completely different. — Daniel Ellison, Feb 27 '15 at 16:23
You're welcome Daniel. If what I said is along the lines of what you're looking for, I'll be able to write up an answer with a few examples. I'll just need to know which API you're using to connect to your DB with. However, if you're unsure which API you'll be using, SQL is SQL ;-) *Cheers* — Funk Forty Niner, Feb 27 '15 at 16:25
Much appreciated, Im currently attempting in asp.net, its all new to me and learning as i go. In order to work around the Ad login page i was trying to build, im using iis basic authentication and capturing the user's logon name. Im only using it for the login portion to of my appliciation to capture the username from post. But if you can provide me with a php sample i can try translating it afterwards :) thanks again. — Daniel Ellison, Feb 27 '15 at 16:35
You're welcome Daniel. I've posted something for you below. Let me know how that works out. — Funk Forty Niner, Feb 27 '15 at 17:40

score 0 · Accepted Answer · edited May 23 '17 at 12:20

You can use the following inside any SQL statement, and the API of your choosing.

SELECT ID FROM tbl_staff 
WHERE username='username1' 
AND `group`='Administrator'

or (sidenote: column_x, column_y are column example names):

SELECT column_x, column_y FROM tbl_staff 
WHERE username='username1' 
AND `group`='Administrator'

You can also do SELECT * to select all columns, but that is often a method many SQL developers do not like to use.

Here is an article on the subject: Why is SELECT * considered harmful?

However, you did say this was for ASP.net which is something I do not know anything about.

Sidenote: group is a MySQL reserved word which requires special attention.

Either by wrapping the word in ticks, or using another name such as "groups" for instance.

Here is a PDO method using prepared statements:

$db = new PDO("mysql:host=localhost;dbname=db_name", $user, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$username = "username1";
$group = "Administrator";

$query = "SELECT COUNT(*) FROM tbl_staff 
         WHERE username = :username 
         AND `group` = :username";

$statement = $db->prepare($query);
$statement->bindValue(':username', $username);
$statement->bindValue(':password', $group);
$statement->execute();
$count = $statement->fetchColumn();
if ($count === 1)
{
    return TRUE;
}
else 
{
    return FALSE;   
}

References:

How to check mysql db is user is part of a group

1 Answers1

Linked