MS SQL中的Store Procedure中需要帮助 [英] Help needed in Store Procedure in MS SQL

问题描述

我正在使用项目的登录验证页面，我也使用哈希和盐析，在获取盐值后，我用用户输入的密码计算哈希值，并将其发送到sql server以检查密码是否匹配，如果匹配我有存储过程的输出参数来返回用户名和ID或用户传入会话。

但问题是如果我传递了错误的密码它不会返回有效的用户名和ID，这是好的，但它执行Response.Redirect（HomeAppUser.aspx）;不应该发生的页面。

I am working on my Project's Login Validation page, i am also using hashing and salting, after getting salt value i compute the hash value with the user entered password, and send this to sql server to check if password matched, if matched i had output parameter of store procedure to return username and id or that user for passing in session.
But the problem is if i pass the wrong password it doesn't return the valid username and id which is ok, but it executes the Response.Redirect("HomeAppUser.aspx"); page that should not be happened.

string hashpassword =   CreatePasswordHash(TxtBxPassword.Text.Trim(),salt);
         try
           {
           db1.sqlcmd = new SqlCommand("uspAppUserLogin");
           using (SqlDataAdapter sda = new SqlDataAdapter())
           {
               db1.sqlcmd.CommandType = CommandType.StoredProcedure;
               db1.sqlcmd.Parameters.AddWithValue("@AppUserEmail", TxtBxEmail.Text.Trim());
               db1.sqlcmd.Parameters.AddWithValue("@AppUserPassword", hashpassword);
               db1.sqlcmd.Parameters.Add("@AppUserID", SqlDbType.Int);
               db1.sqlcmd.Parameters.Add("@AppUsername", SqlDbType.VarChar, 10);
               db1.sqlcmd.Parameters["@AppUserID"].Direction = ParameterDirection.Output;
               db1.sqlcmd.Parameters["@AppUsername"].Direction = ParameterDirection.Output;
               db1.sqlcmd.Connection = db1.sqlcon;
               db1.sqlcon.Open();
               int usercount = (Int32)db1.sqlcmd.ExecuteScalar();
               Userid = (int)db1.sqlcmd.Parameters["@AppUserID"].Value;
               Username = (string)db1.sqlcmd.Parameters["@AppUsername"].Value;
           }
         }
             catch (Exception ex)
       {
           ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" +"Invalid Login Details "+ "');", true);
       }
         finally
         {
             if (usercount == 0)  // comparing users from table
             {
                 Session["AppUserName"] = Username;
                 Session["AppUserID"] = Userid;
                 Response.Redirect("HomeAppUser.aspx");  //for sucsseful login
             }
             else
             {
                 db1.sqlcon.Close();
                 //Label1.Text = "Invalid User Name or Password";  //for invalid login
             }
         }

CREATE PROC uspAppUserLogin
@AppUserEmail varchar(50),@AppUserPassword varchar(max),@AppUserID int OUT,@AppUsername Varchar(25) OUT
AS
BEGIN
SET NOCOUNT ON; 
SELECT COUNT(*) FROM TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword
SET @AppUserID =(Select UserId  From TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword)
SET @AppUsername =(Select UserName From TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword)
INSERT INTO TblLoginDetails (UserId,LoginDate) VALUES (@AppUserID,GETDATE())
END

推荐答案

结帐： http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx [ ^ ]

你在这里做了一些错误的比较：

Checkout this: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx[^]
You are making some wrong comparison here:

if (usercount == 0)  // comparing users from table  

我会以不同的方式完成它。不使用多个OUT参数，最好使用结果集。



这应该可行：

I would have a done it in a different way. Instead of using multiple OUT params, it's better to use a result set.

This should work anyway:

if (!string.IsNullOrEmpty(Username))  // comparing users from table
{
	Session["AppUserName"] = Username;
	Session["AppUserID"] = Userid;
	Response.Redirect("HomeAppUser.aspx");  //for successful login
}
else
{
	db1.sqlcon.Close();
	//Label1.Text = "Invalid User Name or Password";  //for invalid login
}

这篇关于MS SQL中的Store Procedure中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！