SQL Injection Explained with OWASP Juice Shop

SQL injection happens when an application builds a database query by joining its own SQL text with data that came from a user. The database receives one string and has no way to tell which characters the developer wrote and which ones the user typed. Once the user's text reaches the parser as SQL, it can change what the query means. The query can return other rows, skip a check, change data, or in the worst cases reach things the application never meant to expose.

This article explains the mechanism, shows the one fix that addresses the cause, and gives you two hands-on parts. The first is a login challenge in OWASP Juice Shop, which you run on your own machine. The second is a short Python script with sqlite3 that reproduces the same bug and the fix. If you read OWASP Top 10 for Developers first, you already have the category in mind. This article takes one item from that list and goes down to the query text.

#What SQL injection is

The CWE definition for this weakness, CWE-89, describes it plainly: the software builds part of an SQL command from input and does not neutralize the SQL syntax inside that input. The input is then interpreted as commands rather than as values.

Three words matter in that definition: builds, input, and interpreted. The bug is not that the application talks to a database. The bug is that the application assembles the query text itself, and some of the pieces it assembles came from outside.

#Why it matters

A login query is the smallest useful example. It checks a name and a password. If an attacker can change the query, they can change who gets authenticated. The same flaw in a search box can return rows the user should never see. On an admin page it can change prices, roles or stored data. Many of these bugs begin with one quote character that reaches the database unescaped, and that quote is often how a tester first confirms the problem.

Injection also does not need a clever payload. Developers often find it with an input that makes the application crash or return an odd error. An error that mentions SQL syntax is a strong sign the input reached the query.

#How the query changes meaning

Start with a template that a developer might write:

SELECT * FROM users WHERE name = '<name>' AND pw = '<pw>'

Imagine the name field receives admin' -- and the password field receives wrong. After the application puts those strings into the template, the database parses this:

SELECT * FROM users WHERE name = 'admin' --' AND pw = 'wrong'

Read it step by step. The first quote after admin closes the string literal that the developer opened. Then -- starts a comment in SQLite, and in most SQL dialects. Everything after it is ignored, including the password condition. The database receives a valid query that asks for the row whose name is admin, and nothing else.

Four things happen in order:

1. The application copies the user's characters into the query text.
2. The database parser reads the whole string as SQL. It cannot tell which characters came from the developer and which came from the user.
3. A quote from the user ends the literal early. Text after it is parsed as SQL keywords, operators or comments.
4. The condition the developer wrote is no longer the condition the database runs.

The f-string, string concatenation, % formatting, .format() and template literals all have the same flaw. The language does not matter. The method of joining text and SQL does.

#The root fix: parameterized queries

Parameterization splits the SQL text from the values. You send the query with placeholders, and the values travel to the database through the driver as data. The database never parses them as SQL, so a quote inside a value has no special meaning.

The Python sqlite3 documentation says to use placeholders instead of string formatting when binding Python values to SQL statements, specifically to avoid SQL injection. The OWASP SQL Injection Prevention Cheat Sheet names parameterized queries as the primary defense for the same reason: the query structure is defined separately from the data.

Validation still matters, but it is a second layer. Parameter binding is the control that fixes the cause. If you validate a value and then concatenate it into a query, you have not fixed the bug.

#Hands-on: solve the Login Admin challenge

Juice Shop is an intentionally vulnerable web application that OWASP maintains for training. Run it only on your own machine, and keep it bound to the loopback address so no other computer can reach it.

#Start the lab on localhost

docker run -d -p 127.0.0.1:3000:3000 bkimminich/juice-shop

The 127.0.0.1: prefix publishes the port on your loopback interface only. If port 3000 is already in use, Docker fails with an error such as port is already allocated. On the test machine used for this article, another local container already held port 3000 and the command failed for that reason. Pick a free host port, for example 127.0.0.1:3001:3000, and use that port in the rest of the steps.

Confirm the app answers and check its version:

curl -s http://127.0.0.1:3000/rest/admin/application-version

On the test machine this returned:

{"version":"20.3.0"}

#Find the injection point

The official companion guide for Juice Shop, Pwning OWASP Juice Shop, does not print the payload for this challenge. It gives hints: the challenge description points to the form you should attack, a targeted attack works well if you already know the admin's email address, and a standard injection pattern can succeed even without that knowledge. Follow those hints. Start with the login form and a single quote in the email field. A normal wrong password should give an error. A broken query often gives a different response.

On the test machine, a wrong password for the admin email without any injection returned:

Invalid email or password.

with HTTP status 401. That is the baseline. Next, the same request with a quote and a comment in the email field:

curl -s -X POST http://127.0.0.1:3000/rest/user/login \
  -H 'Content-Type: application/json' \
  -d '{"email":"[email protected]'\''--","password":"wrong"}'

The response contains an authentication object with a token field. The password was wrong, yet the server issued a session token. Decoding the middle section of the JSON Web Token, which is base64url-encoded, shows "role":"admin". The response returned HTTP 200 on the test machine.

Check the scoreboard state through the lab's API:

curl -s "http://127.0.0.1:3000/api/Challenges/?name=Login%20Admin"

On the test machine, the challenge returned as solved, with difficulty 2. The scoreboard is the source of truth for the challenge. Your result should match the same check.

The companion guide and the Juice Shop source code are the places to confirm the exact query behind this form. Read them after you have the result, so you understand the mechanism instead of only copying a payload.

#Stop the lab

docker ps
docker stop <container-name>

Use the container name or ID that docker ps shows. Closing the browser tab does not stop the container.

#Reproduce the bug and the fix in Python

Save this as sqli_demo.py. It needs only the standard library. The script builds a small in-memory users table, then exposes two login functions. One builds the query with an f-string. The other uses a placeholder.

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE users (name TEXT, pw TEXT)")
cur.execute("INSERT INTO users VALUES ('alice', 'correct-horse'), ('admin', 'S3cret!')")

def login_unsafe(user, pw):
    query = f"SELECT * FROM users WHERE name='{user}' AND pw='{pw}'"
    print("  built:", query)
    return cur.execute(query).fetchall()

def login_safe(user, pw):
    return cur.execute('SELECT * FROM users WHERE name=? AND pw=?', (user, pw)).fetchall()

if __name__ == "__main__":
    print("same valid login, unsafe:", login_unsafe("admin", "S3cret!"))
    print("same valid login, safe:  ", login_safe("admin", "S3cret!"))
    print("crafted input, unsafe:")
    print("  result:", login_unsafe("admin' --", "wrong"))
    print("crafted input, safe:")
    print("  result:", login_safe("admin' --", "wrong"))

Run it. On Windows with Python installed as python, the command is python sqli_demo.py. On the test machine python3 was not on the path, so use python there. The output observed on the test machine with Python 3.12.10:

same valid login, unsafe: [('admin', 'S3cret!')]
same valid login, safe:   [('admin', 'S3cret!')]
crafted input, unsafe:
  built: SELECT * FROM users WHERE name='admin' --' AND pw='wrong'
  result: [('admin', 'S3cret!')]
crafted input, safe:
  result: []

Compare the two halves. For a normal login, both functions return the same row, so the unsafe version looks correct. That is why this bug survives code review. With crafted input, the unsafe function prints the query the database received. The comment removes the password check, and the admin row comes back even though the password was wrong. The safe function treats admin' -- as a literal name. No user has that name, so it returns an empty list.

The script stores passwords in plain text because it is a demo. Real systems store a salted password hash and compare hashes.

#Where ORMs and helpers still go wrong

An ORM does not make the bug impossible. It gives you a safe default for normal queries and a set of escape hatches for everything else. These are the places where the bug usually returns:

• Raw SQL methods. Calls such as raw, text, extra or a plain execute accept a string. If you build that string with an f-string or concatenation, you are back in the unsafe pattern.
• Table and column names. Placeholders bind values. They cannot bind identifiers. A user-controlled ORDER BY column or table name has to pass through an allow-list that maps the user's choice to names you wrote yourself. OWASP recommends the same approach for fixed structural choices such as sort direction.
• Dynamic IN lists. The placeholder count changes with the input. Generate the right number of ? markers from your own code, then pass the values as a tuple. Do not join the user values into the text.
• Filter helpers. A function that accepts a filter string and appends it to a WHERE clause is a concatenation point, even when the rest of the app uses the ORM properly.

#Common mistakes

Blacklisting words. A filter that rejects OR, SELECT, quotes or -- is easy to bypass with case changes, encodings and comment styles. The OWASP cheat sheet is clear that validation does not replace parameter binding. Use binding first. Validation is useful for the fixed structural choices above, not as the main defense.

Escaping quotes by hand. Each database driver has its own rules, and a missed case is enough. Let the driver do the work.

Relying on least privilege alone. A database account with fewer permissions limits what an attacker can do after injection. It does not stop the injection. OWASP lists least privilege as a supporting control, and says application accounts should never have DBA or admin rights. Do both: parameterize, and give the application the smallest set of permissions it needs.

Parameterizing values and forgetting the rest. A team converts the WHERE clause to placeholders, then concatenates the sort column without thinking about it. Review every place where text enters a query, not only the most obvious one.

Testing only normal input. The bug appears only when the input contains SQL syntax. Add a test that sends the crafted value and expects an empty result or a rejected request.

#How to go further

Read the Python sqlite3 documentation section on placeholders, then try the same pattern with the driver your project uses. Read the OWASP SQL Injection Prevention Cheat Sheet from start to finish, especially the sections on stored procedures and dynamic identifiers.

Go back to the Juice Shop lab and solve the other SQL-related challenges with the same care. For each one, write down the query you believe the server runs, then check the source code.

Add a regression test to your own code. The test should send admin' -- or a similar value to every query function that accepts user input, and assert that the function does not return rows it should not return.

Search your codebase for f-strings, % formatting, .format() and string concatenation that appear near execute, raw, text or extra. Each match is a place to review, not automatically a bug.

Static analysis tools can flag some of these patterns, and a large language model can help you read unfamiliar query builders. Neither one replaces the test that shows your own query behaves correctly. NotHoly's API is one option for that kind of review, and it is only as useful as the test that checks the result.

The next article in this series covers cross-site scripting (XSS), which has a similar shape: untrusted text ends up interpreted as something it should not be.

#Sources

• OWASP, SQL Injection Prevention Cheat Sheet
• MITRE, CWE-89: Improper Neutralization of Special Elements used in an SQL Command
• Python Software Foundation, sqlite3, DB-API 2.0 interface for SQLite databases
• OWASP, Pwning OWASP Juice Shop, companion guide