重定向到URL如果SQL语句是有效的 - ASP.NET与PHP [英] Redirecting to URL if SQL statement is valid - ASP.NET vs PHP
问题描述
这是一个有点跟进我的线程<一个href=\"http://stackoverflow.com/questions/28769081/how-to-check-mysql-db-is-user-is-part-of-a-group/28771103#28771103\">How检查MySQL数据库是用户是一组的一部分。我正在开发使用IIS基本身份验证和ASp.NET作为我的默认登录页面登录页面。我试图做到的是，当用户登录到asp.net登录页面，它会连接到我的MySQL数据库，检查我的表中指定的列组名，看看用户是管理员，如果是的，他们会重定向到URL1，如果没有重定向到URL2。
在这一点上，我已经非常接近完成翻译我是从PDO但是提供给ASP.NET的code的逻辑，但在部分迷路了，我需要prepare，并结合执行。我不是太熟悉PDO这些功能，甚至是ASP.NET。已在此项目在这一点上学习ASP.Net好几天了，在这一点上我会AP preciate任何帮助。
以下是更新code审查的答案（更新2月28日）后的：
％@页面语言=VB调试=真正的％GT;
＆LT;％@导入命名空间=System.Data这％GT;
＆LT;％@导入命名空间=MySql.Data.MySqlClient％GT;
＆LT; SCRIPT LANGUAGE =VB=服务器＆GT;
小组的Page_Load（发送者为对象，E作为EventArgs的）
昏暗的用户名作为字符串= Convert.ToString（User.Identity.Name.Substring（User.Identity.Name.IndexOf（\\）+ 1））
昏暗的MyConnection的作为的MySqlConnection
昏暗myDataAdapter作为MySqlDataAdapter的
昏暗STRSQL作为字符串
昏暗的MySqlCommand作为的MySqlCommand
昏暗的计数器作为整数
昏暗isInGroup由于布尔 MyConnection的=新的MySqlConnection（服务器=本地主机;用户ID = Directory_Admin;密码= IMCisgreat2014;数据库= imc_directory_tool;池=假;）
STRSQL =SELECT COUNT（*）FROM tbl_staff其中username = @用户名和'GROUP_ID'='1001'; myDataAdapter =新MySqlDataAdapter的（STRSQL，MyConnection的）
的MySqlCommand =新的MySqlCommand（STRSQL）
mySqlCommand.Parameters.AddWithValue（@用户名的用户名）
计数器= mySqlCommand.ExecuteScalar（）
如果isInGroup =柜台＆GT; 0，则
的Response.Redirect（http://www.w3schools.com）
其他
的Response.Redirect（http://www.google.ca）
万一 结束小组＆LT; / SCRIPT＆GT;＆LT; HTML和GT;
＆LT; HEAD＆GT;
＆LT;标题＆GT;简单的MySQL数据库查询＆LT; /标题＆GT;
＆LT; /头＆GT;
＆LT;身体GT;主页 ...＆LT; /身体GT;
＆LT; / HTML＆GT;
我在尝试以下code逻辑复制到我的ASP.net页面。
$ DB =新PDO（MySQL的：主机=本地主机，数据库名= DB_NAME，$用户，$通行证）;
$ DB-GT＆;的setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
$ DB-GT＆;的setAttribute（PDO :: ATTR_EMULATE_ prePARES，FALSE）;$用户名=用户名1;
$组=管理员;$查询=SELECT COUNT（*）FROM tbl_staff
其中username =：用户名
和`group` =：用户名;$声明= $ DB-GT＆; prepare（$查询）;
$陈述书＆GT; bindValue（'：用户名'，$用户名）;
$陈述书＆GT; bindValue（'：密码'，$组）;
$陈述书＆GT;执行（）;
$数= $陈述书＆GT; fetchColumn（）;
如果（$计数=== 1）
{
返回TRUE;
}
其他
{
返回FALSE;
}
首先，在ASP.NET成员提供阅读起来。你在做什么是一个非常常见的任务，所以MS内置了对会员的模式到ASP.NET。您可以根据您的数据库创建一个成员资格提供程序，然后您可以指定，而不必检查每一个网页或使用Context.User.IsInRole（管理员）`仍然会工作，如果在Web.config权限，说，您切换你的会员数据库的东西如Active Directory。
要回答你问如何使用绑定的参数code问题，这将是这样（在C＃中，因为这个问题是标签C＃）
VAR MyConnection的=新的MySqlConnection（连接字符串）;
VAR的MySqlCommand =新的MySqlCommand（SELECT COUNT（*）FROM tbl_staff其中username = @用户名和'GROUP_ID'='1001'）;
mySqlCommand.AddWithValue（@用户名的用户名）;
诠释计数=（int）的mySqlCommand.ExecuteScalar（）;
布尔isInGroup =计数＆GT; 0;
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;
这篇关于重定向到URL如果SQL语句是有效的 - ASP.NET与PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
