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

Question

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:

/// <summary>
/// Fix Single Quote - Used to remove Double quotes from strings that would confuse Access database by replacing with Single Quotes.
/// </summary>
/// <param name="s">String text to be fixed by removing double quotes.</param>
/// <returns>The original string with any double-quotes removed and replaced with single quotes. If an error occurs will return an empty string.</returns>
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 1

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.

Answer 2

If you are passing it as a varchar/nvarchar parameter to sql server there will be no issue. You don't need to replace ' to ''.

Answer 3

While dealing with string data you should be very cautions on how you handle it in SQL Server because of SQL Injection attack. SQL Inject Attack refers to an attack where the attacker inputs malformed text that will result into maliciously executing SQL Statements on your database.

Let's say you have a products table and following is the query you may have written to handle

var query = "SELECT * FROM Products WHERE ProductDesc = " + txtProductDesc;

As an attacker I may input "Smart Phones OR 1=1" and you can see that the query will end up listing all the products in the products table.

There are various sanitizing techniques you can follow to avert such attacks which you may want apply to handle such attacks

SQL Injection Prevention Cheat Sheet

Answer 4

The first thing you need to do is to decouple processing code from persistance using Dependency Injection.
After that, your processed data will be exposed as a System.String and consumable by ANY DBMS via an appropriate interface.
Then, let's suppose you want to indeed want to persist to SQL Server. You are probably going to use Entity Framework, which will in turn handle escaping characters, as it specifically knows where it is going to store that data (of type string)...I suppose at least. This is what I would do! If someone more experienced than me believes this is the wrong way to go, please feel free to correct me!

Answer 5

The following goes along with former replies in regards to using parameters rather than attempting to write your own method to handle apostrophes in your operation be it INSERT, DELETE or UPDATE. The following is simple, add a new record via parameters and return the new primary key.

The first insert has one apostrophe, the second three. The class below is in a class project while the second code block for the form is in a forms project which references the class project.

using System;
using System.Data.OleDb;
using System.IO;

namespace DataLib
{
    public class Operations1
    {
        public Exception InsertException { get; set; }
        private OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder
        {
            Provider = "Microsoft.ACE.OLEDB.12.0",
            DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        };
        public Operations1()
        {
            if (!(File.Exists(Builder.DataSource)))
            {
                throw new FileNotFoundException("Failed to find application's database");
            }
        }
        public bool AddNewRow(string CompanyName, string ContactName, ref int Identfier)
        {

            bool success = true;
            int affected = 0;

            try
            {
                using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString })
                {
                    using (OleDbCommand cmd = new OleDbCommand { Connection = cn })
                    {
                        cmd.CommandText = @"INSERT INTO Customer (CompanyName,ContactName) 
                            VALUES (@CompanyName, @ContactName)";

                        cmd.Parameters.AddWithValue("@CompanyName", CompanyName);
                        cmd.Parameters.AddWithValue("@ContactName", ContactName);

                        cn.Open();

                        affected = cmd.ExecuteNonQuery();
                        if (affected == 1)
                        {
                            cmd.CommandText = "Select @@Identity";
                            Identfier = Convert.ToInt32(cmd.ExecuteScalar());
                            success = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                InsertException = ex;
                success = false;
            }
            return success;
        }
    }
}

Form code (mocked/static data)

using DataLib;
using System;
using System.Windows.Forms;

namespace DataLibDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            Operations1 ops = new Operations1();
            int newIdentifier = 0;
            if (ops.AddNewRow("O'brien and company", "Mary O'brien", ref newIdentifier))
            {
                MessageBox.Show($"New Id for Mary {newIdentifier}");
            }
            else
            {
                MessageBox.Show($"Insert failed: {ops.InsertException.Message}");
            }

            if (ops.AddNewRow("O'''brien and company", "Mary O'brien", ref newIdentifier))
            {
                MessageBox.Show($"New Id for Mary {newIdentifier}");
            }
            else
            {
                MessageBox.Show($"Insert failed: {ops.InsertException.Message}");
            }
        }
    }
}

Answer 6

This the fastest way you can achieve.

public static string FSQ(string s)
{
       try
    {
        s = s.Trim();
        return s = (s != null && s != "")?(s.Contains("'") && !s.Contains("''"))?s.Replace("'", "''"): s:"";

    }
    catch (Exception ex)
    {
        PEH("FDQ", "Common Module", ex.Message);
        return "";
    }
}