Search code examples
asp.netsqlsqldatasource

Using C# SQL Parameterization on Column Names

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.

Solution

  • 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].