How to escape simple SQL queries in C# for SqlServer

sql

sql-injection

parameterized-queries

sql-security

byAnton Shumikhin·Nov 14, 2024

If you need to escape SQL in C#, use parameterized queries. Involving SqlCommand with @parameters provides automatic escaping and prevents SQL injection:

using (var connection = new SqlConnection(connectionString)) {
    var query = "SELECT * FROM Users WHERE Username = @Username";
    using (var command = new SqlCommand(query, connection)) {
        command.Parameters.AddWithValue("@Username", username);
        connection.Open();
        // Execute your mighty command here
    }
}

Put simply, swap out inputs with parameters in your SQL commands to fortify against SQL injection.

Why you should care about parameterized queries

Let's talk about the notorious SQL injection threat. You may have seen a concatenation-based SQL command like so:

var query = "SELECT * FROM Users WHERE Username = '" + username.Replace("'", "''") + "'";
// Not in my house!

It looks innocent enough, right? It might escape solo quotes by doubling them. But this method is brittle and prone to slip-ups. Frankly, who wants to manually escape every darn time user input is inserted into a query?

Parameterized queries, my friend, do the heavy lifting for you. The ADO.NET framework got your back:

Handles escaping special characters without you having to remember.
Keeps the naughty abstraction bypass at bay; a trickster technique attackers love.
Improves maintainability; can't count the times developers forget to escape input manually.

So, to hold the security fort, always ride with SqlCommand and parameters.

Deeper into the rabbit hole: Advanced SQL security

Dabbling with `sp_executesql`

While parameterized queries are the core of defense, let's explore sp_executesql. It's a stored procedure that slings a T-SQL statement with parameters. This approach promotes execution plan reuse and provides better shielding against injection threats:

var query = "sp_executesql N'SELECT * FROM Users WHERE Username = @Username', N'@Username NVARCHAR(100)', @Username = @userInput";
// Now you're a SQL wizard!

Protip: Always cast the types explicitly which enhances both performance and security.

Handling dynamic queries

Sometimes, we find ourselves dancing with the devil: dynamic SQL. When you can't swerve away from dynamic SQL due to complex filtering or sorting, turn to parameterized dynamic SQL and sp_executesql:

var query = "sp_executesql N'SELECT * FROM Users WHERE " + dynamicFilter + " = @Value', N'@Value NVARCHAR(100)', @Value = @filterValue";
// SQL ninja moves!

Note: The dynamic part (dynamicFilter) should be curated from predefined column names or via whitelisting methods. Safety first!

The pitfalls of string manipulation

String manipulation is enticing due to its simplicity, but it's also fraught with security pitfalls.

String concatenation: Dodge crafting SQL queries by concatenating strings with user input, it's a trap!
Character replacement: Swapping single quotes with double quotes (' to '') seems like smart escaping. But it's a slippery slope.

Striking a balance between security and performance

Yes, security measures are paramount, however, we have to ponder over the performance implications of how we escape our SQL queries.

Plan Reuse: Parameterized queries let SQL Server cache and reuse execution plans.
Batching Parameters: Club queries with identical parameters into batches to reduce database round trips.
Overhead Avoidance: Skirt around complex functions/features that carry unnecessary performance overhead, like excessive dynamic queries.

Remember, harmonizing SQL efficiency and security is essential. Each escaped character should counteract injection attacks, and also minimize performance pitfalls.

explain-codes / Sql / How to escape simple SQL queries in C# for SqlServer

Linked

How do parameterized queries help against SQL injection?



Can parameterized statement stop all SQL injection?



How to insert a value that contains an apostrophe (single quote)?



Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?



Including parameters in OPENQUERY

