Redirecting to URL if SQL statement is valid - ASP.NET vs PHP

Question

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;   
}

Updated the original post with the code I have so far. Any help is appreciated. — Daniel Ellison, Feb 28 '15 at 17:02

score 0 · Accepted Answer · answered Feb 28 '15 at 08:22

0

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;

answered Feb 28 '15 at 08:22

Richard P

123
4

Thanks Richard for that, I translated your C# code's logic into VB but getting the error `Exception Details: System.InvalidOperationException: Connection must be valid and open.` something about my line 20 it dosent like `counter = mySqlCommand.ExecuteScalar()` would anyone have any advice on what it could be? – Daniel Ellison Feb 28 '15 at 16:50
Also updated my post to remove C# and replaced it with VB.NET to reflect the actual coding language I am building this in. Any help is appreciated. – Daniel Ellison Feb 28 '15 at 16:52
Ah. I forgot to add the step myConnection.Open() – Richard P Mar 01 '15 at 05:43
Thanks Richard, where would you place the myConnection.Open() code in? Should it follow right under the connection string? – Daniel Ellison Mar 01 '15 at 05:48

Redirecting to URL if SQL statement is valid - ASP.NET vs PHP

1 Answers1