1

I'm having a problem. I want this to work, but it doesn't:

SqlDataSource.SelectCommand = "SELECT blah1, blah2 FROM myTable WHERE @ColumnName = @Value";

SqlDataSource.SelectParameters.Add("ColumnName", System.Data.DbType.String, "one");
SqlDataSource.SelectParameters.Add("Value", System.Data.DbType.String, "two");

It won't substitue the first paramter "ColumnName." If I remove that parameter and place the column name in it like this, it will work:

SqlDataSource.SelectCommand = "SELECT blah1, blah2 FROM myTable WHERE one = @Value";

SqlDataSource.SelectParameters.Add("Value", System.Data.DbType.String, "two");

I have a UI where the user can select the DB column name to search on. I want to protect myself from any sort of injection attacks. Any ideas how I can make this work?

One idea I read about was to use a look-up table to take the index from the DropDownList and pull column names that way. I could make that work, but I'd rather get parameterization working if possible since that seems more natural to me.

Thank you in advance for any help you can provide.

Improve this question
2
  • 1
    This simply isn't supported. You'll need to build the SQL text by hand, checking to make sure the column names are valid (perhaps using a enum).. Commented Mar 14, 2013 at 21:11
  • If this method works, then parameterised queries become unsafe. Commented Mar 14, 2013 at 21:51

3 Answers 3

Reset to default
2

Since query parameters are resolved after the SQL is parsed and an execution plan is generated, you can't actually dynamically build SQL with parameters. I would recommend building the SQL string itself, in a safe way of course. Perhaps first create an enum of valid column names:

enum DbColumns { One, Two, Three };

And then build the SQL string like so:

DbColumns colName = (DbColumns)Enum.Parse(typeof(DbColumns), "One");
SqlDataSource.SelectCommand = String.Format("SELECT blah1, blah1 FROM myTable WHERE {0} = @Value", colName);

Another idea would be to validate the column name using a regular expression, perhaps only allowing [a-z].

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

oh you put your answer too just at the moment i updated mine=)
Heh that's fine.. Both answers are for sure worth considering.
The solution I came up with was essentially the same - use the selected index of the dropdownlist as a look-up into the column names that were bound to the dropdownlist initially. If the index is not within bounds or of the right type (a.k.a not valid) I return no results.
1

I'm afraid you can't do that, what you can do instead is a little trick:

SELECT blah1, blah1 FROM myTable 
WHERE (@blah1 is null or blah1 = @blah1)
   or (@blah2 is null or blah2 = @blah2)

and provide all params @blah1, @blah2 but only assign those you need.

NB solution that Mike Christensen offering you is basically building string with right condition, which in simplest case would be 

public bool BuildQueryWithCondition(string fieldName, string fieldValue) {
   var queryTemplate = "SELECT blah1, blah1 FROM myTable WHERE {0} = @Value"
     , query = string.Format(queryTemplate, fieldName)
   SqlDataSource.SelectCommand = query;
   SqlDataSource.SelectParameters.Add("Value", System.Data.DbType.String, fieldValue);
}
Improve this answer

6 Comments

I'm kinda curious if this could have any major perf impacts or generate non-optimal execution plans.
Well as you potentially can execute query with any conditions listed it is not really. Moreover, I suspect that preparing one statement like that is better than executed unprepared statements with the only one condition specified. But you curiousity is totaly healthy, go ahead and run some tests!
The SQL parsing and compilation is gonna be really fast either way. I'm worried that it might touch a bunch of unnecessary indexes, or build giant bit-mapped indexes in memory or something. But yea, all speculation and I'd have to first have a good idea of the type of data involved, the size of the database, and all sorts of other variables not specified in the question.
That SQL is considered best practice for providing "All" value to users in reporting logic like crystal & SSRS, I'd recommend not speculating too much about it esp. if you can't discern why it would or wouldn't have performance problems.
Yeap, we actually hit exactly in the case you just described. but it is not problem of this approach, we just did have proper indexes on one table.
|
0

I have figured out a way to include a work around for parametrized column names. I had the same problem but came up with a different way and since I would be the only one using the column names then I believe this is still a safe bet.

            String sqlcomm = "SELECT * FROM Asset WHERE " + assetColName + " = ";
            command.CommandText = sqlcomm + "$assetColValue";

            //command.CommandText = @"SELECT * FROM Asset WHERE $assetColName = '$assetColValue'";
            //command.Parameters.AddWithValue("$assetColName", assetColName);

            command.Parameters.AddWithValue("$assetColValue", assetColValue);

As you can see from the code above. I tried almost what you did which I then had to comment out. I then concatenated strings together and was able to use my paramterized column name and value which then the value is securely added. The column name however is not secured but this is a method that only I will be using so its still somewhat safe. You can add regular expressions if you want to be more secure but you get the idea of the fix.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.