Can Column Names Be Parameters in C# Dynamic SQL?

Dynamic SQL with Column Name Parameters in C#

Can you include column names as parameters in a SqlCommand query? The answer is normally "no." The reason is that the database engine processes the query plan when the connection opens and before you have set any parameters. However, there are some techniques you can leverage to achieve the desired outcome.

Let's consider this scenario:

SqlCommand command = new SqlCommand("SELECT @slot FROM Users WHERE name=@name; ");
prikaz.Parameters.AddWithValue("name", name);
prikaz.Parameters.AddWithValue("slot", slot);

This code will fail. Instead, you need to dynamically build the query at runtime, ensuring the proper whitelisting of inputs to prevent injection attacks:

// Verify that "slot" is an approved/expected value
SqlCommand command = new SqlCommand("SELECT [" + slot + "] FROM Users WHERE name=@name; ")
prikaz.Parameters.AddWithValue("name", name);

In this approach, @name remains parameterized, allowing for safe and efficient execution of the query.

The above is the detailed content of Can Column Names Be Parameters in C# Dynamic SQL?. For more information, please follow other related articles on the PHP Chinese website!