Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 53 ยท Web Application Security

SQL Injection: Understanding and Preventing It

Understand how SQL injection happens, compare vulnerable and parameterized queries on a toy database, and learn ORM caveats, least privilege and log detection.

SQL injection happens when an application builds a database query by gluing user input into a string, so the input can change the structure of the query instead of being treated as plain data. It has been on top-risk lists for over two decades because the cause is so simple and the damage can be so large: reading other users' records, bypassing logins, changing data, and in bad setups reaching the database server itself.

It is also one of the most completely preventable bugs. In this lesson you will see exactly how the flaw arises on a made-up database, how parameterized queries remove it, where ORMs still leave holes, and what an attack looks like in a log. Everything runs against a small in-memory SQLite database inside your browser, with fake data. No real application or server is involved, and you should never test injection against a system you are not authorized to test. If you need SQL basics first, see the SQL tutorial.

Code or data?

Input The user types some text.
Concatenation The app glues it into SQL.
Injection The text becomes part of the code.

The bug is mixing data into the code channel.

1 / 2

How the flaw happens

Imagine a login check that builds its query like this (shown as pseudocode, read-only):

TEXT
query = "SELECT * FROM users WHERE name = '" + name + "' AND password = '" + pw + "'"

If name is alice, the query is what the developer expected. But a quote character in the input ends the string literal early, and whatever follows is parsed as SQL. The database cannot tell which characters the developer wrote and which the visitor typed, because by the time it receives the text, they are one string.

That is the general shape of every injection bug, whether the target is SQL, a shell command, an LDAP filter or a template: untrusted data and code are mixed in one channel. The fix is therefore always to separate them again.

A toy database and a vulnerable lookup

The stand-in below creates an in-memory SQLite database with three fake users and a vulnerable function that formats input into the query. It then tries a normal name, a name with an apostrophe (which is a perfectly legitimate input, like O'Brien), and an input crafted so that its meaning changes the query. The crafted input is a classic harmless textbook example: it makes the condition always true. The data is fake and the database exists only for this run.

Python Vulnerable query on a toy database

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the three runs. The normal input returns one row. O'Brien makes the query invalid, so the database raises an error: the apostrophe broke the string literal. This is the first symptom developers see, and it is itself a clue that input is being concatenated. The third input produces a query whose WHERE clause is true for every row, so all users come back, including the admin. The attacker never needed to know any names.

Real attacks go further than this simple example, for instance combining results from other tables or inferring data one bit at a time from true/false behaviour. You do not need those techniques to defend: the same fix stops all of them.

The fix: parameterized queries

A parameterized query (also called a prepared statement) sends the SQL text with placeholders, and the values separately. The database parses the structure first and then plugs in the values as pure data, so no value can ever add keywords or quotes to the structure.

Python The same lookups, parameterized

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Now O'Brien works as the legitimate name it is, and the crafted text is simply a name that nobody has, so it returns no rows. The second half of the example covers a gap that placeholders cannot fill: identifiers such as column names, table names and sort directions cannot be bound as parameters. When an application lets users choose a sort column, map their choice through an allow-list (here a dictionary) and use only the value you wrote.

Real vs stand-in. SQLite's driver here is real and its ? placeholders work the same way as in other languages (?, :name, $1 or %s depending on the driver). The injected text is a textbook example on fake data. Production databases differ in dialect and in what an injection can reach, but the prevention is identical.

ORM caveats

Object-relational mappers such as Eloquent, SQLAlchemy, Hibernate and ActiveRecord use parameters internally for normal operations such as where('name', $name). They are not magic, though. Injection still appears when:

  • Raw fragments are built from input, for example whereRaw("name = '$name'") or a string passed to an order by helper.
  • Dynamic column or table names come from the request without an allow-list.
  • Search helpers interpolate user text into LIKE patterns inside raw SQL.
  • Stored procedures build dynamic SQL internally from their arguments.

The rule in review is simple: any raw SQL helper that receives a variable needs to take it through a binding mechanism or an allow-list. Grep for raw, execute(f", string + near SQL keywords and format( as part of a code review. See Secure Coding and DevSecOps for more review checklists.

Limit the damage: least privilege

Assume that someday a bug slips through. The database account the web application uses decides how bad it gets. A good setup:

  • Uses a dedicated account for the app, never root or sa.
  • Grants only the needed rights on only the needed tables (often SELECT, INSERT, UPDATE and rarely DELETE).
  • Keeps schema changes (CREATE, DROP, ALTER) in a separate migration account.
  • Blocks file and command features of the database server and does not expose the database to the internet.
  • Stores passwords as slow hashes, as in Password Attacks and Defenses, so a leak of the users table is not a leak of passwords.

None of this removes injection. It only reduces what an attacker gets from one.

Detection in logs

Injection attempts leave traces in places defenders can watch:

  • Web server logs: requests whose parameters contain quotes, comment markers (--), words such as UNION or SELECT, or very long encoded strings, often coming from one address in rapid bursts.
  • Application error logs: a spike of database syntax errors, which is what a person probing for the bug produces.
  • Database logs: unusual query shapes, queries touching tables the app never uses, or slow queries that might indicate data being inferred bit by bit.
  • Web application firewalls (WAFs) can block known patterns, but they are a second layer. A determined attacker can usually rephrase, so they never replace parameterized queries.

You will build a log scanner of this kind in Project: Detect Attacks in Web Server Logs, and the broader practice is in Logging, Monitoring and SIEM Basics.

Testing in your own lab

An authorized tester looks for injection by sending inputs such as a single quote or a pair of quotes and watching for errors or changed behaviour, then confirming with controlled true/false comparisons, and stopping at proof. Automated tools exist for this, and they are run only against labs or in-scope targets, never against real systems on a whim. Pull-request reviews and unit tests that include awkward names (O'Brien, a"b, a very long string) catch many cases early.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the root cause of SQL injection?

    Code and data share one string. Separating them with parameters removes the problem.
  2. 2. Why does the input O'Brien raise a database error in the vulnerable query?

    An unescaped quote closes the literal. The leftover text is parsed as SQL, which is invalid here.
  3. 3. Which part of a query can NOT be supplied through a placeholder?

    Placeholders bind values, not identifiers. Map user choices of column names through an allow-list.
  4. 4. Does using an ORM make injection impossible?

    ORMs bind parameters in their normal APIs, but raw helpers and dynamic names reopen the hole.
  5. 5. What is the main purpose of least-privilege database accounts?

    Least privilege is damage control. Parameterized queries prevent the flaw; limited rights shrink the impact of any that slip through.

Summary

  • SQL injection occurs when input is concatenated into query text and so changes the query's structure.
  • A quote in a harmless name like O'Brien causing an error is a warning sign of concatenation.
  • Parameterized queries send values separately and are the primary fix.
  • Column, table and sort names need an allow-list because they cannot be parameters.
  • ORMs are safe only in their bound APIs; raw fragments built from input remain risky.
  • Use a dedicated low-privilege database account and slow password hashes to limit damage.
  • Look for quotes, SQL keywords and bursts of syntax errors in logs, and never test against systems you are not authorized to test.

Next: Cross-Site Scripting and CSRF Defense.