Single quote escaping in SQL Server query - using parameterised value

Question

Below is my trimmed down C#:

thisConnection.Open(); string commandtext = "Insert into Table (Comments)values('@Comments')"; SqlCommand command = new SqlCommand(commandtext, thisConnection); command.Parameters.Add("@Comments", SqlDbType.VarChar).Value = Comments; command.ExecuteNonQuery(); thisConnection.Close();

I can enter almost anything, with certain special characters being stripped out before being entered into the database, which is fine by me, by even just one single quote will throw a spanner in the works. I've tried adding .Replace("'","''"); to the Comments variable but this doesn't change anything, and I though using parameters should prevent this anyway.

I know questions like this have been asked a lot, but everywhere just points at "use parameters!"

Edit: Seeing as four people have said the same thing, I'm replying to it all here. I have removed the single quotes around @Comments, but the issue is the exact same. Any input with single quotes isn't entered to the database at all.

I have added .replace(/'/g,"''") before we get here with javascript, and this is working, but I don't see why I should have to.

Perhaps you simplified your code for this question, but you've got a commandtext variable somewhere that you pass to the SqlCommand constructor, and you then immediately override the CommandText property of the command, so commandtext is effectively unused. — user743382
– user743382, Commented Sep 27, 2013 at 9:55
@hvd you're absolutely right it's unused above. I've stopped overriding it. I'll change the code above. — dudledok
– dudledok, Commented Sep 27, 2013 at 10:00

Giannis Paraskevopoulos · Accepted Answer · 2013-09-27 10:07:30Z

You do not need the single quotes.

command.CommandText = "Insert into Table (Comments)values(@Comments)";

Ok, reading around a little after the comment of hvd, i saw that the way this works is by executing sp_executesql.

A way to get around this could be the following:

thisConnection.Open(); SqlCommand command = new SqlCommand(commandtext, thisConnection); command.CommandText = "Insert into Table (Comments)values(@Comments)"; SqlParameter param = new SqlParameter(); param.ParameterName = "@Comments"; param.Value = Comments; command.Parameters.Add(param); command.ExecuteNonQuery(); thisConnection.Close();

Noted, and updated my code, but it didn't change anything. See edit.
@dudledok You may check my edit and let me know if it is clear.
-1: that isn't how parameters work at all. They don't work by textual replacement.
@hvd Where did i say that it will be a textual replacement? I am just saying how this is interpreted in SQL.
@hvd By the way, thanks for at least giving a reason for downvoting. Much better than just downvoting. :)

seph · Accepted Answer · 2013-09-27 09:22:37Z

You don't have to escape single quotes when using parameters.
Assuming that Comments is type String then just do:

command.CommandText = "Insert into Table (Comments)values(@Comments)"; command.Parameters.Add("@Comments", SqlDbType.VarChar).Value = Comments;

coder · Accepted Answer · 2013-09-27 09:22:18Z

0

It should be as shown:

cmd.CommandText = "INSERT INTO Table (Comments) VALUES(@Comments)"

answered Sep 27, 2013 at 9:22

coder

13.2k32 gold badges116 silver badges221 bronze badges

Comments

Christian Phillips · Accepted Answer · 2013-09-27 09:22:46Z

"Insert into Table (Comments)values('@Comments')";

should be

"Insert into Table (Comments)values(@Comments)"; <-- no single quotes

The reason people are suggesting you use parameters is to avoid SQL injection. Take a look at Bobby Tables.

Mahib · Accepted Answer · 2019-09-09 19:55:06Z

I've tried something like this and I didn't get any exception but it may not safe for SQL injection.

Comments.Replace("'", $"{(char)39}");

Collectives™ on Stack Overflow

Single quote escaping in SQL Server query - using parameterised value

5 Answers 5

12 Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

12 Comments

Comments

Comments

Comments

Comments

Related