Working with User Input

After this lesson you can

• Pass user input to a query safely with SqlCommand parameters
• Explain how SQL injection works and why parameters prevent it

Key takeaways

• Never glue what a user types into your SQL: a quote in their text can end your string and run their SQL. That's SQL injection.
• Put a parameter such as @ProductName in the SQL where the value goes, and give it the value with command.Parameters.AddWithValue.
• The server treats a parameter only ever as a value, so quotes and semicolons in it are just text.
• Use a parameter for every value from outside your code, in INSERT, UPDATE and DELETE too. A parameter can't stand for a table name, column name or keyword.
• For a LIKE search, put the % wildcards in the SQL, not in the user's text: LIKE '%' + @Search + '%'.

Apps run on what users type: a product to search for, a shipping address, a name. Your code takes that input and uses it to work with the database. It's also exactly where applications are most often attacked. The rule is simple: never trust what a user types, because it may be SQL in disguise.

The dangerous way

Here a user types a product name, and the program glues it into the SQL:

Console.Write("Product name: ");
string? name = Console.ReadLine();

// DON'T DO THIS: the user's text becomes part of the SQL
string sql = "SELECT ProductId, ProductName, UnitPrice FROM Products WHERE ProductName = '" + name + "';";
Console.WriteLine(sql);

If the user types Chai, the SQL is what you expect:

SELECT ProductId, ProductName, UnitPrice FROM Products WHERE ProductName = 'Chai';

But what if they type this?

x'; DELETE FROM OrderDetails; --

Then the SQL becomes:

SELECT ProductId, ProductName, UnitPrice FROM Products WHERE ProductName = 'x'; DELETE FROM OrderDetails; --';

The quote in their text ends your string early, ; ends your statement, and what follows is their SQL, which the server runs: every order line in the company, gone. -- turns the rest of your line into a comment, so nothing complains. This is called SQL injection, and it's one of the most common ways real websites are broken into.

The safe way: parameters

Instead of gluing the input into the SQL, put a parameter in the SQL where the value goes, a name starting with @, and give the command the value separately:

using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

Console.Write("Product name: ");
string name = Console.ReadLine() ?? "Chai";

string sql = """
    SELECT ProductId, ProductName, UnitPrice
    FROM Products
    WHERE ProductName = @ProductName;
    """;

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
using SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@ProductName", name);

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(quot;{reader.GetInt32(0)}: {reader.GetString(1)} {reader.GetDecimal(2):C}");
}

Typing Chai prints:

1: Chai $18.00

The SQL and the value now travel to the server separately. The server treats @ProductName as a value, and only ever as a value: whatever the user types, quotes, semicolons and all, it's just text to compare with product names. Typing the attack above simply finds no product.

?? "Chai" gives a value to use when ReadLine() returns null (when there's no input at all), so name is never null.

One parameter per value

Every value from outside the program gets its own parameter, and the same rule applies to INSERT, UPDATE and DELETE:

string sql = """
    UPDATE Products
    SET UnitPrice = @NewPrice
    WHERE ProductId = @ProductId;
    """;

using SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@NewPrice", newPrice);
command.Parameters.AddWithValue("@ProductId", productId);

int rowsAffected = command.ExecuteNonQuery();

A parameter can stand in for a value only, never for a table or column name or a keyword.

Searching with LIKE

To search for text anywhere in a name, add the % wildcards inside the SQL, not in the user's text:

string sql = """
    SELECT ProductName
    FROM Products
    WHERE ProductName LIKE '%' + @Search + '%'
    ORDER BY ProductName;
    """;

With @Search set to Sauce, this finds Louisiana Fiery Hot Pepper Sauce and Northwoods Cranberry Sauce.

Check yourself

2 questions to try on the lesson page.

So what

Parameters make your queries safe whatever a user types. Use one for every value that comes from outside your code, every time, with no exceptions.

Key terms

• SQL Injection
• Parameterized Query
• Null-Coalescing Operator

Cheat sheets

• Working with User Input