SQL attribute name in parameterized query

Question

I am using following function for retrieving record on choice. I gave table my column name and value and it shows the result. but the problem is, Its not getting column name as parameter like:

public List<Products> ListAllProducts(string searchOption, string searchValue)
{
   db.ClearParameters();
   db.AddParameter(db.MakeInParam("@ColumnName", DbType.String, 50, searchOption));
   db.AddParameter(db.MakeInParam("@Value", DbType.String, 50, searchValue));
   string query = @"SELECT * 
                    FROM [Products] 
                    WHERE @ColumnName LIKE '%'+@Value+'%'";

   ds = db.GetDataSet(query);
   //Rest of code but above query is not executing
}

but when I use query like this:

string query = @"SELECT * 
                 FROM [Products] 
                 WHERE "+searchOption+" LIKE '%'+@Value+'%'";

It runs fine and give me result. I read this, this and this one specially, but got no idea. Kindly guide me.

score 2 · Accepted Answer · answered Jan 03 '14 at 14:04

Parameters can be used in place of values inside expressions, and for nothing else: in particular, you cannot use parameters to denote table names, column names, sort order specifiers, or other parts of SQL statement that are not values.

Your non-parametersized query works, because searchOption is copied into your SQL, and becomes part of the query string.

If you need to build a query that changes conditions based on a parameter, you need to change the condition to account for all possible values of @ColumnName, like this

string query = @"SELECT * 
                FROM [Products] 
                WHERE (@ColumnName='FirstName' AND FirstName LIKE '%'+@Value+'%')
                   OR (@ColumnName='LastName' AND LastName LIKE '%'+@Value+'%')
                   OR (@ColumnName='Location' AND Location LIKE '%'+@Value+'%')";

or fall back on generating your query dynamically. As long as searchOption is not coming from user's input directly, you are safe from SQL injection attacks even though your SQL is generated dynamically.

score 1 · Answer 2 · answered Jan 06 '14 at 05:36

While Trying, I came across following option and posted in answer ,so other may get benifit of this.

string query = String.Format(
                             @"SELECT * 
                             FROM [Products]
                             WHERE {0} LIKE '%'+@Value+'%'", searchOption
                            );

So the complete function becomes:

public List<Products> ListAllProducts(string searchOption, string searchValue)
{
  db.ClearParameters();
  db.AddParameter(db.MakeInParam("@Value", DbType.String, 50, searchValue));
  string query = String.Format(
                               @"SELECT * 
                               FROM [Products]
                               WHERE {0} LIKE '%'+@Value+'%'", searchOption
                              );

  ds = db.GetDataSet(query);
    //Rest of code 
}

SQL attribute name in parameterized query

2 Answers2