Escape simple SQL queries in C# for Sql Server

Hellow,

Guess you are also stuck while writing a Query with C#.NET to insert a SQL Query to one of your tables, like I was few days ago! Or, you are trying to write a SQL Query in C#.NET which will insert a row in your table and you want to fix the issues with Single Quote character which is breaking your current code. If yes, then you are at the right place, since, I have also faced similar problems few days back and after little bit researching I found a useful way which can make your life also easier.

Here is the way you were doing it earlier:

1. You wrote a Query string and passing variables to it using String.Format method
2. Then this query string is being passed to a SQL Command object
3. After that you are setting the connection and executing the query

string queryString = string.Format(@”INSERT INTO MYTABLE(COL1, COL2)
VALUES(‘{0}’, ‘{1}’)”,
valueForCol1, valueForCol2);

using(SqlConnection conn = new SqlConnection(connectionString)){

try
{

using(SqlCommand insertCommand = new SqlCommand())
{

insertCommand.CommandType = CommandType.Text;
insertCommand.CommandText = queryString;
insertCommand.Connection = conn;

if(conn.State != ConnectionState.Open) conn.Open();

int numberOfAffectedRows = insertCommand.ExecuteNonQuery();

}

}
catch(Exception)
{
throw;
}
finally
{
if(null != conn && conn.State != ConnectionState.Closed)
conn.Close();
}

}

So, above example is one such instance which you might have been doing in your code and whenever the values for COL1 or COL2 contains a single Quote (‘), the query is failing. This is because single quote character is one of the reserved characters for SQL.

In order to bypass this problem, you can either run a String Replace() to escape the single quote character by another single quote, i.e.

string myNewString = myOldString.Replace(“‘”, “””);

But, this is not a perfect way of handling SQL queries since there are issues with SQL Injection. So, the better way is to parametrize the query.

Example:

string queryString = @”INSERT INTO MYTABLE(COL1, COL2)
VALUES(@colParam1, @colParam2)”;

SqlCommand insertCommand = new SqlCommand();

insertCommand.CommandType = CommandType.Text;
insertCommand.CommandText = queryString;

insertCommand.Parameters.AddWithValue(“@colParam1”, valueForCol1);
insertCommand.Parameters.AddWithValue(“@colParam2”, valueForCol2);

using(SqlConnection conn = new SqlConnection(connectionString)){

try{

insertCommand.Connection = conn;

if(conn.State != ConnectionState.Open) conn.Open();

int numberOfAffectedRows = insertCommand.ExecuteNonQuery();

}
catch(Exception)
{
throw;
}
finally
{
if(null != conn && conn.State != ConnectionState.Closed)
conn.Close();
}

}

Note: I know you can find a way to dispose the SqlCommand object safely before moving out of the scope.

So, you have your fix now. You can use this method where we declare parameters in a SQL Command and then add values to those parameters before execution.

Hope this post will help you!!