How exactly works this SQL injection example related to the DVWA application?

Question

I am a software developer converting do application security and I have some doubts about SQL injection example.

I am following a tutorial related the famous DVWA: http://www.dvwa.co.uk/

So I have the following doubt (probably pretty trivial).

I have this PHP code defining the query and the code to perform it:

<?php

if( isset( $_GET[ 'Submit' ] ) ) {
    // Get input
    $id = $_GET[ 'id' ];

    // Check database
    $getid  = "SELECT first_name, last_name FROM users WHERE user_id = '$id';";
    $result = mysqli_query($GLOBALS["___mysqli_ston"],  $getid ); // Removed 'or die' to suppress mysql errors

    // Get results
    $num = @mysqli_num_rows( $result ); // The '@' character suppresses errors
    if( $num > 0 ) {
        // Feedback for end user
        $html .= '<pre>User ID exists in the database.</pre>';
    }
    else {
        // User wasn't found, so the page wasn't!
        header( $_SERVER[ 'SERVER_PROTOCOL' ] . ' 404 Not Found' );

        // Feedback for end user
        $html .= '<pre>User ID is MISSING from the database.</pre>';
    }

    ((is_null($___mysqli_res = mysqli_close($GLOBALS["___mysqli_ston"]))) ? false : $___mysqli_res);
}

?>

As you can see the query is definied as string concatenation:

$getid  = "SELECT first_name, last_name FROM users WHERE user_id = '$id';";

So I can inject what I want into the $id variable and perform extra SQL code as:

$id = 1 OR 1=1

that will be always true. Ok this is clear.

My doubt is different:

Inserting a valid value (such as 1) into the form) I obtain this URL: http://localhost/DVWA-master/vulnerabilities/sqli_blind/?id=1&Submit=Submit#

The query is performed correctly and I am obtaining the following message result: User ID exists in the database.

If I try to insert a totally wrong ID in the form, for example "ABC" I am obtaining the following message error: User ID is MISSING from the database.. Ok, and this is ok

But if I try to insert a "wrong" value such as 1' in the form, the following URL is generated: http://localhost/DVWA-master/vulnerabilities/sqli_blind/?id=1%27&Submit=Submit#

And I obtain a valid message: User ID is MISSING from the database.

So it seems that the query was correctly executed searching for the user with ID=1.

Why the ' char is not brocking the query? I was thinking that it have to search a user with ID=1' that is not existing in the database (as the case of ID=ABC).

Why? What am I missing? Probably it is a trivial question but I want to understand it in deep

Answer 1

So there are two things going on here. I'll list and then explain:

1. In this case the value being injected is inside single quotes in the query, a fact which is easy to miss but is relevant for all your example payloads.
2. The application is suppressing errors. As a result if a payload results in an SQL syntax error, you will get the "No results" message.

Let's run through your payloads keeping those two facts in mind:

1 OR 1=1

Here, the quotes around the value are important. You actually end up with this query:

SELECT first_name, last_name FROM users WHERE user_id = '1 OR 1=1'

Which, contrary to what you said, doesn't actually match anything. Instead, it will only ever match the user with id=1. If there is no such user it will return no matches. The reason is because the whole thing is in single quotes, so the OR 1=1 is part of what it is searching for. If anything therefore you would expect it to match nothing, since there is no user with an id 1 OR 1=1. And yet it does match. This is almost certainly because the user_id field is an integer, and MySQL will helpfully cast id='1 OR 1=1' into an integer, resulting in it throwing away the OR 1=1 part and actually searching for id=1, which matches.

You can confirm that casting is giving you a false positive by trying out this query: ASDF OR 1=1 which you will find gives you no matches, showing that your current payload is not actually injecting anything.

To get the OR 1=1 out of the single quotes you first need to close the single quotes, so you would want something like: 1' OR 1=1. Except that won't work either because the lone single quote at the end of the original query will cause an SQL injection error (which will result in no matches per #2), so you actually have to inject 1' OR 1=1--. You should be able to test that this injection is working properly by using a payload like this: ASDF' OR 1=1--. That results in this query:

SELECT first_name, last_name FROM users WHERE user_id = 'ASDF' OR 1=1--'

Which will match anything and therefore return results even though there are obviously no users with an id of ASDF.

1'

It's important to see the exact query this builds:

SELECT first_name, last_name FROM users WHERE user_id = '1''

You tried to close the quotes but the query itself already had an opening and closing quotes, so your quote injection caused an SQL syntax error (notice how it ends in two single quotes). Per #2 above, an SQL syntax error will cause it to return no matches and not broadcast the SQL error.

Summary

All that to say: you are very close but there are some easy-to-miss details. If it helps, take your injection payload, build the actual query yourself, and run that in SQL to see what happens. In cases like this where you have code access it is even easier to figure out what is happening, because you can adjust the application to print out the query is running. This makes it very easy to run it in MySQL and see exactly what is happening, especially in cases like this where the application suppresses errors. Getting used to these sorts of things helps in cases in the future where you don't have such control.