C#中SQL服务无查询结果时返回空列表而非抛出空引用异常

Hey there, let's sort out this issue you're facing. The null reference exception pops up because when there are no matching records in the database, your base.Get(sql) method is likely returning null instead of an empty List<Items>. We can fix this, plus address a critical security gap in your original code.

Step 1: Resolve the Null Reference Issue #

The easiest way to ensure you return an empty list instead of letting a null slip through is to use the null-coalescing operator (??). This checks if the result from base.Get is null, and if so, returns a fresh empty list instead.

Step 2: Fix the SQL Injection Vulnerability #

Your original code uses string.Format to plug the username into the SQL query—this is a massive security risk. A malicious user could input a username like ' OR 1=1 -- to access every item in your database (or even worse, modify/delete data). Always use parameterized queries to avoid this.

Updated Safe Code #

Here's the revised method that handles both problems:

public List<Items> GetList(string username) 
{ 
    // Use parameterized SQL to block injection attacks
    string sql = "Select * From Items Where username = @Username";
    
    // Adjust parameter passing to match your base.Get method's signature
    // Example 1: If base.Get accepts an anonymous parameter object
    var result = base.Get(sql, new { Username = username });
    
    // Example 2: If base.Get accepts a SqlParameter array (for SQL Server)
    // var parameters = new SqlParameter[] { new SqlParameter("@Username", username) };
    // var result = base.Get(sql, parameters);
    
    // Return empty list if result is null
    return result ?? new List<Items>();
}

What If base.Get Throws an Exception Instead of Returning Null? #

If your base.Get method throws a null reference exception (or other database-related exception) when no records are found, wrap the call in a try-catch block to handle it gracefully:

public List<Items> GetList(string username) 
{ 
    string sql = "Select * From Items Where username = @Username";
    var parameters = new SqlParameter[] { new SqlParameter("@Username", username) };
    
    try
    {
        var result = base.Get(sql, parameters);
        return result ?? new List<Items>();
    }
    catch (NullReferenceException ex)
    {
        // Optional: Log the exception for debugging purposes
        // Logger.LogError(ex, "Failed to fetch items for username {Username}", username);
        return new List<Items>();
    }
    // Add additional catch blocks for other DB exceptions if needed
}

Key Takeaways #

• Never use string concatenation/formatting for SQL queries—parameterized queries are non-negotiable for secure code.
• Use the ?? operator to convert null results to empty lists, or try-catch blocks to handle exceptions when no data exists.

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