How to pass string with single quotes to Sql Stored Procedure

Question

I'm using a stored procedure - how can I pass a string like:

'White','Black','Green'

I'm trying to execute a procedure as follows:

 var data = new List<dynamic>();
 string colorstr = "'White','Black','Green'";
 using (SqlConnection connection = new SqlConnection(connectionString))
 {
     connection.Open();
     string sql = $"exec GetData '{colorstr }'";
     data = connection.Query(sql).AsList();
     connection.Close();
 }

This gives an error:

incorrect syntax near 'White'

So the question is how can I pass a comma separated string with single quotes to a stored procedure?

Possible duplicate of [How do I escape a single quote in SQL Server?](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) — Joakim Danielson, Apr 26 '19 at 07:11
You'd better use parameterization in your queries and won't concern about quotes anymore. — Denis Rubashkin, Apr 26 '19 at 07:17
@Faisal google [`Table-Valued Parameters`](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017) , instead of passing a comma delimited list pass a table to your stored procedure. It will be a much cleaner solution with some performance benefits too. — M.Ali, Apr 26 '19 at 07:19

score 3 · Accepted Answer · answered Apr 26 '19 at 07:16

You are creating a SQL Injection problem. Instead, use SQL Command like below. You avoid the problem and SQL injection


    using (SqlCommand cmd = new SqlCommand("connectionString", con)) {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@colorstr", SqlDbType.VarChar).Value = "'White','Black','Green'";

      con.Open();
      cmd.ExecuteNonQuery();
    }

I don't know your parameter name so I used colorstr, and parameter type, so I used SqlDbType.VarChar. Check it in DB and fix.

score 0 · Answer 2 · answered Apr 26 '19 at 07:22

You're actually looking for how pass an array as a parameter to a stored procedure. SQL Server doesnt explicity support arrays as a parameter but there are ways to get it to work using XML or using the List variable type.

For more information see: How to pass an array into a SQL Server stored procedure

How to pass string with single quotes to Sql Stored Procedure

2 Answers2