SQL Update Query within dataReader C#

Question

Currently I trying to allow people to reset there user password. My code seems to working fine, I am able to query the database, and also send Emails with password, my only issue I am having is running the update Command query within the data Reader Also how do I Hash a plain Text Password in an SQL Query.

Thanks

below is my code:

protected void Page_Load(object sender, EventArgs e)
{ }

protected void Button1_Click(object sender, EventArgs e)
{

    string connetionString = null;
    string sqlupdate = null;
    string sqlCheckUser = null;
    string sqlCheck = null;
    string user = username.Text;
    string password = password_row.Text;
    //Simple Text Field Vaildator
    if (user == String.Empty)
    {
        required.Text = "Please Enter Username";
        return;
    }//end if 
    else 
    {
        SqlConnection cnn;
        SqlCommand command;
        string ConnectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
        sqlCheckUser = "SELECT * FROM Users WHERE LoginID='" + user + "'";

        sqlupdate = "UPDATE Users SET Password='" + password + "' WHERE LoginID='" + user + "'";
        cnn = new SqlConnection(ConnectionString);

        try
        {
            cnn.Open();
            using (command = new SqlCommand(sqlCheckUser, cnn))
            {
                SqlDataReader dataReader = command.ExecuteReader(); //Not used
                while (dataReader.Read())
                {
                string email = (string)dataReader["Email"];
                string userName = (string)dataReader["LoginID"];
                TextBox1.Text = Convert.ToString(email); //Testing OutPut
                required.Text = "Please Check your Email Address for New Password"; 
                if (userName == user)
                {
                    SqlCommand update = new SqlCommand(sqlupdate, cnn);
                    required.Text = "Please Check your Email Address for New Password"; 
                    /*
                     * Send Email to User with New Password.
                     */
                     MailMessage mail = new MailMessage();
                     SmtpClient SmtpServer = new SmtpClient("");

                     mail.From = new MailAddress("");
                     mail.To.Add(email);
                     mail.Subject = "FTP Password Reset";
                     mail.Body = "The Password for your FTP account has been reset. Your new password is the following:   " + password;
                     SmtpServer.Send(mail);
                     /*
                      * End of Email
                      **/

                }//End If
                }//End While
            }//End Using
            command.Dispose();//Dispose of Command
            cnn.Close();//Close Database Connection
        }//End Try
        catch (Exception ex)
        {
            TextBox1.Text = Convert.ToString("Can not open connection ! ");//Output on Connection
        }//End Catach
    }//End Else 
}//End Button on Click

please please use commandParameters in queries. This code is open to sql injections in so many ways. — mcy
– mcy, Commented May 25, 2016 at 8:29

Joan Guarner · Accepted Answer · 2023-06-02 12:27:12Z

The code above was somewhat hard to go over, but I did my own take of it all, this should work. What you need to do Before executing the update is to Close the SqlDataReader object which is locking up the SqlCommand object.

And

NEVER EVER TAKE RAW USER INPUT IN YOUR QUERIES!

Use parameters instead, in order to avoid being an easy target for a pesky SQL Injection attack.

So, when all is said and done I'd probably do it more or less like this:

protected void Button1_Click(object sender, EventArgs e)
    {
        string sqlupdate = null;
        string sqlCheckUser = null;
        string sqlCheck = null;
        string user = username.Text;
        string password = password_row.Text;

        if (user == String.Empty)
        {
            required.Text = "Please Enter Username";
            return;
        }
        else
        {
            try
            {
                SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
                SqlCommand command;
                SqlDataReader dataReader;

                cnn.Open();
                //NEVER EVER use raw input from the user in a SQL query, use parameters at all times to
                //prevent SQL Injection
                command = new SqlCommand("SELECT * FROM Users WHERE LoginID=@User", cnn);
                command.Parameters.Add("@User",SqlDbType.NVarChar,100).Value = user;

                dataReader = command.ExecuteReader();

                if(dataReader.HasRows)
                {
                    dataReader.Read();
                    string email = dataReader["Email"].ToString();
                    string userName = dataReader["LoginID"].ToString();
                    
                    TextBox1.Text = email;
                    required.Text = "Please Check your Email Address for New Password";

                    dataReader.Close(); //All data is fetched, Close the datareader in order to be able to run the update command

                    SqlCommand update = new SqlCommand("UPDATE Users SET Password=@Password WHERE LoginID=@User", cnn);
                    update.Parameters.Add("@Password",SqlDbType.NVarChar,255).Value = password;
                    update.Parameters.Add("@User",SqlDbType.NVarChar,100).Value = user;
                    update.ExecuteNonQuery();

                    required.Text = "Please Check your Email Address for New Password";

                    MailMessage mail = new MailMessage();
                    SmtpClient SmtpServer = new SmtpClient("");

                    mail.From = new MailAddress("");
                    mail.To.Add(email);
                    mail.Subject = "FTP Password Reset";
                    mail.Body = "The Password for your FTP account has been reset. Your new password is the following:   " + password;
                    SmtpServer.Send(mail);
                }
                else
                {
                    dataReader.Close();
                    //No user was found, do some fancy thing here!
                }

                command.Dispose();
                cnn.Close();
                cnn.Dispose();
            }
            catch (Exception ex)
            {
                TextBox1.Text = Convert.ToString("Can not open connection ! ");
            }
        }
    }

David · Accepted Answer · 2016-05-25 07:57:20Z

-1

I closed the SQL connection and opened a new one within the if Statement which seems to work, but does give me the Catch Error. but not worried about that as it seems to be working.

answered May 25, 2016 at 7:57

David

3292 gold badges3 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL Update Query within dataReader C#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related