In the Introduction to Information Security (IIS) course I took in OMSCS, I learned about SQL Injection. Although I had previously only heard the name, I didn’t fully understand how it operates. Therefore, I will organize my findings using a CTF problem as a case study.

Overview

During the IIS course, one of the projects was related to SQL Injection. While I had heard the term before, I lacked specific information about the steps involved in executing such an attack. Consequently, I gained new insights, even as someone who deals with databases in a professional context. Below, I will explain the content in detail. This time, I will refer to picoCTF, focusing on a SQLLite challenge within Web Exploitation.

picoCTF

Details regarding the IIS course and picoCTF can be found in the following articles:

CS 6035 Introduction to Information Security Binary Exploitation

Please note that this post is not intended for actual attacks; rather, it aims to raise awareness about these types of vulnerabilities.

Solution

Starting with the picoCTF challenge, you will encounter a login screen that requests the input of a Username and Password as shown below:

With no information available, I decided to try clicking the Login button without entering anything. As a result, the screen transitioned, and I was able to observe the following query:

1

SELECT * FROM users WHERE name='' AND password=''

It appears that the implementation accepts input for the username and password on the login screen and passes these values to a query in the following manner. The target query is likely designed to check the users table, where valid usernames and passwords are stored. It specifies the WHERE clause to filter by name and password, returning only those records that match. Based on this, it can be inferred that the application determines a successful login when it receives a result from this query.

1
2
3

username = ''
password = ''
SELECT * FROM users WHERE name=@username AND password=@password

To verify the specific behavior of the database, I will utilize a site called SQL Test. This platform allows users to issue queries against databases like SQL Server, MySQL, and Oracle without the need to set up a database. This makes it easy to conduct operational tests. For this instance, I will focus on SQL Server to observe its functionality.

SQL Test

Referring to the displayed query, I will define the users table as follows and register one user. As a result, the table will contain the following data:

1
2
3
4
5
6

CREATE TABLE [users] (
    name NVARCHAR(50) NOT NULL,
    password NVARCHAR(255) NOT NULL
);

INSERT INTO users values ('user1', 'P@ssw0rd');

In this situation, if I directly execute the following query without specifying a name or password, as done previously on the login screen, I will not receive any results since there are no matching users:

1

SELECT * FROM users WHERE name='' AND password=''

However, by using the input username = '' OR 1=1; --', the final query would look like this:

1

SELECT * FROM users WHERE name='' OR 1=1;--' AND password=''

As a result, everything after – is treated as a comment and ignored, which means the AND condition is disregarded. The query will return results when either name = ’’ or 1=1 is true. Since 1=1 is always true, this query will consistently return results regardless of the value of name.

The query looks as follows, with the AND condition commented out:

Returning to the login screen, by entering ' OR 1=1; -- in the username field and leaving the password field empty, I can confirm that I successfully log in to the next screen.

In this case, the flag can be verified by using the browser’s developer tools on this screen.

Reflection

I had heard of SQL Injection before, but I have gained a deeper understanding by learning about specific attack patterns. Although I have been using databases in my work, I had never considered these types of vulnerabilities, so this has been a valuable learning experience for me.

I find it difficult to imagine how one might come up with such vulnerabilities. Attackers often exploit weaknesses in ways that we do not expect. This has made me realize that flexible thinking is essential for enhancing security against such threats.