Can Table Names Be Parameterized in .NET/SQL for Clean and Secure Code?

Parameterizing Table Names in SQL?

Question:

Is it possible to pass table names as parameters in .NET/SQL? The end goal is to enhance the cleanliness and avoid messy code without compromising security.

Answer:

Directly parameterizing table names is not supported. However, you can indirectly achieve this using sp_ExecuteSQL:

SqlConnection conn = new SqlConnection("Your database connection string");
DataTable dt = new DataTable();

using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "sp_ExecuteSQL";

    cmd.Parameters.AddWithValue("@stmt","SELECT * FROM table");

    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
    {
        da.Fill(dt);
    }
}

Alternatively, you can construct the (parameterized) SQL statement in C#, appending the table name, and execute it as a command:

var name = ...; //Determine table name dynamically here
var sql = $"SELECT * FROM {"table"}";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.Add("", ...);
    var result = cmd.ExecuteReader();
    ...
}

Remember to whitelist the table names to prevent potential security vulnerabilities.

The above is the detailed content of Can Table Names Be Parameterized in .NET/SQL for Clean and Secure Code?. For more information, please follow other related articles on the PHP Chinese website!