Wednesday 13 September 2023

Introduction to SQL Injection

in , , ,  
We noticed that our old post (see here) created by one of the students through the re:educate program was broken. As a result, Alysha, our new team member, gives this topic a fresh perspective. During the course of her research, she will provide more information on SQL Injection in our blog.

Introduction to SQL Injection

Summary
SQL Injection (SQLi) is generally rated as a severe vulnerability in web applications as it allows attackers to execute malicious queries that can compromise both web application and database security. It is one of the oldest and dangerous vulnerabilities.

What is SQL database?
SQL database is a type of relational database management system used for storing and managing data. It utilises SQL language to define, manipulate and query data, organising it into tables composed of rows and columns.

What is SQL?
Structured Query Language (SQL) is a versatile and case-insensitive language for querying databases, commonly used for selecting, inserting, updating and deleting data. While syntax may vary between different databases, this explanation focuses on MySQL.

Assume that we have created a table called staff in the database.

For a better illustration, we can use SQL Fiddle to show the queries and the outputs.

  1. INSERT
    INSERT clause is used to add data into the database.

    Example:
    INSERT INTO staff (username, password) VALUES ('user', 'user123');
    The query inserts a new record into table staff with username user and password user123.

  2. SELECT
    SELECT clause is used to retrieve data from the database.

    Examples:
    SELECT * FROM staff;
    The asterisk * indicates that we request for all data in the table staff.

    SELECT * FROM staff LIMIT 1,2;
    The LIMIT 1,2 clause restricts the database to skip the first row and return just two rows of data.

    SELECT * FROM staff WHERE username != 'vendor';
    The WHERE clause is used to specify specific conditions. The != 'vendor' instructs the database to retrieve data that the username is NOT equal to vendor.

    SELECT * FROM staff WHERE username = 'admin' OR username = 'administrator';
    The OR operator specifies that the data returned must match either admin or administrator.

    SELECT * FROM staff WHERE username = 'rehack' AND password = 'reh4ckisc00l';
    The AND operator specifies that the data returned must match both rehack and reh4ckisc00l.

    SELECT * FROM staff WHERE username LIKE 'v%';
    The LIKE 'v%' clause allows us to retrieve data that starts with the letter 'v'. The % symbol acts as a wildcard, matching any characters.

    SELECT * FROM staffs WHERE username LIKE '%a%';
    The LIKE '%a%' clause allows us to retrieve data containing 'a' in it.

  3. UPDATE
    UPDATE clause is used to modify data in the database.

    Example:
    UPDATE staff SET password = 'isnotmyname' WHERE username = 'user';
    The query updates the password of user to isnotmyname.

  4. DELETE
    DELETE clause is used to remove data from the database.

    Examples:
    DELETE FROM staff WHERE username = 'vendor';
    The query removes a row with username vendor.

    DELETE FROM staff;
    If we omit the WHERE clause, all data in the table will be deleted. Do note that the record count is 0, meaning there are zero data remaining in the table.

  5. UNION
    UNION clause is used to combine data from multiple SELECT queries.

    Example:
    Assume that we have two tables of different departments.

    Table 1 - english

    Table 2 - history

    SELECT staff_name, dept_id FROM english UNION SELECT staff_name, dept_id FROM history;
    The query combines and retrieves staff_name and dept_id from two tables. Do note that there are no duplicate data in the output.

Now that we have covered some SQL basics, let’s explore SQL injection.

What is SQL injection?
SQL injection is a hacking technique in which attackers exploit weak input validation in web applications to inject malicious SQL queries into database. This allows for unauthorised access, data manipulation or data theft, posing a significant threat to application and database security.

What does SQL injection look like?
Let’s explore with a simple SQL injection scenario from PortSwigger Academy.
Resources: PortSwigger: SQL Injection - Login Bypass

Scenario:
SQL injection vulnerability allowing login bypass.

This lab contains a SQL injection vulnerability in the login function.
To solve the lab, perform a SQL injection attack that logs in to the application as the administrator user.

After gaining access to the lab, we will be greeted with an e-commerce website.

To access the login page, click on ‘My Account,’ where we will find the login form.

Considering the vulnerability exists on the login form, we will assume the SQL query used might look like this:
SELECT * FROM users WHERE username = '$username' AND password = '$password';

To bypass the login as administrator, we can inject the username field with administrator' -- and random input for the password field.

After injecting administrator' -- into the username field, the SQL query becomes:
SELECT * FROM users WHERE username = ' administrator' --' AND password = 'random';

In SQL, the -- functions as a comment, causing the rest of queries to be ignored.

As a result, we successfully bypassed the password check with the SQL query:
SELECT * FROM users WHERE username = 'administrator' --

Upon clicking the ‘Login’, observe that we are now login as the administrator.

How to detect SQL injection vulnerabilties?

  1. Error-based testing
    Submit a single quote ' character into input fields or URLs and check for error messages or unusual behavior. If the application mishandles this input, it might be vulnerable to SQL injection. This generally possible when the application enables the debug messages.

    Example:
    Inject ' into URL like http://web.abc/event.php?id=1 and see if there error messages like 'You have an error in your SQL syntax; ~.

  2. Boolean-based testing
    Test Boolean conditions like OR 1=1 and OR 1=2 within input fields and observe any differences in the application’s response. If the application behaves differently for these conditions, it may indicate a potential vulnerability. Do note that, whenever possible avoid using OR 1=1 on sensitive forms such as Login or Reset Password pages. As this may creates unncessary changes in the database if the application is poorly coded. You may use AND 1=1 as an alternative.

    Example:
    Inject 'OR 1=1 -- in a login field and see if it grants access without valid credentials.

  3. Time-based testing
    Submit a payload designed to trigger time delays and monitor the application’s response times for variations. If there are significant delays, it could suggest an SQL injection vulnerability.

    Example:
    Inject SLEEP(5) into the database and see if there’s a delay of 5 seconds before the application responds.

How to prevent from SQL injection?

  1. Prepared statements
    Prepared statements also known as parameterised queries, involving predefined SQL query and adding user inputs as separate parameters. This method safeguards the SQL query’s structure and improves security.

    Example:

    $stmt = $conn -> prepare("INSERT INTO users (username, password, email) VALUES (?, ?, ?)");
$stmt -> bind_param("sss", $username, $password, $email);

  2. Escaping user input
    Escaping user input involves adding a backslash () to special characters to treat them as regular strings, preventing them from breaking SQL queries or being used in injection attacks. There are cases where a bypass may exist on a certain condition. Hence, this approach may not very reliable, depending on how the application is coded.

    Example:
    We can use 'mysqli_real_escape_strings' function in PHP to escape any special character.

  3. Input validation
    Input validation restricts user input to predefined values or patterns. An allowlist can be used to only accept specific strings.

    Example:
    We can use filter_var($email, FILTER_VALIDATE_EMAIL) function in PHP to validate e-mail address

References

Author: Alysha from RE:HACK

Share: