Geek Answers Handbook

How to prevent SQL injection attack?

I am currently 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

+4
source share
6 answers

Here is a recommendation from Microsoft:

  • Use Code Analysis to discover areas in Visual Studio projects that are subject to SQL injection;
  • See the article on how to reduce the risk of attack:

In short, they talk about:

  • using a stored procedure.
  • using a parameterized command line.
  • confirmation of user input for both type and content before creating the command line.

Btw, you can enable static analysis as part of the build process and configure it so that if the security rule is violated, the assembly will also break. A great way to make sure your team writes safe code!

+2
source

Unfortunately, you cannot pass an array as a parameter without adding a custom type for the table parameters. The simplest way to do this is to create individually named parameters for each element of the array in the 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 into it, and then execute a query that is an internal join to the temporary word table.

+3
source

Using ADO, you can do this with parameters

 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;
+2
source

For SQL Server, you must use the Table-Valued Parameter . SQL has one structure, which is a collection of several elements of the same type. He named the table. It has no arrays.

Of course, your supposed updated query:

 where report in @allTheseWords

Does not match your original request, but may be closer to the intent. In a query built using AND , you say that the same column on the same row should be equal to several different words. If all words are not equal, this will never return a string. The updated query answers whether any of the words matches, and not all.

+1
source

You need to use prepared statements. The way they are processed is that you write your request and put placeholders for the values ​​you want to use. Here is an example:

 SELECT * FROM table WHERE column1 = @word

Then you need to go through the preparation phase, in which the SQL engine knows that it will need to bind parameters to the query. Then you can execute the request. The SQL engine needs to know when and how to interpret the parameters that you associate with your query.

Here is the code for this:

 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();
+1
source 
 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()) { } } }
0
source

More articles:


All Articles