使用IN运算符时含单引号的选中记录无法返回完整结果问题

Great question—this is a super common gotcha when you're first combining C# and SQL, so let's break it down with your exact scenario.

When you select items A and B, your queryEventIDs variable becomes "A,B" (let's assume A and B are numeric values like 1 and 2 for this example, since that matches your result behavior). Let's look at both versions of your SQL:

1. With Single Quotes: inq1.event_id in('" + queryEventIDs + "') #

When you wrap queryEventIDs in single quotes, the final SQL string ends up as:

inq1.event_id in('1,2')

Here’s the key: SQL sees '1,2' as one single string value, not a list of two separate numbers.

Since your event_id is likely a numeric type (like int), SQL tries to convert that string to a number. Most databases (like SQL Server) parse the string until they hit a non-numeric character—so '1,2' gets converted to just 1. That’s why you only saw results for A (the first value in your list).

If event_id were a string type, this would look for rows where event_id exactly equals the full string "1,2"—not "1" or "2"—so you’d get no results (unless you had a row with that exact value).

2. Without Single Quotes: inq1.event_id in(" + queryEventIDs + ") #

Without the single quotes, your SQL becomes:

inq1.event_id in(1,2)

Now SQL correctly interprets 1 and 2 as separate values in the IN clause. It checks if event_id is equal to 1 or 2, so you get results for both A and B—exactly what you wanted.

Critical Note: Avoid SQL Injection! #

Right now, you’re building your SQL by concatenating strings, which is extremely risky because it leaves your code wide open to SQL injection attacks. Instead, use parameterized queries to safely pass your list values. Here’s a quick example for C#:

// Create parameters for each selected event ID
var parameters = new List<SqlParameter>();
var inClauseParams = string.Join(",", queryEventIDList.Select((id, index) => 
{
    var paramName = $"@EventID{index}";
    parameters.Add(new SqlParameter(paramName, id));
    return paramName;
}));

// Build the safe SQL query
var sql = $"SELECT ... WHERE (inq1.event_id IN ({inClauseParams})) AND ...";

// Execute with SqlCommand and parameters
using (var cmd = new SqlCommand(sql, yourConnection))
{
    cmd.Parameters.AddRange(parameters.ToArray());
    // Run your query here
}

This approach not only prevents attacks but also handles data types correctly (like wrapping string values in quotes automatically).

内容的提问来源于stack exchange，提问作者chetan kambli