How to overcome SQL Injection

Question

A part of my code is subjected to SQL injection. Below is the code

public int Insert(string usrtest )

    {
DataTable dt = new DataTable();
            SqlConnection con = new SqlConnection(conn);
            // SqlCommand cmd = new SqlCommand("select * from table where name=@name", con);
            SqlDataAdapter adp = new SqlDataAdapter("select * from table where name=@name", con);
            con.Open();
            adp.SelectCommand.Parameters.AddWithValue("@name", usrtest );

            adp.Fill(dt);
            SqlCommand cmd1 = new SqlCommand("Update table set Date='" + DateTime.Now + "' where name='" + usrtest + "'", con);

            cmd1.ExecuteNonQuery();
            con.Close();
}

Don't you see the difference between the two pieces of code? First you properly use parameterized queries, and then after that you bypass everything and hand-craft SQL by string concatenation again. Also use parameters in your second query and you're done. — CodeCaster, Apr 21 '17 at 09:40
You should also use a "using" statement for the SqlConnection, SqlCommand, and SqlDataAdapter, see https://www.dotnetperls.com/sqlconnection and http://stackoverflow.com/questions/18205560/do-i-need-to-explicitly-dispose-sqldataadapter. — Polyfun, Apr 21 '17 at 10:02
I have an itemtemplate textbox control for gridview which enables editing. This is the code Does this pose a cross site script risk? — Aswini, Apr 21 '17 at 11:56
This is the query in the code behind string query = "Select Uid,Uname,Utype,Uemail,ClientName,ProjectName,Ulog from table where ClientName='" + clientname + "' and Utype='Admin' or ClientName='" + clientname + "' and Utype='Normal'"; is this also because am not using parameterized query? its XSS risk not sql injection — Aswini, Apr 21 '17 at 11:58

score 2 · Answer 1 · answered Apr 21 '17 at 09:40

2

You seem to already know how to use bind parameters, as you did just 4 lines before in your code. Use them for your second statement as well.

answered Apr 21 '17 at 09:40

nvoigt

75,013
26
93
142

score 0 · Accepted Answer · answered Apr 21 '17 at 09:40

The problem is in the following command, where you use string concatenation:

SqlCommand cmd1 = new SqlCommand("Update Usrtable set password_change_status=1, Date='" + DateTime.Now + "' where Uname='" + txtusr + "'", con);

The above command should be treaded as you have already done with the previous one, where you use Parameters.

var cmd1 = new SqlCommand("Update Usrtable set password_change_status=1, Date=@Date where Uname=@Uname", con);
cmd1.Parameters.AddWithValue("@Date",DateTime.Now);
cmd1.Parameters.AddWithValue("@Uname",txtusr);

How to overcome SQL Injection

2 Answers2