如何将SQL查询完全参数化以规避SQL注入？附现有代码

Great question—parameterized queries are the right call to block SQL injection, and you're almost there! The main issue in your current code is that the [Directory] value is still being concatenated into the SQL string instead of using a parameter. Plus, we need to handle the column name safely since SQL doesn't let you parameterize identifiers like column names or table names directly. Here's how to fix it completely:

Step 1: Secure the Column Name with a Whitelist #

SQL Server won't let you use a parameter for a column name (parameters only work for values, not identifiers). So you need to validate that Employee_Column is a known, safe value to avoid injection here. A whitelist is the best approach:

// Define all allowed column names for your query
var allowedColumns = new List<string> { "EmployeeId", "FullName", "Department", "JobTitle" };

// Validate the input column name
if (!allowedColumns.Contains(Employee_Column))
{
    throw new ArgumentException("Invalid column name requested", nameof(Employee_Column));
}

Step 2: Rewrite the Query with Full Parameterization #

Now replace the concatenated [Directory] part with a parameter, and use the validated column name:

// Build the SQL with the safe column name and parameter placeholders
string sqlText = $"SELECT {Employee_Column} FROM Test_Attachments WHERE Project_Id = @PID1 AND [Directory] = @Directory";

try 
{
    // Always use 'using' statements to ensure proper disposal of database objects
    using (SqlCommand myCommand = new SqlCommand(sqlText, SqlConnection))
    {
        // Instead of AddWithValue, explicitly define parameter types for reliability
        myCommand.Parameters.Add(new SqlParameter("@PID1", SqlDbType.Int) { Value = Project_ID1 });
        
        // Calculate the directory value first, then pass it as a parameter
        string cleanedDirectory = qAttachment.Directory1.Replace(@"\" + Root_Directory, "");
        myCommand.Parameters.Add(new SqlParameter("@Directory", SqlDbType.NVarChar, 255) { Value = cleanedDirectory });

        // Execute your query here (example with a data reader)
        using (SqlDataReader reader = myCommand.ExecuteReader())
        {
            // Process results...
        }
    }
}
catch (Exception ex)
{
    // Handle exceptions appropriately (log, notify, etc.)
}

Key Reasons This Works: #

• No more string concatenation for values: The @Directory parameter tells SQL Server to treat the directory value as pure data, not executable SQL code—this eliminates injection risk for that clause.
• Whitelisted column names: By only allowing predefined column names, you block any attempts to inject malicious SQL via the Employee_Column variable (like someone passing a value like 1; DROP TABLE Test_Attachments--).
• Explicit parameter types: Using SqlParameter with specified types/lengths avoids potential performance issues from AddWithValue's automatic type inference (which can sometimes lead to index scans instead of seeks).

内容的提问来源于stack exchange，提问作者newbieForAngular