vulnerable patterns

Overview

Now that you know a query mixes code and data, this lesson shows the concrete coding patterns that make SQL injection possible. The common thread is always the same: user input is concatenated directly into a SQL string, so specially crafted input escapes the data slot and changes the query's meaning. Recognizing these patterns — in a CTF challenge or in real code you are reviewing — is how you spot an injection point. We keep everything in an authorized, isolated practice frame: the goal is to understand why the pattern is dangerous so you can identify and fix it.

Learning objectives

• Identify the string-concatenation pattern that causes SQL injection.
• Explain how a crafted value breaks out of a string literal.
• Recognize classic injection payloads and why they work at the query level.
• Describe common contexts (login, search, numeric id) where the pattern appears.

Core concept

The vulnerable pattern is any code that assembles a SQL statement by inserting untrusted input into the query text as if it were part of the SQL. In pseudo-code:

query = "SELECT * FROM users WHERE username = '" + username + "'"

Because the database parses the finished string as a whole, any SQL syntax the attacker sneaks into username is parsed as code. The vulnerability is not in SQL itself — SQL is working exactly as designed. The flaw is in how the application builds the string: it fails to keep user data separate from query code.

The same weakness appears whether the input goes into a WHERE clause, an ORDER BY, an INSERT, or anywhere else, and regardless of language. If untrusted input is glued into the query text, the pattern is vulnerable.

How it works

Start from the intended query:

SELECT * FROM users WHERE username = 'alice';

Suppose the attacker sets the username to:

' OR '1'='1

Concatenation produces:

SELECT * FROM users WHERE username = '' OR '1'='1';

Trace what happened:

1. The leading ' closes the string literal that was meant to hold the username.
2. OR '1'='1' is now parsed as code — an additional boolean condition.
3. '1'='1' is always true, so the WHERE clause matches every row.

The attacker's data broke out of the data slot and rewrote the query's logic. Other classic examples work the same way:

• Comment out the rest. Input admin'-- turns the query into ... WHERE username = 'admin'--', where -- starts a SQL comment that discards everything after it (such as a password check).
• Numeric context (no quotes). If an id is inserted without quotes — SELECT * FROM items WHERE id = + input — then input 1 OR 1=1 yields WHERE id = 1 OR 1=1, matching all rows without needing to break any quote at all.
• Stacked or UNION-based reads. Input can append UNION SELECT ... to pull data from other tables into the results, when the query shape allows it.

In every case the mechanism is identical: input crosses the code/data boundary because the query was built by concatenation.

Practical example

In an authorized CTF login challenge, the target app builds:

query = "SELECT * FROM users WHERE username = '" + user + "' AND password = '" + pass + "'"

You submit the username admin'-- and any password. The assembled query becomes:

SELECT * FROM users WHERE username = 'admin'--' AND password = 'x';

The -- comments out AND password = 'x', so the database only checks username = 'admin' and returns the admin row — the password condition never runs. The challenge flag might be displayed once you are "logged in" as admin. You solved it not by guessing a password but by recognizing the concatenation pattern and supplying input that broke out of the username string and neutralized the password check.

[!NOTE] This works only because the practice app was built with the vulnerable pattern on purpose, in an isolated lab. The same input against a system you are not authorized to test would be illegal. The lesson is the pattern, so you can find and eliminate it.

Cybersecurity relevance

These patterns are exactly what security testers look for and what secure-code reviewers flag. During code review, seeing untrusted input concatenated into a query string is an immediate red flag — the fix is to stop concatenating and use parameterization (next lesson). During testing, probing an input with a single quote and watching for errors or changed behavior is the classic way to detect a possible injection point. Understanding why payloads like ' OR '1'='1 work — not just memorizing them — lets you reason about novel contexts (numeric fields, ORDER BY, LIKE patterns) and recognize the vulnerability wherever it hides.

Common mistakes

• Trying to "sanitize" by escaping quotes manually. Ad-hoc escaping is fragile; encodings, numeric contexts, and edge cases slip through. Parameterization is the real fix.
• Assuming only login forms are vulnerable. Any concatenated input — search, sorting, filters, ids, headers — can be an injection point.
• Thinking numeric fields are safe. Unquoted numeric contexts are often easier to exploit because no quote needs to be broken.
• Blocklisting keywords like OR or UNION. Blocklists are bypassable and miss the real problem: input is being treated as code.

Key takeaways

• The vulnerable pattern is concatenating untrusted input directly into a SQL string, letting input become code.
• Payloads like ' OR '1'='1, admin'--, and 1 OR 1=1 work by closing the data slot (or exploiting an unquoted one) and injecting new SQL logic.
• The flaw is in how the query is built, not in SQL — so it appears in logins, searches, numeric ids, sorting, and more.
• Recognizing the pattern is the detection skill; the cure is parameterization, covered next.

Check your understanding

• Walk through why the input ' OR '1'='1 causes WHERE username = '' OR '1'='1' to match every row.
• Why can an unquoted numeric field be injected without using any quote character at all?
• Why is manually escaping quotes an unreliable defense compared with changing how the query is built?