What is SQL Injection (SQLi) and how do you prevent an attack?

12 Mar SQL Injection

In this post, we’ll cover SQL injection, its definition, types of attacks, and how to detect and prevent them.

Table of Contents

What is SQL Injection?

SQL injection, commonly referred to as SQLi, is a common cyber-attack, which involves adding malicious SQL code to manipulate backend databases and gain access to information. This information may include sensitive corporate data, user lists, or confidential customer information.

The impact of SQL injection attacks on a business can be substantial. Successful attacks may result in unauthorized access to user lists, the complete deletion of tables, and, in some instances, granting attackers administrative privileges to a database, which can be highly detrimental to a company’s operations.

To accurately estimate the possible ramifications of an SQL injection attack, it is essential to consider the loss of customer trust that may result if private data, such as phone numbers, addresses, and credit card details, were to be compromised.

Although any SQL database can be targeted using this method, websites are the most frequent targets.

What are SQL Queries?

SQL queries are commands used to access and manipulate databases. They can retrieve, update or delete records from the database. For instance, a database administrator could use a query like this to retrieve all the customer names and addresses from a customer database:

SELECT Name, Address
FROM Customer

This would return a list of all customer names and addresses in the database. Similarly, an online store might use a query like this to retrieve product information based on user input:

SELECT ProductName, ProductDescription
FROM Product
WHERE ProductID = '12345'

In this case, the user input would be the product ID ‘12345’, and the query would retrieve the name and description of that product from the database. It’s important to ensure that user input is properly sanitized to prevent SQL injection attacks, which can manipulate the query to retrieve unauthorized information.

Types of SQL Injection Attacks

SQL Injection attacks can be classified into three types: In-band SQLi (Classic), Inferential SQLi (Blind), and Out-of-band SQLi. These types of SQL injections are based on the methods attackers use to manipulate the backend data and the potential damage they can cause.

In-band SQLi

This type of SQLi is when attackers use the same communication channel to launch attacks and obtain results. In-band SQLi is one of the most common types of SQLi due to its simplicity and effectiveness. There are two subtypes of In-band SQLi:

Error-based SQLi: In this method, attackers perform actions that cause the database to produce error messages. These error messages can potentially provide information about the database structure that the attacker can leverage.
Union-based SQLi: This method exploits the UNION SQL operator to fuse multiple select statements generated by the database to get a single HTTP response. This response may contain data that can be used by the attacker.

Inferential (Blind) SQLi

The attacker sends data payloads to the server and observes the response and behavior of the server to learn more about its structure. This type of SQLi is called blind because the attacker cannot see the information about the attack in-band, as the data is not transferred from the website database to the attacker.

Blind SQL injections rely on the server’s response and behavioral patterns, making them slower to execute but can still be as harmful. Blind SQL injections can be classified into the following types:

Boolean-based: In this method, the attacker sends a SQL query to the database to return a result. The result varies depending on whether the query is true or false. Based on the result, the information within the HTTP response will modify or stay unchanged, and the attacker can work out if the message generated a true or false result.
Time-based: In this method, the attacker sends a SQL query to the database, which makes it wait seconds before it can react. The attacker can determine if the query is true or false based on the time it takes the database to respond. The HTTP response will be generated instantly or after a waiting period. The attacker can then work out if the message they used returned true or false without relying on data from the database.

Out-of-band SQLi

This type of attack can only be performed when certain features are enabled on the database server used by the web application.

Out-of-band SQLi is an alternative to the in-band and inferential SQLi techniques. The attacker must use a different communication channel to launch the attack and obtain information, or the server needs to be faster or more stable to perform these actions. This attack relies on the server’s ability to create DNS or HTTP requests to transfer data to the attacker.

Examples of SQL Injection Attacks

SQL injection attacks occur when attackers exploit non-validated input vulnerabilities in a database by manipulating a standard SQL query using malicious code.

There are several ways to execute this attack vector. Below are a couple of examples to give you a general idea of how SQLi works.

Example 1

Consider a website named “www.bookstore.com”. This website has a search bar where users can search for books by title, author, or ISBN. The website runs a SQL query to fetch the books from the database based on the user’s input.

Here is a sample SQL query that the website might use:

SELECT title, author, price FROM books WHERE title = ''

An attacker can manipulate the user input to inject malicious SQL code into the query. For example, if the user searches for “Harry Potter”, the attacker can enter the following input:

' OR 1=1--

This input will modify the original query to become:

SELECT title, author, price FROM books WHERE title = '' OR 1=1--'

The double hyphen at the end of the input is to comment out the rest of the query.

This modified query will return all the books in the database, as the statement 1=1 is always true, allowing the attacker to see all the books in the bookstore.

Example 2

Consider a website named “www.banking.com”. This website has a login page where users can enter their credentials to access their account. The website runs a SQL query to validate the user’s credentials.

Here is a sample SQL query that the website might use:

SELECT * FROM users WHERE username = '<user_input>' AND password = '<password_input>'

An attacker can manipulate the user input to inject malicious SQL code into the query. For example, if the user enters their username and password, the attacker can enter the following input as the username:

' OR 1=1--

This input will modify the original query to become:

SELECT * FROM users WHERE username = '' OR 1=1--' AND password = '<password_input>'

This modified query will return all the users in the database, as the statement 1=1 is always true, allowing the attacker to bypass the login page and access any account in the banking system.

How to Test for SQL Injection

Testing for SQL injection vulnerabilities involves sending malicious inputs to a web application and observing how it handles them. Here are some steps to follow when testing for SQL injection:

Identify the input fields: Identify all the input fields on the application that interact with the database, such as login forms, search boxes, and registration forms.
Submit valid input: Submit valid input to ensure the application functions correctly. This will give you a baseline for comparison when you submit malicious input.
Submit malicious input: Use special characters, quotes, and SQL commands as input to see how the application responds. For example, you can try entering a single quote (‘) or a double quote (“) to test if the application is vulnerable to SQL injection.
Observe the response: Observe how the application responds to the malicious input. If the application returns an error message, it may be vulnerable to SQL injection.
Test for blind SQL injection: Test for blind SQL injection by submitting input that does not generate an error message but causes the application to behave differently. For example, you can submit input that causes a delay in the response time or changes the application’s behavior in some other way.
Use automated tools: Use automated tools such as SQLmap, Havij, or Nessus to test for SQL injection vulnerabilities. These tools can automatically submit malicious input and detect vulnerabilities in the application.
Verify vulnerabilities: Once a vulnerability is detected, verify it by manually testing the input field to ensure that the vulnerability is real and exploitable.

It is important to note that testing for SQL injection vulnerabilities should only be done on applications you have permission to test. Unauthorized testing of applications can be illegal and can result in legal consequences.

How to Prevent SQL Injection Attacks

SQL injection attacks can be prevented by following best practices for coding and database management. This includes validating user input, using parameterized queries, limiting database permissions, and keeping software and security measures up to date.

Validating user input: One of the most important ways to prevent SQL injection attacks is by validating user input. This means checking that the data entered by the user matches the expected format, such as only allowing alphanumeric characters in a username field. It’s also important to sanitize input by removing potentially harmful characters or code.
Using parameterized queries: Another key prevention measure is using precompiled SQL statements that allow input parameters to be passed in as variables. This helps to prevent attackers from injecting malicious code into the SQL query, as the parameters are treated as data rather than executable code.
Limiting database permissions: Limiting permissions to the database is also critical to preventing SQL injection attacks. By only granting the minimum permissions necessary for each user or application, you can reduce the risk of an attacker gaining access to sensitive data or being able to execute malicious code.
Keeping software and security measures up to date: Finally, it’s important to keep both software and security measures up to date. This includes regularly updating your database management system, web server, and other software or applications that interact with the database. It’s also important to keep your security measures, such as firewalls and intrusion detection systems, up to date to ensure they can detect and prevent new types of attacks.

It’s important to be proactive in preventing SQL injection attacks, as they can be difficult and costly to fix after the fact.

SQL Injection