How to execute a TSQL input command in C# which includes a string with "-", "\" or ";"

Question

I have a SqlCommand that attempts to insert a row in a SQL Server database table. The column of interest is a nvarchar(100) and the data that needs to be input will include characters such as "-", ";" and "\". When I insert a string without these characters everything works fine. When I attempt to insert a string that includes these characters the code fails because these characters are literally understood by the code and thus reports a syntax error. I have resolved such an issue in TSQL alone using dynamic sql, however I cannot find any good references to perform this action in C#. I suppose I could create a stored procedure and pass the values, but is there a way in which I could efficiently perform this using C# alone? If so, How? Or is passing values to a Stored Procedure a better approach?

Here is a simplified version of the code:

String SQLServerInstanceNames = "ussqlclus-db43\ussqlclusdb43; ussqlclus-db44\ussqltrysdb44; ussqltrys-db45\ussqltrysdb45;"

//Create Connection (Get Connection string from Server Explorer)
SqlConnection myConnection = new SqlConnection("Data Source=SERVER1;Initial Catalog=Database1;Integrated Security=True");

//Open connection
try { myConnection.Open(); }
catch (Exception e) { Console.WriteLine(e.ToString()); }

SqlCommand myCommand = new SqlCommand("INSERT INTO [dbo].[Table1]" +
"([SQLServerInstanceNames])" +
"VALUES (SQLServerInstanceNames);", myConnection);

//Execute command
myCommand.ExecuteNonQuery();

//Close connection
try { myConnection.Close(); }
catch (Exception e) { Console.WriteLine(e.ToString()); }

Answer 1

Try with SqlParameters. It will save you from Sql Injection as well as from your current problem.

myCommand.Parameters.AddWithValue("@param1", myValueWithCharacters);

Answer 2

C# uses \ as a control character. You can ignore those by prepending the string with an @ character:

String SQLServerInstanceNames = @"ussqlclus-db43\ussqlclusdb43; ussqlclus-db44\ussqltrysdb44; ussqltrys-db45\ussqltrysdb45;"

Answer 3

Just update your code like this to include parmeters in INSERT statement

SqlCommand myCommand = new SqlCommand("INSERT INTO [dbo].[Table1]" + 
        "([SQLServerInstanceNames])" + "VALUES (@SQLServerInstanceNames);", myConnection);
    myCommand.Parameters.AddWithValue("@SQLServerInstanceNames", "instance name");

Notice I updated VALUES part and added @SQLServerInstanceNames – this is how you add parameters to your query.

Now that you use parameters you won’t have to worry about special characters. These will be handled automatically.