1

I am trying to insert a given string into my table table1, but I get an error

Incorrect syntax near s

It runs fine if I pass a string like "rahul s sister" as input, but throws an error when trying to pass in "Rahul's sister".

Please tell me what should I do? 

String str = "Rahul's Sister"
cmd.Connection = con;

con.Open();

cmd.CommandText = "insert into table1(relation_n) values(N'"+str+"')";
con.Close();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amit Singh
  • 210
  • 2
  • 10
  • 5
    Have you tried pass it as parameter like `cmd.Parameters.AddWithValue("@str", str);` – Hasan Gholamali Jun 09 '18 at 07:42
  • 3
    Look into parameterising your code. Don't concatenate a literal string as you have done. Imagine if someone passed the name `'); USE master; CREATE LOGIN NewSA WITH PASSWORD = '123', CHECK_POLICY = FALSE; ALTER SERVER ROLE sysadmin ADD MEMBER NewSA;--`. That could be a nice new sysadmin you have on your server to exploit :) – Thom A Jun 09 '18 at 08:31
  • [Give me parameterized SQL, or give me death](https://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/) – EdSF Jun 09 '18 at 17:15

3 Answers3

2

Below is a parameterized query example that will solve your quote problem. Specify your actual database column data type and maximum length.

cmd.CommandText = "insert into table1(relation_n) values(@relation_n)";
cmd.Parameters.Add("@relation_n", SqlDbType.VarChar, 50).Value = str;

Parameterized queries:

  • are more secure, preventing SQL injection
  • eliminate the need to escape quotes within strings
  • avoid the need to format date string literals in a particular way, which vary by culture
  • do not require decimal separators
  • improve performance by promoting plan cache reuse
  • code that is cleaner and more maintainable

I suggest one avoid AddWithValue because that method infers the SQL data type from the .NET type. This can cause undesired results like full table scans when data types do not match. Also, since the length of string types is determined by the actual string length, you'll end up with many more cached plans than necessary in SQL Server, wasting memory and increased compilation cost.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
1

use double apostrophe

String str = "Rahul''s Sister";

instead of 

String str = "Rahul's Sister";

By the way, you need to use parameters to avoid SQL-injection.

D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • But what about the situation when i will get input from a textbox and in there lot of words contain '?? every time i used ' ' Sign?? – Amit Singh Jun 09 '18 at 07:43
  • 2
    if you get input from textbox I seriously suggest you pass it as parameter because of sql injection. – Hasan Gholamali Jun 09 '18 at 07:44
  • @AmitSingh a simple way you can do by `Replace` function before you insert the data. http://rextester.com/SUAL52540,I will suggest you use `sqlparameter`, just like Hasan Gholamali said – D-Shih Jun 09 '18 at 07:50
0

Try removing ' from Rahul's. Or try "Rahul''s sister"

Kr4iT 11
  • 3
  • 2