Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 80 ยท Web Application Security

SQL Injection and Parameterized Queries

See how SQL injection works on a fake in-memory database, then fix it with parameterized queries, allow-lists, ORMs and least-privilege database accounts.

SQL injection happens when an application builds a SQL statement by gluing user input into a string. The database cannot tell which part of the text the programmer wrote and which part the user typed, so a cleverly chosen input changes the meaning of the query. It is one of the oldest web vulnerabilities and still appears in the OWASP Top 10 under Injection, because it is easy to introduce and the damage can be total: stolen tables, bypassed logins, modified or deleted data.

The good news is that SQL injection is also one of the most completely solvable problems in security. The fix is a single habit: never build SQL from strings, use parameterized queries. In this lesson you will see the flaw on a fake in-memory SQLite database (nothing real is touched), fix it, and learn the extra layers that limit the damage if you ever slip. You should be comfortable with basic SQL; if not, see the SQL tutorial.

How the flaw works

Imagine a login check written like this:

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

If name is alice, the query is fine. If an attacker types alice' -- the result is:

SQL
SELECT * FROM users WHERE name = 'alice' --' AND password = ''

The ' closed the string early and -- started a comment, so the password test disappeared. The input stopped being data and became code. The same idea works for OR 1=1 (make a condition always true), UNION SELECT (append rows from another table) and stacked statements on databases that allow them.

flowchart LR
    U["User input<br/>alice' --"] --> S["String concatenation"]
    S --> Q["Query text:<br/>password check is commented out"]
    Q --> DB[("Database<br/>runs it as written")]
    DB --> R["Login succeeds<br/>without the password"]

Concatenation lets input cross from the data channel into the code channel.

See it on a fake database

The first demo builds a tiny in-memory SQLite database with two users. Everything is made up and disappears when the page closes. The vulnerable function concatenates; the attacker's inputs are shown with what they do.

Python Injection against a string-built query

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 example 6. Even an honest user with an apostrophe in their name breaks the query. If you have ever seen a site crash on "O'Brien", you have seen the same root cause. Injection bugs are often found first as plain bugs.

The fix: parameterized queries

A parameterized query (also called a prepared statement or bound parameters) sends the SQL text and the values separately. The query is parsed with placeholders, and the values are bound afterwards purely as data. No value can change the structure of the statement, whatever characters it contains.

Python The same attacks against a parameterized query

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.

Every attack now fails because the whole string is compared against the name column as a value. The honest user with an apostrophe in their name now logs in correctly. Security and correctness go together here.

In SQLite from Python the placeholder is ?. Other drivers use %s (psycopg, MySQL drivers), :name or %(name)s. The syntax differs but the principle does not, and it is never Python string formatting: f"... {name}" and "%s" % name are concatenation in disguise.

What placeholders cannot do

Placeholders replace values only. They cannot stand in for table names, column names, ORDER BY directions or SQL keywords. Sorting by a user-chosen column is a common reason people go back to concatenation. The correct approach is an allow-list: map the user's choice to a fixed set of known-good identifiers and reject everything else.

Python Allow-listing identifiers

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.

The f-string here is safe only because column and order are looked up from dictionaries you wrote. The user's text never reaches the query. Notice the value max_price still goes through a placeholder.

The same advice covers LIKE searches (pass "%" + term + "%" as the parameter, and consider escaping % and _ inside the term) and IN (...) lists (generate one ? per item and pass the items as parameters).

ORMs and query builders

Object-relational mappers such as SQLAlchemy, Django's ORM or Laravel's Eloquent generate parameterized SQL for you, which removes most of the risk. They are not magic, though. Every one of them offers an escape hatch for raw SQL (text(), .raw(), DB::raw()), and that is where injection returns when someone concatenates into it. Rules of thumb:

  • Use the ORM's normal filter methods wherever possible.
  • When you must write raw SQL, use the framework's bound parameter feature, not string formatting.
  • Treat order_by, column names and similar dynamic parts with the allow-list approach above.
  • Search the code base for raw-SQL calls during security code review; there are usually few of them.

Defense in depth: limit the blast radius

Parameterization should stop injection, but good security assumes some layer will fail. Add more:

  • Least-privilege database accounts. The web application's account should be able to SELECT, INSERT, UPDATE and DELETE on its own tables and nothing else: no DROP, no schema changes, no access to other databases, no file or OS-command features. Migrations run under a separate, more powerful account used only at deploy time. This turns "attacker drops the table" into "attacker is refused". See the CIA triad and core principles for least privilege in general.
  • Separate accounts per role where practical, for example a read-only account for reporting pages.
  • Generic error messages for users and detailed errors only in logs, so attackers cannot read database errors to refine their payloads.
  • Input validation as an extra filter (a numeric ID must be digits). It helps, but it is not a substitute: names like O'Brien are legitimate. See Input Validation and Sanitization.
  • Monitoring for suspicious patterns such as many quote characters or UNION in parameters, and alerts on database errors (Logging, Log Analysis and SIEM).
  • A web application firewall may block known payloads, but attackers evade signatures, so treat it as a bonus.

Also remember second-order injection: a malicious string is stored safely (through a parameterized insert) but later read back and concatenated into another query. Parameterize every query, not just the ones that take direct user input. And blind injection works even when nothing is printed: the attacker infers data from true/false behaviour or timing. Hiding error messages alone never fixes injection.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

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

    When input is concatenated into the statement, the database cannot distinguish the programmer's code from the attacker's data.
  2. 2. Which line is safe?

    Only the third form sends the value separately as a bound parameter. The others are all string building.
  3. 3. A page lets users sort by a column they choose. How do you handle the column name safely?

    Placeholders cannot stand in for identifiers. Map the user's choice to fixed column names you control and reject anything else.
  4. 4. Why should the application's database account have limited privileges?

    Least privilege is defense in depth. It does not replace parameterization, but it limits what an attacker can do through a flaw.
  5. 5. Which statement about ORMs is correct?

    ORMs generate bound parameters for standard operations. Concatenating into raw SQL or dynamic identifiers reintroduces the risk.

Summary

  • SQL injection lets input change the structure of a query because the app builds SQL by concatenation.
  • Parameterized queries send SQL and values separately, so values can never become code. This is the primary fix.
  • Placeholders cover values only; for column names or sort directions use an allow-list of fixed identifiers.
  • ORMs help but raw-SQL escape hatches need the same care.
  • Add least-privilege database accounts, generic error messages, validation and monitoring as extra layers.
  • Beware second-order and blind injection: parameterize every query and do not rely on hidden errors.

Next: Cross-Site Scripting and Output Encoding, where untrusted data crosses a different boundary: into the browser.