Parameterized SQL Queries in C#

This example demonstrates how to use parameterized queries in C# to prevent SQL injection attacks and improve database performance.

Basic Parameterized Query

This code snippet showcases a simple parameterized query. It establishes a connection to a SQL Server database, defines a query with a parameter placeholder (@CustomerID), adds a parameter to the command with the provided `customerId`, and then executes the query. The results are read using a `SqlDataReader` and printed to the console.

using System.Data.SqlClient;

public class DataAccess
{
    public static void GetCustomer(string customerId)
    {
        string connectionString = "Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True;";
        string query = "SELECT * FROM Customers WHERE CustomerID = @CustomerID;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@CustomerID", customerId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(reader["CustomerID"] + ", " + reader["ContactName"]);
                }
                reader.Close();
            }
        }
    }
}

Explanation of Parameterization

Parameterization replaces the direct insertion of values into a SQL query string with placeholders. These placeholders are then associated with actual values separately via the `SqlCommand.Parameters` collection. This method ensures that the values are treated as data, not as executable SQL code, thus preventing SQL injection vulnerabilities.

Concepts Behind the Snippet

SQL injection occurs when malicious SQL code is inserted into a query string, potentially allowing attackers to access or modify database data. Parameterized queries prevent this by treating all input values as data, regardless of their content. The database driver handles escaping and quoting the values appropriately before executing the query. This separation of code and data is crucial for secure database interactions.

Real-Life Use Case

Imagine a website where users can search for products by name. Without parameterization, a malicious user could input SQL code into the search box, potentially revealing sensitive information about the product catalog or other users. Parameterized queries would prevent the injected code from being executed, ensuring the security of the database.

Best Practices

• Always use parameterized queries when dealing with user input or any other potentially untrusted data.
• Avoid string concatenation to build SQL queries.
• Use appropriate data types for parameters. `AddWithValue` infers the data type, but explicitly specifying the type (e.g., `SqlDbType.NVarChar`) can improve performance and prevent unexpected behavior.
• Validate user input before passing it to the database layer.

Interview Tip

Be prepared to explain what SQL injection is and how parameterized queries prevent it. Understand the difference between parameterized queries and string concatenation. Be ready to discuss the benefits of using stored procedures, which also help prevent SQL injection.

When to Use Parameterized Queries

Use parameterized queries in every scenario where you're incorporating external data into a SQL query. This applies to user inputs, data from files, API responses, or any other source of data that you don't fully control.

Memory Footprint

The memory footprint is relatively small. Parameterized queries primarily add a small overhead for storing the parameter name, data type, and value. The main memory usage comes from the `SqlConnection`, `SqlCommand`, and `SqlDataReader` objects, which are used regardless of whether the query is parameterized.

Alternatives

• Object-Relational Mappers (ORMs): ORMs like Entity Framework and Dapper automatically handle parameterization, simplifying database interactions and further mitigating SQL injection risks. They provide a higher level of abstraction.
• Stored Procedures: Stored procedures are pre-compiled SQL code stored in the database. They can also be parameterized, providing an additional layer of security and performance benefits.

Pros of Parameterized Queries

• Security: Prevents SQL injection attacks.
• Performance: Can improve query performance, especially for frequently executed queries, as the database can cache the execution plan.
• Readability: Makes the code cleaner and easier to understand.

Cons of Parameterized Queries

• Slightly more code: Requires adding parameters to the command object.
• Potential for misuse: Although rare, misuse of `AddWithValue` can sometimes lead to unexpected behavior if the inferred data type is incorrect. Using the specific `Add` overload with explicitly defined types is more robust.

Parameterized Query with Multiple Parameters

This code demonstrates the use of multiple parameters in a single SQL query. The query selects customers based on both city and country. Each parameter is added to the `SqlCommand`'s `Parameters` collection. This shows how to parameterize more complex queries with multiple inputs.

using System.Data.SqlClient;

public class DataAccess
{
    public static void GetCustomersByCityAndCountry(string city, string country)
    {
        string connectionString = "Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True;";
        string query = "SELECT * FROM Customers WHERE City = @City AND Country = @Country;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@City", city);
                command.Parameters.AddWithValue("@Country", country);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(reader["CustomerID"] + ", " + reader["ContactName"] + ", " + reader["City"] + ", " + reader["Country"]);
                }
                reader.Close();
            }
        }
    }
}