how to write sql query text for IN clause in C#

Question

I want to know how can I write a sql query in C# where I have to use multiple strings in the IN clause.

I have a List ListSessionId, and I want to use each of them in my IN clause.

Something like:

foreach (var sessionid in ListSessionId)
{
    query = " SELECT OM.ORDERCONFIRMATIONID AS OCN ";
    query += " FROM [DGS].[DGSCRM].[LEAD] ";
    query += " WHERE ";
    query += " SESSIONID in ('" + sessionid + "') ";
}

so as my query is something like:

SELECT OM.ORDERCONFIRMATIONID AS OCN
FROM [DGS].[DGSCRM].[LEAD]
WHERE
SESSIONID in ('sessionid1', 'sessionid2', 'sessionid3')

if the content of ListSessionId is directly or indirectly user provided, you are facing a possible SQL Injection attack. — Cleptus
– Cleptus, Commented Apr 16, 2018 at 13:36
@Jamiec wow, I disagree with all of those listed duplicates; don't get me wrong - this is certainly a common question, but those are all miles away — Marc Gravell
– Marc Gravell, Commented Apr 16, 2018 at 13:41
@MarcGravell do you think? Hmm. ok. Happy to be proved wrong - if its a common question what are the right dupes? — Jamiec
– Jamiec, Commented Apr 16, 2018 at 13:42
@Jamiec looking now, but this is basically "how to add parameterize an in", which isn't the same as those - it might involve multiple parameters — Marc Gravell
– Marc Gravell, Commented Apr 16, 2018 at 13:42
Note In has a cap (at least in Oracle) of 1000 items as such if you are envisioning a large amount you may want to switch to or = instead of in. Also look up parameter binding especially if this is coming from user input to avoid sql injection — SCFi
– SCFi, Commented Apr 16, 2018 at 13:49

JNevill · Accepted Answer · 2021-05-19 13:36:11Z

13

While you can solve this with a string .join or just iterating your loop to build the stuff between the parentheses in your IN clause, it will leave you wide open for SQL injection.

To avoid that you MUST parameterize your inputs to the SQL string. We do this by using the sql command .Parameters class.

It will look something like:

var query = "SELECT OM.ORDERCONFIRMATIONID AS OCN FROM [DGS].[DGSCRM].[LEAD] SESSIONID in ({0})"; 

var idParameterList = new List<string>();
var index = 0;
foreach (var sessionid in ListSessionId)
{
    var paramName = "@idParam" + index;
    sqlCommand.Parameters.AddWithValue(paramName, sessionid);
    idParameterList.Add(paramName);
    index++;
}
sqlCommand.CommandText = String.Format(query, string.Join(",", idParameterList));

Note that I'm totally guessing at your sqlCommand variable name, but you can swap that out as needed.

With this we are splitting our SQL string/command up into two chunks

The sql itself. This ends up being submitted to the database looking like SELECT OM.ORDERCONFIRMATIONID AS OCN FROM [DGS].[DGSCRM].[LEAD] SESSIONID in (@idParam0, @idParam1, @idParam3, ... @idParamN). The database gets this sql string and compiles it as if it had values in it. It determines the execution path and just sits on it waiting for the values to come through in the second part
The parameter's values come in. This is the list we are building in the loop. Each value in your ListSessionId gets paired up with a new sqlCommand.Parameters which takes a name @idParam0 or @idParam1, etc.

Because your SQL is compiled without any of the actual stuff in your ListSessionID there is no way for bad SQL to show up and get executed in your database. It's compiled and ready to execute. So when the parameters hit, it runs the plan for the sql statement and barfs back results. Nice and clean.

edited May 19, 2021 at 13:36

answered Apr 16, 2018 at 13:54

JNevill

50.6k4 gold badges46 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ruyut Over a year ago

The fifth line should be "foreach (var sessionid in ListSessionId)"

JNevill Over a year ago

@Ruyut Thanks for spotting that. I have updated the answer with the edit.

Rune Antonsen Over a year ago

Keep in mind if you have a large list, the max amount of parameters is 2100. (learn.microsoft.com/en-us/sql/sql-server/…)

Igor · Accepted Answer · 2018-04-16 14:05:43Z

9

You can solve this with Linq and using parameters which is critical to ensuring your code is safe from Sql Injection attacks. When using parameters be sure to also specify the parameter type and the length if applicable. I guessed at a length of 100 and a type of VarChar, you can adjust this as necessary.

// create the parameters
var parameters = ListSessionId.Select((sessionId, indx) => new SqlParameter("@session_" + indx, SqlDbType.VarChar, 100) {Value = sessionId}).ToArray();
// create the query
command.CommandText = $"SELECT OM.ORDERCONFIRMATIONID AS OCN FROM [DGS].[DGSCRM].[LEAD] WHERE SESSIONID IN ({string.Join(",", parameters.Select(x=>x.ParameterName))})";
// add parameters to the command
sqlCommand.Parameters.AddRange(parameters);

answered Apr 16, 2018 at 14:05

Igor

62.5k10 gold badges111 silver badges181 bronze badges

1 Comment

JNevill Over a year ago

Totally digging the Lambda. And the .AddRange() method. That's elegant. I'm going to steal that.

Collectives™ on Stack Overflow

how to write sql query text for IN clause in C#

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related