Add sql statement into database

Question

I have to insert a SQL statement as string into database like:

string content = "insert into myTable(content) values ('" + myContent + "')";
string sql = "insert into myTable2(sqlStatement) values ('" + content + "')";

Obviously this doesnt not work because of ' inside content, so I added the following:

Console.WriteLine(content);
content = content.Replace("'", "\\'");
Console.WriteLine(content);

I'm sure the variable content has been changed, but still have error with ExecuteNonQuery()

I have tried the following too, all failed:

content = content.Replace("'", "\\\'");
content = content.Replace("'", "\\\\'");
content = content.Replace("'", @"\'");

Answer 1

When you want to escape single quote in a string, do not use \ but instead double the quotes. Example, you want to insert St. Peter's Chapel, it should be

string content = "St. Peter''s Chapel"

As a side note, it is not the proper way to do. The correct way is to parameterized the values to avoid from SQL Injection.

C# Code Snippet:

string content = "St. Peter's Chapel"
string connStr = "connection string here";
string sqlStatement = "INSERT INTO tableName (content) VALUES (@content)";
using (SqlConnection conn = new SqlConnection(connStr))
{
    using(SqlCommand comm = new SqlCommand())
    {
        comm.Connection = conn;
        comm.CommandText = sqlStatement;
        comm.CommandType = CommandType.Text;

        comm.Parameters.AddWithValue("@content", content);

        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
        }
        catch(SqlException e)
        {
            // do something with the exception
            // do not hide it
            // e.Message.ToString()
        }
    }
}

For proper coding

• use using statement for propr object disposal
• use try-catch block to properly handle objects