Getting SQL error when executing INSERT: Incorrect syntax near 's'

Question

I'm trying to create a Windows form that inserts data into my database when I type into it and click submit. It works for short things, but when I tried to type some longer sentences, it says this:

And here's the code:

private void SubmitButton_Click(object sender, EventArgs e)
{
    con.Open();
    cmd = new SqlCommand("INSERT INTO Jokes VALUES('" + EnterJoke.Text + "', '" + EnterAnswer.Text + "')", con);

    cmd.ExecuteNonQuery();

    MessageBox.Show(" Data Has Been Saved In Database ");
    con.Close();
}

I get the error on this line:

cmd.ExecuteNonQuery();

It could also be something to do with the text you are entering having special characters which 'may be' transforming into some SQL punctuation. Can you post an example of the failing input. — Thimmu Lanka, Jun 07 '20 at 05:09
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Jun 07 '20 at 05:10
Enjoy the SQL Injection... may you should use prepared statements. the error will be removed anyway if you use it correctly. — Oswald, Jun 07 '20 at 05:11
Does this answer your query? https://stackoverflow.com/questions/62081911/incorrect-syntax-near-s-when-passing-string-parameter/62082749#62082749 — MBB, Jun 07 '20 at 05:33

score 1 · Accepted Answer · edited Jun 07 '20 at 05:32

1

Input text must be having a special character punctuation that is not being taken as part of the literal text as your code is doing a plain concatenation.

Try the below code that fixes two issues:

Most importantly, the SQL injection vulnerability.
Secondly, your issue (if it is something related to the input string having special characters)

    private void SubmitButton_Click(object sender, EventArgs e)
    {
        SqlParameter joke = new SqlParameter();
        joke.ParameterName = "@joke";
        joke.SqlDbType = SqlDbType.VarChar;
        joke.Value = EnterJoke.Text;

        SqlParameter answer = new SqlParameter();
        answer.ParameterName = "@answer";
        answer.SqlDbType = SqlDbType.VarChar;
        answer.Value = EnterAnswer.Text;

        cmd = new SqlCommand("INSERT INTO Jokes VALUES(@joke, @answer)", con);    
        cmd.Parameters.Add(joke);
        cmd.Parameters.Add(answer);

        con.Open();
        cmd.ExecuteNonQuery();

        MessageBox.Show(" Data Has Been Saved In Database ");
        con.Close();
    }

edited Jun 07 '20 at 05:32

Dale K

25,246
15
42
71

answered Jun 07 '20 at 05:30

Thimmu Lanka

427
3
12

1

Thanks very much, it worked great! As you can likely tell, I'm very new to the .NET platform and SQL. Could I ask, what is it that you changed fixed the issue with the special characters? Perhaps you could refer me to somewhere that talks about it? Thanks. – MJT Jun 08 '20 at 07:34
Glad it worked! Before I get to the fix, let me restate the root cause in simple terms - your the original code mixed up the data values with the query body. So, if your values have SQL-recognized chars, then SQL Server tries to interpret those as per it's semantics causing either a failure or some erroneous update. Now the fix is - to address the root cause statement - separated out the data values from the query body. So, it doesn't interpret these data values passed as parameters as per it's language rules but takes as literal strings. This is very specific to how your problem got addressed – Thimmu Lanka Jun 09 '20 at 02:14
There are other reasons and added advantages for parameterized queries than the our current problem scenario. Article [here](https://visualstudiomagazine.com/articles/2017/07/01/parameterized-queries.aspx) seems to be a level 1, simple, crisp and clear read on this subject. There are many such others online that you can browse through for parameterized queries. – Thimmu Lanka Jun 09 '20 at 02:17
1

Ah okay thanks very much! If you'd like to, I have another question similar to this one I just posted that perhaps you could look at. I'm having trouble inserting data into my database through a DataGridView. But either way, thanks. – MJT Jun 09 '20 at 04:42

Getting SQL error when executing INSERT: Incorrect syntax near 's'

1 Answers1