how to prevent an SQL Injection Attack?

Question

Currently, I am creating an SQL Query by doing something like

string SQLQuery = "SELECT * FROM table WHERE ";
foreach(word in allTheseWords)
{
     SQLQuery = SQLQuery + " column1 = '" + word + "' AND";
}

I understand that this can lead to an SQL Injection attack. I don't know how to pass an array as a parameter

where report in @allTheseWords

===========

I am using SQL Server 2012

Answer 1

Unfortunately, you cannot pass an array as a parameter without adding a user-defined type for table-valued parameters. The simplest way around this restriction is to create individually named parameters for each element of the array in a loop, and then bind the values to each of these elements:

string SQLQuery = "SELECT * FROM table WHERE column1 in (";
for(int i = 0 ; i != words.Count ; i++) {
    if (i != 0) SQLQuery += ",";
    SQLQuery += "@word"+i;
}
...
for(int i = 0 ; i != words.Count ; i++) {
    command.Parameters.Add("@word"+i, DbType.String).Value = words[i];
}

You can also create a temporary table, insert individual words in it, and then do a query that inner-joins with the temp table of words.