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 |
------------------------------------------------
解决方案
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.
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: