0

If I want to insert a statement which contains quotation mark, how is it possible ?

For Example I have a text box and I enter:

Future Swami Vivekananda’s grand father's name was "____" .
Rushikesh Korgaonkar
  • 191
  • 2
  • 9
  • 21

3 Answers3

8

If you use properly parameterized statements, you shouldn't need to worry about it. Something like this (though please don't learn C# techniques from me):

string sql = @"UPDATE dbo.table SET col = @p1 WHERE ...;";
string myString = @"hello'foo""bar";

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@p1", SqlDbType.VarChar, 30).Value = myString;

(Though you really should be using stored procedures.)

If you are building your strings manually (which you really, really, really shouldn't be doing), you need to escape string delimiters by doubling them up:

INSERT dbo.tbl(col) VALUES('hello''foo"bar');
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
4

Use a parameterized query - then quotes don't matter at all. Also - your database doesn't get taken over by SQL injection - so win/win really.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
0

You can double up the quote:

INSERT INTO table
VALUES ('Future Swami Vivekananda''s grand father''s name was "____"')
bhamby
  • 15,112
  • 1
  • 45
  • 66
  • Its client side input so how m i suppose to manage it in this way ? – Rushikesh Korgaonkar Aug 23 '12 at 18:07
  • Rick - You should be able to replace ' (single quotes) in the user input with two single quotes before forming the insert statement. However, really you should refactor your code to use parametized query (as suggested by Aaron) or use stored procedures. – Ashish Gupta Aug 23 '12 at 18:23