Geek Answers Handbook

SQL Injection Prevention in .NET

I usually write my SQL like this in .NET

sql.Append("SELECT id, code, email FROM mytable WHERE variable = @variable ");

Then do something like this:

 using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConfigurationManager.AppSettings["defaultConnection"]].ConnectionString)) { using (SqlCommand myCommand = new SqlCommand(sql.ToString(), conn)) { myCommand.Parameters.AddWithValue("@variable", myVariableName"); ...

But should I also do this addParameter when the data I received comes directly from the database like that?

 likesql.Append(string.Format("SELECT group_id, like_text FROM likeTerms ORDER BY group_id ASC ")); DataTable dtLike = SqlHelper.GetDataTable(likesql.ToString()); foreach (DataRow dr in dtLike) { buildsql.Append(".... varId = " + dr["group_id"].ToString() + "..."); ...

It is acceptable? What is the practice?

+6
source share
4 answers

You should always use options:

  • Where are the values ​​in your database?
  • Can you trust in your example that "group_id" has not been changed to be what you do not expect?

Wait noone

Can someone with limited access to the database enter directly into the field used elsewhere?

Performance

In addition, it helps performance. Cached execution plans will ignore the parameter value, which means that you save the server from recompiling the request each time the parameters are changed.

+16
source

When you use DbCommands with parameters, parameters are never "embedded" in the request. Instead, the request and parameter data are passed to the special system stored procedure sp_executesql. When this is done, any parameter data that you have is considered exactly that and is not analyzed from the query string; therefore, an incorrect command that may have passed the test is never executed.

Using parameters is best practice for data access levels based on ADO.NET, regardless of where the data comes from, and whether IMO is the only way it should do at that level (if you are not using ORM). You NEVER need to concatenate the values ​​that you extract from a web form or a Windows form into an SQL statement, and if you follow this rule, why would you implement it differently, simply because you are sure or certain that the information is not directly from user? Follow the same pattern, and if and when you find that this method does not allow you to save the data specified by the user, you will not be burned.

+5
source

This is acceptable (in the sense, if you know the limitations, which can sometimes work).

Is it good to use values ​​from the database and build an SQL query with string concatenation - no.

those. in your example, what if "group_id" "'--" ?

+4
source

I recommend using a stored procedure, but that is acceptable; I also recommend that you sanitize your settings before assigning them to the request

0
source

More articles:


All Articles