Skip to content

SQL injection

Related resources

SQL stands for Structure Query Language. SQL injection is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database: to view data, to retrieve it, to modify it, to delete it, to compromise the infrastructure with what is known for instance as a denial of service attack.

OWASP

OWASP Web Security Testing Guide 4.2 > 7. Data Validation Testing > 7.5. Testing for SQL Injection

ID Link to Hackinglife Link to OWASP Description
7.5 WSTG-INPV-05 Testing for SQL Injection - Identify SQL injection points. - Assess the severity of the injection and the level of access that can be achieved through it.
Sources for these notes
Languages and dictionaries
Server Dictionary
MySQL MySQL payloads.
MSSQL MSSQL payloads.
PostgreSQL PostgreSQL payloads.
Oracle Oracle SQL payloads.
SQLite SQLite payloads.
Cassandra Cassandra payloads.
Attack-based dictionaries

How does SQL injection work?

1. Retrieving hidden data

Examples at a shopping application

Request URL SQL Query Explained
http://insecure-website.com/products?category=Gifts SELECT * FROM products WHERE category='Gift' AND release=1 Restriction "released" is being used to hide products that are not released. Unreleased products will be presumably released=0
http://insecure-website.com/products?category=Gifts'-- SELECT * FROM products WHERE category='Gift'--' AND released=1 Explained: Double dash sequence -- is a comment indicator in SQL which means that the rest of the query is interpretated as a comment. The application will display all the products in a category, being released or not.
https://insecure-website.com/products?category=Gifts'+OR+1=1-- SELECT * FROM products WHERE category='Gifts' OR 1=1--' AND released=1 This will return all items where category is Gifts, or 1=1. Since 1=1 is always true, the query will return all items.

2. Subverting application logic

Request URL SQL Query Explained
Login SELECT * FROM users WHERE username="admin" AND password="lalalala" Login process, probably with a POST method
Login: Adding admin'-- in the username and '' in the password field SELECT * FROM users where username="admin'-- AND password='' This query returns the user whose name is admin and succesfully logs the attacker as that user.

Clasification

SQLi (for SQL injection) typically falls under three categories.

1. In-band SQLi / or Classic SQL injection

In-band SQL injection is the most common type of SQL injection attack. It occurs when an attacker uses the same communication channel to send the attack and receive the results. In other words, the attacker injects malicious SQL code into the web application and receives the results of the attack through the same channel used to submit the code. In-band SQL injection attacks are dangerous because they can be used to steal sensitive information, modify or delete data, or take over the entire web application or even the entire server. Attacks are sent from the same channel in which results are collected.

In-band SQL injection can be further divided into two subtypes/exploitation techniques:

1.1. Error-based SQLi

Error-based SQL injection: In error-based SQL injection, the attacker injects SQL code that causes the web application to generate an error message. The error message can contain valuable information about the database schema or the contents of the database itself, which the attacker can use to further exploit the vulnerability.

The attacker performs actions that cause the database to produce error messages. The attacker can potentially use the data provided by these error messages to gather information about the structure of the database.

1.2. Union-based SQLi

The UNION operator is used in SQL to combine the results of two or more SELECT statements into a single result set. Therefore, it requires that the number of columns and their data types match in the SELECT statements being combined.

Union-based SQL injection: In union-based SQL injection, the attacker injects additional SELECT statements through the vulnerable input. By manipulating the injected SQL code, the attacker can extract data from the database that they are not authorized to access.

Here's an example to illustrate the concept. Consider the following vulnerable code snippet:

SELECT id, name FROM users WHERE id = '<user_input>'

An attacker can exploit this vulnerability by injecting a UNION-based attack payload into the parameter. They could inject a statement like:

' UNION SELECT credit_card_number, 'hack' FROM credit_cards --

The injected payload modifies the original query to retrieve the credit card numbers along with a custom value ('hack') from the credit_cards table. The double dash at the end is used to comment out the remaining part of the original query.

2. Inferential (Blind) SQLi

Blind SQL Injection is a type of SQL Injection attack where an attacker can exploit a vulnerability in a web application that does not directly reveal information about the database or the results of the injected SQL query. In this type of attack, the attacker injects malicious SQL code into the application's input field, but the application does not return any useful information or error messages to the attacker in the response. The attacker typically uses various techniques to infer information about the database, such as time delays or Boolean logic. The attacker may inject SQL code that causes the application to delay for a specified amount of time, depending on the result of a query.

Blind SQL injection can be further divided into two subtypes/exploitation techniques:

2.1. Boolean-based (content-based) Blind SQLi

Boolean-based SQL Injection: In this type of attack, the attacker exploits the application's response to boolean conditions to infer information about the database. The attacker sends a malicious SQL query to the application and evaluates the response based on whether the query executed successfully or failed.

Inferential SQL injection technique that relies on sending a SQL query to the database which forces the application to return a different result depending on whether the query returns a TRUE or FALSE result.

See this example:

' OR LENGTH(database()) > 5--

This payload test whether the length of the database name is greater than 5 characters. Afterwards, you can start testing each character and, therefore, retrieve the name of the database.

2.2. Time-Based Blind SQLi

Time-based Blind Injection: In this type of attack, the attacker exploits the application's response time to infer information about the database. The attacker sends a malicious SQL query to the application and measures the time it takes for the application to respond.

If you don't get a TRUE or FALSE response, sometimes you may infer if it is TRUE or FALSE based on time of response. Time-based SQL injection is a inferential SQL injection technique that relies on sending a SQL query to the database, which forces the database to wait for a specified amount of time (in seconds) before responding. The response time will indicate to the attacker whether the result of the query is TRUE or FALSE.

3. Out-of-Band SQLi

Out-of-band SQL Injection is the least common type of SQL injection attack. It involves an attacker exploiting a vulnerability in a web application to extract data from a database using a different channel, other than the web application itself. Unlike in-band SQL Injection, where the attacker can observe the result of the injected SQL query in the application's response, out-of-band SQL Injection does not require the attacker to receive any response from the application. The attacker can use various techniques to extract data from the database, such as sending HTTP requests to an external server controlled by the attacker or using DNS queries to extract data.

It's used when an attacker is unabled to use the same channel to launch the attack and gather results. Out-of-band SQLi techniques would rely on the database server's ability to make DNS or HTTP request to deliver data to an attacker.

Such is the case of Microsoft SQL Server's xp_dirtree command, which can be used to make DNS request to a server that an attacker controls, as well as Oracle Database's UTL_HTTP package, which can be used to send HTTP requests from SQL and PL/SQL ti a server that an attacker controls.

Databases

In computing, a database is typically managed by a Database Management System (DBMS) that provides a set of tools and interfaces to interact with the data. DBMS stands for "Database Management System". It is a software system that enables users to create, store, organize, manage, and retrieve data from a database.

DBMS provides an interface between the user and the database, allowing users to interact with the database without having to understand the underlying technical details of data storage, retrieval, and management. DBMS provides various functionalities such as creating, deleting, modifying, and querying the data stored in the database. It also manages security, concurrency control, backup, recovery, and other important aspects of data management.

Types of databases:

  • Relational Databases - A database that organizes data into one or more tables or relations, where each table represents an entity or a concept, and the columns of the table represent the attributes of that entity or concept. SQL databases are relational databases that store data in tables with rows and columns, and use SQL (Structured Query Language) as their standard language for managing data. They enforce strict data integrity rules and support transactions to ensure data consistency. SQL databases are widely used in applications that require complex data queries and the ability to handle large amounts of structured data. Some examples of SQL databases include MySQL, Oracle, Microsoft SQL Server, and PostgreSQL.
  • NoSQL Databases - A type of database that does not use the traditional tabular relations used in relational databases. Instead, NoSQL databases use a variety of data models to store and access data.
  • Object-oriented Databases - A database that stores data as objects rather than in tables, allowing for more complex data structures and relationships.

SQL databases

1. RDBMS - Relational Database Management System

RDBMS stands for Relational Database Management System. It is a software system that enables the creation, management, and administration of relational databases. RDBMSs are designed to store, organize, and retrieve large amounts of structured data efficiently. RDBMSs provide a set of features and functionalities that allow users to create database schemas, define relationships between tables, insert, update, and retrieve data, and perform complex queries using SQL. They also handle aspects like data security, transaction Management, and concurrency control to ensure data integrity and consistency.

The following are examples of popular DBMS (Database Management Systems):

  • MySQL - A free, open-source relational database management system that is widely used for web applications.
  • PostgreSQL - Another popular open-source relational database management system that is known for its advanced features and reliability.
  • Oracle Database - A commercial relational database management system developed by Oracle Corporation that is widely used in enterprise applications.
  • Microsoft SQL Server - A commercial relational database Management system developed.

How relational databases work:

  • Tables: The basic building blocks of a relational database are tables, also known as relations. A table consists of rows (also called records or tuples) and columns (also known as attributes). Each row represents a unique record or instance of an entity, and each column represents a specific attribute or characteristic of that entity.

  • Keys: Keys are used to uniquely identify records within a table and establish relationships between tables. The primary key is a column or set of columns that uniquely identifies each row in a table. It ensures the integrity and uniqueness of the data. Foreign keys are columns in one table that reference the primary key of another table, establishing relationships between the tables.

  • Relationships: Relationships define how tables are connected or associated with each other. Common types of relationships include one-to-one, one-to-many, and many-to-many. These relationships are established using primary and foreign keys, allowing data to be linked and retrieved across multiple tables.

  • Structured Query Language (SQL): Relational databases are typically accessed and manipulated using the Structured Query Language (SQL). SQL provides a standardized language for querying, inserting, updating, and deleting data from relational databases. It allows users to perform operations such as retrieving specific records, filtering data based on conditions, joining tables to combine data, and aggregating data using functions.

2. NoSQL

[NoSQL] (Not Only SQL) databases are a type of database management system that differ from traditional relational databases (RDBMS) in terms of data model, scalability, and flexibility. NoSQL databases are designed to handle large volumes of unstructured, semi-structured, and rapidly changing data. NoSQL databases are commonly used in modern web applications, big data analytics, real-time streaming, content management systems, and other scenarios where the flexibility, scalability, and performance advantages they offer are valuable.

There are several popular NoSQL databases available, each with its own strengths and use cases. Here are some examples of well-known NoSQL databases:

  • MongoDB: MongoDB is a document database that stores data in flexible, JSON-like documents. It provides scalability, high performance, and rich query capabilities. MongoDB is widely used in web applications, content management systems, and real-time analytics. It uses MQL (MongoDB Query Language).
  • Redis: Redis is an in-memory data store that supports various data structures, including strings, hashes, lists, sets, and sorted sets. It is known for its exceptional performance and low latency. Redis is often used for caching, real-time analytics, session management, and pub/sub messaging.
  • Amazon DynamoDB.
  • CouchBase Server.
  • Apache Cassandra: Distributed columnar database designed to handle large amounts of data across multiple commodity servers. It offers hight availability, fault tolerance, and linear scalability,
  • Apache HBase.
  • Riak.

How web applications utilize SQL queries

The following code contains a PHP example of a connection to a MySQL database and the execution of a SQL query.

$dbhostname='1.2.3.4';
$dbuser='username';
$dbpassword='password';
$dbname='database';

$connection = mysqli_connect($dbhostname, $dbuser, $dbpassword, $dbname);
$query = "SELECT Name, Description FROM Products WHERE ID='3' UNION SELECT Username, Password FROM Accounts;";

$results = mysqli_query($connection, $query);
display_results($results);

Most of the times queries are not static; they are indeed dynamically built by using user' inputs. Here you can find a vulnerable dynamic query example:

$id = $_GET['id'];

$connection = mysqli_connect($dbhostname, $dbuser, $dbpassword, $dbname);
$query = "SELECT Name, Description FROM Products WHERE ID='$id';";

$results = mysqli_query($connection, $query);
display_results($results);

If an attacker crafts an $id value which can actually change the query, like:

' OR 'a'='a

Then the query becomes:

SELECT Name, Description FROM Products WHERE ID='' OR 'a'='a';

This tells the database to select the items by checking two conditions:

  • The id must be empty (id='') OR an always true condition ('a'='a’)
  • While the first condition is not met, the SQL engine will consider the second condition of the OR. This second condition is crafted as an always true condition.

In other words, this tells the database to select all the items in the Products table.

Common injectable fields

SQL injection vulnerabilities can exist in various input fields within an application.

  • Login forms: The username and password fields in a login form are common targets for SQL injection attacks.
  • Search boxes: Input fields used for searching within an application are potential targets for SQL injection. If the search query is directly incorporated into a SQL statement without proper validation, an attacker can inject malicious SQL code to manipulate the query and potentially access unauthorized data.
  • URL parameters: Web applications often use URL parameters to pass data between pages. If the application uses these parameters directly in constructing SQL queries without proper validation and sanitization, it can be susceptible to SQL injection attacks.
  • Form fields: Any input fields in forms, such as registration forms, contact forms, or comment fields, can be vulnerable to SQL injection if the input is not properly validated and sanitized before being used in SQL queries.
  • Hidden fields: Hidden fields in HTML forms can also be susceptible to SQL injection attacks if the data from these fields is directly incorporated into SQL queries without proper validation.
  • Cookies: In some cases, cookies containing user data or session information may be used in SQL queries. If the application does not validate or sanitize the cookie data properly, it can lead to SQL injection vulnerabilities.

Tools and payloads

Last update: 2024-11-03
Created: January 3, 2023 01:16:43