Cyber attacks are a common and constant threat. This paper is the first in a series on cyber security.
The goal is to enable software engineers to understand the main threats and how to address them. Most exploits are based on basic bugs.
According to the OWASP top 10 report [1], injection remains among the top three threats. However, it is important to note that the report covers more than just SQL injection [2]. It also includes:
- CWE-79: Cross-site scripting
- CWE-89: SQL injection
- CWE-73: External filename or path control
Here we’ll focus on SQL injections, their types, how to prevent them, and some real-world examples.
Content
- What is SQL injection?
- Basic example
- Different kind
3.1 Internal SQLi
3.2 Inferential SQLi
3.3 Out-of-band SQLi - Prevention
4.1 Prevention in the interface
4.2 Prevention in the Backend - Real life SQLi examples
5.1 Sony
5.2 Tesla
5.3 Cisco
5.4 Fortnite - Conclusion
- Sources
1. What is SQL injection?
SQL Injection (SQLi) is a code injection technique that exploits a security vulnerability that occurs in the database layer of an application.
The vulnerability exists when user input is either improperly filtered for string literal output characters embedded in SQL commands or when user input is not strictly typed and is therefore executed unexpectedly.
This allows an attacker to manipulate SQL queries, allowing unauthorized access, modification and deletion of data in the database. This can lead to significant breaches of confidentiality, integrity and availability, ranging from unauthorized viewing of data to complete database compromise.
2. Basic example
Consider a simple web application that uses a SQL database to store user information. Users log into the application by entering a username and password, which the application verifies by running an SQL query:
SELECT * FROM users WHERE username="[username]" AND password = '[password]';
An attacker could exploit this by entering a username that always returns true, such as “bash”.
' OR '1'='1
If an application directly concatenates this input into an SQL query without proper remediation, the resulting query becomes:
SELECT * FROM users WHERE username="" OR '1'='1' AND password = '[password]';
From '1'='1' is always true, the query returns all rows from users table, effectively bypassing the authentication mechanism.
This is a very simple example that illustrates the basic idea behind SQL injection.
3. Different types
There are three main types of SQLi: In-band, Inferential and Out-of-band.
3.1. Inside the band
This type of SQL Injection uses the same communication channel to launch the attack and collect the results [3].
Tautologies
To cheat a conditional return and gain access to unauthorized data, you can use a statement that is always true.
' OR '1'='1
Union Queries
The goal is to use it UNION keyword to add a new query to retrieve additional data.
SELECT title, author FROM books WHERE title LIKE '%[user_input]%';
Insert query:
' UNION SELECT username, password FROM users
Based on errors
An attacker tries to gain information about the structure of the database by exploiting error messages. This is a form of malicious reverse engineering.
3.2 Conclusion (Blind-SQLi)
This attack occurs when an attacker sends data to a server and observes the server’s response or behavior to learn its structure. The attack is called “blind” because the attacker cannot directly see the result of the executed query and there is no data exchange [3].
Based on Boolean
The attacker sends a specific query to get a Boolean response. Based on these responses, the attacker tries to enumerate the data structure.
Based on time
This is a blind attack that delays query execution in order to infer the database structure from the response time.
3.3. Outside the band
This type of attack is used when the attacker cannot use the same channel to launch the attack and gather information, or when the server is too slow or unstable. It relies on the server’s ability to make DNS or HTTP requests to transmit data to the attacker [3].
4. Prevention
In modern web applications, injection can occur at many different levels and will be handled differently depending on the language, framework, or transport protocol used at each level.
Your user interface and APIs are the most exposed parts of your web application. They are often available on the Internet, and even if they are protected by authentication protocols and authorization levels, they are still the most vulnerable.
4.1 Prevention in the interface
In modern web development, frameworks like Angular provide built-in features to prevent SQL injection, primarily by separating code from data. This separation ensures that user input is handled in a way that minimizes the risk of unintentionally executing malicious SQL code [4].
Example: Angular Data Binding
Angular uses data binding techniques that automatically handle coding and handling user input, preventing executable code from being injected into the application. Let’s consider a simple form entry related to a model property:
<input [(ngModel)]="userInput" type="text">
// Component code
userInput: string;
Corner treats userInputas text rather than executable code, which allows for efficient input remediation.
Example: HTTPClient and parameterized APIs
When making an HTTP request, Angular’s HttpClient the service automatically avoids query parameters, reducing the risk of SQL Injection attacks originating from the front end. Consider the following example where user input is sent to a server-side API:
searchProducts(searchTerm: string): Observable<Product[]>
const params = new HttpParams().set('query', searchTerm);
return this.httpClient.get<Product[]>('/api/products/search', params );
In this case, HttpParams ensures that searchTerm is correctly coded, preventing any attempt to inject SQL code through the front end.
4.2 Background prevention
For background prevention, frameworks such as Spring and Hibernate provide robust mechanisms to control API input, improving security against SQL injection [4].
Input validation
The Spring approach is focused on usability @RequestParam or @PathVariable notes for strict control of input types and the use of Spring Security for comprehensive input validation.
Spring Data JPA repositories
Spring Data JPA repositories abstract away the complexity of direct database interactions, using Hibernate to prevent SQL Injection. Here’s an example of a repository method that finds a user by username:
public interface UserRepository extends JpaRepository<User, Long>
User findByUsername(String username);
Spring Data JPA automatically translates this method into a SQL query that uses prepared statements, ensuring that username is treated as a parameter and not as part of the SQL statement itself.
Hibernation
Hibernate, on the other hand, emphasizes the use of HQL (Hibernate Query Language) with named parameters to prevent user input from being directly included in queries, thus protecting against injection attacks [4].
Here’s a simplified example using HQL with named parameters:
// Unsafe HQL Statement
String hql = "FROM Inventory WHERE productId = '" + userInput + "'";
// Safe HQL using named parameters
String safeHql = "FROM Inventory WHERE productId = :productId";
Query query = session.createQuery(safeHql);
query.setParameter("productId", userInput);
This approach ensures that user input is handled securely, effectively preventing SQL injection by separating code from data within the query execution process.
5. SQLi examples from real life
5.1 Sony Pictures (2011)
In 2011, Sony Pictures faced a significant cybersecurity breach, with an attack compromising approximately 77 million PlayStation Network accounts and exposing users’ personal information. As reported by The Washington Post, this incident resulted in financial losses of about 170 million dollars for Sony. This episode not only demonstrated the vulnerability of advanced digital networks to cyber threats such as SQL Injection, but also highlighted the urgent need for strict cyber security measures across the digital entertainment sector to protect user data. [5].
5.2 Tesla (2014)
In 2014, Tesla faced a security breach when researchers exploited a SQL Injection vulnerability on its website, gaining administrative rights and accessing user data. This incident highlighted the critical need for strict web application security measures [6].
5.3 Cisco (2018)
Cisco’s Prime License Manager was compromised in 2018 due to a SQL injection vulnerability, which allowed attackers to gain shell access to systems. Cisco quickly addressed the issue, highlighting the ongoing challenge of protecting software against SQL injection attacks [7].
5.4 Fortnite (2019)
In 2019, Fortnite experienced a significant security breach. This incident involved a vulnerability within one of Epic Games’ subdomains, which attackers exploited to perform SQL injection attacks. This allowed unauthorized access to user accounts and their personal information. The breach highlighted the importance of robust cybersecurity practices and the constant vigilance required to protect digital assets and user data in the gaming industry [8].
6. Conclusion
SQL Injection (SQLi) represents a significant vulnerability that exposes web applications to a variety of attacks, potentially leading to unauthorized data access or manipulation.
This detailed research identified multiple types of SQLi, including In-band, Inferential (Blind SQLi), and Out-of-band attacks, each with unique characteristics and exploitation techniques.
To combat these vulnerabilities, we have introduced a number of preventative measures, using modern frameworks and best practices such as input validation, parameterized queries, and the use of prepared statements.
These strategies are critical for developers to implement, ensuring the security and integrity of their applications.
7. Sources
[1] : OWASP Top Ten Project: OWASP
[2] : Injection Disadvantages — OWASP Top 10 A03:2021: OWASP Injection
[3] : Academic Research on SQLi: JISRC, Sifisheriessciences
[4] : SQL Injection Prevention Cheat Sheet: OWASP Cheat Sheet
[5] : 2014 Sony Pictures Hack: Wikipedia
[6] : Tesla Motors Blind SQL Injection: Bitquark
[7] : Cisco patches Prime License Manager SQL Injection Vulnerability: SC Magazine
[8] : Fortnite account hacked via SQL injection: Hacker News