Level: intermediate. You should be comfortable reading a small application in Python, PHP or Java and know what a database query is. This explains the mechanism, then works through the seven patterns that keep causing SQL injection in real codebases, with the fix for each. It is not an exploitation guide. New to the subject? Read how an attack chain unfolds first.

SQL injection is a vulnerability, not an attack technique. It happens when data a user supplied is pasted into the text of a query instead of being passed to the database as a value. The database cannot distinguish SQL syntax the developer wrote from SQL syntax the visitor typed, so it executes both. The OWASP SQL Injection Prevention Cheat Sheet is worth keeping open while you work, and PortSwigger’s Web Security Academy covers the mechanism in more depth.

How Untrusted Input Reaches a Query

Every case has the same three ingredients. There is a query. There is input the developer did not write. And somewhere between them the two are combined into a single string before the database sees it. That last point is the whole issue. When a driver sends a statement with the address supplied as a separate parameter, the database sees a literal string and returns rows. When your code assembles the whole sentence first and passes it over as text, the parser receives a sentence you did not intend, and its job is to execute it.

Two consequences follow. The query can return data the developer never meant to expose, and it can be changed into a different query entirely. The same flaw affects INSERT and UPDATE, stored procedures, and ORM helpers that accept raw fragments. Escaping and keyword filtering are weaker than they look: blacklists of “dangerous” words are defeated by encoding variations.

The Seven SQL Injection Patterns to Avoid

These are the recurring shapes in production code. Each is a design mistake with a specific, well-understood fix.

Pattern	Why it is dangerous	The fix
1. String concatenation	The classic case, still common in legacy code	Parameterised query
2. String interpolation	Looks tidier, identical in effect	Parameterised query
3. Dynamic ORDER BY	Identifiers cannot be parameterised	Map names to a fixed column list
4. Dynamic LIKE patterns	Wildcards and escapes handled by the engine	Escape properly, or use full-text search
5. Second-order injection	Stored data re-enters a query later	Parameterise on read, validate on write
6. Dynamic IN clauses	A list must become part of the statement	Fixed number of placeholders
7. NoSQL and ORM equivalents	Different syntax, same mistake	Typed operators, ORM parameter binding

Patterns 1 and 2: concatenation and interpolation

These are one mistake in two syntaxes. Concatenation joins a variable onto a string with the language’s string operator. Interpolation drops the variable into a template that reads like code, which feels safer and is exactly as dangerous.

The fix is to pass the value as a parameter. In a Python database API you write the statement with placeholders and a tuple of values; in PHP PDO you name the placeholder and bind it; in JDBC you use a PreparedStatement with question marks. The driver, not your code, places the value into the query, in a form the parser cannot mistake for syntax. Such a query stays safe whatever the value contains, so you never have to enumerate the inputs that might be dangerous.

Pattern 3: dynamic ORDER BY and column names

You cannot parameterise an identifier, only a value. That limitation is why this pattern is so common: the sort column arrives from the query string and the obvious handling is to paste it in.

The fix is an allow-list that never reaches the database as caller-controlled text. Take a short name from the request, look it up in a map you wrote, and use the column name from your side. A request for sort=name becomes ORDER BY last_name; anything not in the map is rejected. The same approach covers dynamic column selection in reports.

Pattern 4: dynamic LIKE and search

Search boxes invite concatenation, and LIKE queries tempt developers into building the pattern themselves. Two things go wrong. Wildcards the user typed behave as operators, and the escape character is not handled the way the developer assumed.

Two defensible options. Escape the wildcard and percent characters in the user’s term so the search is literal, or, better for anything user-facing, use the database’s full-text search features, which were designed for this and can use an index. The OWASP Database Security Cheat Sheet gives the escaping rules per engine.

Pattern 5: second-order injection

This one survives a codebase-wide fix, because the dangerous query is not the one that wrote the data. A display name is validated on the way in, stored safely, and months later a different feature builds a report by pasting those stored values into a query. It was trusted because it was already in the database.

Being in the database proves nothing. Anything writable that is later read into a query is a path. Validate on write, and parameterise on read as well.

Pattern 6: dynamic IN clauses and lists

“Fetch these twenty selected items” produces a list of identifiers, and lists are awkward to parameterise because the count is unknown when the statement is prepared. Concatenating the list is the common shortcut, and it is genuinely dangerous.

The safe route is a fixed number of placeholders, one per possible position, with unused positions filled by a value that cannot match. Where the database supports it, a table-valued parameter avoids the question. This pattern is also a route to a denial of service through an oversized list, so cap the length regardless.

Pattern 7: NoSQL and ORM equivalents

The same injection mistake survives the move to other data stores. A NoSQL query takes an object describing what to match, so code that copies request data straight into that object lets a visitor choose the operator. A value expected to be a string arriving as an object is the same injection in different clothes.

ORMs reduce the risk because their builders parameterise by default, but they keep escape hatches for exactly the identifiers described above. Raw fragments, literal SQL, and helpers that accept a whole dictionary of conditions are where a codebase reintroduces the problem. Any helper whose argument is a string of query text should never receive user input.

Defence in Depth After the Fix

Parameterised queries close the injection vector. Three further controls limit the damage if something slips through.

Least privilege comes first, because it most changes the outcome of an injection. The database account an application uses should not be able to drop tables, read every row or run administrative commands. Use separate accounts for the application, for migrations and for reporting, each with the minimum it needs.

Second, run static analysis in continuous integration. Tools flag string-built queries across several languages and catch the regression where it is written, the only moment it is cheap. The OWASP Web Security Testing Guide covers what static analysis misses.

Third, a web application firewall, treated honestly as a backstop that reduces noisy scanning and buys time. Rules are bypassable, and a WAF cannot reason about whether a query is correct in context.

Testing Your Own Application Safely

Test only code you own or have written permission to test. Unauthorised access to a computer system is an offence under the UK Computer Misuse Act 1990 and the Nigerian Cybercrimes Act 2015, and sending test strings at someone else’s login form is not harmless curiosity. A scope note naming the hosts, accounts and test window removes the ambiguity.

Within that permission, start with the codebase rather than a scanner. Search for query-building patterns: concatenation near SELECT, INSERT and UPDATE, and any helper that takes a fragment of SQL. Read each one and trace which inputs reach it. This finds the same class of bug a scanner reports, and adds the data flow a scanner cannot see.

Then confirm the fix. A vulnerable query behaves differently from a parameterised one when the input contains a quote or a comment marker, so a single test input containing each character is a reasonable smoke test on a system you own. Check that the application treats it as data and that the database log shows one statement rather than a modified one.

Frequently Asked Questions

Is SQL injection still common?

It remains in the OWASP Top Ten and is regularly found in long-lived applications, particularly where older code, stored procedures and reporting tools build SQL by hand. It also appears in newer systems wherever sort orders or list filters must be dynamic, exactly where parameterisation cannot be applied directly.

Do stored procedures prevent injection?

No. A procedure that assembles a statement with concatenation inside it is as injectable as the application code it replaced. Procedures help when they accept typed parameters and build no text.

Are ORMs completely safe?

They are much safer by default because their query builders parameterise automatically, but not immune. Injection returns through raw fragments, literal SQL and helpers that take a dictionary of conditions. Review how your project uses those escape hatches.

Does a web application firewall fix this?

No. A WAF filters known bad patterns at the edge and is useful as a temporary mitigation, but rules are bypassable and lag behind new techniques. It complements parameterised queries rather than replacing them.

What about escaping quotes in user input?

Escaping can be made to work, but it depends on the database, the connection mode and the character set, and a mistake leaves a hole you will not notice. Parameterised queries move that escaping into the driver, which is why they are the recommended control.

Key Takeaways

• SQL injection happens when input becomes part of a query’s text rather than a value passed to it.
• Parameterised queries are the fix, and they stay safe whatever the value contains.
• Identifiers, sort columns and lists cannot be parameterised, so map names to a fixed allow-list.
• Data already stored is not trusted. Parameterise on read and validate on write.
• Run the application’s database account with the least privilege it can survive losing.
• Treat a WAF and static analysis as backstops, not as the control that closes the hole.
• Test only systems you own or have written permission to test, and record the scope first.

If the queries you are protecting sit behind an application interface, read the API security vulnerabilities developers must fix next, since this is only one of the ways a request can go wrong.

Sources: OWASP SQL Injection Prevention Cheat Sheet; OWASP Database Security Cheat Sheet; PortSwigger Web Security Academy, SQL injection; OWASP Web Security Testing Guide.