Start learning free

LESSON 158 OF 173 · BACKEND: BACKEND WITH AI

The AI Pasted Text into SQL in TypeScript

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 by joining strings with +:

const word = req.query.q || "";
const rows = db.prepare("SELECT name, price FROM products WHERE name LIKE '%" + word + "%'").all() as ProductRow[];

The + 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. TypeScript can't catch it: to the compiler, the AI's SQL is just a string like any other.

The fix is a placeholder: write ? in the SQL and pass the value beside it, to .all(...). Here it is on another table:

const rows = db.prepare("SELECT name FROM customers WHERE city = ?").all(city) as CustomerRow[];

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 handler of GET /products/search. Read q (default ""), build the pattern %word% as a string, run the query with LIKE ? and the pattern as its value, typed as ProductRow[], and answer the rows as JSON. 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 route with a placeholder.


Hint
The % signs go into the value, not into the SQL: the SQL only says LIKE ?. Pass pattern to .all(...) and add as ProductRow[]: it gives an array with one object per row, ready for res.json.


STEP 1 OF 4
    Ctrl + Enter to check

    Ai Sql Injection in TypeScript

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