如何获取含参数值的SqlCommand命令字符串？解决SQL注入风险

First, let's tackle the core issue: eliminating SQL injection by properly leveraging parameters with your SqlManager class. Your current attempt with a standalone SqlCommand won't work because your SqlManager relies on its own internal Parameters collection, not parameters from an external command instance.

Step 1: Update the Select Method to Support Parameters #

Modify your existing Select method to accept a parameterized condition and a list of SqlParameter objects. This keeps your query safe while maintaining compatibility with your auto-generated data access layer structure:

public List<Model.Account> Select(string condition, string orderBy, int limit = 0, int offset = 0, List<SqlParameter> parameters = null) {
    var list = new List<Model.Account>();
    var query = "SELECT " + TABLE_COLUMN + " FROM [DBO].[ACCOUNT]";
    
    if (!string.IsNullOrEmpty(condition)) 
        query += " WHERE " + condition;
    
    if (!string.IsNullOrEmpty(orderBy) || limit > 0) 
        query += " ORDER BY " + (string.IsNullOrEmpty(orderBy) ? "ID DESC" : orderBy);
    
    if (limit > 0) 
        query += " OFFSET " + offset + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
    
    using (var db = new SqlManager()) {
        // Add parameters to the SqlManager if provided
        if (parameters != null) {
            db.Parameters.AddRange(parameters.ToArray());
        }
        
        using (var reader = db.ExecuteReader(query)) {
            while (reader.Read()) {
                var item = BindData(reader);
                list.Add(item);
            }
        }
    }
    
    return list; // Don't forget to return the populated list!
}

Step 2: Call the Updated Method Safely #

Now, when fetching an account, use a parameterized condition and pass the parameters along to avoid injection:

// Parameterized condition (no direct string concatenation!)
var condition = "[Username] = @Username AND [Password] = @Password AND Active = 1";

// Create secure parameters
var parameters = new List<SqlParameter> {
    new SqlParameter("@Username", username),
    new SqlParameter("@Password", password)
};

// Fetch the account without injection risks
var account = new Data.Account().Select(condition, string.Empty, 0, 0, parameters);

Getting the "Expanded" Query String (Debugging Only!) #

If you need to see what the query would look like with parameter values filled in (for debugging/logging), you can generate it manually. Never use this generated string for execution—it reintroduces all the SQL injection risks you're trying to avoid.

Here's a helper method to generate this debug string:

private string GetDebugQueryString(string commandText, IEnumerable<SqlParameter> parameters) {
    var debugQuery = commandText;
    
    foreach (var param in parameters) {
        string formattedValue;
        
        if (param.Value == null || param.Value == DBNull.Value) {
            formattedValue = "NULL";
        } 
        else if (param.DbType == DbType.String || param.DbType == DbType.AnsiString) {
            // Escape single quotes to avoid breaking the string
            formattedValue = $"'{param.Value.ToString().Replace("'", "''")}'";
        } 
        else {
            formattedValue = param.Value.ToString();
        }
        
        debugQuery = debugQuery.Replace(param.ParameterName, formattedValue);
    }
    
    return debugQuery;
}

Use it like this for debugging:

var debugQuery = GetDebugQueryString(condition, parameters);
// Log or print debugQuery here only for troubleshooting

Important note: SQL Server doesn't actually create this expanded string internally. It sends the parameterized query and parameter values separately, which is why parameterized queries are secure against injection.

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