NEVER use string concatenation (or
string.Format
, or
AppendFormat
, or interpolated strings *, ...) to build a SQL query.
ALWAYS use a parameterized query.
In this particular case, since your parameters are all
int
values, you've avoided a
SQL Injection[
^] vulnerability. But it's a bad habit to start, and it's far too easy to slip up and use string concatenation for things that aren't safe.
Passing multiple parameter values correctly is
slightly more complicated than it should be. But it's not particularly difficult:
private List<objecttype> GetFullData(int id, int[] workgroups)
{
try
{
using (var conn = DB.GetConnection())
using (var command = conn.CreateCommand())
{
command.CommandType = CommandType.Text;
var sb = new StringBuilder(@"SELECT * FROM ...");
if (workgrouns.Length != 0)
{
sb.Append(" WHERE column_3 IN (");
for (int index = 0; index < workgroups.Length; index++)
{
if (index != 0) sb.Append(", ");
string name = "@p" + index;
command.Parameters.AddWithName(name, workgroups[index]);
sb.Append(name);
}
sb.Append(")");
}
command.CommandText = sb.ToString();
using (var reader = command.ExecuteReader())
{
...
You might want to take a look at
Dapper[
^], which makes this sort of query a lot easier.
* There was an article which explained how to do this safely, but I can't find the link at the moment.
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]