SQL Injection Explained: Lab Practice and Prevention

SQL injection (SQLi) is one of the oldest and best-understood web vulnerabilities, and it still appears in real systems because of one repeated mistake: building database queries by joining strings that contain user input. This lesson explains exactly why it happens, shows the vulnerable and safe versions of code side by side and gives you lab exercises to see the problem and verify the fix, all inside your own environment.

What Is a SQL Query, and Where Does It Go Wrong?

A database such as MySQL, PostgreSQL or SQLite stores data in tables. Applications talk to it with SQL statements, for example:

SELECT id, name FROM users WHERE username = 'asha' AND password_hash = '...';

If a developer builds that statement by pasting in whatever the user typed, a person can type characters that change the structure of the statement rather than just supplying a value. The database cannot tell which parts the developer meant as code and which parts came from the user, so it runs the lot. That is injection: data crossing the boundary into code.

Vulnerable Code Versus Safe Code

The following Python example uses SQLite, which needs no server. It shows the flaw and the fix in the same file so you can compare.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE users (id INTEGER, username TEXT, role TEXT)")
cur.execute("INSERT INTO users VALUES (1, 'asha', 'user'), (2, 'admin', 'admin')")

def find_user_unsafe(username):
    # DANGEROUS: user input is concatenated into the SQL text
    query = "SELECT id, username, role FROM users WHERE username = '" + username + "'"
    return cur.execute(query).fetchall()

def find_user_safe(username):
    # SAFE: the ? placeholder keeps data separate from SQL code
    return cur.execute(
        "SELECT id, username, role FROM users WHERE username = ?", (username,)
    ).fetchall()

print(find_user_safe("asha"))        # normal use
print(find_user_unsafe("asha"))      # normal use
# Now try an input that contains a quote and a condition that is always true:
test = "x' OR '1'='1"
print(find_user_unsafe(test))        # returns every row: the query structure changed
print(find_user_safe(test))          # returns nothing: it was treated purely as a name

Run this in your lab. The unsafe function returns all users when given the crafted text, because the quote character closed the string and the added condition became part of the query. The safe version treats the entire input as a name, finds no match and returns an empty list. The only difference is the use of a parameterised query (also called a prepared statement).

Why the Fix Works

With parameterised queries, the database receives the SQL statement and the values separately. The statement is parsed and its structure fixed before values arrive. Values are then bound as plain data and can never change the statement’s logic, whatever characters they contain. Escaping characters by hand is fragile; separating code and data removes the whole problem.

Types of SQL Injection (Concepts)

TypeHow it appearsWhy it matters
In-band (error-based, union-based)Results or database errors appear directly in the pageEasiest to notice; verbose errors help attackers
Blind (boolean-based)The page behaves differently for true or false conditions, but shows no dataData can still be extracted slowly
Blind (time-based)Response delays reveal answersWorks even when output looks identical
Out-of-bandThe database is made to contact another systemUsed when responses are not visible
Second-orderMalicious data is stored first and used unsafely laterEasy to miss in code review

For defenders, the key point is that all of these arise from the same root cause, and parameterisation prevents them all.

What an Attacker Could Achieve (Impact)

  • Reading data they should not see: customer lists, password hashes, personal data.
  • Bypassing logins.
  • Modifying or deleting data.
  • In some configurations, reading files or running commands on the database server.
  • Stepping stone to wider network compromise.

Legal note: in India, unauthorised access and data theft fall under the Information Technology Act, 2000 (for example section 43 and section 66), and data protection duties under the Digital Personal Data Protection Act, 2023 apply to organisations that fail to protect personal data. Treat SQL injection as both a technical and a compliance risk.

Defence in Depth: A Checklist

  1. Parameterised queries everywhere. Use the placeholder syntax of your language: ? or %s style parameters in Python drivers, PreparedStatement in Java, parameterised commands in C# and PDO prepared statements in PHP.
  2. Use an ORM carefully. Object-relational mappers parameterise by default, but raw-query features can reintroduce the flaw.
  3. Stored procedures help only when they do not build dynamic SQL from strings inside.
  4. Allow-list dynamic parts. Table names, column names and sort directions cannot be parameters; map user choices to a fixed list in code.
  5. Validate input for type, length and format as a second layer, never as the only layer.
  6. Least privilege. The application’s database account should have only the permissions it needs: no administrative rights, no access to unrelated databases, limited to the necessary tables.
  7. Hide internals. Show generic error messages to users; log details privately.
  8. Use a web application firewall (WAF) as a safety net and for virtual patching, but never as a substitute for fixing code.
  9. Test continuously: static analysis, dependency checks and authorised dynamic testing in the pipeline.
  10. Monitor for unusual database errors and query patterns.

Allow-listing example for dynamic sorting

ALLOWED_SORT = {"name": "username", "role": "role"}

def list_users(sort_key):
    column = ALLOWED_SORT.get(sort_key, "username")   # unknown values fall back to a safe default
    query = f"SELECT id, username, role FROM users ORDER BY {column}"
    return cur.execute(query).fetchall()

Here the user never supplies SQL text; they choose a key, and the code supplies the trusted column name.

Practice Lab 1: Run the Python Demonstration

  1. Save the vulnerable and safe example to a file called sqli_demo.py in your lab VM and run it.
  2. Print the final SQL text that the unsafe function builds for each input. See how the structure changes.
  3. Add a third function that uses string escaping by replacing quotes. Think about why this is less reliable than parameters, and try different inputs.
  4. Write tests that confirm the safe function returns only exact matches for ten different odd inputs, including quotes, semicolons and very long text.

Practice Lab 2: Observe It in a Deliberately Vulnerable App

  1. Start DVWA or Juice Shop bound to 127.0.0.1 as described in the home lab lesson.
  2. Set DVWA’s security level to low and open its SQL Injection page. Read the source view available in the app to see the vulnerable code.
  3. Try the simple always-true condition demonstrated in the Python example against the login or search field, and note what changes in the output.
  4. Switch the security level to impossible, read the code and identify the defences used (prepared statements, anti-CSRF token, limited privileges).
  5. Write a short report: vulnerability, cause, impact, fix, using the format in How to Write a Penetration Test Report.

Practice Lab 3: Detect It

  1. Enable the web server and application logs in your lab.
  2. Repeat your test input and find the matching log entries. Which characters look unusual in the request line?
  3. Write a simple search over logs that flags requests containing quotes, UNION, -- or OR 1=1 patterns.
  4. Observe how easily an attacker can vary these patterns. This shows why detection signatures are a safety net, and fixing the code is the real solution.

Common Mistakes

  • Believing that front-end validation protects the server. Anyone can send requests directly.
  • Escaping manually instead of parameterising.
  • Using a single powerful database account for the whole application.
  • Showing raw database errors to users.
  • Fixing one query but forgetting reporting screens, admin tools and batch jobs that use similar patterns.

Practice Exercises

  1. In two sentences, explain why parameterised queries stop SQL injection.
  2. Rewrite a login query that uses string concatenation into a parameterised form in a language you know.
  3. Why can a table name not be passed as a query parameter, and how do you handle user-selected sorting safely?
  4. List three differences between error-based and blind SQL injection from a defender’s viewpoint.
  5. Design an application database user with minimum privileges for a read-only product catalogue.

Mentor tip: In code review, search the codebase for string-building around queries: plus signs, f-strings and format calls near SELECT, INSERT and UPDATE. Ten minutes of searching often finds more than a week of scanning.

Educational use only: test for SQL injection only on applications you own, deliberately vulnerable lab apps, or targets where you have written permission.

Key takeaways
  • SQL injection occurs when user input becomes part of the SQL command structure.
  • Parameterised queries separate code and data and prevent the whole class of flaw.
  • Use allow-lists for dynamic identifiers, least-privilege database accounts and generic error messages.
  • WAFs and monitoring are safety nets, not fixes.
  • Practise only on your own systems or deliberately vulnerable labs.

Related Lessons