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

2

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();

edited Sep 27, 2013 at 10:07

answered Sep 27, 2013 at 9:21

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

dudledok Over a year ago

Noted, and updated my code, but it didn't change anything. See edit.

Giannis Paraskevopoulos Over a year ago

@dudledok You may check my edit and let me know if it is clear.

user743382 Over a year ago

-1: that isn't how parameters work at all. They don't work by textual replacement.

Giannis Paraskevopoulos Over a year ago

@hvd Where did i say that it will be a textual replacement? I am just saying how this is interpreted in SQL.

Giannis Paraskevopoulos Over a year ago

@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

1

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;

answered Sep 27, 2013 at 9:22

seph

6942 gold badges8 silver badges23 bronze badges

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

0

"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.

answered Sep 27, 2013 at 9:22

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

Comments

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

0

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}");

answered Sep 9, 2019 at 19:55

Mahib

4,0936 gold badges58 silver badges65 bronze badges

Collectives™ on Stack Overflow

Single quote escaping in SQL Server query - using parameterised value

5 Answers 5

12 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

12 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related