Pass string array as parameter in SQL query in C#

Question

In my application written in C# , I am writing a SQL query. Following is the query

SELECT [Resource No_] where [Resource No_] In (@resources) 

@resources is user input parameters having one or more that one strings.

My query is failing without showing an error

According to me, query is failing because in @resources parameter following is being passed

"'123,'124','125'"

(there are 2 inverted commas in the beginning and at the end and that is failing my query).

[Resource No_] is of type NVARCHAR in the database.

After Googling, I have found some help on this topic but all are applicable when [Resource No_] is of type Integer

Solution 1

While I don't agree with the selected answer (or many of the tricky answers) for the "duplicate question", here is an answer to it which shows an approach very similar with my following recommendation.

(I've voted to close this question as a duplicate, because there are such answers, even if buried.)

---

Only one SQL value can be bound to any given placeholder.

While there ways to send all the data as "one value", I'd recommend creating the placeholders dynamically: it's simple, clean, and will work reliably in most cases.

Consider this:

ICollection<string> resources = GetResources();

if (!resources.Any()) {
    // "[Resource No_] IN ()" doesn't make sense
    throw new Exception("Whoops, have to use different query!");
}

// If there is 1 resource, the result would be "@res0" ..
// If there were 3 resources, the result would be "@res0,@res1,@res2" .. etc
var resourceParams = string.Join(",",
    resources.Select((r, i) => "@res" + i));

// This is NOT vulnerable to classic SQL Injection because resourceParams
// does NOT contain user data; only the parameter names.
// However, a large number of items in resources could result in degenerate
// or "too many parameter" queries so limit guards should be used.
var sql = string.Format("SELECT [Resource No_] where [Resource No_] In ({0})",
    resourceParams);

var cmd = conn.CreateCommand();
cmd.CommandText = sql;

// Assign values to placeholders, using the same naming scheme.
// Parameters prevent SQL Injection (accidental or malicious).
int i = 0;
foreach (var r in resources) {
   cmd.Parameters.AddWithValue("@res" + i, r);
   i++;
}

Solution 2

Use a user defined table type to accept your parameter, then a JOIN clause in your select to limit the results set. See http://social.msdn.microsoft.com/Forums/en-US/2f466c93-43cd-436d-8a7c-e458feae71a0/how-to-use-user-defined-table-types

Comments

[fcm]

This is not the question. Your answer is how to create a new SQL statement; not nice when you have a few thousand entries.

[undefined]

@fcm I don't think you understand the answer, this is about how you parameterise the query when you have an unknown number of parameters.