System.Data.SqlClient.SqlException: 'Incorrect syntax near 'Judgment'.'

Question

I can't find out why I'm getting this error:

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'Judgment'.'

Code:

protected void gvComic_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    int ID = Convert.ToInt32(gvComic.DataKeys[e.RowIndex].Value.ToString());
    string Name = ((TextBox)(gvComic.Rows[e.RowIndex].Cells[1].Controls[0])).Text;
    string UnitPrice = ((TextBox)(gvComic.Rows[e.RowIndex].Cells[2].Controls[0])).Text;
    string PublishCountry = ((TextBox)(gvComic.Rows[e.RowIndex].Cells[3].Controls[0])).Text;
    string Author = ((TextBox)(gvComic.Rows[e.RowIndex].Cells[4].Controls[0])).Text;
    string Description = ((TextBox)(gvComic.Rows[e.RowIndex].Cells[5].Controls[0])).Text;
    string Translator = ((TextBox)(gvComic.Rows[e.RowIndex].Cells[6].Controls[0])).Text;
    string CoverPage = ((TextBox)(gvComic.Rows[e.RowIndex].Cells[7].Controls[0])).Text;

    using (SqlConnection conn = new SqlConnection(cs))
    {
        conn.Open();

        SqlCommand cmd = new SqlCommand("UPDATE Comics SET Name='" + Name + "', UnitPrice='" + UnitPrice + "', PublishCountry='" + PublishCountry + "', Author='" + Author + "', Description='" + Description + "', Translator='" + Translator + "',CoverFile='" + CoverPage + "'  WHERE CId='" + ID + "'", conn);            

        int t = cmd.ExecuteNonQuery();

        if (t > 0 )
        {
            Response.Write("<script>alert('Data has updated!')</script>");
            gvComic.EditIndex = -1;
            BindGrind();
        }
    }
}

enter image description here

Let me guess: One input is like `Judgement's Day`? Always escape your user input!! Use prepared statements for that. Don't patch together your queries like that. — juergen d, Jul 19 '20 at 07:43
SQL injection is happy about your code. https://www.w3schools.com/sql/sql_injection.asp — Legacy Code, Jul 19 '20 at 07:46
[Why is SQL Injection still a thing?](https://zoharpeled.wordpress.com/2020/07/16/back-to-basics-sql-injection/) — Zohar Peled, Jul 19 '20 at 08:02
Does this answer your question? [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement) — Progman, Jul 19 '20 at 08:44

score 1 · Answer 1 · answered Jul 19 '20 at 08:28

So the reason for this error is that you've broken your query string into several parts and when value comes with some unexpected or special character, it breaks the query string.

Here is a much cleaner and safer way to do it

SqlCommand cmd = new SqlCommand("UPDATE Comics SET Name=@name, UnitPrice=@unit, PublishCountry=@pubcontry, Author=@author, Description=@desc, Translator=@trans,CoverFile=@cfile  WHERE CId=@id", conn)
           
            cmd.Parameters.AddWithValue("@id", ID);
            cmd.Parameters.AddWithValue("@cfile",CoverPage);
            cmd.Parameters.AddWithValue("@trans",Translator);
            cmd.Parameters.AddWithValue("@desc",Description);
            cmd.Parameters.AddWithValue("@author",Author);
            cmd.Parameters.AddWithValue("@unit",UnitPrice);
            cmd.Parameters.AddWithValue("@name",Name);
            
            cmd.ExecuteNonQuery();

So in this way you write your query in a single string and define a @key wherever you want to insert a value in your query. and provide the value of key like this:

        cmd.Parameters.AddWithValue("@key",value)

[Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Zohar Peled, Jul 19 '20 at 08:39

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'Judgment'.'

1 Answers1