this is a bit of a follow up on my thread How to check mysql db is user is part of a group. I am developing a login page using IIS Basic authentication and ASp.NET as my default login page. What I am trying to accomplish is that, when a user logs into the asp.net login page, it will connect to my MYSQL database, check in my table column named group_name and see if the user is an Administrator, if yes they will be redirected to url1 and if no to redirect to URL2.
At this point I was getting very close to finish translating the logic of the code I was provided from PDO to ASP.NET however, but got lost at the part where I need to prepare, bind and execute. I'm not too familiar with those functions in PDO or even is ASP.NET. Been on this project for several days at this point learning ASP.Net, at this point I would appreciate any help.
Below is the updated code after reviewing the answers (Updated February 28th):
%@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim username As String = Convert.ToString(User.Identity.Name.Substring(User.Identity.Name.IndexOf("\") + 1))
Dim myConnection As MySqlConnection
Dim myDataAdapter As MySqlDataAdapter
Dim strSQL As String
Dim mySqlCommand As MySqlCommand
Dim counter As Integer
Dim isInGroup As Boolean
myConnection = New MySqlConnection("server=localhost; user id=Directory_Admin; password=IMCisgreat2014; database=imc_directory_tool; pooling=false;")
strSQL = "SELECT COUNT(*) FROM tbl_staff WHERE username = @username AND 'group_id' = '1001';"
myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
mySqlCommand = New MySqlCommand(strSQL)
mySqlCommand.Parameters.AddWithValue("@username", username)
counter = mySqlCommand.ExecuteScalar()
If isInGroup = counter > 0 Then
Response.Redirect("http://www.w3schools.com")
Else
Response.Redirect("http://www.google.ca")
End If
End Sub
</script>
<html>
<head>
<title>Simple MySQL Database Query</title>
</head>
<body>
Main page ...
</body>
</html>
I am attempting to copy the logic of the following code into my ASP.net page.
$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;
}
First of all, read up on Membership Providers in ASP.NET. What you are doing is a very common task, so MS built in a pattern for Membership into ASP.NET. You can create a Membership Provider based on your database, and then you can specify permissions in the Web.config instead of having to check every page or use the Context.User.IsInRole("Administrators")` which would still work if, say, you switched your membership database to something like Active Directory.
To answer the code question you asked on how to use bound parameters, it would be something like (in C#, since this question is tagged C#)
var myConnection = new MySqlConnection("connection string");
var mySqlCommand = new MySqlCommand("SELECT COUNT(*) FROM tbl_staff WHERE username = @username AND 'group_id' = '1001'");
mySqlCommand.AddWithValue("@username", username);
int count = (int)mySqlCommand.ExecuteScalar();
bool isInGroup = count > 0;