Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 38 ยท Cryptographic Failures and Injection

SQL Injection and Parameterized Queries

Understand SQL injection by running vulnerable and safe queries against an in-memory SQLite database, then fix it with parameterized queries, allow-lists and least-privilege database accounts.

Injection (A03 in 2021) happens when untrusted data is sent to an interpreter as part of a command or query, and the interpreter cannot tell the difference between the developer's instructions and the user's data. The best-known form is SQL injection (SQLi), where input changes the meaning of a database query.

It has been well understood for over twenty years and is still found regularly, because string concatenation is the easiest way to build a query and the vulnerable code looks perfectly normal.

The root cause: code and data mixed in one string

Python
# VULNERABLE
query = "SELECT * FROM users WHERE name = '" + name + "'"

If name is alice the query is fine. If the user types ' OR '1'='1, the query becomes:

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

The quote in the input closed the string, and the rest became SQL logic. The condition is now always true and the query returns every user.

See it in a safe sandbox

This runs entirely in memory against a SQLite database holding fake data.

Python Concatenation versus parameters

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.

With a parameter placeholder (?), the SQL text is fixed first and the value is sent separately. The database treats the value strictly as data, so quotes inside it are just characters.

What attackers can do with SQLi

Depending on the database and privileges, injection can:

  • Read data from other tables (using UNION SELECT, error messages or yes/no behavior).
  • Bypass authentication (' OR '1'='1' -- in a login form).
  • Modify or delete data.
  • In some configurations, read files or execute commands on the database server.

Even when the page shows no results, attackers can extract data bit by bit by observing differences in response (blind SQLi) or timing. "The error messages are hidden" is therefore not a fix.

Python A login bypass, and why parameters stop it

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 password check here uses a plain SHA-256 for brevity; real systems use the slow salted hashes from the previous lesson.)

Defenses

  1. Parameterized queries (prepared statements). The primary defense. Every database driver supports them: ? or named placeholders in Python, PDO in PHP, PreparedStatement in Java, SqlCommand.Parameters in C#, db.query(sql, [values]) in Node drivers.
  2. ORMs and query builders, used properly. They parameterize for you, but raw fragments (whereRaw, .extra(), string interpolation inside .filter()) bring the risk back.
  3. Allow-lists for things you cannot parameterize. Table names, column names and sort directions cannot be bound as parameters. Map user choices to fixed values instead.
  4. Least privilege. The application's database account should have only the rights it needs (no DROP, no file access, no access to other schemas), so a successful injection does less damage.
  5. Input validation as a secondary layer (types, lengths, formats). It reduces noise but is not a substitute: O'Brien is a legitimate name.
  6. Do not show raw database errors to users; log them instead.

Escaping functions are fragile and encoding-dependent. Prefer parameters over escaping.

Python Allow-listing a column and direction

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 value (max_price) is a parameter; the structural parts (col, way) come from a closed list the developer wrote.

Second-order injection

Sometimes input is stored safely, then used unsafely later. A user registers with the name x'; --, the insert uses parameters, but an admin report later concatenates stored names into SQL. Treat data from your own database as untrusted whenever it is placed into a new query.

Beyond relational SQL

The same principle applies to every query language: NoSQL query objects ({"$ne": null} in a JSON body can turn a lookup into "match anything"), LDAP filters, XPath, GraphQL resolvers that build SQL, and ORM fragments. The next lesson covers other injection types (command injection and more).

Testing for SQLi

  • Code review: search for SQL built with +, f"...", .format, % or string interpolation.
  • Static analysis (SAST): tools flag tainted data reaching query functions.
  • Automated tests: send quotes, --, ; and long strings to every endpoint in your own test environment and check you get normal handling, not 500 errors.
  • Dynamic scans (DAST) in a lab, against apps you own.

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 code and data share one string, input can add SQL syntax.
  2. 2. What is the primary defense?

    Parameters keep the SQL structure fixed and send values separately.
  3. 3. A sort column comes from the URL. Parameters cannot bind identifiers. What do you do?

    Allow-lists ensure only developer-written fragments reach the query.
  4. 4. Why is least privilege for the database account useful?

    Defense in depth: a restricted account cannot drop tables or read unrelated schemas.
  5. 5. What is second-order injection?

    Data that was safely stored becomes dangerous when later concatenated into a command.

Summary

  • Injection happens when untrusted input becomes part of an interpreter's instructions; SQL injection is the classic case.
  • Always use parameterized queries; never build SQL by string concatenation.
  • Allow-list identifiers and sort directions, give the app a least-privilege database account and keep raw errors out of responses.
  • Validation is a helpful extra layer, not the fix; escaping is fragile.
  • Treat stored data as untrusted when reusing it in queries, and test with review, SAST and DAST in your own environment.

Next: Cross-Site Scripting and Output Encoding