Find the SQL Injection Sink: Spotting String-Built SQL in Node.js
Le défi
Cette route Node construit une requête SQL en collant l'entrée utilisateur directement dans la chaîne. Un appel exécute ce SQL non filtré contre la base de données. Lisez les deux fichiers et tapez le nom de cet appel.
Ce que tu vas apprendre
- Recognise string-concatenated SQL on user input as a SQL injection sink
- Trace a request parameter from its entry point to the call that executes it
- Tell apart a concatenated query (db.query) from a parameterised one (db.execute with placeholders)
- Read two similar routes and pick out which one is exploitable
- Explain why bound parameters defeat injection
Compétences testées
Prérequis
- Basic JavaScript reading
- Familiarity with HTTP query parameters
- What a SQL SELECT looks like
Comment ça marche
SQL injection happens when untrusted input is mixed into the text of a SQL statement instead of being sent as data. The database parser cannot tell which part of the string was meant as a value and which part is a command, so an attacker who controls the value can change the meaning of the whole query.
In users.js the route reads req.query.id and builds the statement with "SELECT ... WHERE id=" + id, then runs it with db.query(sql). A benign ?id=42 works fine, which is why the bug ships. But ?id=0 OR 1=1 makes the WHERE clause always true and dumps every user, and a UNION SELECT payload can read other tables or even credentials. The value was never escaped, so it is interpreted as SQL.
The fix sits in orders.js. It uses a placeholder ? and passes the value in a separate array: db.execute(sql, [req.query.user]). The driver binds the value as a typed parameter, so it can never break out of its slot and become SQL. Spotting the bug in review means following the user value and noticing it lands inside the query string rather than the parameter list.
Erreurs fréquentes
- Blaming req.query.id. Reading input is fine; the vulnerability is concatenating it into SQL and executing it. Name the call that runs the query.
- Picking db.execute. That is the safe, parameterised route - it binds values, so it is not the sink.
- Assuming an integer id is safe. Nothing validates that
idis numeric; it is a raw string from the URL. - Thinking an ORM or framework auto-escapes this. Raw concatenation bypasses any protection the driver offers.
Comment s'en protéger
Never build SQL by concatenating user input. Always pass values as bound parameters so the database treats them as data, not code.
- Use parameterised queries or prepared statements (
db.execute(sql, [value])) for every user-supplied value. - Validate and type-cast inputs (e.g. coerce an id to a number) before they reach the data layer.
- Apply least-privilege database accounts so an injected query cannot read unrelated tables.
- Add a lint or code-review rule that flags string concatenation next to
db.query.