I cant enter characters in password field. I can enter number only

Question

I used the following code to encrypt the password using md5 and store it in the database.

public partial class register : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection("Data Source=Shihab-PC;Initial Catalog=test;User ID=sh;Password=admin1");
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter ad = new SqlDataAdapter();
        DataSet ds = new DataSet();
        SqlDataReader dr;
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection myconnection;
            SqlCommand mycommand;
            string query;
            myconnection = new SqlConnection("Data Source=Shihab-PC;Initial Catalog=test;User ID=sh;Password=admin1");
            if (!Page.IsPostBack)
            {
                myconnection.Open();
                query = "select * from Users";
                mycommand = new SqlCommand(query, myconnection);
                dr = mycommand.ExecuteReader();
                dr.Read();
                int count = Convert.ToInt16(dr[0].ToString());
                while (dr.Read())
                { count++; }
                TextBox4.Text = Convert.ToString(count + 1);

            }



        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection myconnection;
            SqlCommand mycommand;
            int ra;
            string query;
            myconnection = new SqlConnection("Data Source=Shihab-PC;Initial Catalog=test;User ID=sh;Password=admin1");
            myconnection.Open();



            MD5CryptoServiceProvider md5hasher = new MD5CryptoServiceProvider();
            Byte[] hashedDataBytes;
            UTF8Encoding encoder = new UTF8Encoding();
            hashedDataBytes = md5hasher.ComputeHash(encoder.GetBytes(TextBox2.Text));

            StringBuilder hex = new StringBuilder(hashedDataBytes.Length * 2);
            foreach (Byte b in hashedDataBytes)
            {
                string x = b.ToString() + " ";
                hex.AppendFormat("{0:x2}", b);

            }


            query = "Insert into Users values(" + TextBox4.Text + ",'" +
                                                   TextBox3.Text + "','" +

                                                   hex.ToString() + "','" +

                                                   TextBox1.Text + "','" +
                                                    TextBox5.Text + "','" +
                                                    TextBox6.Text + "','" +
                                                    TextBox7.Text + "','" +
                                                    TextBox8.Text + "','" +
                                                    TextBox9.Text + "','" +
                                                   TextBox10.Text + "')";

            mycommand = new SqlCommand(query, myconnection);
            ra = mycommand.ExecuteNonQuery();
            if (ra > 0)
            {
                string msg = "alert('Record Inserted Sucessfuly')";
                Page.ClientScript.RegisterStartupScript(this.GetType(), "Message", msg, true);
                Response.Redirect("signin.aspx");
            }
            else
            {
                string msg = "alert('Unable to Insert Record ')";
                Page.ClientScript.RegisterStartupScript(this.GetType(), "Message", msg, true);
            }
            myconnection.Close();
        }
    }

the problem when I run the code and enter characters in password field I get the following error (Conversion failed when converting the varchar value '1234567yY' to data type int.)

Answer 1

Your code has a lot of issues.

It's going to leak connections, it's ripe for SQL Injection, and it's very brittle. Taking these one at a time.

First, you should wrap every object that implements IDisposable in a using clause. This is the best practice way of making sure the objects get cleaned up at the appropriate time. See this question for an example: c# closing sqlconnection and sqldatareader or not?

On a very lightly used system you may not notice the issue do to connection pool recycling. However, as soon as you get any level of traffic this code will blow up in interesting and sometimes unpredictable ways.

Second, to help protect against sql injection do NOT use string concatenation to build your queries. Instead use parameters. @decyclone's answer shows an example of how to do this. It would be trivial to crack the code you have now.

Third, your queries are very brittle. Your insert statement depends on the order of the fields in the users table and, therefore, that the table is never modified. The first time you either reorder those fields OR add a new one your code will break. And you won't find this bug until runtime. You should explicitly set which fields you are inserting into. For example:

insert into Users(Username,Password) values('SomeUser', 'SomePassword')

Answer 2

First Option: Specify the columns in your INSERT SQL.

INSERT INTO Users (UserID, Col2, Col3 ...) VALUES (...)

Second Option: See what query actually is and debug it with SQL Server Management Studio.

Put a breakpoint on this line and check the Locals for query.

mycommand = new SqlCommand(query, myconnection);

Third Option: Use a parameterized query and set the values in the Parameters property of the command.

Those maintaining this after you will thank you and you'll get rid of the SQL injection possibility in this section of code.

Answer 3

Looks like you are trying to insert a varchar value into an int type column in database.

A better way to go would be using Command Parameters instead to prevent errors like this.

Example:

SqlCommand command = new SqlCommand("Insert into Users(UserName, Password) Values(@UserName, @Password)", connection);
command.Parameters.AddWithValue("@UserName", "SomeUser"); // You pass something else instead of "SomeUser"
command.Parameters.AddWithValue("@Password", "Password"); // You pass something else instead of "Password"
command.ExecuteNonQuery();