CODEWITHSUNDEEP
c#sqlsql-injectionsql-server-2012
Cocoa Dev
Cocoa Dev

Reputation: 9541

how to prevent an SQL Injection Attack?

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

Upvotes: 4

Views: 2502

Answers (6)

will simmons
will simmons

Reputation: 187

I combine the use of params with HtmlEncoding(to get rid of special characters where not needed). Give that a shot.

using (SqlConnection conn = new SqlConnection(conString))
{     
    string sql = "SELECT * FROM table WHERE id = @id";
    using (SqlCommand cmd = new SqlCommand(sql, conn))
    {
        cmd.paramaters.AddWithValue("@id", System.Net.WebUtility.HtmlEncode(id));
        conn.Open();
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {

        }
    }
}

Upvotes: 0

Bogdan Gavril MSFT
Bogdan Gavril MSFT

Reputation: 21448

Here is the recommendation from Microsoft:

  1. Use Code Analysis to detect areas in your Visual Studio projects that are prone to sql injection;
  2. Refer to the article on how to reduce risk of attack:

On short they talk about:

  • using a stored procedure.
  • using a parameterized command string.
  • validating the user input for both type and content before you build the command string.

Btw, you can enable static analysis as part of your build process and configure it so that when a security rule is broken, the build also breaks. Great way to make sure your team writes secure code!

Upvotes: 2

Alex
Alex

Reputation: 8937

Using ADO you can do it with the help of params 

SqlConnection Con = new SqlConnection(conString);
SqlCommand Com = new SqlCommand();
string SQLQuery = "SELECT * FROM table WHERE ";
int i=1;
foreach(word in words)
{
      Com.Parameters.Add("@word"+i.ToString(),SqlDbType.Text).Value = word;
      SQLQuery = SQLQuery + " column1 = '@word"+i.ToString()+"' AND ";
      i++;
}
Com.CommandText =SQLQuery;

Upvotes: 2

Simon Germain
Simon Germain

Reputation: 6844

You need to use prepared statements. The way those are handled is that you write your query and put placeholders for the values you want to use. Here's an example:

SELECT * FROM table WHERE column1 = @word

You then have to go through a prepare phase where the SQL engine knows it will need to bind parameters to the query. You can then execute the query. The SQL engine should know when and how to interpret the parameters you bind to your query.

Here's some code to do that:

SqlCommand command = new SqlCommand(null, rConn);

// Create and prepare an SQL statement.
command.CommandText = "SELECT * FROM table WHERE column1 = @word";
command.Parameters.Add ("@word", word);
command.Prepare();
command.ExecuteNonQuery();

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

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.

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

For SQL Server, you'd use a Table-Valued Parameter. SQL has one structure that represents a collection of multiple items of the same type. It's called a table. It doesn't have arrays.

Of course, your supposed updated query:

where report in @allTheseWords

Isn't equivalent to your original query, but may be closer to the intent. In the query constructed using AND, you're saying that the same column, in the same row has to be equal to multiple different words. Unless all of the words are equal, this will never return any rows. The updated query answers whether any of the words match, rather than all.

Upvotes: 1

Related Questions