Find the SQL Injection Sink: Spotting String-Built SQL in Node.js
O desafio
Esta rota Node monta uma consulta SQL colando a entrada do usuário direto na string. Uma chamada executa esse SQL não filtrado contra o banco. Leia os dois arquivos e digite o nome dessa chamada.
O que você vai aprender
- 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
Habilidades testadas
Pré-requisitos
- Basic JavaScript reading
- Familiarity with HTTP query parameters
- What a SQL SELECT looks like
Como funciona
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.
Erros comuns
- 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.
Como se proteger
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.