Detect SQL-injection metacharacters in user input

Overview

Imagine a login form. The user types a username, and your program builds a database question like "find the row where username equals what they typed." If you build that question by gluing the user's text directly into the SQL command, a clever attacker can type text that changes the command itself rather than just answering it. That attack is called SQL injection, and it is one of the most common and damaging web vulnerabilities in history.

The real, correct cure is a parameterised query (also called a prepared statement): you send the SQL command and the user's data to the database on separate channels, so the data can never be read as code. You will practice that separately. This lesson teaches a complementary skill: a metacharacter detector that walks the input once and raises a flag when it sees characters that have special meaning to a SQL engine, such as a quote or a comment marker.

This builds directly on C strings: you are scanning a NUL-terminated char array one byte at a time, comparing characters, and looking for short substrings. Everything you learned about where a string ends (the '\0' terminator) and how to walk it safely applies here. In plain language first: you are a security guard reading names at a door and noticing anyone whose "name" is actually a set of instructions. In terminology: you are implementing an input validation heuristic that detects SQL metacharacters and injection markers as a defence-in-depth control.

Why it matters

SQL injection has topped vulnerability lists for over two decades. A single unguarded query can let an attacker read every password hash in a database, delete tables, or bypass a login entirely. Understanding why string-built queries fail is essential for any programmer who touches a database.

Parameterised queries are the proper fix, and a metacharacter detector does not replace them. What the detector adds:

• Defence in depth at the input layer, before data ever reaches the query layer. If one control is misconfigured, another still stands.
• Observability: flagged inputs become log signal. A spike of inputs containing UNION SELECT is a live probe against your app, and you want to see it.
• Early rejection: obviously hostile input can be dropped at the front door instead of travelling deep into the system.

Learning to write this detector also trains the security mindset: treat all external input as untrusted, look for the specific tokens attackers rely on, and fail safe.

Core concepts

1. SQL injection: data that becomes code

Definition. SQL injection is a vulnerability where untrusted input is placed into a SQL statement in a way that lets the input change the statement's structure or meaning.

Plain-language explanation. Suppose your code builds this query by concatenation:

SELECT * FROM users WHERE name = '<INPUT>';

If a user types alice, the query becomes harmless:

SELECT * FROM users WHERE name = 'alice';

But if the user types ' OR '1'='1, the query becomes:

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

The single quote closed the string early, and OR '1'='1' is always true, so the query returns every user. The data crossed the line from value to code.

How it works internally. The database receives one flat string of SQL text and parses it into a command. It has no way to know which characters came from your program and which came from the user, because they were merged into the same string before parsing. The quote character is the pivot: it is the boundary marker between "still inside a string literal" and "back to SQL keywords."

When to defend this way / when not. Always defend at the query layer with parameterised queries. Use a metacharacter detector as an extra layer and for logging, never as your only defence.

Common pitfall. Believing that removing or escaping bad characters makes concatenation safe. Escaping is fragile and easy to get wrong; the robust answer is to never concatenate user data into SQL at all.

Knowledge check. In SELECT * FROM users WHERE name = '<INPUT>';, which single character does the attacker most need to escape the value and start writing SQL code?

2. Metacharacters and injection markers

Definition. A metacharacter is a character that has special meaning to the SQL parser rather than being ordinary data. An injection marker is a short token attackers commonly use to steer a query.

The signals this lesson's detector looks for:

How it works internally. Your scanner reads the input byte by byte. For single-character markers (', ;) you compare one char. For two-character markers (--, /*) you check the current character and the next one. For the keyword pair UNION SELECT you match a longer substring, ignoring case because SQL keywords are case-insensitive.

When to use / when not. Detect-and-reject is appropriate for fields that should never contain these characters (usernames, numeric IDs). It is not appropriate to blanket-reject on fields where such characters are legitimate data (a comment box may legitimately contain an apostrophe). That is exactly why parameterised queries — not blacklists — are the real fix.

Common pitfall. Case sensitivity. union select, UNION SELECT, and UnIoN sElEcT are all equivalent to the database. A naive strstr for the exact uppercase form misses the lowercase attack.

input:  a d m i n ' -- \0
         ^                  scan position moves left to right
                ^  single quote  -> FLAG
                    ^ ^  '--'   -> also would FLAG
return 1 as soon as any marker is found (early exit)

Knowledge check. Why must the UNION SELECT check fold case, when the ' check does not?

3. Flagging vs. sanitising

Definition. Flagging means classifying input as suspicious and letting the caller decide (reject, log, alert). Sanitising means altering the input to try to make it safe.

Plain-language explanation. This function is a smoke detector, not a fire extinguisher. It reports; it does not repair. Sanitising by stripping characters produces subtle bugs and can be bypassed (for example, an attacker nests admin'-- inside text that survives a naive filter). Rejecting outright is simpler and safer for fields that should be clean.

How it works internally. The function returns 1 (suspicious) or 0 (clean) and never writes to the input. The caller then chooses policy.

When to use / when not. Flag-and-reject at boundaries where the character set is known and narrow. Do not use flagging to "clean" free-text and then concatenate it — that path always leads back to injection.

Common pitfall. Treating a 0 return as "this input is safe to concatenate." It only means "no known markers found." Safe query construction still requires parameterisation.

Knowledge check. Explain in your own words the difference between a detector that returns 1/0 and a sanitiser that rewrites the string. Which one belongs in front of a parameterised query?

Syntax notes

Key building blocks. Scan with pointers, compare chars, and check the next byte only after confirming the current one is not the terminator.

#include <ctype.h>   // tolower
#include <string.h>  // strlen, strncmp

// Case-insensitive compare of the first n bytes of a and b.
static int ci_startswith(const char *a, const char *b, size_t n) {
    for (size_t i = 0; i < n; i++) {
        // cast to unsigned char before tolower: passing a negative
        // char to tolower is undefined behaviour.
        int ca = tolower((unsigned char)a[i]);
        int cb = tolower((unsigned char)b[i]);
        if (ca != cb) return 0;
        if (a[i] == '\0') return 0; // a ended early
    }
    return 1;
}

Notes:

• Look-ahead pattern for two-character markers: at position i, only read s[i+1] after you know s[i] != '\0'.
• tolower((unsigned char)c) — always cast; a plain char may be signed and negative, which is undefined for the ctype.h functions.
• Return early the moment a marker is found; there is no need to keep scanning.

Lesson

Why this matters

The real defence against SQL injection is parameterised queries (also called prepared statements): you send the SQL and the user data separately, so the data can never be interpreted as code.

But on the way to that, defenders write input validators that flag suspicious metacharacters early, before the input reaches the query layer. A metacharacter is a character that has special meaning to the SQL engine, such as a quote or a comment marker.

This exercise teaches the metacharacter detector. The proper cure, prepared statements, is mentioned here but practiced separately.

What the function detects

• single quotes (') — the classic injection delimiter
• SQL comment markers (-- and /*)
• statement terminators (;)
• common UNION-injection keywords (UNION SELECT, matched case-insensitively)

Your job

Implement:

int has_sqli_markers(const char *input);

Return:

• 0 — input contains no suspicious patterns
• 1 — input contains at least one suspicious pattern

The function is a flagger, not a sanitiser. Real defence is parameterised queries. This function just tells the application: "this input deserves a second look."

What this is NOT

• A SQL parser. You flag metacharacters; you do not parse intent.
• A replacement for prepared statements. It is one layer, not the cure.

Code examples

#include <stdio.h> #include <ctype.h> #include <string.h>

/* Case-insensitive check: does the text at p begin with the C-string kw? */ static int ci_prefix(const char *p, const char kw) { for (size_t i = 0; kw[i] != '\0'; i++) { if (p[i] == '\0') return 0; / input ended first / if (tolower((unsigned char)p[i]) != tolower((unsigned char)kw[i])) return 0; / mismatch */ } return 1; }

/*

• Flag input that contains classic SQL-injection markers.

• Returns 1 if any marker is present, otherwise 0.

• The input is never modified: this detects, it does not sanitise. */ int has_sqli_markers(const char input) { if (input == NULL) return 0; / nothing to scan */

  for (size_t i = 0; input[i] != '\0'; i++) { char c = input[i]; if (c == ''' || c == ';') return 1; /* quote or terminator / / two-char markers: safe to read input[i+1] because input[i] * is not the NUL, so i+1 is at worst the NUL itself. / if (c == '-' && input[i + 1] == '-') return 1; / -- comment / if (c == '/' && input[i + 1] == '') return 1; /* /* comment / / keyword probe, case-insensitive / if (ci_prefix(&input[i], "union select")) return 1; } return 0; / clean */ }

int main(void) { const char samples[] = { "alice", "' OR '1'='1", "admin'--", "bob; DROP TABLE users", "1 UNION SELECT password FROM users", "o'brien", / legitimate apostrophe still flags */ NULL };

for (int i = 0; samples[i] != NULL; i++) {
    printf("%-38s -> %d\n",
           samples[i], has_sqli_markers(samples[i]));
}
return 0;

}

**What it does.** `has_sqli_markers` scans a NUL-terminated string once. It returns `1` the instant it finds a single quote, a semicolon, a `--` or `/*` comment start, or the keyword pair `union select` (any case). Otherwise it returns `0`. The `main` runs six sample strings through it.

**Expected output:**

```text
alice                                  -> 0
' OR '1'='1                            -> 1
admin'--                               -> 1
bob; DROP TABLE users                  -> 1
1 UNION SELECT password FROM users     -> 1
o'brien                                -> 1

Edge cases. A NULL pointer returns 0 (nothing to flag) rather than crashing. The legitimate name o'brien is flagged too — a reminder that blacklists produce false positives, which is why this is a layer, not the cure. An empty string "" returns 0.

Line by line

1. ci_prefix(p, kw) compares the text starting at p against keyword kw, one character at a time, folding case with tolower. If the input ends ('\0') before the keyword does, it returns 0 (no false match past the string end). It returns 1 only when every keyword character matches.
2. In has_sqli_markers, the NULL guard returns 0 immediately so we never dereference a null pointer.
3. The for loop condition input[i] != '\0' stops exactly at the terminator — we never read past the string.
4. c == '\'' || c == ';' catches the two single-character markers and returns at once.
5. For -- and /*, we read input[i + 1]. This is safe because the loop guarantees input[i] is not the NUL; therefore i + 1 indexes at worst the NUL byte itself, which is a valid position, not past the end.
6. ci_prefix(&input[i], "union select") probes for the keyword starting at the current position, case-insensitively.
7. If the whole loop finishes with no marker found, we return 0.

Trace for input admin'--:

The scan stops at position 5 the moment the quote appears; the -- is never reached because the function already returned.

Common mistakes

Mistake 1 — Case-sensitive keyword match.

if (strstr(input, "UNION SELECT")) return 1;   // WRONG: misses lowercase

Why it is wrong: SQL keywords are case-insensitive, so union select slips through. Corrected: fold case during the compare (the ci_prefix helper). Recognise it by testing your detector with a lowercase payload — if 1 union select x returns 0, you have this bug.

Mistake 2 — Reading past the terminator when checking two-char markers.

for (size_t i = 0; i <= strlen(input); i++)      // WRONG boundary
    if (input[i] == '-' && input[i+1] == '-') ...  // reads past end

Why it is wrong: when i is the last valid index, input[i+1] is the NUL (fine), but with a <= loop i can reach the NUL and input[i+1] reads one byte past the array. Corrected: loop while input[i] != '\0' so the current byte is always a real character and i+1 is at worst the NUL. Recognise it with AddressSanitizer or Valgrind reporting a heap/stack overread.

Mistake 3 — Passing a raw char to tolower.

if (tolower(input[i]) == tolower(kw[i])) ...   // WRONG on signed char

Why it is wrong: char may be signed; a byte like 0xE9 becomes negative, and tolower is only defined for unsigned char values and EOF. Corrected: tolower((unsigned char)input[i]). Recognise it as crashes or garbage results on non-ASCII input.

Mistake 4 — Trying to sanitise instead of reject.

// WRONG mindset: strip quotes then concatenate into SQL
remove_quotes(input);
sprintf(query, "... WHERE name = '%s'", input);

Why it is wrong: character-stripping blacklists are routinely bypassed, and the query is still built by concatenation. Corrected: reject flagged input and use a parameterised query for the rest. Recognise it whenever you see user data glued into a SQL string.

Debugging tips

Compiler errors.

• implicit declaration of function 'tolower' — you forgot #include <ctype.h>.
• comparison always false warnings on the quote check usually mean you wrote '\'' incorrectly; the character literal for a single quote is '\''.

Runtime errors.

• A crash or ASan "heap-buffer-overflow"/"stack-buffer-overflow" almost always means a look-ahead read (input[i+1]) ran past the string. Confirm your loop stops at the NUL, not after it. Build with -fsanitize=address -g and rerun.
• A crash on the very first call may be a NULL input; verify the null guard is present.

Logic errors.

• Detector returns 0 for an obvious payload: print each character and the check result inside the loop, or test the smallest failing input (e.g. just "'"). If lowercase keywords slip through, your compare is case-sensitive.
• Detector returns 1 for clean input: check for an off-by-one in ci_prefix that matches a shorter prefix than intended.

Questions to ask when it doesn't work.

• Does the loop condition guarantee input[i] is a real character before I read input[i+1]?
• Am I casting to unsigned char before every ctype.h call?
• Have I tested empty string, NULL, lowercase keywords, and a legitimate apostrophe?

Memory safety

This is a security lesson, so both memory safety and injection defence matter.

Memory / undefined behaviour for this code:

• Bounds / overreads. The only risky reads are the two-character look-aheads input[i + 1]. They are safe only because the loop condition proves input[i] is not the NUL, so i + 1 is a valid index (at worst the NUL). Never combine a look-ahead with a <= or strlen-based loop that lets i sit on the terminator.
• NUL termination. The scanner trusts that input is a proper C string. If a caller hands you a non-terminated buffer, the loop runs off the end — a classic overread. In real code, prefer a length-bounded variant that also takes a size_t len.
• ctype.h domain. Always cast to unsigned char before tolower/isalpha/etc. Passing a negative char is undefined behaviour.
• No writes, no ownership. The function takes const char * and never modifies or frees the input, so there are no lifetime or ownership hazards here.

Defensive / injection practices:

• The vulnerability (labelled): building SQL by concatenation — sprintf(q, "... name='%s'", user) — is injectable. The fix: a parameterised query where the driver binds user as data (e.g. a ? placeholder), so it is never parsed as SQL.
• Validate at the boundary. Use this detector to reject input on fields with a narrow character set, and log every flag.
• Least privilege. The database account your app uses should have only the rights it needs (no DROP, no access to unrelated tables), limiting damage if a query is ever abused.
• Fail safe. On any doubt, reject and log rather than "clean and proceed." This is lab/education material — never point injection tests at systems you do not own.

Real-world uses

Concrete use cases.

• Web application firewalls (WAFs) run metacharacter and keyword heuristics much like this one to flag and block probes before requests reach the app.
• API gateways and input-validation middleware reject malformed identifiers (usernames, slugs, numeric IDs) at the edge.
• Security logging / SIEM pipelines count flagged inputs to detect scanning and injection campaigns in near real time.
• Database access layers in professional apps pair strict input validation with parameterised queries as defence in depth.

Professional best-practice habits.

Beginner:

• Never concatenate user input into SQL; use parameter binding from day one.
• Give functions clear names (has_sqli_markers) and comment only the non-obvious lines.
• Test with adversarial inputs: quotes, comments, lowercase keywords, empty string, NULL.

Advanced:

• Prefer length-bounded scanning (const char *s, size_t len) so the code is safe even on non-NUL-terminated data.
• Treat blacklists as signal, not security: measure false-positive/negative rates and keep parameterisation as the real control.
• Enforce least-privilege database accounts and centralise validation so every entry point is covered consistently.
• Feed flags into monitoring so a probe becomes an alert, not just a dropped request.

Practice tasks

Beginner 1 — Single-quote and semicolon flag. Objective: write int has_quote_or_semicolon(const char *s) returning 1 if s contains ' or ;, else 0. Requirements: single pass; stop at the NUL; handle NULL by returning 0. Example: "alice" -> 0, "a'b" -> 1, "a;b" -> 1. Hint: one for loop, two comparisons. Concepts: string scanning, char comparison.

Beginner 2 — Two-character comment markers. Objective: extend the scan to also flag -- and /*. Requirements: use a safe look-ahead — only read s[i+1] after confirming s[i] != '\0'. Example: "admin--" -> 1, "a/*b" -> 1, "a-b" -> 0. Constraint: no reads past the terminator. Hint: check s[i] first, then s[i+1]. Concepts: look-ahead, bounds care.

Intermediate 1 — Case-insensitive keyword. Objective: add detection of union select in any case. Requirements: implement a ci_prefix-style helper using tolower((unsigned char)...); match the two-word keyword with a single space between the words. Example: "1 UNION SELECT x" -> 1, "1 union select x" -> 1, "reunion selecting" -> ? (decide and justify). Hint: match the exact substring "union select". Concepts: case folding, substring match.

Intermediate 2 — Length-bounded, safer version. Objective: write int has_sqli_markers_n(const char *s, size_t len) that scans at most len bytes and does not require NUL termination. Requirements: never read s[i] for i >= len; guard the look-ahead with i + 1 < len. Example: same flags as before, but safe on a non-terminated buffer. Hint: replace s[i] != '\0' with i < len. Concepts: bounded scanning, memory safety.

Challenge — Detector plus safe-query demo. Objective: build a small program that (a) validates a username with your detector and rejects+logs it if flagged, and (b) shows, in comments or pseudocode, how the accepted username would be passed to a parameterised query rather than concatenated. Requirements: print a clear ACCEPT/REJECT decision and the reason; keep all data lab-only; do not connect to a real database. Constraints: the parameterised path must never build SQL by string concatenation. Hint: model the query as "SELECT * FROM users WHERE name = ?" with the value bound separately. Concepts: layered defence, flag-vs-sanitise, parameterisation.

Summary

SQL injection happens when untrusted input is merged into a SQL command and gets parsed as code instead of data. The proper cure is the parameterised query, which sends command and data on separate channels. This lesson adds a complementary layer: a metacharacter detector.

Key ideas:

• Scan the input in a single pass, stopping at the '\0' terminator.
• Flag ', ;, --, /*, and union select (case-insensitively).
• Flag, do not sanitise — return 1/0 and let the caller reject and log; never strip characters and concatenate.

Most important syntax: guard the two-character look-ahead by looping while input[i] != '\0' so input[i+1] is always in bounds, and always cast to unsigned char before tolower.

Common mistakes: case-sensitive keyword matching, reading past the terminator, un-cast ctype.h calls, and the mindset trap of sanitising instead of rejecting.

Remember: this detector is one layer of defence in depth and a source of log signal. Parameterised queries plus least-privilege database accounts remain the real defence.

Practice with these exercises

• Detect SQL metacharacters
• Escape single quotes
• Flag SQL-injection metacharacters in input