Is there a risk of embedding SQL with this query? If so, how can I avoid this?

Question

I usually create parameterized queries to avoid SQL Injection attacks. However, I have this particular situation where I could not fully fulfill it:

public DataSet getLiveAccountingDSByParameterAndValue(string parameter, string value) { string sql = "select table_ref as Source, method as Method, sip_code as Code, " + " from view_accountandmissed " + " where " + parameter + " like @value " + " order by time DESC "; MySqlCommand cmd = commonDA.createCommand(sql); cmd.Parameters.Add("@value", MySqlDbType.String); cmd.Parameters["@value"].Value = "%" + value + "%"; MySqlDataAdapter objDA = commonDA.createDataAdapter(cmd); DataSet objDS = new DataSet(); objDA.Fill(objDS); return objDS; } 

As you can see, I create @value as a parameter, but if I tried to do the same with the parameter, the request would fail.

So, is there a risk of SQL injection with this query? Also note that the parameter is set using DropDownList SelectedValue (not TextBox, so input is limited). If so, how can I improve this query?

Answer 1

Yes there is:

 " where " + parameter + " like @value " + 

The value of the parameter is your risk. In the postback, you should check if the selected value is in the set of initial values ​​of the drop-down list.

Make the enum parameter and pass the enumeration to your function. This will eliminate the risk (something like: not verified):

 public DataSet getLiveAccountingDSByParameterAndValue(ParameterEnum parameter, string value) ..... " where " + parameter.ToString() + " like @value " + 

ParameterEnum contains a list of all possible values ​​in the drop-down list. In your code behind, analyze the selected value for listing.

Answer 2

So, is there a risk of SQL injection with this query?

I think yes, it is vulnerable to SQL injection. For example, parameter = "1 = 1 OR value"

Also, note that the parameter is set by the DropDownList SelectedValue function (not a TextBox, so input is limited)

Irrelevant. An attacker can enter any value of the executable file or network packet itself (and, thus, send a value that does not exist in DropDown).

If so, how can I improve this query?

You should check the argument of the parameter and compare it with DropDown values. To get more general data, I think there should be libraries that check such things (but I don't have a C # idea ...).

Answer 3

 var columns = new [] {"column1", column2", ....}; if (!columns.Contains(parameter)) return or do something else 

EDIT
The only risk of SQL injection is passing the column name to the where clause using string concatenation. There is no other way. The real shield is to verify that the column name is valid; it exists in the table.
Even ASP.Net has event checking (verifies that the published value is one of the drop-down lists), you cannot rely on this, since this protection can be disabled.
The parameter used with the like is not an SQL injection object

Answer 4

Since 2.0, ASP.NET automatically checks the arguments of the postback and callback to see if they are different. So this is a good example when EnableEventValidation is useful.

http://odetocode.com/blogs/scott/archive/2006/03/20/asp-net-event-validation-and-invalid-callback-or-postback-argument.aspx

You will get the following exception:

"Invalid callback or callback argument"

You can verify that it is set to true by explicitly setting it to codebehind, for example, on the Init event page:

 protected void Page_Init( object sender, EventArgs e ) { // don't remove this Page.EnableEventValidation = True; } 

Strike>

Change Unfortunately, in fact, this parameter cannot be changed from the code; it compiles, but produces the following runtime error:

The EnableEventValidation property can only be set on the directive page or in the configuration section.