帮助我重新处理我卡在dis上的查询，请帮助 [英] help me to reslove that query i stuck on dis please help

问题描述

查询看起来正确，但没有错误，但未验证用户名和密码..
这是查询

query looks right but no error but not authenticating the user name and password..
here is the query

sCommandText="SELECT users.username, users.password, groups.name FROM users INNER JOIN roles ON roles.userid = users.userid INNER JOIN groups ON roles.groupid = groups.groupid Where users.username = '"+TextBox1+"' and users.password='"+TextBox2.Text+"'";

麻烦我建议我这样写...

现在没有错误.但是登录页面未重定向到任何页面用户或管理员..

表用户:

用户名，密码，用户名

表组

groupid，名称

表角色

groupid，用户ID


这是整个代码

troublesum suggest me to write it like that...

no error now. but login page is not redirecting to any page user or admin..

table users:

username,password, userid

table groups

groupid , name

table roles

groupid , userid


here is the whole code

string sConnectionString = string.Empty;
       string sCommandText = string.Empty;
       SqlConnection sqlConn;
       SqlCommand sqlComm;


       sConnectionString = "Data Source=FAROOQPC\\SQLEXPRESS; Initial Catalog=userlogin; Integrated Security=True";
       sqlConn = new SqlConnection(sConnectionString);

       sqlConn.Open();


       sCommandText="SELECT users.username, users.password, groups.name FROM users LEFT JOIN roles ON roles.userid = users.userid LEFT JOIN groups ON roles.groupid = groups.groupid Where users.username = '"+TextBox1+"' and users.password='"+TextBox2.Text+"'";


       sqlComm = new SqlCommand(sCommandText, sqlConn);

       SqlDataReader login = sqlComm.ExecuteReader();


       if (login.HasRows)
       {
           Session["username"] = TextBox1.Text;
           Session["password"] = TextBox2.Text;

           login.Close();
           SqlDataAdapter sqlDA = new SqlDataAdapter(sqlComm);
           DataTable dt = new DataTable();
           sqlDA.Fill(dt);
           Jabatan = dt.Rows[0]["role"].ToString();

           switch (Jabatan)
           {
               case "admin":
                   Session["role"] = "admin";
                   Response.Redirect("admin.aspx");
                   break;
               case "user":
                   Session["role"] = "user";
                   Response.Redirect("userpage.aspx");
                   break;
               default:
                  // lblResult.Text = "you dont have role";
                   break;
           }
       }

推荐答案

这很明显:

It''s pretty obvious:

Where users.username = ''"+TextBox1+"'' and users.password

应为:

Where users.username = ''"+TextBox1.Text+"'' and users.password

但是，您实际上绝对不应该那样做.不要在数据库访问中使用串联字符串-这是对SQL Injection Attack的邀请.请改用参数化查询.

另外，切勿以明文形式存储密码-这是主要的安全风险.这里有一些有关如何执行此操作的信息:密码存储:如何进行 [ ^ ]




"thanxx originaGriff可以进行更正..我可以使用storedprocedure来进行校正..用storeproceduer进行校正是安全的吗?"


并非特别如此:即使那样，您仍希望对其进行参数化.
但是问题就出在这里，无论何时做，串联字符串都是危险的.减少您的工作量:

However, you should really not do it that way anyway. Don''t use concatenated strings fro DB access - it is an invitation to SQL Injection Attack. Use parametrized queries instead.

Also never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]




"thanxx originaGriff to make correction..can i do it with storedprocedure.. is ti safe to do it with storeproceduer??"


Not particularly: Even then, you want it parametrized.
The problem is then however and when ever you do it, concatenating strings is dangerous. Cutting yours down:

"SELECT * FROM users WHERE name='" + tbName.Text + "' AND password='" + tbPW.Text" + "'"

如果您的用户键入他的姓名为"Admin";-"，然后在密码中键入任何内容，然后SQL会将其视为:

If your user types his name as "Admin'';--" and types anything at all into his password, then SQL sees it as:

SELECT * FROM users WHERE name='Admin';--' AND password=''

由于;-"是SQL的开始注释，请忽略右边的所有内容"，匹配您的用户名并登录时，根本不会考虑使用密码.
如果他将用户名键入为"x"; DROP TABLE users;-，那么您将不会获得任何结果，但是整个用户表都将被删除...
改为这样:

Since ";--" is SQL for "start of comment, ignore everything to the right" the password is not considered at all when matching your username and logging him in.
If he types his username as "x'';DROP TABLE users;--" then you get no results, but your entire users table is deleted...
Do it this way instead:

sCommandText="SELECT users.username, users.password, groups.name FROM users LEFT JOIN roles ON roles.userid = users.userid LEFT JOIN groups ON roles.groupid = groups.groupid Where users.username = @UN and users.password=@PW";
sqlComm = new SqlCommand(sCommandText, sqlConn);
sqlComm.Parameters.AddWithValue("@UN", TextBox1.Text);
sqlComm.Parameters.AddWithValue("@PW", TextBox2.Text);
SqlDataReader login = sqlComm.ExecuteReader();

这会很安全.

明确存储密码仍然不是一个好主意！

And you will be safe.

It''s still a bad idea to store passwords in clear!

这篇关于帮助我重新处理我卡在dis上的查询，请帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！