C# executescalar not returning value from stored procedure.

[pinman]

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

ALTER PROCEDURE validlogin
        @uname nchar(30),
        @pass nchar(100),
        @valid int        OUTPUT
 
AS
    IF EXISTS(SELECT * FROM Users WHERE User_name = @uname AND Password = @pass)
        SET @valid = 1
    ELSE
        SET @valid = 0
        RETURN @valid
 

for the stored procedure and the code in my asp.net application to run this procedure is

private bool UserAuthenticate(string uname, string pass)
        {
            int validateresult;
            string hashedpwd = EncodePassword(pass);
            //query the username and password
            sqlSelectCommand1.CommandType = CommandType.StoredProcedure;
            sqlSelectCommand1.CommandText = "validlogin";
            sqlSelectCommand1.Parameters.Add(new SqlParameter("@uname", SqlDbType.NChar, 30));
            sqlSelectCommand1.Parameters["@uname"].Value = uname;
            sqlSelectCommand1.Parameters.Add(new SqlParameter("@pass", SqlDbType.NChar, 30));
            sqlSelectCommand1.Parameters["@pass"].Value = hashedpwd; //hashedpwd;
            sqlSelectCommand1.Parameters.Add(new SqlParameter("@valid", SqlDbType.NChar, 30));
            sqlSelectCommand1.Parameters["@valid"].Direction = ParameterDirection.Output;
            //sqlDataAdapter1.SelectCommand = sqlSelectCommand1;
            sqlConnection1.Open();
 
            validateresult = Convert.ToInt32(sqlSelectCommand1.ExecuteScalar());
            sqlConnection1.Close();
 
            if (validateresult > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
 

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.

[kenobewan]

Is the password stored as a hashed password? Is the entry be the same - write the value first to verify.

[balabaster]

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.

[pinman]

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

public string EncodePassword(string originalPassword)
        {
            Byte[] originalBytes;
            Byte[] encodedBytes;
            MD5 md5;
 
            // Conver the original password to bytes; then create the hash
            md5 = new MD5CryptoServiceProvider();
            //originalBytes = (new UnicodeEncoding()).GetBytes(originalPassword);
            originalBytes = ASCIIEncoding.Default.GetBytes(originalPassword);
            encodedBytes = md5.ComputeHash(originalBytes);
 
            // Bytes to string
            return System.Text.RegularExpressions.Regex.Replace(BitConverter.ToString(encodedBytes), "-", "").ToLower();
        }
 

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

private void btnSubmit_Click(object sender, System.EventArgs e)
        {
            //insert command to insert new data
            sqlInsertCommand1.CommandType = CommandType.Text;
            sqlInsertCommand1.CommandText = "INSERT INTO Users (U_name, F_name, Surname, Nickname, Pword) VALUES (@User_name, @First_name, @Surname, @Nickname, @Password)";
            sqlInsertCommand1.Parameters.Clear();
            sqlInsertCommand1.Parameters.Add("@First_name",SqlDbType.NChar,30, "First_name");
            sqlInsertCommand1.Parameters.Add("@User_name",SqlDbType.NChar,30, "User_name");
            sqlInsertCommand1.Parameters.Add("@Nickname",SqlDbType.NChar,30, "Nickname");
            sqlInsertCommand1.Parameters.Add("@Surname",SqlDbType.NChar,30, "Surname");
            sqlInsertCommand1.Parameters.Add("@Password",SqlDbType.NChar,30, "Password");
            //select command to fill dataset
            sqlSelectCommand1.CommandType = CommandType.Text;
            sqlSelectCommand1.CommandText = "SELECT * FROM Users";
            DataSet Ds = new DataSet();
            sqlDataAdapter1.SelectCommand = sqlSelectCommand1;
            sqlDataAdapter1.InsertCommand = sqlInsertCommand1;
            sqlDataAdapter1.Fill(Ds, "Users");
            //create new data to insert
            DataRow drow = Ds.Tables["Users"].NewRow();
            drow[0] =    textusername.Text;
            drow[1] =    textfirstname.Text;
            drow[2] =    textsurname.Text;
            drow[3] =    textnickname.Text;
            string hashedpwd = EncodePassword(textpassword2.Text);
            drow[4] =    hashedpwd;
            Ds.Tables["Users"].Rows.Add(drow);
            sqlDataAdapter1.Update(Ds, "Users");
        }
 

i'll try to alter the stored procedure to see if i can get the required result later and post back later. thanks

[Plater]

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?

[balabaster]

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.

[pinman]

after playing around i think i may of fixed situation. instead of using this code to assign the value from the stroed procedure

sqlConnection1.Open();
validateresult = Convert.ToInt32(sqlSelectCommand1.ExecuteScalar());
sqlConnection1.Close();
 

i changed it for this code

sqlConnection1.Open();
sqlSelectCommand1.ExecuteScalar();
validateresult = convert.ToInt32(sqlSelectCommand1.Parameters["@valid"].Value);
sqlConnection1.Close();
 

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.

[Plater]

sqlSelectCommand1.ExecuteScalar() does not return the return value from your stored procedure

[pinman]

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).

[MATTXtwo]

can you guys separately checking wether username is invalid or password invalid?
Coz if username doesn't exist it will know you well about

[balabaster]

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.

If Exists(Select * From UserTable Where UserName = @Username)
  Set @Out = -1
Else
  Begin
    Select @Count = Count(*)
    From UserTable
    Where UserName = @UserName
    And PwdCompare(Password, @Password) = 1
    If (@Count = 0) 
      Set @Out = -2
    Else If (@Count = 1
      Set @Out = 1
  End
 
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...