Skip to main content
Mikail Aydoğdu

TryHackMe Advent of Cyber 2023 [Day 10] SQL injection Inject the Halls with EXEC Queries

SQL

Structured query language (SQL) is essential for working with relational databases and building dynamic websites. Even if you’ve never explicitly used SQL before, chances are you frequently interact with databases. Whether you’re checking your bank account balance online, browsing through products on an e-commerce website, or posting a status on social media, you’re indirectly querying and altering databases. SQL is one of the most popular languages that make this all possible.

We can run various SQL queries against this table to retrieve, update, or delete specific data. For example:

SELECT * FROM tbl_ornaments WHERE material = 'Wood';

This SELECT statement returns all columns for the ornaments where the material is specified as " Wood ".

SELECT ornament_id, colour, category FROM tbl_ornaments WHERE elf_id = 102;

This SELECT statement will return all the ornaments created by the Elf with the ID 102 . Unlike the first statement, this query only returns the ornament's ID , colour , and category .

INSERT INTO tbl_ornaments (ornament_id, elf_id, colour, category, material, date_created, price) VALUES (5, 105, 'Blue', 'Star', 'Glass', '2023-12-10', 4.99);

This INSERT statement adds a new ornament to the table created by the Elf with the ID 105 and the specified values for each column.

PHP

PHP is a popular general-purpose scripting language that plays a crucial role in web development. It enables developers to create dynamic and interactive websites by generating HTML content on the server and delivering it to the client’s web browser. PHP’s versatility and seamless integration with SQL databases make it a powerful tool for building feature-rich, dynamic web applications.

SQL Injection (SQLi)

Taking in user-supplied input gives us powerful ways to create dynamic content, but failing to secure this input correctly can expose a critical vulnerability known as SQL injection (SQLi) . SQL injection is an attack technique that exploits how web applications handle user input, particularly in SQL queries. Instead of providing legitimate input (like the ornament colour in the example above), the attacker injects malicious SQL statements into a web application’s input fields or parameters. The application’s database server then executes this rogue SQL query.

SQL injection vulnerabilities pose a considerable risk to web applications as they can lead to unauthorised access, data theft, data manipulation, or even the complete compromise of a web application and its underlying database through remote code execution. If an attacker can control which queries the database executes, they can control the database functions performed and the data returned. As such, the impact can be catastrophic, ranging from exposing sensitive user information to causing significant data breaches.

SQL injection vulnerabilities continue to be highly pervasive despite numerous advancements to mitigate them. This type of vulnerability is featured prominently in the OWASP Top 10 list of critical web application security risks ( A03:2021-Injection ).

When a web application incorporates user input into SQL queries without proper validation and sanitisation, it opens the door to SQL injection. For example, consider our previous PHP code for fetching user input to search for ornament colours:

// Retrieve the GET parameter and save it as a variable  
$colour = $_GET['colour'];  
// Execute an SQL query with the user-supplied variable  
$query = "SELECT * FROM tbl_ornaments WHERE colour = '$colour'";  
$result = sqlsrv_query($conn, $query);

Without adequate security measures, an attacker could manipulate the “ colour ” parameter to execute malicious SQL queries. For instance, instead of searching for a benign colour, they might input ' OR 1=1 -- as the input parameter, which would transform the query into:

SELECT * FROM tbl_ornaments WHERE colour = '' OR 1=1 --'

As the query above shows, the attacker injected the malicious payload into the dynamic query. Let’s take a look at the payload in more detail:

  • ' OR is part of the injected code, where OR is a logical operator in SQL that allows for multiple conditions. In this case, the injected code appends a secondary WHERE condition in the query.
  • 1=1 is the condition following the OR operator. This condition is always true because, in SQL, 1=1 is a simple equality check where the left and right sides are equal. Since 1 always equals 1, this condition always evaluates to true.
  • The -- at the end of the input is a comment in SQL. It tells the database server to ignore everything that comes after it. Ending with a comment is crucial for the attacker because it nullifies the rest of the query and ensures that any additional conditions or syntax in the original query are effectively ignored.
  • The condition colour = '' is empty, and the OR 1=1 condition is always true, effectively making the entire WHERE condition true for every row in the table.

As a result, this SQL injection successfully manipulates the query to return all rows from the tbl_ornaments table, regardless of the actual ornament colour values. This is a classic example of an SQL injection payload, where the attacker leverages the OR 1=1 condition to bypass any intended conditions or logic in the query and retrieve data they are not supposed to access.

Stacked Queries

SQL injection attacks can come in various forms. A technique that often gives an attacker a lot of control is known as a “ stacked query ”. Stacked queries enable attackers to terminate the original (intended) query and execute additional SQL statements in a single injection, potentially leading to more severe consequences such as data modification and calls to stored procedures or functions.

In SQL, the semicolon typically signifies one statement’s conclusion and another’s commencement. This feature facilitates the execution of multiple SQL statements within a single interaction with the database server. It’s important to note that certain web application technologies and database management systems (DBMS) may demand different syntax or lack support for stacked queries. Consequently, enumeration is essential for precision when conducting injection attacks.

Suppose our attacker in the previous example wants to go beyond just retrieving all rows and intends to insert some malicious data into the database. They can modify the previous injection payload to this:

' ; INSERT INTO tbl_ornaments (elf_id, colour, category, material, price) VALUES (109, 'Evil Red', 'Broken Candy Cane', 'Coal', 99.99); --

When the web application processes this input, here’s the resulting query the database would execute:

SELECT * FROM tbl_ornaments WHERE colour = '' ; INSERT INTO tbl_ornaments (elf_id, colour, category, material, price) VALUES (109, 'Evil Red', 'Broken Candy Cane', 'Coal', 99.99); --'

As a result, the attacker successfully ends the original query using a semicolon and introduces an additional SQL statement to insert malicious data into the tbl_ornaments table. This showcases the potential impact of stacked queries, allowing attackers to not only manipulate the retrieved data but also perform permanent data modification.

Day 10 — Tasks Answers

  1. Manually navigate the defaced website to find the vulnerable search form. What is the first webpage you come across that contains the gift-finding feature?
/giftsearch.php
  1. Analyze the SQL error message that is returned. What ODBC Driver is being used in the back end of the website?
ODBC Driver 17 for SQL Server
  1. Inject the 1=1 condition into the Gift Search form. What is the last result returned in the database?
THM{a4ffc901c27fb89efe3c31642ece4447}
  1. What flag is in the note file Gr33dstr left behind on the system?
THM{b06674fedd8dfc28ca75176d3d51409e}
  1. What is the flag you receive on the homepage after restoring the website?
THM{4cbc043631e322450bc55b42c}

Reference

https://tryhackme.com/room/adventofcyber2023