468,720 Members | 1,907 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,720 developers. It's quick & easy.

C# executescalar not returning value from stored procedure.

20
hi i am trying to implemement forms authentication for my website but can't seem to get the stored procedure to output the correct value when checking a users credentials. the code is

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE validlogin
  2.         @uname nchar(30),
  3.         @pass nchar(100),
  4.         @valid int        OUTPUT
  5.  
  6. AS
  7.     IF EXISTS(SELECT * FROM Users WHERE User_name = @uname AND Password = @pass)
  8.         SET @valid = 1
  9.     ELSE
  10.         SET @valid = 0
  11.         RETURN @valid
  12.  
for the stored procedure and the code in my asp.net application to run this procedure is
Expand|Select|Wrap|Line Numbers
  1. private bool UserAuthenticate(string uname, string pass)
  2.         {
  3.             int validateresult;
  4.             string hashedpwd = EncodePassword(pass);
  5.             //query the username and password
  6.             sqlSelectCommand1.CommandType = CommandType.StoredProcedure;
  7.             sqlSelectCommand1.CommandText = "validlogin";
  8.             sqlSelectCommand1.Parameters.Add(new SqlParameter("@uname", SqlDbType.NChar, 30));
  9.             sqlSelectCommand1.Parameters["@uname"].Value = uname;
  10.             sqlSelectCommand1.Parameters.Add(new SqlParameter("@pass", SqlDbType.NChar, 30));
  11.             sqlSelectCommand1.Parameters["@pass"].Value = hashedpwd; //hashedpwd;
  12.             sqlSelectCommand1.Parameters.Add(new SqlParameter("@valid", SqlDbType.NChar, 30));
  13.             sqlSelectCommand1.Parameters["@valid"].Direction = ParameterDirection.Output;
  14.             //sqlDataAdapter1.SelectCommand = sqlSelectCommand1;
  15.             sqlConnection1.Open();
  16.  
  17.             validateresult = Convert.ToInt32(sqlSelectCommand1.ExecuteScalar());
  18.             sqlConnection1.Close();
  19.  
  20.             if (validateresult > 0)
  21.             {
  22.                 return true;
  23.             }
  24.             else
  25.             {
  26.                 return false;
  27.             }
  28.         }
  29.  
the calling code then checks for the result of this function and acts accordingly. when i do a trace on the code i can never get validateresult value to be anything other than 0. any help would be appreciated.
Sep 6 '08 #1
10 24491
kenobewan
4,871 Expert 4TB
Is the password stored as a hashed password? Is the entry be the same - write the value first to verify.
Sep 7 '08 #2
balabaster
797 Expert 512MB
Firstly, what's the structure of your table holding the credentials?

Secondly, when a stored procedure is using output variables, you don't need to return the output variable with Return @Output, in this instance, you would return a value to verify if the procedure ran correctly or not - for instance:

Return @@Error

@Output already contains the output value and will automatically be returned when the procedure returns.

If you want to return the validation using "Return @Output", then the output variable is not necessary in the header, as all stored procedures should return some integer value and usually this would be used to verify that the procedure completed successfully - but there's nothing to say you couldn't use it to return whether or not the user validated successfully and ditch the output parameter in the header.
Sep 7 '08 #3
pinman
20
hi thanks for the replies. yes the password is hashed and i use exactly the same code when entering the password into the database when a user is registered and when validating the users login
Expand|Select|Wrap|Line Numbers
  1. public string EncodePassword(string originalPassword)
  2.         {
  3.             Byte[] originalBytes;
  4.             Byte[] encodedBytes;
  5.             MD5 md5;
  6.  
  7.             // Conver the original password to bytes; then create the hash
  8.             md5 = new MD5CryptoServiceProvider();
  9.             //originalBytes = (new UnicodeEncoding()).GetBytes(originalPassword);
  10.             originalBytes = ASCIIEncoding.Default.GetBytes(originalPassword);
  11.             encodedBytes = md5.ComputeHash(originalBytes);
  12.  
  13.             // Bytes to string
  14.             return System.Text.RegularExpressions.Regex.Replace(BitConverter.ToString(encodedBytes), "-", "").ToLower();
  15.         }
  16.  
the structure of the database so far is
Table name "Users"
U_name nchar 30 (username is primary key field)
F_name nchar 30
Surname nchar 30
Nickname nchar 30
Pword nchar 100

here also the code that registers the new users onto the system

Expand|Select|Wrap|Line Numbers
  1. private void btnSubmit_Click(object sender, System.EventArgs e)
  2.         {
  3.             //insert command to insert new data
  4.             sqlInsertCommand1.CommandType = CommandType.Text;
  5.             sqlInsertCommand1.CommandText = "INSERT INTO Users (U_name, F_name, Surname, Nickname, Pword) VALUES (@User_name, @First_name, @Surname, @Nickname, @Password)";
  6.             sqlInsertCommand1.Parameters.Clear();
  7.             sqlInsertCommand1.Parameters.Add("@First_name",SqlDbType.NChar,30, "First_name");
  8.             sqlInsertCommand1.Parameters.Add("@User_name",SqlDbType.NChar,30, "User_name");
  9.             sqlInsertCommand1.Parameters.Add("@Nickname",SqlDbType.NChar,30, "Nickname");
  10.             sqlInsertCommand1.Parameters.Add("@Surname",SqlDbType.NChar,30, "Surname");
  11.             sqlInsertCommand1.Parameters.Add("@Password",SqlDbType.NChar,30, "Password");
  12.             //select command to fill dataset
  13.             sqlSelectCommand1.CommandType = CommandType.Text;
  14.             sqlSelectCommand1.CommandText = "SELECT * FROM Users";
  15.             DataSet Ds = new DataSet();
  16.             sqlDataAdapter1.SelectCommand = sqlSelectCommand1;
  17.             sqlDataAdapter1.InsertCommand = sqlInsertCommand1;
  18.             sqlDataAdapter1.Fill(Ds, "Users");
  19.             //create new data to insert
  20.             DataRow drow = Ds.Tables["Users"].NewRow();
  21.             drow[0] =    textusername.Text;
  22.             drow[1] =    textfirstname.Text;
  23.             drow[2] =    textsurname.Text;
  24.             drow[3] =    textnickname.Text;
  25.             string hashedpwd = EncodePassword(textpassword2.Text);
  26.             drow[4] =    hashedpwd;
  27.             Ds.Tables["Users"].Rows.Add(drow);
  28.             sqlDataAdapter1.Update(Ds, "Users");
  29.         }
  30.  
i'll try to alter the stored procedure to see if i can get the required result later and post back later. thanks
Sep 8 '08 #4
Plater
7,872 Expert 4TB
If you are trying to geth value from store procedure that is sent with the RETURN keyword, your parameter type would not be Input or Output but the ReturnValue parameter type. That might help?
Sep 8 '08 #5
balabaster
797 Expert 512MB
When you run the query database side, does it return the right value? For instance, when I'm using encrypted passwords in the database, I usually use PwdEncrypt and PwdCompare to validate. This makes password verification a lot simpler because I don't have to handle the encryption routines on the client side, it's all handled in the database. I'm curious if the client side code is failing to provide the right hash or if the stored procedure is failing to produce the correct result, or if the stored procedure is not talking to the client side's command object. It's hard to say for sure.
Sep 8 '08 #6
pinman
20
after playing around i think i may of fixed situation. instead of using this code to assign the value from the stroed procedure

Expand|Select|Wrap|Line Numbers
  1. sqlConnection1.Open();
  2. validateresult = Convert.ToInt32(sqlSelectCommand1.ExecuteScalar());
  3. sqlConnection1.Close();
  4.  
i changed it for this code

Expand|Select|Wrap|Line Numbers
  1. sqlConnection1.Open();
  2. sqlSelectCommand1.ExecuteScalar();
  3. validateresult = convert.ToInt32(sqlSelectCommand1.Parameters["@valid"].Value);
  4. sqlConnection1.Close();
  5.  
i have also taken the RETURN @valid from the stored procedure but even with this left in the code seems to work.

balabaster
"I usually use PwdEncrypt and PwdCompare to validate"
do you mean there are ways to carry out all the password encryption in side sql database itself? i'd be interested to see an example if possible. well thanks for all you help on this guys. i'm sure my noodie coding skills mean getting stuck again very soon lol.
Sep 9 '08 #7
Plater
7,872 Expert 4TB
sqlSelectCommand1.ExecuteScalar() does not return the return value from your stored procedure
Sep 9 '08 #8
pinman
20
Yes good point and thanks for pointing that out , but the sqlcommand objects parameters list seems to be updated when the stored procedure runs as i get the desired outcome. if i change the executescalar to executenonquery i can still retrieve a value from the command objects output parameter (@valid).
Sep 9 '08 #9
can you guys separately checking wether username is invalid or password invalid?
Coz if username doesn't exist it will know you well about
Sep 18 '08 #10
balabaster
797 Expert 512MB
can you guys separately checking wether username is invalid or password invalid?
Coz if username doesn't exist it will know you well about
Well you'd have to do a two part lookup in that instance...return different values in the output params spit out a different value if the username doesn't exist than if the password was wrong.

i.e.

Expand|Select|Wrap|Line Numbers
  1. If Exists(Select * From UserTable Where UserName = @Username)
  2.   Set @Out = -1
  3. Else
  4.   Begin
  5.     Select @Count = Count(*)
  6.     From UserTable
  7.     Where UserName = @UserName
  8.     And PwdCompare(Password, @Password) = 1
  9.     If (@Count = 0) 
  10.       Set @Out = -2
  11.     Else If (@Count = 1
  12.       Set @Out = 1
  13.   End
  14.  
  15. Return @Out
If -1 is returned, the username doesn't exist, if -2 is returned the username exists but the password was wrong.

However, for security purposes, I don't recommend passing that information back to the end user. They could figure out what usernames exist and then start a brute force attack using passwords against that username...
Sep 18 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Chad Dittmer via .NET 247 | last post: by
7 posts views Thread by Neven Klofutar | last post: by
1 post views Thread by rn5a | last post: by
4 posts views Thread by scparker | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Oskars | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.