C# - Program to add an extra ' where a ' is found

I am working on something which adds a single quote ' for every ' found in the string.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
        string insertStatement = "INSERT INTO EXCEPTION_LOG (value1, value2" +
开发者_JS百科                                    "VALUES ('test', 'test2');";

        AddEscapeStrings(insertStatement);
        Console.Read();
    }

    private static void AddEscapeStrings(string insertStatement)
    {
        int i = 0;
        char[] c = insertStatement.ToCharArray();

        while (i < c.Length)
        {
            if (c.ElementAt(i) == '\'')
            {
                Console.Write(i + ", ");
                c[i - 1] = '\'';
            }

            i++;

            Console.WriteLine("\r\n ");

            for (int j = 0; j < c.Length; j++ )
            {
                Console.Write(c.GetValue(j));
            }
        }            
    }
  } 
}

That piece of code is finding all the positions of the ', now I want to add an extra ' before it. However I am not sure which is the best way to do it, does c# have a method which adds a ' in the position required without replacing the current character at that position, or do I have to shift the array each time?

The desired result would be (note the double single quotes):

"INSERT INTO EXCEPTION_LOG (value1, value2" + "VALUES (''test'', ''test2'');";

Maybe I'm missing something but why not simply use the Replace method?

insertStatement = insertStatement.Replace("'","''"); 

What do you expect your code to do? Do you expect this:

VALUES ('test', 'test2')

to turn into this?

VALUES (''test'', ''test2'')

Surely that is invalid SQL; surely you don’t want that.

You actually want to escape the strings first, and then construct the SQL query:

var escapedString1 = escape("test");
var escapedString2 = escape("test2");
string insertStatement = string.Format(
    "INSERT INTO EXCEPTION_LOG (value1, value2) VALUES ({0}, {1});",
    escapedString1, escapedString2);

Now writing the escaping function is really easy:

public static string escape(string input)
{
    return "'" + input.Replace("'", "''") + "'";
}

That code is a very very long way about doing it, you can just run a replace on all ' characters and replace with ''. Code below:

private static string AddEscapeStrings(string insertStatement)
{
    var returnString = returnString.Replace("'","''");
    return returnString;
}

You are overwriting the previous character with c[i - 1] = '\'';

you should insert it instead.

First, try to use '@' synax to break lines:

string insertStatement = @"INSERT INTO EXCEPTION_LOG (value1, value2) 
                                    VALUES ('test', 'test2');";

Second, why would you need to invalidate the request with double ' ?

And finally, you can use replace:

insertStatement = insertStatement.Replace("'","''"); 

if there're no '' in the statement already.

You should use:

    private static string AddEscapeStrings(string insertStatement)
    {
        return insertStatement.Replace("'", "''");
    }

You can insert directly into the string...

string result = "";

foreach(char c in insertStatement)
{
   if(c == '\'')
      result += "'";
   result += c;
}

return result;

EDIT: I missed the bit where you are doing SQL queries - you should be using parameters. Here is a link that you should read: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx