Introduction to SQL Injection
Introduction
Today is the era of data. It’s the most powerful weapon in the world now. To store, manage and use the data of a certain topic, we use databases. A database is used to organize the data using some table, column, keys and etc. In this task, we will learn about how to use data and how to use them for ethical hacking.
SQL (Standard Query Language)
SQL is a domain-specific, standardized programming language used to interact with relational database management systems (RDBMS) such as MySQL, SQL Server, IBM Db2, PostgreSQL and Oracle Database.
SQL queries (also known as SQL commands or SQL statements) allow users to easily add, retrieve, update, delete, aggregate and otherwise manage data in a relational database (or SQL database). In these systems, structured data is ordered into rows and columns, which together form tables. Data is typically structured across multiple related tables that can be linked using primary or foreign keys. Unlike other programming languages, SQL is a declarative language, which means it describes what the user wants the computer to do rather than how to achieve it. This contrasts with imperative or procedural languages (such as Java and COBOL) which require step-by-step instructions.
For example, when users write an SQL query to fetch data, they don’t need to outline the steps the database should take to gather that data; they just describe what the result should look like:
SELECT order_id, address
FROM customers
WHERE city = ‘Cumilla’ ;
SQL is a fundamental and versatile tool in the modern tech stack, known for its data manipulation capabilities, rapid query processing and strong security features. It also offers compatibility across different systems, scalability to handle growing data needs and robust open source and community support.
How SQL works with Database
A web application commonly communicates with a database through SQL queries. When a user performs an action, such as logging in or searching for a product, the application receives the user’s input and sends an appropriate query to the database.
The basic process is:
User → Web Application → SQL Query → Database → Result → Web Application → User
When we browse a website, many of the pages we see, are generated dynamically from a database. Consider a blog application where each article has a unique ID. When we visit https://website.thm/article?id=1, the web server takes the value 1 from the URL and inserts it into a SQL query:
SELECT * FROM articles WHERE id = 1 AND public = 1;
Most login forms work by sending the username and password to the server, which constructs a query like:
SELECT * FROM users WHERE username=’abedul.arif’ AND password=’secret123' LIMIT 1;
There are many functions like UPDATE, DELETE, CONCATE etc.
Now, let’s begin our important part for cybersecurity and ethical hacking.
SQL Injection
SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. This can allow an attacker to view data that they are not normally able to retrieve. This might include data that belongs to other users, or any other data that the application can access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application’s content or behavior.
In some situations, an attacker can escalate a SQL injection attack to compromise the underlying server or other back-end infrastructure. It can also enable them to perform denial-of-service attacks.
How it occurs
When we browse a website, many of the pages we see are generated dynamically from a database. Consider a blog application where each article has a unique ID. When we visit https://website.thm/article?id=1, the web server takes the value 1 from the URL and inserts it into a SQL query:
SELECT * FROM articles WHERE id = 1 AND public = 1;
The database returns the article with ID 1 (if it’s public), and the web server renders it into the page we see. This is how most data-driven web applications work: user input is passed to SQL queries, and the results are returned to the user.
The problem arises when the application builds the query by directly concatenating user input into the SQL string. If the server-side code looks something like this:
$query = “SELECT * FROM articles WHERE id = “ . $_GET[‘id’] . “ AND public = 1;”;
Then whatever we put in the id parameter becomes part of the SQL query. If we change the URL to ?id=1 OR 1=1 — , the query becomes:
Get abedularif’s stories in your inbox
Join Medium for free to get updates from this writer.
SELECT * FROM articles WHERE id = 1 OR 1=1 — AND public = 1;
The OR 1=1 makes the WHERE clause always true, and the — comments out the AND public = 1 check. The database now returns every article, including private ones.
Types of SQL Injection
SQL Injection techniques are categorised based on how the attacker receives feedback from the database:
1. In-Band SQL Injection is when the results of the injection are returned directly in the web application’s response. This is the most straightforward type. It has two subtypes:
i. Error-Based: The database returns error messages that reveal information about its structure.
ii. Union-Based: The attacker uses UNION to append a second query and extract data through the page output.
2. Blind SQL Injection is when the application does not display query results or error messages. The attacker must infer information from indirect signals:
i. Authentication Bypass: The login succeeds or fails based on the injected query.
ii. Boolean-Based: The application’s response changes subtly (e.g., different content, true/false) based on whether a condition is true.
iii. Time-Based: The attacker uses SLEEP() to introduce a time delay and observes whether the response is slow (true) or fast (false).
3. Out-of-Band SQL Injection is when the attacker causes the database server to make an external network request (e.g., a DNS lookup) that exfiltrates data through a separate channel. This is used when neither in-band nor blind techniques are viable.
Security Impacts of SQL Injection
A successful SQL injection attack can result in unauthorized access to sensitive data, such as:
- Passwords.
- Credit card details.
- Personal user information.
SQL injection attacks have been used in many high-profile data breaches over the years. These have caused reputational damage and regulatory fines. In some cases, an attacker can obtain a persistent backdoor into an organization’s systems, leading to a long-term compromise that can go unnoticed for an extended period.
Prevention of SQL Injection
The most effective defense is to ensure that SQL code and user-supplied data are kept separate.
1. Use Prepared Statements / Parameterized Queries
Parameterized queries are the primary recommended defense. They allow developers to define the SQL structure separately from user-provided values, preventing input from changing the intended SQL command.
2. Use Safe Stored Procedures
Stored procedures can help prevent SQL Injection when they are properly implemented and do not construct unsafe dynamic SQL internally.
3. Apply Allow-List Input Validation
Applications should validate input according to what is actually expected. For example, if an input should contain an integer, the application should validate it as an integer. Allow-list validation is particularly useful for values such as table names, column names, and sorting options where parameterization may not be possible.
4. Apply Least Privilege
The application’s database account should have only the permissions it actually needs. An application should not normally connect to a database using a highly privileged administrator account. This limits the potential damage if SQL Injection occurs.
5. Avoid Relying Only on Input Escaping
Escaping user input can provide some protection in specific situations, but OWASP considers it a weaker and fragile defense compared with parameterized queries.
Conclusion
SQL is fundamental to applications that store and manage relational data, but improper handling of SQL queries can introduce SQL Injection vulnerabilities. SQL Injection occurs when untrusted input is allowed to influence the structure or logic of a database query. It can result in unauthorized access, data theft, data modification, and other serious security consequences. The strongest approach to preventing SQL Injection is to use prepared statements or parameterized queries, supported by proper input validation, secure stored procedures, least-privilege database accounts, secure coding practices, and regular security testing.
