Getting error while store data in sql server 2005

问题:

I am storing data (approx. 1500 words) in SQL server 2005 through textbox and button. I am using this code.

protected void Button1_Click(object sender, EventArgs e)
{
    conn.Open();
    String query = String.Format("insert into try (data,sno) values ('{0}',22)",TextBox1.Text);
    SqlCommand cmd = new SqlCommand(query, conn);
    cmd.ExecuteNonQuery();
    Label1.Text = "submitted";
    conn.Close();
}

I have column 'data' of data type 'char(4000)'.

Problem is that, when I store 1st paragraph (approx 1500 words), it stored successfully. But when I stored another paragraph (approx 1500 words), it show me the error.

"Incorrect syntax near 's'. Unclosed quotation mark after the character string ',22)'."

What is the problem ??

回答1:

Use Parameters

String query = "insert into try (data,sno) values (@data,22)"; 
SqlCommand cmd = new SqlCommand(query, conn); 
cmd.Parameters.AddWithValue("@data", TextBox1.text);
cmd.ExecuteNonQuery(); 

In this way you don't need to worry about the presence of single quotes in your text and, the most important thing, you avoid SqlInjection Attacks

回答2:

String.Format will not escape the input string suitably for use in an SQL statement, which will lead to errors & serious vulnerabilities.

You should use Parameterized Queries which are designed specifically to address this.

回答3:

This sounds like you have an ', or multiple 's, in the TextBox1.Text. You will need to replace all single quotes for double.

String query = String.Format("insert into try (data,sno) values ('{0}',22)",Replace(TextBox1.Text,"'","''")); 

However, this approach will open you up to SQL Injection attacks. I'd recommend using a Stored Procedure, like the following:

SqlCommand cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spInsertDataIntoTry";
cmd.Parameters.AddWithValue("@data", TextBox.Text);
cmd.ExecuteNonQuery();

Otherwise, you could use Parameters like others have mentioned.

回答4:

Does your text contains ' letter? If yes then it is breaking INSERT query.

If you would try to insert following text:

Hello' there

Then your query would look like this:

insert into try (data,sno) values ('Hello' there,22)

Which results in incorrect query.

This is not the way queries should be done, because it leads to security issues (read more: SQL Injection) you should use parametrized queries.

回答5:

"Incorrect syntax near 's' - this indicates your sql statements is wrong. i guess that your input content maybe contains sql server keywords, so check your 2nd paragraph is there any keyword such as "'".
for example:
2nd paragraph is: how's the weather? it's cool!!!!!!!
so the sql statement is: insert into try (data,sno) values ('how's the weather? it's cool!!!!!!!',22)
it will arise an exception incorrect syntax near 's'