c# - What is the best way to save string data to SQL Server that contains single quote

I have a C# program that needs to process data and store it in SQL Server. The issue I am having is that some of the data being saved legitimately contains a single quote. When saving the data I need to locate the items that have single quotes in them and actively replace the single quote with two single quotes together so that I don't get a truncated string.

At present whereever I am adding data that might contain a single quote I am passing it through the following routine which I have in a static module called FSQ (Fix Single Quote), this is the routine:

/// 

/// Fix Single Quote - Used to remove Double quotes from strings that would confuse Access database by replacing with Single Quotes.
/// 
/// String text to be fixed by removing double quotes.
/// The original string with any double-quotes removed and replaced with single quotes. If an error occurs will return an empty string.
public static string FSQ(string s)
{

    string tmp ="";

    try
    {
        if (s == null)
            return "";

        s = s.Trim();

        if (s == "")

            return s;

        if(s.Contains("'"))
        {
            if(!s.Contains("''"))//Already been fixed previously so skip here
                tmp = s.Replace("'", "''");

                s = tmp;
        }


        return s;


    }
    catch (Exception ex)
    {
        PEH("FDQ", "Common Module", ex.Message);
        return "";
    }
} //public static String FDQ(String s)

This works and my strings get saved OK to SQL but because there are a lot of calls to this routine, the performance sucks as the program loops through thousands of rows of data whilst it's processing.

Is there a more efficient routine that would negate the need to call this function? Mostly I am just building update or insert queries that contain these items.

Any help appreciated.

Answer

NEVER EVER try to build SQL statements in your code. NEVER EVER try do do escape stuff yourself. You get at 100% a attackable code (sql injection).

Depending on your DB adapter you are using, you can use parameterized queries.

Here is a sample using ado.net:

var Query = "select * from customers where city = @city";
var cmd = new SqlCommand(Query);
cmd.Parameters.AddWithValue("@city", txtCity);

The @city in the query will be the placeholder, which is later replaced on driver level.