dpbz14739
dpbz14739
2015-02-28 06:33

如果SQL语句有效，则重定向到URL - ASP.NET与PHP

已采纳

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;   
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • doukanmang3687 doukanmang3687 6年前

    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;
    点赞 评论 复制链接分享
提交

为你推荐