Working with User Input

Use what a user types in a query safely with SqlCommand parameters, and see why building SQL from their text opens the door to SQL injection.

Journeyman · C# · T-SQL · Updated

By

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.

Do it: find customers by city, safely

The sales team's app asks the user for a city, then lists the customers there. Right now the method glues the city the user typed into the SQL, so a user can type SQL of their own. Make it safe with a parameter.

The method gets one value:

  • city: the text the user typed, such as London.

The practice box can't reach SQL Server, so the bottom of Program.cs has a stand-in for SqlCommand, named StandInCommand. It runs nothing: it keeps its SQL in CommandText, and keeps each parameter you add with Parameters.AddWithValue(name, value), as SqlCommand does, so the tests can look at them. Unlike SqlCommand, it's made from the SQL alone, with no connection: new StandInCommand(sql). Leave it as it is.

For example, when the user types London:

The command's Holds
CommandText SELECT CompanyName FROM Customers WHERE City = @City;
Parameter @City "London"

When the user types an attack such as x'; DELETE FROM Customers; --, the CommandText is the very same SQL, and @City holds that text, which is now only a city name to compare with.

Your job: change the method StandInCommand CustomersInCity(string city) so the user's text never goes into the SQL.

  • Make the command with exactly this SQL, whatever the user typed: SELECT CompanyName FROM Customers WHERE City = @City;
  • Add one parameter, named @City, whose value is city.
  • Return the command.

Practiced live on the lesson page: write the C# right there and check it.

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

Cheat sheets