Parameterised QUERY in C# for simple login

Question

I have been doing simple website using ASP, but am not sure how to add parameterised query to avoid any SQL Injection attacks, can anybody help me to do it i always encounter errors and it has been more than a week that am doing and still i can't figured out. below i attached my simple code.

protected void btnLogin_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            string sql = "Select * From Users Where UserID='" + txtUser.Text + "' And Password='" + txtPwd.Text + "'";
        con.Open();//opens the connection
        //create the command object
        cmd = new SqlCommand(sql, con);

        //assigns the result to the reader
        dr = cmd.ExecuteReader();
        dr.Read();//read the record's data
        //if there's a matching record found
        if (dr.HasRows)
        {
            if (dr["UserType"].Equals("admin"))
            {
                Response.Redirect("dhome.aspx");
            }

            else if (dr["UserType"].Equals("staff"))
            {
                Response.Redirect("shome.aspx");
            }
            else if (dr["UserType"].Equals("member"))
            {
                Response.Redirect("mhome.aspx");
            }

        }
        else
        {
            lblAlert.Text = "Invalid username or password!";
        }
        dr.Close(); //close the data reader
        con.Close();//close the connection //declaration of data access components
    }

post the code you have tried and also indicate what errors you are getting — pm100, Dec 06 '18 at 22:00
also note , its a really bad idea to store passwords in a database. You should store hashes — pm100, Dec 06 '18 at 22:02
What sample code were you trying to use? This is easily researched. — LarsTech, Dec 06 '18 at 22:06
You should check dr.HasRows *before* you try to read the reader. — LarsTech, Dec 06 '18 at 22:14

score 1 · Answer 1 · answered Dec 06 '18 at 22:04

You should add them using SqlCommand.Parameters.Add():

using (SqlConnection con = new SqlConnection(ConnectionString))
{
    SqlCommand cmd = new SqlCommand("Select * From Users Where UserID=@username And Password=@password", con);
    cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
    cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
   //rest of the code ...
}

mr.coffee · Answer 2 · 2018-12-06T22:07:14.067

0

You need to use SqlCommand.Parameters.Add. You should also implement dispose (via using blocks or calling Dispose) to release resources after use:

string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string sql = "Select * From Users Where UserID=@user And Password=@pwd";
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(sql, connection))
{
    command.Parameters.Add("@user", SqlDbType.VarChar);
    command.Parameters["@user"].Value = "value";
    command.Parameters.Add("@pwd", SqlDbType.VarChar);
    command.Parameters["@pwd"].Value = "value";
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        if (reader.HasRows)
        {
            // read row
        }
    }
}

edited Dec 06 '18 at 22:07

answered Dec 06 '18 at 21:58

mr.coffee

962
8
22

`AddWithValue` should be avoided in favor of `Add(string, dbType)` – Ňɏssa Pøngjǣrdenlarp Dec 06 '18 at 22:01
Why should `AddWithValue` be avoided? – mr.coffee Dec 06 '18 at 22:02
1

Please [stop using `AddWithValue`](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/); it has to guess data types and sometimes it guesses wrong. – Dour High Arch Dec 06 '18 at 22:03
I have updated my answer to reflect feedback. – mr.coffee Dec 06 '18 at 22:07
@WelcomeOverflow thanks for your help i was using AddWithValue and it was giving me errors. may be this way it will be different thanks again, i really appreciate your help. – Mawada Rahm Dec 06 '18 at 23:25
@WelcomeOverflow i have received this error i don't know where it is coming. "Object reference not set to an instance of an object" – Mawada Rahm Dec 06 '18 at 23:48
1

@MawadaRahm I dont know why you are blaming me for this answer - I have nothing to do with it. I just posted a comment to the writer about some issues it had. You might want to read **[What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142)** as this is a whole new and different problem. – Ňɏssa Pøngjǣrdenlarp Dec 06 '18 at 23:54
oh sorry i thought you were the one who gave me the answer sorry, it was mr coffee. am so sorry. – Mawada Rahm Dec 06 '18 at 23:57

Parameterised QUERY in C# for simple login

2 Answers2