Start learning free

LESSON 158 OF 173 · BACKEND: BACKEND WITH AI

The AI Pasted Text into SQL in C#

Practice

An AI assistant wrote a product search for this shop: /products/search?q=te finds every product whose name contains te. It built the SQL with an interpolated string:

var word = request.Query["q"].ToString();
var cmd = db.CreateCommand();
cmd.CommandText = $"SELECT name, price FROM products WHERE name LIKE '%{word}%'";

The $ before the quotes pastes the visitor's text into the SQL itself. A quote in that text ends the SQL string early, and the rest runs as SQL: Ana's breaks the query, and x' UNION SELECT name, email FROM customers -- answers every customer's name and email, as if they were products. This is SQL injection.

The fix is a placeholder: write a name like $city in a plain SQL string, and give it its value with cmd.Parameters.AddWithValue. Here it is on another table:

var cmd = db.CreateCommand();
cmd.CommandText = "SELECT name FROM customers WHERE city = $city";
cmd.Parameters.AddWithValue("$city", city);

The SQL text stays the same whatever the visitor types, and the database only compares the value, quotes and all. Your code must give these answers:

GET /products/search?q=te     →  200  [{"name": "Notebook", "price": 7}, {"name": "Teapot", "price": 22}]
GET /products/search?q=MUG    →  200  [{"name": "Mug", "price": 12}]
GET /products/search?q=Ana's  →  200  []
GET /products/search?q=x' UNION SELECT name, email FROM customers --  →  200  []
GET /products/search          →  200  [{"name": "Mug", "price": 12}, {"name": "Notebook", "price": 7}, … all 8 products]

Your task: rewrite the MapGet function. Read q with .ToString() (it gives "" when q is missing), build the pattern %word% as a C# string, run the query with LIKE $pattern and the pattern as its value, and answer the rows as a list of objects with name and price. Keep the same SELECT name, price FROM products as the old code and do not add ORDER BY. The rows must come back in the order they are stored in the table, and that order is not alphabetical.


Assignment
Follow the Coding Tutorial to rewrite the search with a placeholder.


Hint
The % signs go into the C# value, not into the SQL: the SQL only says LIKE $pattern, with no {word} in it. while (reader.Read()) visits each row, and reader["name"] reads a column of that row.


STEP 1 OF 10
    Ctrl + Enter to check

    Ai Sql Injection in C#

    Keep going with C#1,600+ step-by-step lessons, from your first line of code to coding interviews. Free to start.
    Start learning free