7

I have a string

good overview of ESP's in more detail than you probably need.

While inserting into SQL table it is giving error. So I want replace apostrophe in the string with double apostrophe like

good overview of ESP''s in more detail than you probably need

How to manipulate this in c#?

James123
  • 11,184
  • 66
  • 189
  • 343
  • 1
    You should use your database escape functions. You could leave yourself vulnerable to SQL injections if you don't. http://stackoverflow.com/questions/249567/algorithm-to-avoid-sql-injection-on-mssql-server-from-c-code – CheeseSucker Oct 12 '11 at 22:38
  • Or, even better: Parameterised queries. – MatBailie Oct 12 '11 at 22:46

5 Answers5

10

Very easy:

string s = "good overview of ESP's in more detail than you probably need.";
string escaped = s.Replace("'","''");

Note: It is usually safer to use command parameters. Especially if the values of the input strings are not controlled by your code (i.e. user entries).

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • 4
    While this is technically correct, it's steering the original asker in the wrong direction. Much better to use proper parameters and avoid SQL injection vulnerabilities. – Matt Hamilton Oct 12 '11 at 22:39
  • Can injection still occur, if the string is escaped? Injection, as I understand it, terminates the sql prematurely by using a single apostophy. – Olivier Jacot-Descombes Oct 12 '11 at 22:45
10

Use the Parameter object.

  myCommand.InsertCommand.Parameters.Add("@myString", SqlDbType.VarChar, 200);
  myCommand.InsertCommand.Parameters["@myString"].Value = @"good overview of ESP's in more detail than you probably need.";
Patrick Desjardins
  • 136,852
  • 88
  • 292
  • 341
2

I was working on this problem for ages. Do it on the client, replacing the single quote with two single quotes. This works if you are executing an sp with several varchar input parameters. The only problem with this is SQL injection i.e. people can see what you are up to on the client which is never a good thing. The only way round this is to use SQLparameters on the server, as they have said earlier.

Otto Williams
  • 21
  • 1
0

myCommand.InsertCommand.Parameters.Add("@myString", SqlDbType.VarChar, 200); myCommand.InsertCommand.Parameters["@myString"].Value

Pankaj
  • 19
  • 1
0

String.Replace(String,String) should work fine. In this example, you'd want:

String.Replace("'", "''")

However, I don't think that that'll fix your issue. I imagine you're more appropriately looking for:

String.Replace("'", "\'")

The reason for this being that MySQL, and I'd imagine other versions of SQL, expect strings to be enclosed in single quotes.

Dan Ambrisco
  • 865
  • 7
  • 13
  • I agree that this directly answers the question, but it assists the OP in a bad practice. If parameterised queries are used, this problem goes away AND they adopt a practice that protects against injection attacks, assists in execution plan caching, etc, etc. – MatBailie Oct 12 '11 at 22:45
  • @dambrisco: Some SQL dialects use a double apostrophe ('') instead of the escape sequence (\') you suggested for MySQL. – Olivier Jacot-Descombes Oct 12 '11 at 22:48
  • @Dems - I fully agree, but, given the question, I assumed it might be better to take baby steps. Explaining parameterization to someone who doesn't seem to know about escape sequences could be a bad place to start. Regardless, though, it would be far better to use Daok's solution than mine. – Dan Ambrisco Oct 13 '11 at 00:13
  • @Olivier Jacot-Descombes - As in, the escape sequence for an apostrophe is a double apostrophe in certain dialects? I'm a bit dumbfounded by that idea, so I'll just hope there's some practical reason for that instead of something that I'd assume would be easier to parse. – Dan Ambrisco Oct 13 '11 at 00:16