Application Security Security Bloggers Network

Home » Security Bloggers Network » How to Prevent SQL Injection Attacks?

How to Prevent SQL Injection Attacks?

by Venkatesh Sundar on February 24, 2023

Are you aware of the increasing threat of SQL injection vulnerabilities?

In Q4 2022, AppTrana stopped 1,111,548 of these attacks. With over a million SQL injection attacks blocked in just three months, it’s clear that web applications are under siege.

Sponsorships Available

How to stay ahead of the game and protect your business now?

Here is a guide to understanding this OWASP top 10 vulnerability and how to prevent SQL injection attacks.

What are Database and SQL?

A database is a set of described tables from which data can be accessed or stored. A database application requires a communication medium between the front end and the database. This is where SQL comes into the picture.

What does SQL Injection stand for?

Developed in the 1970s, Structured Query Language (SQL) is a language for accessing and manipulating data from the database. An application can communicate with the database using SQL statements.

With the use of SQL statements, the application can perform some standard SQL commands such as “SELECT,” “UPDATE,” “INSERT,” “DELETE,” “CREATE,” and “DROP.”

Attackers use the input fields in web applications to run arbitrary queries (injection) on the server. Hence, the attack process is called SQL Injection or SQLi attack.

They gain access to information that is not intended to be displayed. These injection attacks are categorized as ‘high impact severity’ by OWASP Top 10.

What causes SQLi?

The root cause of SQLi is usually a failure to properly sanitize user input.

When an application allows user input in SQL statements without proper validation, an attacker can craft SQL queries with additional commands. It results in unauthorized access, modification of data, or other malicious actions.

For example, an attacker could use SQL injection to bypass authentication mechanisms by crafting an SQL query that always returns true. It allows them to log in without a valid username and password.

Or they could use SQL injection to modify the data stored in the database, steal sensitive data, or execute other malicious actions.

How Does SQL Injection Work?

Here’s an example to illustrate how SQL Injection works:

How does SQLi attack works

Let’s say we have a website with a SQL database to store user information. The website has a login form where users enter their usernames and password. The website’s code might look something like this:

$username = $_POST[‘username’];
$password = $_POST[‘password’];
$sql = “SELECT * FROM users WHERE username=’$username’ AND password=’$password’”;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// User has successfully logged in
} else {
// Invalid username or password
}

In this code, the user’s input is directly inserted into the SQL statement. If a user enters a username like admin’–, the SQL statement becomes:

SELECT * FROM users WHERE username=’admin’–‘ AND password=”

The double dash (–) is a comment character in SQL, which means everything after it is ignored. This allows the attacker to bypass the password check and log in as the administrator.

Attacks try different variations of SQLi using common SQL injection commands to see which commands get executed by the database.

Based on this, they keep executing SQLi attacks to access the required information. They may stop after gathering what they need or keep coming back to do their bidding until these vulnerabilities exist.

What are the Types of SQL Injection Attacks?

A big hurdle to SQL injection prevention and mitigation is that it spans various techniques. Here is a breakdown of different SQL injection types:

Types of SQL Injection attacks

Error-Based SQL Injection

An error-based SQL injection is an attack that a malicious user uses with malicious SQL queries to get some kind of error or confirmation that there was a problem with their input.

It is normally about a rule in the database’s syntax. Then they can use this information to extract information from the database, such as passwords or personal bank accounts.

How to detect Error based SQL injection?

There are so many types of SQL commands that an attacker could execute, and they’re all quite unpredictable.

Here are some examples: single quotes, double quotes, or SQL operators like AND, OR, and NOT

For example:

http://demo.testfire.net/index.php?title=1’

And the error message greets the attacker: “You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the right syntax to use near ‘‘VALUE’’.

The error message gives him vital information like:

DB used as MySQL
Error in the syntax is a double quote
The place of the error caused is at the end of the parameter

Union-based SQL Injection

In this type of SQL Injection, attackers try to exploit the vulnerability with the help of the “UNION” operator.

The UNION operator is used for combining 2 tables or performing 2 select queries simultaneously. In union operators, they remove duplicate rows or columns, which we try to execute simultaneously.

Union-based SQL Injection Example

Let’s assume we have a web application that takes a user’s input and builds an SQL query like this:

SELECT name, email, phone FROM users WHERE name = ‘[user_input]’

The user input is not properly sanitized, so an attacker could inject their own SQL code. For example, they could enter the following value as their name:

‘ UNION SELECT password, NULL, NULL FROM users —

This would result in the following SQL query being executed:

SELECT name, email, phone FROM users WHERE name = ” UNION SELECT password, NULL, NULL FROM users –‘

The — at the end of the injected string is a comment symbol, which comments out the rest of the original query. So, the resulting query would be equivalent to:

SELECT name, email, phone FROM users WHERE name = ”
UNION SELECT password, NULL, NULL FROM users

This query would return a table with the user’s name, email, and phone number and a table with all the passwords in the user table. The attacker could then use this information to further compromise the system.

Blind SQL Injection

Blind SQLi is a type of SQL injection attack where an attacker exploits a vulnerability in a web application to extract sensitive information from a database without being able to directly see the data.

This type of attack is known as “blind” because the attacker cannot see the actual database content. But instead relies on the application’s response to infer the information.

There are two main types of blind SQL injection attacks:

1. Boolean-based SQLi
2. Time-based SQLi

Boolean-based SQLi

In this type of SQL Injection attack, the attacker sends a series of SQL queries that evaluate either true or false, depending on whether the injected code was executed successfully.

The attacker can then use the application’s response to infer information about the database by constructing complex queries that probe for specific information.

Boolean-based SQLi Example: Deleting a user database using Boolean-based SQLi

How it works: A common online shop’s SQL database query could be like this:

SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = ItemNumber

So, a product URL on the online store could be

http://www.exampleshop.com/items/items.asp?itemid=999 or 1=1.

The SQL query could be

SELECT ItemName, ItemDescription FROM Items WHERE ItemNumber = 999 OR 1=1

One is always equal to one. It’s just a basic mathematical fact that holds true no matter where you are. SQL queries return all product names and descriptions in the database, even those you lack permission to access.

Time-based SQLi

This attack involves injecting a query that is designed to cause a delay in the application’s response. By measuring the time, it takes for the application to respond to the query, the attacker can determine whether the query was successful.

This technique is helpful when the attacker has no answer (error/output) from the application because the input validation has been sanitized.

Time-based SQL injection example

For example, let’s say there is a login form on a web application that uses a SQL query to check whether a user’s credentials are valid. The query might look something like this:

SELECT * FROM users WHERE username = ‘admin’ AND password = ‘password123’

To perform a blind SQLi attack, an attacker could inject a query like this:

SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END;

This query will cause the application to sleep for 10 seconds if the condition “1=1” is true. The attacker can determine whether the condition was true or false by measuring the time it takes for the application to respond to this query.

If the response takes 10 seconds, the attacker knows that the condition was true and that the application is vulnerable to blind SQLi. If the response is immediate, the attacker knows the condition was false.

Once the attacker has confirmed that blind SQLi is possible, they can start injecting more complex queries to extract sensitive information from the database.

How are SQL Injections Bot-Driven?

SQL injection attacks can be bot-driven in a few different ways. Bots can be programmed to automatically scan websites for vulnerabilities and then attempt to exploit them using SQL injection techniques. Here are some examples:

Automated scanning: Bots can be programmed to scan many websites automatically, looking for vulnerabilities that could be exploited with SQL injection attacks. Once a vulnerable website is identified, the bot can then attempt to exploit the vulnerability using a pre-defined set of SQL injection payloads.

Brute-force attacks: Bots can also be used to conduct brute-force attacks on web forms, such as login pages or search boxes, to try to inject SQL code into the application. The bot can use different techniques, such as a dictionary attack or a fuzzing attack, to generate different payloads and submit them to the application to find the vulnerable entry point.

Automated exploitation: Once a vulnerable entry point is identified, bots can be used to automate injecting malicious SQL code and extracting data from the database.

The bot can use different techniques, such as blind SQL injection or error-based SQL injection, to extract sensitive data from the database.

Overall, bots can be used to automate the process of identifying and exploiting SQL injection vulnerabilities on a large scale. It makes it easier for attackers to compromise many websites and extract sensitive data. That’s why it’s important to implement proper security measures to prevent SQL injection attacks.

What are the Impacts of Injection Attacks?

The impact of SQL injection attacks can be far-reaching and may vary depending on the target. Data integrity and confidentiality breach are the most common consequences.

When the database executes the malicious code, it potentially allows the attacker to:

Steal sensitive data: An attacker can use SQL injection to extract sensitive data from a database, such as usernames, passwords, credit card numbers, or personal information.
Modify or delete data: An attacker can use SQL injection to modify or delete data in a database, which can cause data loss or damage.
Take control of a system: An attacker can use SQL injection to gain administrative access, allowing them to perform further attacks, install malware, or use the system for malicious purposes.

The potential cost of an SQL injection attack is even higher. Financial loss can come in direct financial losses, such as the cost of restoring systems and data after an attack. Indirect losses include lost revenue due to a disruption of business operations.

In addition, businesses may also face financial penalties or legal action due to data breaches caused by injection attacks. This can particularly damage businesses that handle sensitive information, such as financial institutions or healthcare providers.

Furthermore, reputation damage can be long-term and difficult to recover from, negatively affecting the company’s future growth and profitability.

Common SQL injection attacks result in loss of knowledge or denial of access. However, over the years, hackers coupled these attacks with insufficient authentication, DNS hijacking, XSS, and DDoS attacks to cause heavy financial damage and absolute host takeover.

Downtime can lead to lost revenue and customer frustration, further damaging the business’s reputation.

Most Notorious SQLi Attacks in History

The following are some of the most famous SQL attacks in recent years that every company must be aware of:

Heartland Payment Systems: In March 2010, Albert Gonzalez was sentenced to 20 years. He installed his code into the credit card server of Heartland Payment Systems and stole 130 million credit card numbers. The attack cost was around $12 million for the company.

The Sony PlayStation Network Hack: In 2011, the Sony PlayStation Network (PSN) was hacked, resulting in the loss of personal information for 77 million users. The hack was reportedly the result of a SQL injection attack. The attackers were able to gain access to sensitive information such as users’ names, addresses, and credit card numbers.

Yahoo Hack: In July 2012, 453,000 email addresses and passwords of Yahoo Voices users were leaked. The credentials were stored in an unencrypted way. Hackers have stolen the data by executing a SQL Injection attack.

The Target Data Breach: In 2013, the Target Corporation was the victim of a massive data breach that affected 40 million customers. Experts claim that the server fell to SQL injection attacks.

Drupal SQL Injection: In October 2014, Drupal declared its high vulnerability against the attack. Lack of user input sanitization resulted in SQL injection vulnerability. Drupal core versions ranging from 7.0 to 7.31 were vulnerable.

Kaseye ransomware attack: In July 2021, a notorious group called REvil affected over 1500 businesses managed by Kaseya. Hacker remotely exploited the SQL vulnerability of the Kaseya VSA servers.

WooCommerce unauthenticated SQL Injection: In July 2021, WooCommerce disclosed that several of its feature plug-ins and software versions were vulnerable to SQL injections; they noticed several security attacks occurring during that time.

How to Prevent SQL Injection Attacks?

Preventing or mitigating SQL injection attacks is about ensuring that none of the fields are vulnerable to invalid inputs and application execution. It is manually impossible to check every page and every application on the website. Especially when updates are frequent, and user-friendliness is the top priority.

Nonetheless, security analysts and developers recommend that a number of the subsequent points guarantee your database is well protected inside the server.

Continuous Scanning and Penetration Testing

Continuous web application scanning involves regularly running automated vulnerability scanning tools to identify and assess any potential vulnerabilities in a system.

Penetration testing, also known as “pen testing,” involves simulating an attack on a system to identify and exploit vulnerabilities.

By regularly performing these tests, organizations can quickly identify and address any vulnerabilities before attackers can exploit them.

Developers can follow best practice guidelines and use prepared statements with parameterized inputs to prevent SQL injection attacks.

Restrict Privileges

Restricting privileges is another important measure for preventing impact of SQL injection attacks. This involves limiting the access and permissions of users and applications to the database and its components.

Doing so limits the potential attack surface and makes it more difficult for attackers to exploit vulnerabilities.

One way to restrict privileges is to use the principle of least privilege. It involves giving users and applications only the minimum access and permissions necessary to perform their tasks.

This can be done by creating separate user accounts for different roles and responsibilities. And also by limiting the permissions of those accounts to specific database objects and operations.

Another way to restrict privileges is by using a security framework such as Role-based Access Control (RBAC). It allows you to define user roles and assign specific permissions.

In short, restricting privileges can reduce the potential attack surface and make it more difficult for attackers to exploit vulnerabilities.

Use Query Parameters

Dynamic queries create a lot of trouble for security professionals. They must deal with variable vulnerabilities in each application, which only get severer with updates and changes. It is recommended to prepare parameterized queries.

A query parameter is a placeholder in a SQL query filled with a value from user input or another source. Using query parameters, you can separate user input from the SQL query, making it much more difficult for an attacker to inject malicious code.

Here is an example of a SQL query using query parameters:

$username = $_POST[‘username’];
$password = $_POST[‘password’];
$query = “SELECT * FROM users WHERE username = :username AND password = :password”;
$stmt = $pdo->prepare ($query);
$stmt = bindParam(‘:username’, $username);
$stmt->bindParam(‘:password’, $password);
$stmt->execute();

In this example, the SQL query is separated from the user input using query parameters :username and :password.

The values for these parameters are then passed separately to the bindParam() function, which binds the values to the parameters before the query is executed.

These queries are simple, easy to write, and only pass when each parameter in SQL code is clearly defined. This way, your info is supplied with weapons to differentiate between code and information inputs.

Use Web Application Firewall

Most organizations fail the problems like outdated code, scarcity of resources to test and make changes, no knowledge of application security, and frequent updates in the application. For these, web application protection is the best solution.

A managed web application firewall (WAF) can be deployed for immediate mitigation of such attacks. It can detect and block malicious traffic before it reaches the web application. A WAF can be configured to detect and block known SQL injection payloads.

A WAF can analyze the behavior of incoming traffic and look for patterns that indicate a SQL injection attack. For example, it could look for repeated attempts to submit SQL code or unusual activity in a short period of time.

It contains custom policies to block traffic not in compliance with the application’s security policies. This way, you do not have to manually look for loopholes and mend problems afterward.

It is common to introduce injection issues even with minor code changes. Because such changes may not be subjected to a fully-fledged security review process in the development cycle.

When fixing the code of detected vulnerabilities cannot be done promptly, a WAF can be used for virtual patching after deployment.

Prevent SQL Injection with AppTrana

AppTrana WAF help protects against SQL injection attacks by analyzing the incoming traffic to your web application and identifying malicious SQL code. It can then block the malicious traffic before it reaches your application, preventing the attack from occurring.

AppTrana uses a combination of machine learning, rule-based, and behavioral-based detection techniques to identify and block SQL injection attacks.

Additionally, AppTrana provides real-time visibility and reporting on all incoming traffic, so you can see what is happening on your application and quickly respond to potential threats.

You can start by determining if your website has SQL Injection risks with AppTrana Free Trial.

Stay tuned for more relevant and interesting security updates. Follow Indusface on Facebook, Twitter, and LinkedIn